logo标签列表

使用Excel OleDb获取工作表名称按照工作表顺序

c#exceloledbserver-side
110

我正在使用OleDb从一个包含多个工作表的Excel工作簿中读取数据。

我需要读取工作表的名称，但我需要按照它们在电子表格中定义的顺序进行排序；所以如果我的文件看起来像这样;

|_____|_____|____|____|____|____|____|____|____|
|_____|_____|____|____|____|____|____|____|____|
|_____|_____|____|____|____|____|____|____|____|
\__GERMANY__/\__UK__/\__IRELAND__/

然后我需要获取字典

1="GERMANY", 
2="UK", 
3="IRELAND"

我曾尝试使用OleDbConnection.GetOleDbSchemaTable()方法，它可以给我列出名称的列表，但是这个列表是按字母排序的。由于字母排序，我不知道每个名称对应哪个工作表编号。所以得到了下面的结果：

GERMANY, IRELAND, UK

更改了UK和IRELAND的顺序。

我需要进行排序的原因是，我必须让用户通过名称或索引选择数据范围。他们可以请求“从德国到爱尔兰的所有数据”或“从第1张表到第3张表的数据”。

任何想法都将不胜感激。

如果我能使用Office互操作类，这将是简单明了的。不幸的是，我不能，因为互操作类在非交互式环境（如Windows服务和ASP.NET网站）中不可靠，所以我必须使用OLEDB。

你正在读取哪个版本的Excel文件？ - yamen
33
哇，你是怎么画出那幅画的？你又是如何有耐心画完它的？ - Alex Gordon
4
它们是用竖线（|）和下划线（_）表示的表格行，斜杠（/）则用于标签页。将其复制到文本编辑器中，您就可以看到了。 - Sid Holland
11个回答
83
你可以通过循环遍历从0到名字数量减1的表格来获取它们的正确顺序。
编辑
我注意到在评论中有很多人对使用Interop类检索工作表名称表示担忧。因此，这里提供了一个使用OLEDB来检索它们的示例：
/// <summary>
/// This method retrieves the excel sheet names from 
/// an excel workbook.
/// </summary>
/// <param name="excelFile">The excel file.</param>
/// <returns>String[]</returns>
private String[] GetExcelSheetNames(string excelFile)
{
    OleDbConnection objConn = null;
    System.Data.DataTable dt = null;

    try
    {
        // Connection String. Change the excel file to the file you
        // will search.
        String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + 
          "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
        // Create connection object by using the preceding connection string.
        objConn = new OleDbConnection(connString);
        // Open connection with the database.
        objConn.Open();
        // Get the data table containg the schema guid.
        dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        if(dt == null)
        {
           return null;
        }

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

        // Add the sheet name to the string array.
        foreach(DataRow row in dt.Rows)
        {
           excelSheets[i] = row["TABLE_NAME"].ToString();
           i++;
        }

        // Loop through all of the sheets if you want too...
        for(int j=0; j < excelSheets.Length; j++)
        {
            // Query each excel sheet.
        }

        return excelSheets;
   }
   catch(Exception ex)
   {
       return null;
   }
   finally
   {
      // Clean up.
      if(objConn != null)
      {
          objConn.Close();
          objConn.Dispose();
      }
      if(dt != null)
      {
          dt.Dispose();
      }
   }
}

从CodeProject的文章中提取。

这是我想看到的代码！如何查询“第N个工作表”和工作表数量？ - Steve Cooper
13
嗨，詹姆斯。这基本上是我的原始问题 - GetOleDbSchemaTable()方法可以获取名称，但行号与工作簿表格的编号不对应。因此，如果字母表中先出现Sheet 4，则其行号将为0。 - Steve Cooper
27
不回答海报的问题（他想按Excel中出现的顺序排列）。 - Andrew White
8
我不认为它直接解决了提问者的问题，但它似乎对许多遇到类似问题的人有所帮助。 - James
1
并没有解决我来寻找的OP问题。（我总是发布downvote的原因。） - Phil Nicholas
显示剩余2条评论
23

