Parsing data from xlsx worksheet return wrong data (Open XML SDK)

Question

I used the code example off of (Open XML SDK) website and it opens the file and reads the data but returns back values but they are not the values in the cells. I've attached my code and a link to a sample xlsx file.

https://file-examples.com/wp-content/uploads/2017/02/file_example_XLSX_10.xlsx

Private Sub ReadExcelFileDOM(ByVal fileName As String)
        Using spreadsheetDocument As SpreadsheetDocument = SpreadsheetDocument.Open(fileName, False)
            Dim workbookPart As WorkbookPart = spreadsheetDocument.WorkbookPart
            Dim worksheetPart As WorksheetPart = workbookPart.WorksheetParts.First()
            Dim sheetData As SheetData = worksheetPart.Worksheet.Elements(Of SheetData)().First()
            Dim text As String
            For Each r As Row In sheetData.Elements(Of Row)()
                For Each c As Cell In r.Elements(Of Cell)()
                    text = c.CellValue.Text
                    Console.Write(text & " ")
                Next
            Next
            Console.WriteLine()
            Console.ReadKey()
        End Using
    End Sub

The code was copied from here: https://learn.microsoft.com/en-us/office/open-xml/how-to-parse-and-read-a-large-spreadsheet — glenn, Jan 24 '20 at 16:25

score 0 · Answer 1 · answered Jan 25 '20 at 20:29

Similar question was asked here: Retrieve cell value issue with open xml sdk

And in your case here is a solution:

In the link that you have provided you have comment in the code:

// Note that the code below works only for cells that contain numeric values.

And your sample file contains string values. If you want to get a non-numeric value, you should use SharedStringTable - Working with the SharedStringTable.

You should add if/switch statement to your code, here you have an example based on this article:

If c.DataType.Value == Excel.CellValues.SharedString Then
   Dim stringTable As SharedStringTable = workbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault().SharedStringTable
   text = stringTable.ElementAt(int.Parse(c.InnerText)).InnerText

Parsing data from xlsx worksheet return wrong data (Open XML SDK)

1 Answers1