Importing Excel Data in Datagridview using VB.Net

Question

Hello Everyone Good Afternoon. I have a Program in VB.Net that Exports Data in Datagridview into an Excel File like this

After Exporting it, I will Edit the Excel File and Return it into my Datagridview but sad to say here is my output to that

As what you see on both pictures they are Different. No Commas,No Decimal Places and the 0 in Column Total is also added but Supposed to be it is not.

My Questions is How can I achieve the same format in excel? Put Commas and Decimal Point in Number Columns and Do not Include the Rows that has a 0 or 0.00 in Column Total

All I want is that my Datagridview Data is also same as the Format in Excel.

Here is my code in Import

 Dim conn As OleDbConnection

        Dim dta As OleDbDataAdapter

        Dim dts As DataSet
        Dim excel As String
        Dim OpenFileDialog As New OpenFileDialog

        OpenFileDialog.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments
        OpenFileDialog.Filter = "All Files (*.*)|*.*|Excel files (*.xlsx)|*.xlsx|CSV Files (*.csv)|*.csv|XLS Files (*.xls)|*xls"

        If (OpenFileDialog.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then

            Dim fi As New FileInfo(OpenFileDialog.FileName)
            Dim FileName As String = OpenFileDialog.FileName

            excel = fi.FullName
            conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties=Excel 12.0;")
            dta = New OleDbDataAdapter("Select * From [Sheet1$]", conn)
            dts = New DataSet
            dta.Fill(dts, "[Sheet1$]")
            DataGridView1.DataSource = dts
            DataGridView1.DataMember = "[Sheet1$]"
            conn.Close()

            With DataGridView1
                .RowHeadersVisible = False
                .Columns(0).HeaderCell.Value = "Item Code"
                .Columns(1).HeaderCell.Value = "Description"
                .Columns(2).HeaderCell.Value = "Delivery Date"
                .Columns(3).HeaderCell.Value = "Stock On-Hand"
                .Columns(4).HeaderCell.Value = "Ordering Level"
                .Columns(5).HeaderCell.Value = "Order Qty"
                .Columns(6).HeaderCell.Value = "Approved Qty"
                .Columns(7).HeaderCell.Value = "UOM"
                .Columns(8).HeaderCell.Value = "Unit Price"
                .Columns(9).HeaderCell.Value = "Total"
                .Columns(10).HeaderCell.Value = "Remarks"
            End With
            DataGridView1.Columns.Item(0).Width = 70
            DataGridView1.Columns.Item(1).Width = 180
            DataGridView1.Columns.Item(2).Width = 70
            DataGridView1.Columns.Item(3).Width = 70
            DataGridView1.Columns.Item(4).Width = 70
            DataGridView1.Columns.Item(5).Width = 70
            DataGridView1.Columns.Item(6).Width = 70
            DataGridView1.Columns.Item(7).Width = 61
            DataGridView1.Columns.Item(8).Width = 76
            DataGridView1.Columns.Item(9).Width = 86
            DataGridView1.Columns.Item(10).Width = 125
            DataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            For Each row As DataGridViewRow In DataGridView1.Rows
                row.Cells("Total").Value = row.Cells("Order Qty").Value * row.Cells("Unit Price").Value
            Next

            Dim total As Double = 0
            For i As Integer = 0 To DataGridView1.RowCount - 1
                total += DataGridView1.Rows(i).Cells(9).Value

            Next
            TextBox7.Text = total


            addnewnewandrefresh()
            DELETEROW()



        Else

            Dim con1 As MySqlConnection = New MySqlConnection("server=localhost;userid=root;password=admin1950;database=inventory")
            Dim sql1 As MySqlCommand = New MySqlCommand("select ItemCode,Description,DeliveryDate,StockOnHand,OrderingLevel,OrderQty,ApprovedQty,UoM,UnitPrice,Total,Remarks from final_purch where PRNumber = '" & TextBox1.Text & "';", con1)
            Dim ds1 As DataSet = New DataSet
            Dim adapter1 As MySqlDataAdapter = New MySqlDataAdapter
            con1.Open()
            adapter1.SelectCommand = sql1
            adapter1.Fill(ds1, "MyTable")
            DataGridView1.DataSource = ds1.Tables(0)
            con1.Close()
            With DataGridView1
                .RowHeadersVisible = False
                .Columns(0).HeaderCell.Value = "Item Code"
                .Columns(1).HeaderCell.Value = "Description"
                .Columns(2).HeaderCell.Value = "Delivery Date"
                .Columns(3).HeaderCell.Value = "Stock On-Hand"
                .Columns(4).HeaderCell.Value = "Ordering Level"
                .Columns(5).HeaderCell.Value = "Order Qty"
                .Columns(6).HeaderCell.Value = "Approved Qty"
                .Columns(7).HeaderCell.Value = "UOM"
                .Columns(8).HeaderCell.Value = "Unit Price"
                .Columns(9).HeaderCell.Value = "Total"
                .Columns(10).HeaderCell.Value = "Remarks"
            End With
            DataGridView1.Columns.Item(0).Width = 70
            DataGridView1.Columns.Item(1).Width = 180
            DataGridView1.Columns.Item(2).Width = 70
            DataGridView1.Columns.Item(3).Width = 70
            DataGridView1.Columns.Item(4).Width = 70
            DataGridView1.Columns.Item(5).Width = 70
            DataGridView1.Columns.Item(6).Width = 70
            DataGridView1.Columns.Item(7).Width = 61
            DataGridView1.Columns.Item(8).Width = 76
            DataGridView1.Columns.Item(9).Width = 86
            DataGridView1.Columns.Item(10).Width = 125
            DataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            With Me.DataGridView1
                .RowsDefaultCellStyle.BackColor = Color.WhiteSmoke
                .AlternatingRowsDefaultCellStyle.BackColor = Color.Lavender
            End With


            MsgBox("Importing Data has been Cancelled")


        End If

TYSM for Future Help

Answer 1

you need to use cell format numbers Example: defaultcellstyle.format="N2"

Answer 2

Try this:

When importing first of all I see you are creating the column name. No need to do so. In the extended Properties set DHR=Yes. This will take the column names as is from excel. Also:

use IMEX=1 to avoid crash.

To always use IMEX=1 is a safer way to retrieve data for mixed data columns. Consider the scenario that one Excel file might work fine cause that file's data causes the driver to guess one data type while another file, containing other data, causes the driver to guess another data type. This can cause your app to crash especially if you are editing the Excel file.

";Extended Properties='Excel 12.0;HDR=YES;IMEX=1';