如何从Excel工作表中使用的OpenXML检索标签名称 [英] How to retrieve Tab names from excel sheet using OpenXML

问题描述

我为preadsheet文件有182列在其中。我需要到S preadsheet数据放到一个数据表，标签由标签，但我需要找出因为我从每个标签添加数据，什么是标签名称，并添加标签名称的列在数据表中。

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.

我然后循环工作簿中并深入到 sheetData 对象，遍历每个行和列，获取单元格的数据。

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

不过在字符串数组，我使用的数据表的末尾，我需要添加的标签名称。我如何才能知道因为我在循环中的Open XML工作表的标签名称？

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?

下面是我的code到目前为止：

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;

刚一说明：我没有previously得到的名单的InnerXML属性选项卡名称中的

Just a note: I did previously get the tab names from the InnerXML property of "list" in

OpenXmlElementList list = sheets.ChildElements;

不过，我发现因为我循环在s preadsheet它没有得到标签名以正确的顺序。

however I noticed as I'm looping in the spreadsheet it does not get the tab names in the right order.

推荐答案

工作表名称都存储在 WorkbookPart 在钢板元素有元素的子元素页相当于在Excel文件中的每个工作表。所有你需要做的就是抓住正确的索引出钢板元素的这一点，这将是页你是在在你的循环。我添加了以下code片段做你想做的。

The sheet names are stored in the WorkbookPart in a Sheets element which has children of element Sheet which corresponds to each worksheet in the Excel file. All you have to do is grab the correct index out of that Sheets element and that will be the Sheet you are on in your loop. I added a snippet of code below to do what you want.

int sheetIndex = 0;
foreach (WorksheetPart worksheetpart in workbook.WorkbookPart.WorksheetParts)
{                     
    Worksheet worksheet = worksheetpart.Worksheet;

    // Grab the sheet name each time through your loop
    string sheetName = workbookPart.Workbook.Descendants<Sheet>().ElementAt(sheetIndex).Name;

    foreach (SheetData sheetData in worksheet.Elements<SheetData>())
    {

       ...
    }
    sheetIndex++;
}

这篇关于如何从Excel工作表中使用的OpenXML检索标签名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！