C# Accessing Excel Worksheet

Question

Is this the correct way to access a MS Office Excel 2007 file?

        String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + 
    "Data Source=" + file_path + ";Extended Properties=Excel 8.0;";

If so, how do I access a certain worksheet and insert rows? Links are also welcomed.

Answer 1

There's an article on codeproject - http://www.codeproject.com/KB/office/excel%5Fusing%5Foledb.aspx - which should get you started

Answer 2

Connection string

  connectionString = @"provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + @";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1""";

reading data

 excelConnection = new System.Data.OleDb.OleDbConnection(connectionString);
        excelConnection.Open();
        dbSchema = excelConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
        firstSheetName = dbSchema.Rows[0]["TABLE_NAME"].ToString();
        strSQL = "SELECT * FROM [" + firstSheetName + "]";
        da = new OleDbDataAdapter(strSQL, excelConnection);
        da.Fill(dt);

writing data see Excel Generation this uses automation though. It may help.

Answer 3

You can use Excel Interop (Microsoft.Office.Interop.Excel):

Here's a snippet of some code:

object missing = (object) Type.Missing;

Application app = new Application();

Workbooks books = app.Workbooks;

Workbook book = books.Open("somefile.xls", missing, missing, missing, missing, missing, missing,
            missing, missing, missing, missing, missing, missing, missing, missing);

Worksheet sheet = (Worksheet)book.Worksheets[1];

It's got some weirdnesses (like those "missing" parameters) but it works pretty smoothly. If you take this approach, be careful that the EXCEL.exe process doesn't get orphaned.