Read excel by sheet name with OpenXML

Question

I am new at OpenXML c# and I want to read rows from excel file. But I need to read excel sheet by name. this is my sample code that reads first sheet:

 using (var spreadSheet = SpreadsheetDocument.Open(path, true))
                {
                    WorkbookPart workbookPart = spreadSheet.WorkbookPart;
                    WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
                    SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();

                    foreach (Row r in sheetData.Elements<Row>())
                    {
                        foreach (Cell c in r.Elements<Cell>())
                        {
                            if (c.DataType != null && c.DataType == CellValues.SharedString)
                            {
                               // reading cells
                            }
                        }
                    }

But how can I find by sheet name and read cells.

Toothpick

Answer 1

I've done it like in the code snippet below. It's basically Workbook->Spreadsheet->Sheet then getting the Name attribute of the sheet.

The basic underling xml looks like this:

<x:workbook>
 <x:sheets>
    <x:sheet name="Sheet1" sheetId="1" r:id="rId1" />
    <x:sheet name="TEST sheet Name" sheetId="2" r:id="rId2" />
  </x:sheets>
</x:workbook>

The id value is what the Open XML package uses internally to identify each sheet and link it with the other XML parts. That's why the line of code that follows identifying the name uses GetPartById to pick up the WorksheetPart.

using (SpreadsheetDocument doc = SpreadsheetDocument.Open(path, false))
{
    WorkbookPart bkPart = doc.WorkbookPart;
    DocumentFormat.OpenXml.Spreadsheet.Workbook workbook = bkPart.Workbook;
    DocumentFormat.OpenXml.Spreadsheet.Sheet s = workbook.Descendants<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Where(sht => sht.Name == "Sheet1").FirstOrDefault();
    WorksheetPart wsPart = (WorksheetPart)bkPart.GetPartById(s.Id);
    DocumentFormat.OpenXml.Spreadsheet.SheetData sheetdata = wsPart.Worksheet.Elements<DocumentFormat.OpenXml.Spreadsheet.SheetData>().FirstOrDefault();

    foreach (DocumentFormat.OpenXml.Spreadsheet.Row r in sheetdata.Elements<DocumentFormat.OpenXml.Spreadsheet.Row>())
    {
        DocumentFormat.OpenXml.Spreadsheet.Cell c = r.Elements<DocumentFormat.OpenXml.Spreadsheet.Cell>().First();
        txt += c.CellValue.Text + Environment.NewLine;
    }
    this.txtMessages.Text += txt;
}

Rawhide