How to create and update spreadsheet using OLEDB in C#?

Question

HI! all I am creating .xls spreadsheet file using following code

using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\temp.xls;Extended Properties='Excel 8.0;HDR=Yes'"))
{
    conn.Open();
    OleDbCommand cmd = new OleDbCommand("CREATE TABLE [NewSheet] ([Column1] string, [Column2] string)", conn);
    cmd.ExecuteNonQuery();
}

but i am getting an exception at cmd.ExecuteNonQuery();

Exception is: Cannot modify the design of table 'NewSheet'. It is in a read-only database.

Please help me to resolve this

Thank You

Answer 1

You need to add readonly= false to your connection string, see:

http://stackoverflow.com/questions/57987/writing-into-excel-file-with-oledb

Answer 2

SpreadsheetGear for .NET will let you open, modify and save Excel workbooks, has no limits on the structure of a workbook, and does not rely on OLEDB or anything else (other than .NET 2.0+).

You can see live samples here and download the free trial here if you want to try it yourself.

Disclaimer: I own SpreadsheetGear LLC