get number of sheet in xls excel file

Question

how to get how many worksheet in file

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

now i want know how many worksheet in side given ?

what code i should i write ?

how can i know ?

thanks

see this http://stackoverflow.com/questions/1164698/using-excel-oledb-to-get-sheet-names-in-sheet-order — Haris Hasan, Feb 14 '12 at 11:24

Sid Holland · Answer 1 · 2012-05-06T22:09:17.550

2

While the question pointed to by Haris Hasan will allow you to extract the sheet names, if you just want the number of worksheets in a workbook, the following will do the trick:

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

using (OleDbConnection conn = new OleDbConnection(connectionString))
{
    conn.Open();

    DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

    if (dt != null)
    {
        foreach (DataRow row in dt.Rows)
        {
            if (row["TABLE_NAME"].ToString().EndsWith("$"))
            {
                numberOfSheets++;
            }
        }
    }
}

EDIT:

Or, for a shorter version, use the following (thanks for jb for helping me on this one):

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

using (OleDbConnection conn = new OleDbConnection(connectionString))
{
    conn.Open();

    DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

    if (dt != null)
    {
        numberOfSheets = dt.AsEnumerable().Cast<DataRow>().Where(row => row["TABLE_NAME"].ToString().EndsWith("$")).Count();
    }
}

edited May 06 '12 at 22:09

answered May 06 '12 at 19:42

Sid Holland

2,871
3
27
43

if you want to make this less code, you can do `dt.Rows.Where(row => row["TABLE_NAME"].ToString().EndsWith("$")).Count()` – jb. May 06 '12 at 19:52
@jb. My organisation is still stuck in the dark ages at .Net 2.0 so my knowledge of these newer methods isn't great, but I'm pretty sure that the `DataRowCollection` does not have a `Where()` method. I've read that to do what you are trying to do requires `AsEnumerable()` to be used on the `DataTable`. Please correct me if I'm wrong. – Sid Holland May 06 '12 at 20:28
looks like we were both half right. What you can do is this though: `dt.Rows.AsQueryable().Cast().Where(row => row["TABLE_NAME"].ToString().EndsWith("$")).Count();` – jb. May 06 '12 at 20:34
@jb. That won't work for me either. Visual Studio tells me that `AsQueryable()` is not a member of the `DataRowCollection`. – Sid Holland May 06 '12 at 21:45
I yield. if you're in .NET 3.5 or higher and are using System.Linq, AsQueryable() will work. You're SOL otherwise. – jb. May 06 '12 at 21:50
@jb. OK. Partly my fault. I'm so used to Visual Studio suggesting which namespace I'm missing when I use an object that isn't in the list that it threw me off when it didn't suggest it for the LINQ expression. Once added, I got the following to work perfectly: `dt.AsEnumerable().Cast().Where(row => row["TABLE_NAME"].ToString().EndsWith("$")).Count();` – Sid Holland May 06 '12 at 22:04

get number of sheet in xls excel file

1 Answers1