none
Reading multiple worksheets from Excel by C#

    Question

  • Hi

    What's the best way to retrieve data from multiple worksheets in one Excel using C#?

    Also how to read data starting not from row 1 but row 6 etc.?

    Many thanks

    Leon
    Thursday, September 18, 2008 2:35 PM

Answers

  • In this way you have to have your headersin the first row (HDR=YES), so you can't retrieve data where row 6 is your header.

     

     

     

    To get at different sheets use

     

    String.Format(@"Select * From [{0}]", "Sheet1$");

    fill to datatable

    String.Format(@"Select * From [{0}]", "Sheet2$");

    fill to datatable

    String.Format(@"Select * From [{0}]", "Products$");

    fill to datatable

    String.Format(@"Select * From [{0}]", "Orders$");

    filltodatatable

     

    Friday, September 19, 2008 2:16 PM

All replies

  • A little more info maybe, do you need the worksheets to be opened and calculated and then retrieve the data?

    What version of Excel are you using 2003 or 2007

     

    The very basic is just to loop through your excel spreadsheets, open them, read your particular ranges into objects, and close them.

     

     

    Thursday, September 18, 2008 3:14 PM
  • I don't need the worksheets to be opened.

     

    Actually, I found a way to read single worksheet from row 1 but couldn't figure out the best way to read multiple.

     

    Code Snippet

    string connectionString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1""", filePath);

                DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

                using (DbConnection connection = factory.CreateConnection())
                {
                    connection.ConnectionString = connectionString;

                    using (DbCommand command = connection.CreateCommand())
                    {
                        command.CommandText = String.Format(@"Select * From [{0}]", "Sheet$");

                        connection.Open();

                        using (DbDataReader dr = command.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                               ......

                            }
                        }
                    }
                }

     

     

     

     

    Thursday, September 18, 2008 8:22 PM
  • In this way you have to have your headersin the first row (HDR=YES), so you can't retrieve data where row 6 is your header.

     

     

     

    To get at different sheets use

     

    String.Format(@"Select * From [{0}]", "Sheet1$");

    fill to datatable

    String.Format(@"Select * From [{0}]", "Sheet2$");

    fill to datatable

    String.Format(@"Select * From [{0}]", "Products$");

    fill to datatable

    String.Format(@"Select * From [{0}]", "Orders$");

    filltodatatable

     

    Friday, September 19, 2008 2:16 PM
  • Thanks! I'll try "HDR=NO".

     

    My problem with multiple worksheets is that I don't always know the number of worksheets and names of them.

     

    Is there any way to automatically go through each and retrieve the data?

     

    Thanks

    Monday, September 22, 2008 5:47 PM
  • Hi ,

    If you want retrieve the number and the names of a work book I'll show you a way:

    Code Snippet

      Excel.Application myExcel =new  Microsoft.Office.Interop.Excel.Application();
                Excel.Workbook workbook= myExcel.Workbooks.Open(@"c:\Book1.xlsm", missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                //myExcel.Visible = false;
                Console.WriteLine(workbook.Worksheets.Count.ToString());
                myExcel.Quit();

     

     

    Thanks
    Thursday, September 25, 2008 3:21 AM
  • Hi

     

    Thanks for that. However, it's quite different from the way I'm doing now by using Excel class and the live server where my app will sit doesn't have office. Will that be a problem?

     

    I just can't believe there is no easy way to loop through multiple worksheets using OleDb!

     

    Thanks

    • Proposed as answer by k967 Thursday, May 12, 2011 9:39 AM
    • Unproposed as answer by k967 Thursday, May 12, 2011 9:39 AM
    Thursday, September 25, 2008 9:37 AM
  • Hi Neo

     neo2006 wrote:

    Thanks for that. However, it's quite different from the way I'm doing now by using Excel class and the live server where my app will sit doesn't have office. Will that be a problem?

     

    I just can't believe there is no easy way to loop through multiple worksheets using OleDb!

    I've been letting this thread run, as I know it's difficult to get support for managed code in Office. But you have to realize that this is the VSTO forum and your question is technically rather off-topic here. Correctly, you should be asking this in a forum that supports ADO.NET, or the appropriate Data newsgroup, or the Excel.programming newsgroup.

     

    So it's not surprising that Tim has responded with an object model answer, and that discussion is slow.

     

    My (classical) ADO is very rusty, and I was never into it very deeply. But I seem to recall that in order to get a list of Tables (and that's more or less how ADO sees the worksheets in a workbook) you need to use a different part of ADO. But I think at this point you do need to ask somewhere that specializes in ADO. You'll find links that will take you to the newsgroup interface in the Please Read First message at the top of the forum. The Data newsgroups aren't listed there separately, but you can get to them via the "all newsgroups" link.

    Thursday, September 25, 2008 10:07 AM
  • Hi ,

    I think the GetOleDbSchemaTable  method could help.Please check the following links:

    http://support.microsoft.com/kb/318452

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1633550&SiteID=1

    But it's not VSTO side .......

    Thanks

    Thursday, September 25, 2008 10:09 AM
  • Hi

     

    Thanks a lot for pointing me out.

     

    I'll check other forum...

     

    Thanks

    Thursday, September 25, 2008 10:41 AM
  • Hi,

    you can read multiple worksheets with ease using this Excel .NET library.

    This Excel C# sample code demonstrates how to read entire Excel to DataSet starting from 6th row:

    var ef = new ExcelFile();
    ef.LoadXls("Excel file.xls");
    
    // DataSet schema has to be defined before this.
    for(int i = 0; i < ef.Worksheets.Count; ++i)
    {
    	var ws = ef.Worksheets[i];
    	ws.ExtractToDataTable(dataSet.Tables[i], ws.Rows.Count, ExtractDataOptions.StopAtFirstEmptyRow, ws.Rows[5], ws.Columns[0]);
    }
    

    Tuesday, October 05, 2010 8:13 AM
  • I just can't believe there is no easy way to loop through multiple worksheets using OleDb!

    Hi,

    I wanted to do the same thing, and I came to a result to get a list of the sheets from an Excel file while using OleDB :

    Here is my solution :

    // open the excel file
     OleDbConnection objConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'");

    // connect
    objConn.Open();

                    DataSet ds = new DataSet();
                    OleDbDataAdapter da = new OleDbDataAdapter();

                    // get the schema of the "database"
                    DataTable dtTables = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                    // loop through the list of the tables...
                    foreach (DataRow dr in dtTables.Rows)
                    {
                        string nSheet = dr["TABLE_NAME"].ToString(); // the name of the sheet
                        da = new OleDbDataAdapter("SELECT * FROM [" + nSheet + "]", objConn); // get the data
                        da.Fill(ds, nSheet.Replace("$", "")); // fill the table
                    }

                    // an then you can navigate through the dataset....
                    foreach(DataTable dt in ds.Tables)
                    {
                        foreach (DataColumn dc in dt.Columns)
                        {
                            foreach(DataRow dr in dt.AsEnumerable())
                            {
                                MessageBox.Show(dt.TableName + " - " + dc.ColumnName + " - " + dr[dc.ColumnName].ToString()); // for example 

                          }
                        }
                    }

    objConn.Close();


    • Proposed as answer by k967 Thursday, May 12, 2011 9:42 AM
    Thursday, May 12, 2011 9:41 AM
  • This isn't always available since Excel is not designed to run on servers. If you need any sort of Web UI, this would not be the way to go.
    Monday, June 24, 2013 7:52 PM