title: Importing Excel Data in Datagridview using VB.Net
body: <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>