C# – How to retrieve Tab names from excel sheet using OpenXML

cexcelnetopenxmlopenxml-sdk

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.

Best Answer

Here is a handy helper method to get the Sheet corresponding to a WorksheetPart:

public static Sheet GetSheetFromWorkSheet
    (WorkbookPart workbookPart, WorksheetPart worksheetPart)
{
    string relationshipId = workbookPart.GetIdOfPart(worksheetPart);
    IEnumerable<Sheet> sheets = workbookPart.Workbook.Sheets.Elements<Sheet>();
    return sheets.FirstOrDefault(s => s.Id.HasValue && s.Id.Value == relationshipId);
}

Then you can get the name from the sheets Name-property:

Sheet sheet = GetSheetFromWorkSheet(myWorkbookPart, myWorksheetPart);
string sheetName = sheet.Name;

...this will be the "tab name" OP referred to.

For the record the opposite method would look like:

public static Worksheet GetWorkSheetFromSheet(WorkbookPart workbookPart, Sheet sheet)
{
    var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
    return worksheetPart.Worksheet;
}

...and with that we can also add the following method:

public static IEnumerable<KeyValuePair<string, Worksheet>> GetNamedWorksheets
    (WorkbookPart workbookPart)
{
    return workbookPart.Workbook.Sheets.Elements<Sheet>()
        .Select(sheet => new KeyValuePair<string, Worksheet>
            (sheet.Name, GetWorkSheetFromSheet(workbookPart, sheet)));
}

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:

IDictionary<string, WorkSheet> wsDict = GetNamedWorksheets(myWorkbookPart)
    .ToDictionary(kvp => kvp.Key, kvp => kvp.Value);

...or if you just want one specific sheet by name:

public static Sheet GetSheetFromName(WorkbookPart workbookPart, string sheetName)
{
    return workbookPart.Workbook.Sheets.Elements<Sheet>()
        .FirstOrDefault(s => s.Name.HasValue && s.Name.Value == sheetName);
}

(Then call GetWorkSheetFromSheet to get the corresponding Worksheet.)

Related Topic