Unable to read Excel worksheet with OLEDB driver

Question

I have an excel file with one worksheet. I'm using MicroSoft.Office.Interop.Excel to read this file and then perform further execution.

Here is my code:

 connString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strNewPath + ";Extended Properties=Excel 8.0;";
               conn = new OleDbConnection(connString);
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                System.Data.DataTable dt = null;
                dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

But, worksheet is not in the data table object.

Few questions: 1. is it a XLS or XLSX ? 2. is the worksheet password protected? 3. Did you debug whether the OLEDB connection is made successfully ? — Raptor, Dec 21 '12 at 07:23
1.It is a XLSX file 2. work sheet is not password protected 3. the connection is made successfully — Shoaib Hassan, Dec 21 '12 at 07:26
the same code was working fine some time earlier. Now the only difference is that i am using different excel file for processing. — Shoaib Hassan, Dec 21 '12 at 07:27

score 2 · Answer 1 · edited May 23 '17 at 11:43

2

Where you have mentioned the table(WorkSheet) name ??

    DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                                                       new object[] {null, null, null, "TABLE"});

//Get the First Sheet Name
        string firstSheetName = schemaTable.Rows[0][2].ToString(); 

        //Query String 
        string sql = string.Format("SELECT * FROM [{0}],firstSheetName);

Refer here MSDN

In case if you want to play around refer Reading Excel files from C#

edited May 23 '17 at 11:43

Community

1
1

answered Dec 21 '12 at 07:27

Peru

2,871
5
37
66

I thought this line will get the worksheet in the excel file conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); – Shoaib Hassan Dec 21 '12 at 07:37
Yes. 2nd parameter is restricted columns – Peru Dec 21 '12 at 07:42
How can i mention the worksheet name, as at this point of code the name of worksheet is not accessed,is there any way to get worksheet name? – Shoaib Hassan Dec 21 '12 at 07:43
i have updated my answer to fetch the sheet name. See if it helps. PS: give correct array for .Rows[] [] – Peru Dec 21 '12 at 08:46

score 0 · Answer 2 · answered Dec 21 '12 at 10:33

OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + "; Extended Properties=Excel 12.0;");

        //After connecting to the Excel sheet here we are selecting the data 
        //using select statement from the Excel sheet
        oconn.Open();
        DataTable dbSchema = oconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        if (dbSchema == null || dbSchema.Rows.Count < 1)
        {
            throw new Exception("Error: Could not determine the name of the first worksheet.");
        }
        string firstSheetName = dbSchema.Rows[0]["TABLE_NAME"].ToString();

        string tbstrat = "M1000";
        OleDbCommand cmd = new OleDbCommand();
        cmd.Connection = oconn;
        cmd.CommandText = "select * from [" + firstSheetName + "B8:" + tbstrat + "]";
        OleDbDataAdapter adap = new OleDbDataAdapter();
        DataTable dt = new DataTable();
        adap.SelectCommand = cmd;
        adap.Fill(dt);
        oconn.Close();

score 0 · Answer 3 · answered Dec 21 '12 at 11:08

0

Even you can try this

var adapter = new OleDbDataAdapter("SELECT * FROM [workSheetNameHere$]", connectionString);
var ds = new DataSet();

adapter.Fill(ds, "NameHere");

DataTable data = ds.Tables["NameHere"];

answered Dec 21 '12 at 11:08

Peru

2,871
5
37
66

Unable to read Excel worksheet with OLEDB driver

3 Answers3