C# 使用 Excel OleDb 按工作表顺序获取工作表名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1164698/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using Excel OleDb to get sheet names IN SHEET ORDER
提问by Steve Cooper
I'm using OleDb to read from an excel workbook with many sheets.
我正在使用 OleDb 从包含多张工作表的 Excel 工作簿中读取数据。
I need to read the sheet names, but I need them in the order they are defined in the spreadsheet; so If I have a file that looks like this;
我需要阅读工作表名称，但我需要按照它们在电子表格中定义的顺序；所以如果我有一个看起来像这样的文件；
|_____|_____|____|____|____|____|____|____|____|
|_____|_____|____|____|____|____|____|____|____|
|_____|_____|____|____|____|____|____|____|____|
\__GERMANY__/\__UK__/\__IRELAND__/
Then I need to get the dictionary
然后我需要得到字典
1="GERMANY",
2="UK",
3="IRELAND"
I've tried using OleDbConnection.GetOleDbSchemaTable(), and that gives me the list of names, but it alphabetically sorts them. The alpha-sort means I don't know which sheet number a particular name corresponds to. So I get;
我试过使用OleDbConnection.GetOleDbSchemaTable()，这给了我名字的列表，但它按字母顺序对它们进行了排序。字母排序意味着我不知道特定名称对应于哪个工作表编号。所以我明白了；
GERMANY, IRELAND, UK
which has changed the order of UKand IRELAND.
这已经改变了UK和的顺序IRELAND。
The reason I need it to be sorted is that I have to let the user choose a range of data by name or index; they can ask for 'all the data from GERMANY to IRELAND' or 'data from sheet 1 to sheet 3'.
我需要对它进行排序的原因是我必须让用户按名称或索引选择数据范围；他们可以要求“从德国到爱尔兰的所有数据”或“从表 1 到表 3 的数据”。
Any ideas would be greatly appreciated.
任何想法将不胜感激。
if I could use the office interop classes, this would be straightforward. Unfortunately, I can't because the interop classes don't work reliably in non-interactive environments such as windows services and ASP.NET sites, so I needed to use OLEDB.
如果我可以使用办公室互操作类，这将很简单。不幸的是，我不能，因为互操作类在 Windows 服务和 ASP.NET 站点等非交互式环境中不能可靠地工作，所以我需要使用 OLEDB。
采纳答案by Jeremy Breece
Can't find this in actual MSDN documentation, but a moderator in the forums said
在实际的 MSDN 文档中找不到这个，但论坛中的版主说
I am afraid that OLEDB does not preserve the sheet order as they were in Excel
我担心 OLEDB 不会像在 Excel 中那样保留工作表顺序
Excel Sheet Names in Sheet Order
Seems like this would be a common enough requirement that there would be a decent workaround.
似乎这将是一个足够普遍的要求，它将有一个体面的解决方法。
回答by James
Can you not just loop through the sheets from 0 to Count of names -1? that way you should get them in the correct order.
你不能只循环从 0 到 Count of names -1 的工作表吗？这样你应该以正确的顺序得到它们。
Edit
编辑
I noticed through the comments that there are a lot of concerns about using the Interop classes to retrieve the sheet names. Therefore here is an example using OLEDB to retrieve them:
我通过评论注意到使用 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();
}
}
}
Extracted from Articleon the CodeProject.
摘自CodeProject 上的文章。
回答by evilHyman
This worked for me. Stolen from here: How do you get the name of the first page of an excel workbook?
这对我有用。从这里被盗：您如何获得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;
回答by Ravi Shankar
Try this. Here is the code to get the sheet names in order.
尝试这个。这是按顺序获取工作表名称的代码。
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;
}
回答by Ravi Shankar
Since above code do not cover procedures for extracting list of sheet name for Excel 2007,following code will be applicable for both Excel(97-2003) and Excel 2007 too:
由于以上代码不包括提取 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;
}
Above function returns list of sheet in particular excel file for both excel type(97,2003,2007).
以上函数返回两种 excel 类型 (97,2003,2007) 的工作表列表，特别是 excel 文件。
回答by Romil Kumar Jain
As per MSDN, In a case of spreadsheets inside of Excel it might not work because Excel files are not real databases. So you will be not able to get the sheets name in order of their visualization in workbook.
根据 MSDN，在 Excel 中的电子表格的情况下，它可能不起作用，因为 Excel 文件不是真正的数据库。因此，您将无法按照工作簿中的可视化顺序获取工作表名称。
Code to get sheets name as per their visual appearance using interop:
使用互操作根据视觉外观获取工作表名称的代码：
Add reference to Microsoft Excel 12.0 Object Library.
添加对 Microsoft Excel 12.0 对象库的引用。
Following code will give the sheets name in the actual order stored in workbook, not the sorted name.
以下代码将以存储在工作簿中的实际顺序给出工作表名称，而不是排序名称。
Sample Code:
示例代码：
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);
}
回答by Esen
I like the idea of @deathApril to name the sheets as 1_Germany, 2_UK, 3_IRELAND. I also got your issue to do this rename for hundreds of sheets. If you don't have a problem to rename the sheet name then you can use this macro to do it for you. It will take less than seconds to rename all sheet names. unfortunately ODBC, OLEDB return the sheet name order by asc. There is no replacement for that. You have to either use COM or rename your name to be in the order.
我喜欢@deathApril 将工作表命名为 1_Germany、2_UK、3_IRELAND 的想法。我也遇到了你的问题，要对数百张纸进行重命名。如果您在重命名工作表名称时没有问题，那么您可以使用此宏为您完成此操作。重命名所有工作表名称只需不到几秒钟的时间。不幸的是 ODBC，OLEDB 按 asc 返回工作表名称顺序。没有什么可以替代的。您必须使用 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
UPDATE: After reading @SidHoland comment regarding BIFF an idea flashed. The following steps can be done through code. Don't know if you really want to do that to get the sheet names in the same order. Let me know if you need help to do this through code.
更新：在阅读@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
UPDATE: Another solution - NPOI might be helpful here http://npoi.codeplex.com/
更新：另一个解决方案 - 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();
This solution works for xls. I didn't try xlsx.
此解决方案适用于 xls。我没有尝试 xlsx。
Thanks,
谢谢，
Esen
埃森
回答by kraeppy
Another way:
其它的办法：
a xls(x) file is just a collection of *.xml files stored in a *.zip container. unzip the file "app.xml" in the folder docProps.
xls(x) 文件只是存储在 *.zip 容器中的 *.xml 文件的集合。将文件“app.xml”解压到文件夹 docProps 中。
<?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>
The file is a german file (Arbeitsbl?tter = worksheets). The table names (Tabelle3 etc) are in the correct order. You just need to read these tags;)
该文件是德国文件（Arbeitsbl?tter = 工作表）。表名（Tabelle3 等）的顺序正确。你只需要阅读这些标签；)
regards
问候
回答by rdans
I have created the below function using the information provided in the answer from @kraeppy (https://stackoverflow.com/a/19930386/2617732). This requires the .net framework v4.5 to be used and requires a reference to System.IO.Compression. This only works for xlsx files and not for the older xls files.
我使用@kraeppy ( https://stackoverflow.com/a/19930386/2617732)的答案中提供的信息创建了以下函数。这需要使用 .net framework 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);
}
}
回答by Vern Hamberg
I don't see any documentation that says the order in app.xml is guaranteed to be the order of the sheets. It PROBABLY is, but not according to the OOXML specification.
我没有看到任何文档说 app.xml 中的顺序保证是工作表的顺序。它可能是，但不是根据 OOXML 规范。
The workbook.xml file, on the other hand, includes the sheetId attribute, which does determine the sequence - from 1 to the number of sheets. This is according to the OOXML specification. workbook.xml is described as the place where the sequence of the sheets is kept.
另一方面，workbook.xml 文件包含 sheetId 属性，该属性确定顺序 - 从 1 到工作表数。这是根据 OOXML 规范。workbook.xml 被描述为保存工作表顺序的地方。
So reading workbook.xml after it is extracted form the XLSX would be my recommendation. NOT app.xml. Instead of docProps/app.xml, use xl/workbook.xml and look at the element, as shown here -
因此，我的建议是在从 XLSX 中提取 workbook.xml 后阅读它。不是 app.xml。而不是 docProps/app.xml，使用 xl/workbook.xml 并查看元素，如下所示 -
`
`
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<fileVersion appName="xl" lastEdited="5" lowestEdited="5" rupBuild="9303" />
<workbookPr defaultThemeVersion="124226" />
- <bookViews>
<workbookView xWindow="120" yWindow="135" windowWidth="19035" windowHeight="8445" />
</bookViews>
- <sheets>
<sheet name="By song" sheetId="1" r:id="rId1" />
<sheet name="By actors" sheetId="2" r:id="rId2" />
<sheet name="By pit" sheetId="3" r:id="rId3" />
</sheets>
- <definedNames>
<definedName name="_xlnm._FilterDatabase" localSheetId="0" hidden="1">'By song'!$A:$O</definedName>
</definedNames>
<calcPr calcId="145621" />
</workbook>
`
`
