VB.NET OpenXML to Read Excel File

Question

I have been searching for a way to effectively read an Excel file and have found the following code for parsing and reading a large spreadsheet:

Public Sub ExcelProcessing()

    Dim strDoc As String = "C:\Documents and Settings\Practice.xlsx"
    Dim txt As String

    Dim spreadsheetDocument As SpreadsheetDocument = spreadsheetDocument.Open(strDoc, False)

    Dim workbookPart As WorkbookPart = spreadsheetDocument.WorkbookPart
    Dim worksheetPart As WorksheetPart = workbookPart.WorksheetParts.First()

    Dim reader As OpenXmlReader = OpenXmlReader.Create(worksheetPart)
    Dim text As String
    While reader.Read()

        If reader.ElementType = GetType(CellValue) Then


            text = reader.GetText()
            MessageBox.Show(text)

        End If


    End While

The issue is where I assign reader.GetText() to my string. The value passed is a small integer while the actual cell value is a string. The messagebox fires once for each populated cell, so this tells me the code is finding cells that contain values; however, I can not extract the actual "inner text" of the cell.

Thoughts? Suggestions?

It appears this article. ([link]codeproject.com/Articles/15593/… my question, although I will still need to see if what I am trying can be accomplished with the OpenXmlReader. Any guidance would be greatly appreciated! — Josh Weston
– Josh Weston, Commented Mar 21, 2013 at 21:48

Josh Weston · Accepted Answer · 2013-03-22 12:10:05Z

1

I found my answer; I need to reference the sharedstringtable and pull out the inner text from there:

    Dim strDoc As String = "C:\Documents and Settings\Practice.xlsx"
    Dim txt As String

    Dim spreadsheetDocument As SpreadsheetDocument = spreadsheetDocument.Open(strDoc, False)

    Dim workbookPart As WorkbookPart = spreadsheetDocument.WorkbookPart
    Dim shareStringPart As SharedStringTablePart = workbookPart.SharedStringTablePart


    For Each Item As SharedStringItem In shareStringPart.SharedStringTable.Elements(Of SharedStringItem)()

        MessageBox.Show(Item.InnerText)

    Next

answered Mar 22, 2013 at 12:10

Josh Weston

1,93024 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VB.NET OpenXML to Read Excel File

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related