4

I'm developing a windows forms written in VB.NET.

In a form I need to import an Excel file and read it's data then show or store it in database. When I get cells containing numbers, the value is right but when I get cells containing strings, it just returns single digit numbers.

Here is the code :

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim frm_sample As New frm_saple_importexcel
    frm_sample.ShowDialog()

    Dim ofd As New OpenFileDialog
    ofd.Filter = $"Excel File (*.xlsx)|*.xlsx|Excel File (*.xls)|*.xls|All files (*.*)|*.*"
    ofd.ShowDialog()

    Using spreadsheetDocument As SpreadsheetDocument =
        SpreadsheetDocument.Open(ofd.FileName, False)
        Dim dtSet As DataSet = New DataSet("JustAName")
        Dim workbookPart As WorkbookPart = spreadsheetDocument.WorkbookPart
        Dim worksheetPart As WorksheetPart = workbookPart.WorksheetParts.First()
        Dim sheetData As SheetData = worksheetPart.Worksheet.Elements(Of SheetData)().First()
        Dim dt = New DataTable("tt")
        dt.Columns.Add(New DataColumn("k_code"))
        dt.Columns.Add(New DataColumn("k_name"))
        dt.Columns.Add(New DataColumn("k_unit"))
        dt.Columns.Add(New DataColumn("k_group_name"))

        For Each r As Row In sheetData.Elements(Of Row)()
            Dim row As DataRow = dt.NewRow
            row("k_code") = r.ChildElements.ElementAt(0).InnerText
            row("k_name") = r.ChildElements.ElementAt(1).InnerText
            row("k_unit") = r.ChildElements.ElementAt(2).InnerText
            row("k_group_name") = r.ChildElements.ElementAt(3).InnerText
            dt.Rows.Add(row)
        Next

        dtSet.Tables.Add(dt)
        DataGridView1.DataSource = dtSet.Tables(0)
    End Using
End Sub
Hooman Limouee
  • 1,143
  • 2
  • 21
  • 43
  • Can you isolate the problem?, at what line do you expect a string, and what is the XML file? – Dennis Oct 08 '18 at 06:32
  • 3
    You need to look up the SharedStringTable - eg https://stackoverflow.com/a/23104151 and https://learn.microsoft.com/en-us/office/open-xml/working-with-the-shared-string-table – shunty Oct 09 '18 at 13:53

0 Answers0