读取多个Excel表以不同工作表名称 [英] Reading multiple excel sheets with different worksheet names

问题描述

我想知道我可以读取多个Excel工作表，在C＃中，并与使用OLEDB不同工作表名称。

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;
}

这一件作品。但是我现在在不同的情况下，其中的工作表名称不固定的，例如：工作表Sheet1是狗，是Sheet2的工作表Sheet 3猫鸟是

This one works. But I'm now in different situation wherein the worksheets names are not fixed, example: Sheet1 is Dog, Sheet2 is Cat Sheet3 is Bird.

现在我关心的是我如何可在安装使用我的现有代码的工作表的名称循环。

Now my concern is how I can loop on those worksheet names with the use of my existing code.

推荐答案

这是从VB.net但不知道如何很好它转换，返回包含所有工作表名称字符串的列表：

This is from VB.net but not sure how well it translates, returns a list of strings containing all sheet names:

OleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})

的 MSDN链接

MSDN Link

一旦你有工作表名称的列表，你可以做一个简单的对于每个循环迭代

Once you have a list of sheet names you can do a simple For Each loop to iterate.

编辑：

这应该在C＃中工作。

附加功能

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;
    }
}

您的代码将改变为：

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);

}

只是一定要做点什么 DS 每次迭代之后。

这篇关于读取多个Excel表以不同工作表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！