CODEWITHSUNDEEP
c#excelms-officeconnection-string
sleath
sleath

Reputation: 871

C# Accessing Excel Worksheet

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.

Upvotes: 0

Views: 2866

Answers (3)

Gratzy
Gratzy

Reputation: 9389

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.

Upvotes: 1

Andrew Flanagan
Andrew Flanagan

Reputation: 4277

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.

Upvotes: 1

stuartd
stuartd

Reputation: 73243

There's an article on codeproject - http://www.codeproject.com/KB/office/excel_using_oledb.aspx - which should get you started

Upvotes: 1

Related Questions