I would like to know how I can read multiple excel worksheet with different worksheet name in c# and with the used of oledb.

I have this existing way to read multiple sheets (but with fixed worksheet name):

            DataSet ds = new DataSet();
            var excelConnectionString = string.Format(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0”, path);
            OleDbConnection connection = new OleDbConnection();
            connection.ConnectionString = excelConnectionString;

            var i = 1;
            while (i <= 4)
            {
                string query = “SELECT * FROM [Sheet” + i + “$]”;
                ds.Clear();
                OleDbDataAdapter data = new OleDbDataAdapter(query, connection);
                data.Fill(ds);

                // other stuff
                i = i + 1;
            }

Elegant solutions is:

static DataTable GetSchemaTable(string connectionString)
{
using (OleDbConnection connection = new
OleDbConnection(connectionString))
{
connection.Open();
DataTable schemaTable = connection.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
return schemaTable;
}
}

Your code would change to:

DataSet ds = new DataSet();
var excelConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", path);
OleDbConnection connection = new OleDbConnection();
connection.ConnectionString = excelConnectionString;

DataTable sheets = GetSchemaTable(excelConnectionString);

foreach (dataRow r in sheets.rows)
{
string query = "SELECT * FROM [" + r.Item(0).ToString + "]";
ds.Clear();
OleDbDataAdapter data = new OleDbDataAdapter(query, connection);
data.Fill(ds);

}

By Enrico

My greatest passion is technology. I am interested in multiple fields and I have a lot of experience in software design and development. I started professional development when I was 6 years. Today I am a strong full-stack .NET developer (C#, Xamarin, Azure)

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.