由于上述代码未涵盖提取Excel 2007工作表名称列表的过程，因此以下代码将适用于Excel（97-2003）和Excel 2007：

public List<string> ListSheetInExcel(string filePath)
{
   OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder();
   String strExtendedProperties = String.Empty;
   sbConnection.DataSource = filePath;
   if (Path.GetExtension(filePath).Equals(".xls"))//for 97-03 Excel file
   {
      sbConnection.Provider = "Microsoft.Jet.OLEDB.4.0";
      strExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=1";//HDR=ColumnHeader,IMEX=InterMixed
   }
   else if (Path.GetExtension(filePath).Equals(".xlsx"))  //for 2007 Excel file
   {
      sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
      strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";
   }
   sbConnection.Add("Extended Properties",strExtendedProperties);
   List<string> listSheet = new List<string>();
   using (OleDbConnection conn = new OleDbConnection(sbConnection.ToString()))
   {
     conn.Open();
     DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);         
     foreach (DataRow drSheet in dtSheet.Rows)
     {
        if (drSheet["TABLE_NAME"].ToString().Contains("$"))//checks whether row contains '_xlnm#_FilterDatabase' or sheet name(i.e. sheet name always ends with $ sign)
        {
             listSheet.Add(drSheet["TABLE_NAME"].ToString());
        } 
     }
  }
 return listSheet;
}

上述函数返回特定Excel文件中所有工作表的列表，适用于Excel类型（97、2003、2007）。

14
此代码未按Excel中表格出现的顺序返回表格。 - Andrew White
20

在实际的MSDN文档中找不到这个信息，但论坛中的一位主持人说：

恐怕OLEDB不能保留Excel表格的顺序

按表格顺序排列的Excel表格名称

看起来这应该是一个足够常见的要求，应该有一个不错的解决方法。

然而，尽管这并没有直接回答问题，但它确实节省了很多不必要的尝试时间。 - Shihe Zhang
11

这是简短、快速、安全和易用的...

