Question

我试图在Excel工作手册中检索一份工作表列表,但我收到的收藏有工作表名称和数据列 id s,它们似乎在原xlsxxxml中被称为定义名称。你能告诉我如何只返回工作表名称吗?

Im使用的代码大致如下:

OleDbConnection connExcel = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;"
            + @"Data Source=" + FilePath + ";"
            + @"Extended Properties=""Excel 8.0;HDR=Yes;""");

OleDbCommand cmdExcel = new OleDbCommand();
cmdExcel.Connection = connExcel;
connExcel.Open();

DataTable testTable = connExcel.GetSchema("Tables");

由此得出的测试表收集内容包含下列表表下条目:

DATA1
DATA2
DATA3
DATA4
DATA5
Sheet1$
TEST1 -TEST2
TESTHKEY
TESTKEYS
TESTVKEY

他们都有一个Table的Table类型。

The original workbook corresponding to the above would have 1 worksheet containing 5 columns, the first row would contain a header. I m only interested in the Sheet1$ entry. The spreadsheet is created in Excel 2010, I m trying to process it in an ASP.NET 4 app written in C#. Potentially, the worksheet name may have been changed so I can t guarrantee that it will always be Sheet1$.

Answer 1

我最初的想法是错误的,所以我把它想成了一种变通办法。返回的实际工作表名称应该总是以美元结束,所以我黑了它来检查。混乱,但你明白我所肯定的一般想法。

OleDbConnection connExcel = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;"
        + @"Data Source=c:	est.xlsx;"
        + @"Extended Properties=""Excel 12.0 Xml;HDR=Yes;""");

        OleDbCommand cmdExcel = new OleDbCommand();
        cmdExcel.Connection = connExcel;
        connExcel.Open();

        DataTable testTable = connExcel.GetSchema("Tables");

        String[] excelSheets = new String[testTable.Rows.Count];
        int i = 0;

        foreach (DataRow row in testTable.Rows)
        {
            excelSheets[i] = row["TABLE_NAME"].ToString();

            if (excelSheets[i].EndsWith("$"))
            {
                Console.WriteLine(excelSheets[i] = row["TABLE_NAME"].ToString());
                i++;
            }
            else
            {
                i++;
            }

        }

        Console.ReadLine();

Answer 2

我有一个类似的问题,但除了它向我展示了Excel中不存在的希斯之外。尽管这个文章现在有点老了,但也许有人会发现这一点并发现它很有帮助。

我的代码 :

//OpenFileDialog
try
{
    OpenFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
    OpenFileDialog.Filter = "XLSX Files(*.xlsx)|*.xlsx|All Files (*.*)|*.*";
    OpenFileDialog.ShowDialog();
}
catch (Exception ex)
{
    //some Error Message
}

//read into Combobox
try
{
    OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + OpenFileDialog.FileName + ";Extended Properties=Excel 12.0;");
    con.Open();
    DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    con.Close();
    this.Combobox.Items.Clear();
  
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        String sheetName = dt.Rows[i]["TABLE_NAME"].ToString();
        sheetName = sheetName.Substring(0, sheetName.Length - 1);

        //cheap Filter to filter out unneeded/wrong sheets
        if (sheetName.Replace(" ", " ").Replace("$", " ").TrimStart().TrimEnd().Contains("#") != true)
        {
            this.Combobox.Items.Add(sheetName.Replace(" ", " ").Replace("$", " ").TrimStart().TrimEnd());
        }
    }
}
catch (Exception Ex)
{
    //some Error Message
}

这也许不是最好的解决办法,但对我效果很好。

Answer 3

private static string EXCEL_CONNECTIONSTRING = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties=Excel 12.0;", "#{FILENAME}");

private IEnumerable<string> GetWorksheetNames(string excelFile)
{

     var currentConnectionString = EXCEL_CONNECTIONSTRING.Replace("#{FILENAME}", excelFile);

     using (OleDbConnection connection = new OleDbConnection(currentConnectionString))
     {
        OleDbCommand cmdExcel = new OleDbCommand();


        cmdExcel.Connection = connection;
        connection.Open();

        DataTable dt = connection.GetSchema("Tables");

        IEnumerable<string> excelSheets = dt.Rows.Cast<DataRow>().Select(row => row["TABLE_NAME"].ToString());
        dt.Dispose();
        connection.Close();
        return excelSheets;

     }

  }

友情链接