Click here to Skip to main content
11,641,971 members (63,982 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ASP.NET C#4.0
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
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 at 12-Feb-14 8:53am
   
Thank you!!! GOOD!!! SUPER!!!
asad.addy at 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.
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 at 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  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

You can use below code:

namespace DataFromWorkbookToDB
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\..\Sample.xlsx");            
            string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;data Source=sample.accdb;Persist Security Info=False;";
            DataTable dt = workbook.Worksheets[0].ExportDataTable();
            DataTable dt2 = workbook.Worksheets[1].ExportDataTable();
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                conn.Open();
                OleDbCommand command = new OleDbCommand();
                command.Connection = conn;
                command.CommandText = "CREATE TABLE report(Name VARCHAR(225), Capital VARCHAR(225) ,Continent VARCHAR(225),Area VARCHAR(225),Population VARCHAR(225))";
                command.ExecuteNonQuery();
 
                command.CommandText = "CREATE TABLE lists(PartNo VARCHAR(225), VendorNo VARCHAR(225) ,Description VARCHAR(225),OnHand VARCHAR(225),OnOrder VARCHAR(225))";
                command.ExecuteNonQuery();
 
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow row = dt.Rows[i];
                    string commd = "insert into [report](Name,Capital,Continent,Area,Population) values('" + row[0].ToString() + "','" + row[1].ToString() +
                   " ','" + row[2].ToString() + "','" + row[3].ToString() +"','"+ row[4].ToString() + "')";
                    command.CommandText = commd;
                    command.ExecuteNonQuery();
                }
 
                for (int i = 0; i < dt2.Rows.Count; i++)
                {
                    DataRow row = dt2.Rows[i];
                    string commd = "insert into [lists](PartNo,VendorNo,Description,OnHand,OnOrder) values('" + row[0].ToString() + "','" + row[1].ToString() +
                   " ','" + row[2].ToString() + "','" + row[3].ToString() + "','" + row[4].ToString() + "')";
                    command.CommandText = commd;
                    command.ExecuteNonQuery();
                }
            }
            
        }
    }
}

please note that above code needs this excel library Spire.XLS for .NET, you can give it a try,
  Permalink  

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

  Print Answers RSS
0 DamithSL 268
1 OriginalGriff 150
2 Sergey Alexandrovich Kryukov 134
3 Mika Wendelius 130
4 Afzaal Ahmad Zeeshan 89
0 Mika Wendelius 460
1 OriginalGriff 265
2 DamithSL 258
3 CPallini 169
4 Afzaal Ahmad Zeeshan 149


Advertise | Privacy | Mobile
Web04 | 2.8.150731.1 | Last Updated 25 Feb 2013
Copyright © CodeProject, 1999-2015
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