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?

Shuntwound

Does the workbook already have a sheet named test$? – Hisakohisbe

Answer 1

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

Wareing

Not quite correct - using the ReadOnly attribute causes the error "System.Data.OleDb.OleDbException : Could not find installable ISAM." It is the IMEX=0 that prevents the file being readonly. The string that worked for me (C#) is: @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Mode=ReadWrite;Extended Properties=""Excel 8.0;HDR=YES;MaxScanRows=0;IMEX=0"";"; – Overland

All that is essential is Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Docs\Test.xls;Mode=ReadWrite;Extended Properties=""Excel 8.0;HDR=Yes"" MaxScanRows and IMEX depend on registry settings to some extent and are often not required. – Geographical

Answer 2

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

Arthurarthurian

Answer 3

I also had the same problem. Only remove the extended property IMEX=1. That will solve your problem. Your table will be created in your Excel file...

Soaring

Answer 4

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.

Antoine

Answer 5

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

Shuntwound

Check the permissions by right-clicking on it and hitting the security or permissions tab. Is the Excel file closed when you connect to it? – Antoine

You should try IMEX=0 instead – Overland

Answer 6

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);

Newfashioned

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.)

Shushubert