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?

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;

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