Click here to Skip to main content
13,146,942 members (60,694 online)
Rate this:
 
Please Sign up or sign in to vote.
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 22-Aug-12 22:55pm
Updated 28-Mar-17 1:48am
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

follow this process..


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;
 
        }
  Permalink  
v2
Comments
slawa1 12-Feb-14 8:53am
   
Thank you!!! GOOD!!! SUPER!!!
asad.addy 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!!!
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

It works same as in case of database.
like
 
        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); 
 
  Permalink  
Comments
asad.addy 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.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

refer this link
Read Excel in ASP.NET[^]
and just replace sheet1 to sheet3
may be this will help u

thank you
@ChetanV@
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web03 | 2.8.170915.1 | Last Updated 28 Mar 2017
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100