Writing to excel using OleDb

Question

I am attempting to export rows of data from sql to excel but my Insert Command seems to fail every time. I have spent a good deal of time trying to create this but I have finally run up against the wall.

The excel document is one that is generated by the IRS and we are not aloud to modify anything above row 16. Row 16 is the header row, and everything below that needs to be the data from sql. The header names all have spaces in them, and that seems to be where I am running into trouble.

Starting at row 16 the column names are: Attendee First Name, Attendee Last Name, Attendee PTIN, Program Number, CE Hours Awarded Program, Completion Date

This is how I am attempting to write to excel

private void GenerateReport()
{
    FileInfo xlsFileInfo = new FileInfo(Server.MapPath(CE_REPORTS_PATH + CE_PTIN_TEMPLATE + EXTENSION));

    string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes'", xlsFileInfo.FullName);

    //create connection
    OleDbConnection oleDBConnection = new OleDbConnection(connectionString);
    oleDBConnection.Open();

    //create the adapter with the select to get 
    OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$A16:F16]", oleDBConnection);

    // Create the dataset and fill it by using the adapter.
    DataTable dataTable = new DataTable();
    adapter.FillSchema(dataTable, SchemaType.Source);
    adapter.Fill(dataTable);

    string[] colNames = new string[dataTable.Columns.Count];
    string[] colParms = new string[dataTable.Columns.Count];

    for (int i = 0; i < dataTable.Columns.Count; i++)
    {
        colNames[i] = String.Format("[{0}]", dataTable.Columns[i].ColumnName);
        colParms[i] = "?";
    }

    // Create Insert Command
    adapter.InsertCommand = new OleDbCommand(String.Format("INSERT INTO [Sheet1$] ({0}) values ({1})", string.Join(",", colNames), string.Join(",", colParms)), oleDBConnection);

    // Create Paramaters
    for (int i = 0; i < dataTable.Columns.Count; i++)
    {
        OleDbParameter param = new OleDbParameter(String.Format("@[{0}]", dataTable.Columns[i].ColumnName), OleDbType.Char, 255, dataTable.Columns[i].ColumnName);
        adapter.InsertCommand.Parameters.Add(param);
    }

    // create a new row
    DataRow newCERecord = dataTable.NewRow();

    // populate row with test data
    for (int i = 0; i < dataTable.Columns.Count; i++)
    {
        newCERecord[i] = "new Data";
    }
    dataTable.Rows.Add(newCERecord);

    // Call update on the adapter to save all the changes to the dataset
    adapter.Update(dataTable);

    oleDBConnection.Close();     
}

The error I get happens when adapter.Update(dataTable) is called and is as follows

$exception  {"The INSERT INTO statement contains the following unknown field name: 'Attendee First Name'. Make sure you have typed the name correctly, and try the operation again."}   System.Exception {System.Data.OleDb.OleDbException}

This is frustrating because I pull each field directly from the column name as gotten by colNames[i] = String.Format("[{0}]", dataTable.Columns[i].ColumnName). I discovered I needed the [] to account for the spaces in the column names, but at this point I am not sure what the problem is. When I look at the excel file everything seems correct to me.

Answer 1

I actually found a Microsoft article for you that has the entire code done - you can likely copy & paste whichever solution you like most. Here's the link:

http://support.microsoft.com/kb/306023

It seems like the one with CopyRecordset is your easiest approach, although they do explain the one I mentioned (using a tab-delimited file).

Edit: Here's my original answer for the sake of completeness. See the link above instead for more details and for a possible better solution.

This is not an answer to your question but a suggestion to change your approach (if you can). Excel tends to be very slow when adding data through COM calls and I assume OleDB uses COM internally. In my experience the fastest (and coincidentally the least painful way) to output data to Excel was to generate a tab-separated text file with all the data and then just import the file into Excel and use COM interop to perform any formatting on the sheet. When I generated Excel reports this way, most of my reports used to be generated almost 100x faster than using the Excel COM object model. (I don't know if this would be the same for OleDB calls, since I've never used OleDB with Excel but I'd be willing to bet the OleDB adapter uses COM internally.)

This would also take care of your embedded space problem since tab would be the column separator.

In your particular situation, I'd import the text file into Excel into a new sheet and then copy & paste it into the IRS sheet, at the right location. When done, the temporary sheet can be deleted.