Uploading an Excel sheet with a specified sheet name using oledb

How to upload an excel sheet using asp.net and know the structure of the columns in the sheet so that it w开发者_开发知识库ould be helpful in using sqlbulkcopy to upload to a table with similar structure.

any answers would be appreciated.

Thanks in advance.

I assume you know how to do the uploading part, so I concentrate on the Excel part.

There are a bunch of 3rd-Party tools to read Excel files in .NET, which in my experience is way more flexible than using the capabilities that .NET has out-of-the-box. However here's one way you can do it:

DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

using (DbConnection connection = factory.CreateConnection())
{
    connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
    connection.Open();

    using (DbCommand command = connection.CreateCommand())
    {
        command.CommandText = "SELECT * FROM [Sheet1$]";

        using (DbDataReader dr = command.ExecuteReader())
        {
            while (dr.Read())
            {
                /* read data here */
            }
        }
    }
}

Keep in mind:

1. The Jet OLE DB provider reads a registry key to determine how many rows are to be read to guess the type of the source column. The registry setting is: HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows. By default, the value for this key is 8. Hence, the provider scans the first 8 rows of the source data to determine the data types for the columns (see http://support.microsoft.com/kb/281517) The valid range of values for the TypeGuessRows key is 0 to 16. However, if the value is 0, the number of source rows scanned is 16384.

2. On 64-bit systems the Microsoft.Jet.OLEDB.4.0 driver is currently not supported.

3. For more info on the parameters used in the connection string see here: http://www.connectionstrings.com/excel

4. "HDR=Yes" in the connection string indicates that the provider will not include the first row of the cell range (which may be a header row) in the RecordSet. So if the header row gives you information that you need to build the sqlbulkcopy commands you should set it to "HDR=No".