Importing Excel Data in Datagridview using VB.Net
<p>Hello Everyone Good Afternoon. I have a Program in VB.Net that Exports Data in Datagridview into an Excel File like this</p>

<p><a href="https://i.stack.imgur.com/6qbzp.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6qbzp.png" alt="enter image description here"></a></p>

<p>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</p>

<p><a href="https://i.stack.imgur.com/0KcB1.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0KcB1.png" alt="enter image description here"></a></p>

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

<p>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 <code>0</code> or <code>0.00</code> in Column <code>Total</code> </p>

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

<p>Here is my code in Import</p>

<pre><code> 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 = '" &amp; TextBox1.Text &amp; "';", 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
</code></pre>

<p>TYSM for Future Help</p>