Export into excel file without headers c# using Oledb

c# oledb oledbconnection oledbcommand
Dimitar Tsonev picture Dimitar Tsonev · Apr 19, 2012 · Viewed 8.2k times · Source

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'"

Answer

Steve picture Steve · Apr 19, 2012

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

Related questions

Parsing CSV using OleDb using C#

I know this topic is done to death but I am at wits end. I need to parse a csv. It's a pretty average CSV and the parsing logic has been written using OleDB by another developer who swore that …

Read More
the 'microsoft.ace.oledb.12.0' provider is not registered on the local machine while reading a excel file in c#

I am reading an excel file and I want to access its contents. I am using this connection string: connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0;", Server.MapPath(fileName)); But I see this error: The …

Read More
Get column names from excel file of a specific sheet using c# with OleDbConnection

So far I have managed to get the column names of the whole excel file, but what I would like to do is to get the column names of the excel file of a given table (sheet). How could I …

Read More