CODEWITHSUNDEEP
c#oledb
Shoaib Hassan
Shoaib Hassan

Reputation: 1

Unable to read Excel worksheet with OLEDB driver

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.

Upvotes: 0

Views: 1938

Answers (3)

Peru
Peru

Reputation: 2971

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"];

Upvotes: 0

melaouhia mohamed amine
melaouhia mohamed amine

Reputation: 210

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();

Upvotes: 0

Peru
Peru

Reputation: 2971

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#

Upvotes: 2

Related Questions