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
2

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.