Click here to Skip to main content
14,843,683 members
Please Sign up or sign in to vote.
2.09/5 (3 votes)
See more:
Hi everyone..

I have an Excel Sheet ,in that work sheet names are Sheet1,Sheet2,Sheet3...

I want to load Sheet3 data in to my DataTable.

How to do this can any one help me to do this .


Thank's in advance
Posted
Updated 28-Mar-17 1:48am

follow this process..


c#
public static DataTable exceldata(string filePath)
        {     
            DataTable dtexcel = new DataTable();
               bool hasHeaders = false;
                string HDR = hasHeaders ? "Yes" : "No";
                string strConn;
                if (filePath.Substring(filePath.LastIndexOf('.')).ToLower() == ".xlsx")
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
                else
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                //Looping Total Sheet of Xl File
                /*foreach (DataRow schemaRow in schemaTable.Rows)
                {
                }*/
                //Looping a first Sheet of Xl File
                DataRow schemaRow = schemaTable.Rows[0];
                string sheet = schemaRow["TABLE_NAME"].ToString();
                if (!sheet.EndsWith("_"))
                {
                    string query = "SELECT  * FROM [" + sheet3 + "]";
                    OleDbDataAdapter daexcel = new OleDbDataAdapter(query, conn);
                    dtexcel.Locale = CultureInfo.CurrentCulture;
                    daexcel.Fill(dtexcel);
                }
            
            conn.Close();
            return dtexcel;

        }
   
v2
Comments
slawa1 12-Feb-14 8:53am
   
Thank you!!! GOOD!!! SUPER!!!
AddyRuno 18-Jun-15 8:18am
   
Its not working it always throw following erorr

The Microsoft Office Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.
KC Abramson 12-Oct-15 18:15pm
   
i'm thinking your worksheet may have a different name than the default Sheet1, 2 etc
KC Abramson 12-Oct-15 18:07pm
   
Perfect. This was an excellent super fast solution. Thanks!
thekoko89 20-Mar-17 19:39pm
   
Thank you!!!
Member 7976424 29-Aug-18 5:54am
   
Thank you!!! 3 Times
Srilekha Bolamoni 13-Nov-18 4:46am
   
can you suggest without using OLEDB.
It works same as in case of database.
like
c#
OleDbConnection cnn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(@"~\data\cocustomerdetails.xlsx") + "; Extended Properties=Excel 12.0;");

    OleDbCommand oconn = new OleDbCommand("select * from [Sheet1$]", cnn);
    cnn.Open();
    OleDbDataAdapter adp = new OleDbDataAdapter(oconn);
    DataTable dt = new DataTable();
    adp.Fill(dt);
   
Comments
AddyRuno 18-Jun-15 8:19am
   
ts not working it always throw following erorr

The Microsoft Office Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.
refer this link
Read Excel in ASP.NET[^]
and just replace sheet1 to sheet3
may be this will help u

thank you
@ChetanV@
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900