使用 VB.Net 在 Datagridview 中导入 Excel 数据

Importing Excel Data in Datagridview using VB.Net

提问by

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

大家好，下午好。我在 VB.Net 中有一个程序可以将 Datagridview 中的数据导出到这样的 Excel 文件中

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

导出后，我将编辑 Excel 文件并将其返回到我的 Datagridview 中，但遗憾的是，这是我的输出

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

正如你在两张图片上看到的那样，它们是不同的。没有逗号，没有小数位和0in 列Total也被添加，但应该不是。

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

我的问题是如何在excel中实现相同的格式？把逗号和小数点在列数，不包括具有行0或0.00在列Total

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

我想要的是我的 Datagridview 数据也与 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

TYSM 未来帮助

回答by coder32

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:

首先导入时，我看到您正在创建列名。没有必要这样做。在扩展属性中设置 DHR=Yes。这将采用 excel 中的列名。还：

use IMEX=1 to avoid crash.

使用 IMEX=1 避免崩溃。

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.

始终使用 IMEX=1 是一种更安全的方式来检索混合数据列的数据。考虑这样一种情况：一个 Excel 文件可能工作正常，导致该文件的数据导致驱动程序猜测一种数据类型，而包含其他数据的另一个文件导致驱动程序猜测另一种数据类型。这可能会导致您的应用程序崩溃，尤其是在您编辑 Excel 文件时。

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

回答by Dennis Rey Salinel

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

您需要使用单元格格式编号示例：defaultcellstyle.format="N2"