Using OleDb to insert data into Excel 2007 in C#

Question

I have used as a base for what I want to do this site: http://www.codeproject.com/script/Articles/ViewDownloads.aspx?aid=8500

So basically, I want to use OleDb in my C# Windows form application to add some data to specific cells in my existing Excel spread sheet. All the examples I find want me to have some type of header cells. Like if my 'A1' cell had "Title" in it I could use:

"INSERT INTO [SHEET1$] (Title) Values ('Book')"  

The problem is that my Excel spread sheet does not have any header. What I need is to do:

"INSERT INTO [SHEET1$] (A15) Values ('Book')".

Can someone help me figure out how to put data in specific cells around my spread sheet?

Frank

Answer 1

If you are still interested,

There is no real way to specify a cell to write to using OleDb Insert Command, the OleDbCommand will automatically go to the next open row in the specified column. However you can use an Update Query such as:

sql = "Update [Sheet1$A1:A15] SET A15 = 'DesiredNumber'"; 
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();

This should work given you've defined:

System.Data.OleDb.OleDbConnection MyConnection;
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
string sql = null;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\Example.xls';Extended Properties=Excel 8.0;");
MyConnection.Open();
myCommand.Connection = MyConnection;

Edger

I get the error "No value given for one or more required parameters." when using this sql above – Posh

Answer 2

See How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET at http://support.microsoft.com/kb/316934.

Table Naming Conventions

There are several ways you can reference a table (or range) in an Excel workbook:

• Use the sheet name followed by a dollar sign (for example, [Sheet1$] or [My Worksheet$]). A workbook table that is referenced in this manner includes the whole used range of the worksheet.

  Select * from [Sheet1$] 
  

• Use a range with a defined name (for example, [MyNamedRange]):

  Select * from [MyNamedRange] 
  

• Use a range with a specific address (for example, [Sheet1$A1:B10]):

  Select * from [Sheet1$A1:B10] 
  

Inserted from http://support.microsoft.com/kb/316934

Saracen

Answer 3

I assume that you are using standard MS-Excel COM objects to interact with the excel file. You can try using system level ODBC drivers to interact with the excel file. While using this depending upon presence or absence of headers, you need to specify the same in the oledbConnection string and thereafter you can directly enter values to whichever cell you want.

Bower

None of those would work. So I decided to use Microsoft.Office.Interop.Excel. As found here: support.microsoft.com/default.aspx?scid=kb%3BEN-US%3B302084 – Frank