public static List<string> ToExcelsSheetList(string excelFilePath)
{
    List<string> sheets = new List<string>();
    using (OleDbConnection connection = 
            new OleDbConnection((excelFilePath.TrimEnd().ToLower().EndsWith("x")) 
            ? "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + excelFilePath + "';" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'"
            : "provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + excelFilePath + "';Extended Properties=Excel 8.0;"))
    {
        connection.Open();
        DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        foreach (DataRow drSheet in dt.Rows)
            if (drSheet["TABLE_NAME"].ToString().Contains("$"))
            {
                string s = drSheet["TABLE_NAME"].ToString();
                sheets.Add(s.StartsWith("'")?s.Substring(1, s.Length - 3): s.Substring(0, s.Length - 1));
            }
        connection.Close();
    }
    return sheets;
}
“开箱即用”未能奏效。 exceladdress - 这是什么？ - Michael Hutter
1
不回答实际问题，只是返回所有工作表但不按顺序。 - shas
8

另一种方法：

xls(x)文件实际上是存储在*.zip容器中的*.xml文件集合。解压缩docProps文件夹中的“app.xml”文件即可。

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
-<Properties xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes" xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties">
<TotalTime>0</TotalTime>
<Application>Microsoft Excel</Application>
<DocSecurity>0</DocSecurity>
<ScaleCrop>false</ScaleCrop>
-<HeadingPairs>
  -<vt:vector baseType="variant" size="2">
    -<vt:variant>
      <vt:lpstr>Arbeitsblätter</vt:lpstr>
    </vt:variant>
    -<vt:variant>
      <vt:i4>4</vt:i4>
    </vt:variant>
  </vt:vector>
</HeadingPairs>
-<TitlesOfParts>
  -<vt:vector baseType="lpstr" size="4">
    <vt:lpstr>Tabelle3</vt:lpstr>
    <vt:lpstr>Tabelle4</vt:lpstr>
    <vt:lpstr>Tabelle1</vt:lpstr>
    <vt:lpstr>Tabelle2</vt:lpstr>
  </vt:vector>
</TitlesOfParts>
<Company/>
<LinksUpToDate>false</LinksUpToDate>
<SharedDoc>false</SharedDoc>
<HyperlinksChanged>false</HyperlinksChanged>
<AppVersion>14.0300</AppVersion>
</Properties>

这个文件是德语文件（Arbeitsblätter = 工作表）。 表格名称（Tabelle3等）已按正确顺序排列。 您只需要阅读这些标签即可;)。
敬礼
1
这对于 xlsx 文件很有效，但不适用于 xls 文件。它们没有相同的结构。您知道如何从 xls 文件中提取相同的数据吗？ - rdans
6

我使用@kraeppy提供的答案中提供的信息创建了以下函数(https://dev59.com/GnM_5IYBdhLWcg3w8H82#19930386)。 这需要使用.net框架v4.5并需要引用System.IO.Compression。 这仅适用于xlsx文件，而不适用于旧的xls文件。

    using System.IO.Compression;
    using System.Xml;
    using System.Xml.Linq;

    static IEnumerable<string> GetWorksheetNamesOrdered(string fileName)
    {
        //open the excel file
        using (FileStream data = new FileStream(fileName, FileMode.Open))
        {
            //unzip
            ZipArchive archive = new ZipArchive(data);

            //select the correct file from the archive
            ZipArchiveEntry appxmlFile = archive.Entries.SingleOrDefault(e => e.FullName == "docProps/app.xml");

            //read the xml
            XDocument xdoc = XDocument.Load(appxmlFile.Open());

            //find the titles element
            XElement titlesElement = xdoc.Descendants().Where(e => e.Name.LocalName == "TitlesOfParts").Single();

            //extract the worksheet names
            return titlesElement
                .Elements().Where(e => e.Name.LocalName == "vector").Single()
                .Elements().Where(e => e.Name.LocalName == "lpstr")
                .Select(e => e.Value);
        }
    }
2

我喜欢@deathApril的想法，将工作表命名为1_Germany、2_UK、3_IRELAND。我也知道您需要为数百个工作表进行重命名的问题。如果您没有重命名工作表名称的问题，那么您可以使用此宏来为您完成。它只需要几秒钟就可以重命名所有工作表名称。不幸的是，ODBC、OLEDB按升序返回工作表名称。这是无法更改的。您必须使用COM或将名称重命名为按顺序排列。

Sub Macro1()
'
' Macro1 Macro
'

'
Dim i As Integer
For i = 1 To Sheets.Count
 Dim prefix As String
 prefix = i
 If Len(prefix) < 4 Then
  prefix = "000"
 ElseIf Len(prefix) < 3 Then
  prefix = "00"
 ElseIf Len(prefix) < 2 Then
  prefix = "0"
 End If
 Dim sheetName As String
 sheetName = Sheets(i).Name
 Dim names
 names = Split(sheetName, "-")
 If (UBound(names) > 0) And IsNumeric(names(0)) Then
  'do nothing
 Else
  Sheets(i).Name = prefix & i & "-" & Sheets(i).Name
 End If
Next

End Sub

更新： 在阅读@SidHoland有关BIFF的评论后，我想到了一个想法。以下步骤可以通过代码完成。不知道您是否真的想这样以获得相同顺序的工作表名称。如果您需要通过代码完成此操作，请告诉我需要帮助。

1. Consider XLSX as a zip file. Rename *.xlsx into *.zip
2. Unzip
3. Go to unzipped folder root and open /docprops/app.xml
4. This xml contains the sheet name in the same order of what you see.
5. Parse the xml and get the sheet names

更新： 另一个解决方案 - NPOI可能会有所帮助 http://npoi.codeplex.com/

 FileStream file = new FileStream(@"yourexcelfilename", FileMode.Open, FileAccess.Read);

      HSSFWorkbook  hssfworkbook = new HSSFWorkbook(file);
        for (int i = 0; i < hssfworkbook.NumberOfSheets; i++)
        {
            Console.WriteLine(hssfworkbook.GetSheetName(i));
        }
        file.Close();

这个解决方案适用于xls格式，我没有尝试过xlsx格式。
谢谢，
Esen
1
你不必重命名工作表或仅使用COM，因为我的答案展示了你可以使用DAO。我认为还有一种通过读取BIFF来检索它们的方法，但我仍在调查中。 - Sid Holland
1
@SidHolland：DAO是一个COM组件。在Server 2008中使用COM组件是一个问题，因此Steve选择了ADO.NET。 - Esen
我的大脑没有意识到DAO是一个COM组件，尽管我必须将其添加为COM引用才能使用它。感谢您的纠正。您的补充（将其重命名为zip并读取XML）非常聪明。我不知道那会起作用。到目前为止，这是唯一一种不使用COM就可以按顺序显示工作表的方法。+1！ - Sid Holland
1

这对我有用。从这里偷来的：如何获取Excel工作簿的第一页名称？

object opt = System.Reflection.Missing.Value;
Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook workbook = app.Workbooks.Open(WorkBookToOpen,
                                         opt, opt, opt, opt, opt, opt, opt,
                                         opt, opt, opt, opt, opt, opt, opt);
Excel.Worksheet worksheet = workbook.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
string firstSheetName = worksheet.Name;
3
你好。很高兴你有可工作的代码，但是它使用了Interop类，而它们在服务器上不可靠；例如，你不能在Windows Server 2008上运行这段代码。因此，你不能在Web应用程序或服务器端代码中使用它。这就是为什么我选择oledb而不是Interop的原因。 - Steve Cooper
1

试试这个。这里是按顺序获取工作表名称的代码。

private Dictionary<int, string> GetExcelSheetNames(string fileName)
{
    Excel.Application _excel = null;
    Excel.Workbook _workBook = null;
    Dictionary<int, string> excelSheets = new Dictionary<int, string>();
    try
    {
        object missing = Type.Missing;
        object readOnly = true;
        Excel.XlFileFormat.xlWorkbookNormal
        _excel = new Excel.ApplicationClass();
        _excel.Visible = false;
        _workBook = _excel.Workbooks.Open(fileName, 0, readOnly, 5, missing,
            missing, true, Excel.XlPlatform.xlWindows, "\\t", false, false, 0, true, true, missing);
        if (_workBook != null)
        {
            int index = 0;
            foreach (Excel.Worksheet sheet in _workBook.Sheets)
            {
                // Can get sheet names in order they are in workbook
                excelSheets.Add(++index, sheet.Name);
            }
        }
    }
    catch (Exception e)
    {
        return null;
    }
    finally
    {
        if (_excel != null)
        {

            if (_workBook != null)
                _workBook.Close(false, Type.Missing, Type.Missing);
            _excel.Application.Quit();
        }
        _excel = null;
        _workBook = null;
    }
    return excelSheets;
}
1
无法编译！ （在代码行 Excel.XlFileFormat.xlWorkbookNormal 处） - Michael Hutter
0
根据MSDN的说法，在Excel内部的电子表格中可能无法正常工作，因为Excel文件不是真正的数据库。因此，您将无法按其在工作簿中的可视化顺序获取工作表名称。
使用Interop获取工作表名称的代码：
添加对Microsoft Excel 12.0对象库的引用。
以下代码将以实际存储在工作簿中的顺序给出工作表名称，而不是排序后的名称。
示例代码：
using Microsoft.Office.Interop.Excel;

string filename = "C:\\romil.xlsx";

object missing = System.Reflection.Missing.Value;

Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

Microsoft.Office.Interop.Excel.Workbook wb =excel.Workbooks.Open(filename,  missing,  missing,  missing,  missing,missing,  missing,  missing,  missing,  missing,  missing,  missing,  missing,  missing,  missing);

ArrayList sheetname = new ArrayList();

foreach (Microsoft.Office.Interop.Excel.Worksheet  sheet in wb.Sheets)
{
    sheetname.Add(sheet.Name);
}
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接