Search code examples
c#oledboledbconnectionoledbcommand

Export into excel file without headers c# using Oledb

I'm using OleDB and I want to export my objects into excel table. Each row in the sheet will be one of my objects. The problem is that I don't know how to insert data when there's no column headers in the sheet.

This one:

commandString = "Insert into [Sheet1$] values('test1', 'test2')"

throws this exception:

Number of query values and destination fields are not the same.

My connection string is:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+filename+";Extended Properties='Excel 8.0;HDR=No'"
Solution

  • If the connection string contains HDR=NO then the Jet OLE DB provider automatically names the fields for you (F1 for the first field, F2 for the second field, and so on). I will try to change your query in this way

    commandString = "Insert into [Sheet1$] (F1, F2) values('test1', 'test2')"

    this works only after you have created the excel file and have something inserted in the first two cells of the first row in Sheet1