open xml reading from excel file

I had the same issue as the OP, and the answer above did not work for me.

I think this is the issue: when you create a document in Excel (not programmatically), you have 3 sheets by default and the WorksheetParts that has the row data for Sheet1 is the last WorksheetParts element, not the first.

I figured this out by putting a watch for document.WorkbookPart.WorksheetParts in Visual Studio, expanding Results, then looking at all of the sub elements until I found a SheetData object where HasChildren = true.

Try this:

// open the document read-only
SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, false);
SharedStringTable sharedStringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
string cellValue = null;

foreach (WorksheetPart worksheetPart in document.WorkbookPart.WorksheetParts)
{
    foreach (SheetData sheetData in worksheetPart.Worksheet.Elements<SheetData>())
    {
        if (sheetData.HasChildren)
        {
            foreach (Row row in sheetData.Elements<Row>())
            {
                foreach (Cell cell in row.Elements<Cell>())
                {
                    cellValue = cell.InnerText;

                    if (cell.DataType != null && cell.DataType == CellValues.SharedString)
                    {
                        Console.WriteLine("cell val: " + sharedStringTable.ElementAt(Int32.Parse(cellValue)).InnerText);
                    }
                    else
                    {
                        Console.WriteLine("cell val: " + cellValue);
                    }
                }
                Console.WriteLine("---");
            }
        }
    }
}
document.Close();

Read Large Excel : openxml has two approaches of DOM and SAX to read an excel. the DOM one consume more RAM resource since it loads the whole xml content(Excel file) in Memory but its strong typed approach. SAX in other hand is event base parse. more here

so if you are facing large excel file its better to use SAX.

the below code sample uses SAX approach and also handle two important scenario in excel file reading.

1. open xml skips the empty cells so your dataset faces displacement and wrong index.
2. you need to skip the empty rows also.

this function returns the exact actual index of the cell at the time and handle the first scenario. from here

private static int CellReferenceToIndex(Cell cell)
{
    int index = 0;
    string reference = cell.CellReference.ToString().ToUpper();
    foreach (char ch in reference)
    {
         if (Char.IsLetter(ch))
         {
              int value = (int)ch - (int)'A';
              index = (index == 0) ? value : ((index + 1) * 26) + value;
         }
         else
            return index;
    }
    return index;
}

code to read excel sax approach.

//i want to import excel to data table
dt = new DataTable();

using (SpreadsheetDocument document = SpreadsheetDocument.Open(path, false))
{
    WorkbookPart workbookPart = document.WorkbookPart;
    WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();

    OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);

    //row counter
    int rcnt = 0;

    while (reader.Read())
    {
        //find xml row element type 
        //to understand the element type you can change your excel file eg : test.xlsx to test.zip
        //and inside that you may observe the elements in xl/worksheets/sheet.xml
        //that helps to understand openxml better
        if (reader.ElementType == typeof(Row))
        {
            //create data table row type to be populated by cells of this row
            DataRow tempRow = dt.NewRow();

            //***** HANDLE THE SECOND SENARIO*****
            //if row has attribute means it is not a empty row
            if (reader.HasAttributes)
            { 
                //read the child of row element which is cells

                //here first element
                reader.ReadFirstChild();

                do
                {
                    //find xml cell element type 
                    if (reader.ElementType == typeof(Cell))
                    {
                        Cell c = (Cell)reader.LoadCurrentElement();
                        string cellValue;
                        int actualCellIndex = CellReferenceToIndex(c);

                        if (c.DataType != null && c.DataType == CellValues.SharedString)
                        {
                            SharedStringItem ssi = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(int.Parse(c.CellValue.InnerText));

                            cellValue = ssi.Text.Text;
                        }
                        else
                        {
                            cellValue = c.CellValue.InnerText;
                        }

                        //if row index is 0 its header so columns headers are added & also can do some headers check incase
                        if (rcnt == 0)
                        {
                            dt.Columns.Add(cellValue);
                        }
                        else
                        {
                            // instead of tempRow[c.CellReference] = cellValue;
                            tempRow[actualCellIndex] = cellValue;
                        }
                    }
                }
                while (reader.ReadNextSibling());

                //if its not the header row so append rowdata to the datatable
                if (rcnt != 0)
                {
                    dt.Rows.Add(tempRow);
                }

                rcnt++;
            }
        }
    }
}

One can really do this much easier using some Open XML wrapper like SlapKit or ClosedXML. Technically these two even share the same API since SlapKit is based on ClosedXML. So the code for both of these would look like this:

using var wb = new XLWorkbook("some-file.xlsx");
var ws = wb.Worksheet("sheet1");

foreach (var row in ws.Rows())
{
    foreach (var cell in row.Cells())
    {
        Console.Write("\t|\t" + cell.Value);
    }
    Console.WriteLine();
}

SlapKit documentation maybe explains this somewhat better.

Everything is explained in the accepted answer.
Here is just an extension method to solve the problem

public static string GetCellText(this Cell cell, in SharedStringTable sst)
{
    if (cell.CellValue is null)
        return string.Empty;

    if ((cell.DataType is not null) &&
        (cell.DataType == CellValues.SharedString))
    {
        int ssid = int.Parse(cell.CellValue.Text);
        return sst.ChildElements[ssid].InnerText;
    }

    return cell.CellValue.Text;
}