c# - Writing into excel file with OLEDB

Question

Does anyone know how to write to an excel file (.xls) via OLEDB in C#? I'm doing the following:

   OleDbCommand dbCmd = new OleDbCommand("CREATE TABLE [test$] (...)", connection);
   dbCmd.CommandTimeout = mTimeout;
   results = dbCmd.ExecuteNonQuery();

But I get an OleDbException thrown with message:

"Cannot modify the design of table 'test$'. It is in a read-only database."

My connection seems fine and I can select data fine but I can't seem to insert data into the excel file, does anyone know how I get read/write access to the excel file via OLEDB?

Answer 1

I was also looking for and answer but Zorantula's solution didn't work for me. I found the solution on http://www.cnblogs.com/zwwon/archive/2009/01/09/1372262.html

I removed the ReadOnly=false parameter and the IMEX=1 extended property.

The IMEX=1 property opens the workbook in import mode, so structure-modifying commands (like CREATE TABLE or DROP TABLE) don't work.

My working connection string is:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=workbook.xls;Mode=ReadWrite;Extended Properties="Excel 8.0;HDR=Yes;";"