如何使用 OpenXML 从 Excel 工作表中检索选项卡名称
我有一个电子表格文档,其中有 182 列。我需要将电子表格数据逐个选项卡放入数据表中,但是当我从每个选项卡添加数据时,我需要找出选项卡名称是什么,并将选项卡名称添加到数据表中的列中。
这就是我设置数据表的方式。
然后,我在工作簿中循环并深入到 sheetData
对象并遍历每一行和每一列,获取单元格数据。
DataTable dt = new DataTable();
for (int i = 0; i <= col.GetUpperBound(0); i++)
{
try
{
dt.Columns.Add(new DataColumn(col[i].ToString(), typeof(string)));
}
catch (Exception e)
{
MessageBox.Show("Uploader Error" + e.ToString());
return null;
}
}
dt.Columns.Add(new DataColumn("SheetName", typeof(string)));
但是,在我用于数据表的字符串数组的末尾,我需要添加选项卡名称。当我在 Open XML 中循环工作表时,如何找到选项卡名称?
这是到目前为止我的代码:
using (SpreadsheetDocument spreadSheetDocument =
SpreadsheetDocument.Open(Destination, false))
{
WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
Workbook workbook = spreadSheetDocument.WorkbookPart.Workbook;
Sheets sheets =
spreadSheetDocument
.WorkbookPart
.Workbook
.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
OpenXmlElementList list = sheets.ChildElements;
foreach (WorksheetPart worksheetpart in workbook.WorkbookPart.WorksheetParts)
{
Worksheet worksheet = worksheetpart.Worksheet;
foreach (SheetData sheetData in worksheet.Elements<SheetData>())
{
foreach (Row row in sheetData.Elements())
{
string[] thisarr = new string[183];
int index = 0;
foreach (Cell cell in row.Elements())
{
thisarr[(index)] = GetCellValue(spreadSheetDocument, cell);
index++;
}
thisarr[182] = ""; //need to add tabname here
if (thisarr[0].ToString() != "")
{
dt.Rows.Add(thisarr);
}
}
}
}
}
return dt;
请注意:我之前确实从“list”的 InnerXML 属性中获取了选项卡名称,
OpenXmlElementList list = sheets.ChildElements;
但是我注意到,当我在电子表格中循环时,它没有以正确的顺序获取选项卡名称。
I have a spreadsheet document that has 182 columns in it. I need to place the spreadsheet data into a data table, tab by tab, but i need to find out as I'm adding data from each tab, what is the tab name, and add the tab name to a column in the data table.
This is how I set up the data table.
I then loop in the workbook and drill down to the sheetData
object and walk through each row and column, getting cell data.
DataTable dt = new DataTable();
for (int i = 0; i <= col.GetUpperBound(0); i++)
{
try
{
dt.Columns.Add(new DataColumn(col[i].ToString(), typeof(string)));
}
catch (Exception e)
{
MessageBox.Show("Uploader Error" + e.ToString());
return null;
}
}
dt.Columns.Add(new DataColumn("SheetName", typeof(string)));
However at the end of the string array that I use for the Data Table, I need to add the tab name. How can I find out the tab name as I'm looping in the sheet in Open XML?
Here is my code so far:
using (SpreadsheetDocument spreadSheetDocument =
SpreadsheetDocument.Open(Destination, false))
{
WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
Workbook workbook = spreadSheetDocument.WorkbookPart.Workbook;
Sheets sheets =
spreadSheetDocument
.WorkbookPart
.Workbook
.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
OpenXmlElementList list = sheets.ChildElements;
foreach (WorksheetPart worksheetpart in workbook.WorkbookPart.WorksheetParts)
{
Worksheet worksheet = worksheetpart.Worksheet;
foreach (SheetData sheetData in worksheet.Elements<SheetData>())
{
foreach (Row row in sheetData.Elements())
{
string[] thisarr = new string[183];
int index = 0;
foreach (Cell cell in row.Elements())
{
thisarr[(index)] = GetCellValue(spreadSheetDocument, cell);
index++;
}
thisarr[182] = ""; //need to add tabname here
if (thisarr[0].ToString() != "")
{
dt.Rows.Add(thisarr);
}
}
}
}
}
return dt;
Just a note: I did previously get the tab names from the InnerXML property of "list" in
OpenXmlElementList list = sheets.ChildElements;
however I noticed as I'm looping in the spreadsheet it does not get the tab names in the right order.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
![扫码二维码加入Web技术交流群](/public/img/jiaqun_03.jpg)
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
这里有一个方便的帮助方法来获取与 WorksheetPart 相对应的工作表:
然后您可以从工作表中获取名称 名称属性:
...这将是引用的“选项卡名称”OP。
作为记录,相反的方法如下所示:
...我们还可以添加以下方法:
现在您可以轻松枚举所有工作表,包括其名称。
如果您愿意,请将其全部放入字典中以进行基于名称的查找:
...或者如果您只想按名称查找一张特定工作表:(
然后调用
GetWorkSheetFromSheet
来获取相应的工作表。)Here is a handy helper method to get the Sheet corresponding to a WorksheetPart:
Then you can get the name from the sheets Name-property:
...this will be the "tab name" OP referred to.
For the record the opposite method would look like:
...and with that we can also add the following method:
Now you can easily enumerate through all Worksheets including their name.
Throw it all into a dictionary for name-based lookup if you prefer that:
...or if you just want one specific sheet by name:
(Then call
GetWorkSheetFromSheet
to get the corresponding Worksheet.)工作表名称存储在
Sheets
元素的WorkbookPart
中,该元素具有与 Excel 文件中的每个工作表对应的元素Sheet
的子元素。您所要做的就是从该Sheets
元素中获取正确的索引,这将是您在循环中所在的Sheet
。我在下面添加了一段代码来执行您想要的操作。The sheet names are stored in the
WorkbookPart
in aSheets
element which has children of elementSheet
which corresponds to each worksheet in the Excel file. All you have to do is grab the correct index out of thatSheets
element and that will be theSheet
you are on in your loop. I added a snippet of code below to do what you want.