Export into excel file without headers c# using Oledb

Question

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

Veinlet

Answer 1

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

Delaware

This is not working. Now it says The INSERT INTO statement contains the following unknown field name: 'F1'. Make sure you have typed the name correctly, and try the operation again. – Veinlet

run a command like "select * from sheet1" and look at the field names that are returned, and build you insert with those names – Mononucleosis

Well, I have managed to find the reason for the INVALID FIELD NAME error. Happens when the excel file is empty. If you insert something in the first cell and second cell, then the F1 and F2 field name are recognized. – Delaware

This helped me out immensely! I was running in to this error with a column header called Account #. For whatever reason, the # symbol in the column header created this error. – Gallstone

Answer 2

You need to specify which values you are writing, since you don't use an HDR - just use the cells. The Error "number of query values" simplies implies that - there are no fields assigned to the values supplied.

Update: @Steve was right with the Fields (F1,F2,etc), and the code below does work here:

    OleDbConnection Cn = new OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=No\"", @"D:\test.xls"));
    Cn.Open();
    OleDbCommand Com = new OleDbCommand("INSERT INTO [Sheet1$](F1,F2) VALUES('test3','test4');", Cn);
    Com.ExecuteNonQuery();
    Cn.Close();

Chinn

The INSERT INTO statement contains the following unknown field name: 'A1'. Make sure you have typed the name correctly, and try the operation again. – Veinlet

I was wrong BTW, @Delaware had it right - you need to focus on the fields on numeric base (F1,F2,etc). But the updated source does work. – Chinn

Weird. I'm using the exact same code you provided and again I got this exception. – Veinlet

You're using the F1-Fx fieldstructure?? – Chinn

What fields are returned on a select? (and to be on the safe side, the sheet is called Sheet1 right?) – Chinn

@riffnl: Any idea why only F1 is recognized and an exception is thrown while inserting into F2. – Harbert

@RobinMaben When using a "blank" (with some data) excelfile it should work like normally. However, when you've hidden rows and/or columns the datastructure is altered (FUBAR I might add) and you wont be able to add any columns without exceptions. You could attempt to read the tablestructure using "select *" and loop through the columns even so the results can be unreliable. – Chinn

@riffnl: You're right. I tried using the select method. That too returns only F1. Ugly as it may seem, I'm afraid I might have to accept it and add column headers. (Even though that's what I'm trying to avoid) :( Thanks a lot anyways. – Harbert