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

A couple questions:

• Does the user that executes your app (you?) have permission to write to the file?
• Is the file read-only?
• What is your connection string?

If you're using ASP, you'll need to add the IUSER_* user as in this example.

Answer 2

• How do I check the permissions for writing to an excel file for my application (I'm using excel 2007)?
• The file is not read only, or protected (to my knowledge).
• My connection String is:

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

Answer 3

You need to add "ReadOnly=False;" to your connection string

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=fifa_ng_db.xls;Mode=ReadWrite;ReadOnly=false;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";

Answer 4

Further to Michael Haren's answer. The account you will need to grant Modify permissions to the XLS file will likely be NETWORK SERVICE if this code is running in an ASP.NET application (it's specified in the IIS Application Pool). To find out exactly what account your code is running as, you can do a simple:

Response.Write(Environment.UserDomainName + "\\" + Environment.UserName);

Answer 5

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;\";"

Answer 6

try thsese links

http://csharp.net-informations.com/excel/csharp-excel-oledb.htm
http://csharp.net-informations.com/excel/csharp-excel-oledb-insert.htm

bolton.

Answer 7

I was running under ASP.NET, and encountered both "Cannot modify the design..." and "Cannot locate ISAM..." error messages.

I found that I needed to:

a) use the following connection string:

"Provider=Microsoft.Jet.OLEDB.4.0;Mode=ReadWrite;Extended Properties='Excel 8.0;HDR=Yes;';Data Source=" + {path to file};

Note I too had issues with IMEX=1 and with the ReadOnly=false attributes in the connection string.

b) grant EVERYONE full permissions to the folder in which the file was being written. Normally, ASP.NET runs under the NETWORK SERVICE account, and that already had permissions. However, the OleDb code is unmanaged, so it must run under some other security context. (I am currently too lazy to figure out which account, so I just used EVERYONE.)