使用VB.Net在Datagridview中导入Excel数据 [英] Importing Excel Data in Datagridview using VB.Net

问题描述

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

导出之后，我会编辑的Excel文件，并返回到我的DataGridView但伤心地说这里是我的输出到

正如您在两张图片上看到的一样，它们是不同的。还没有添加逗号，没有小数位，并且在列总计中也添加了 0 ，但假设不是。 / p>

我的问题是如何在excel中获得相同的格式？将逗号和小数点放在数字列中，并且不包括在<$列中具有 0 或 0.00 的行c $ c>总计

我想要的是我的Datagridview数据也与Excel中的Format相同。

这是我在导入中的代码

  Dim conn作为OleDbConnection 
 
 Dim dta作为OleDbDataAdapter 
 
 Dim dts作为数据集
 Dim excel作为字符串
 Dim OpenFileDialog作为新的OpenFileDialog 
 
 OpenFileDialog.InitialDirectory = My.Computer。 FileSystem.SpecialDirectories.MyDocuments 
 OpenFileDialog.Filter =所有文件（*。*）| *。* | Excel文件（* .xlsx）| * .xlsx | CSV文件（* .csv）| * .csv | XLS文件（* .xls）| * xls 
 
如果（OpenFileDialog.ShowDialog（Me）= System.Windows.Forms.DialogResult.OK）然后
 
 Dim fi As新的FileInfo（OpenFileDialog.FileName）
昏暗的FileName作为字符串= OpenFileDialog.FileName 
 
 excel = fi.FullName 
 conn =新建OleDbConnection（ Provider = Microsoft.ACE.OLEDB.12.0; Data Source = + excel +;扩展属性= Excel 12.0;）
 dta = New OleDbDataAdapter（ Select * From [Sheet1 $]，conn）
 dts =新数据集
 dta.Fill（dts， [Sheet1 $]）
 DataGridView1.DataSource = dts 
 DataGridView1.DataMember = [Sheet1 $] 
 conn.Close（）
 
与DataGridView1 
 .RowHeadersVisible = False 
 .Columns（0 ）.HeaderCell.Value =商品代码 
 .Columns（1）.HeaderCell.Value =描述 
 .Columns（2）.HeaderCell.Value =交货日期 
。 Columns（3）.HeaderCell.Value =现有库存 
 .Columns（4）.HeaderCell.Value =订购级别 
 .Columns（5）.HeaderCell.Value =订购数量 
 .Columns（6）.HeaderCell。值=批准的数量 
 .Columns（7）.HeaderCell.Value = UOM 
 .Columns（8）.HeaderCell.Value =单价 
 .Columns（9） .HeaderCell.Value =总计 
 .Columns（10）.HeaderCell.Value =备注 
以
结尾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.Ali gnment = DataGridViewContentAlignment.MiddleCenter 
每行作为DataGridView1.Rows中的DataGridViewRow 
 row.Cells（ Total）。Value = row.Cells（ Order Qty）。Value * row.Cells（单价）。值
下一个
 
总计为Double的Dim = 0 
对于i作为Integer = 0到DataGridView1.RowCount-1 
总计+ = DataGridView1。行（i）。单元格（9）。值
 
下一个
 TextBox7.Text =总计
 
 
 addnewnewandrefresh（）
 DELETEROW（ ）
 
 
 
其他
 
 Dim con1 As MySqlConnection = New MySqlConnection（ server = localhost; userid = root; password = admin1950; database = inventory ）
 Dim sql1 As MySqlCommand = New MySqlCommand（选择ItemCode，Description，DeliveryDate，StockOnHand，OrderingLevel，OrderQty，ApprovedQty，UoM，UnitPrice，Total，来自final_purch的注释，其中PRNumber ='& TextBox1.Text& ';，con1）
 Dim ds1作为DataSet =新数据集
 Dim adapter1作为MySqlDataAdapter = New MySqlDataAdapter 
 con1.Open（）
 adapter1.SelectCommand = sql1 
 adapter1.Fill（ds1， MyTable）
 DataGridView1.DataSource = ds1.Tables（0）
 con1.Close（）
与DataGridView1 
 .RowHeadersVisible = False 
 .Columns（0）.HeaderCell.Value =物品代码 
 .Columns（1）.HeaderCell.Value =描述 
 .Columns（2）.HeaderCell.Value =交付日期 
 .Columns（3）.HeaderCell.Value =现货供应 
 .Columns（4）.HeaderCell.Value =订购级别 
 .Columns（5）.HeaderCell .Value =订单数量 
 .Columns（6）.HeaderCell.Value =批准数量 
 .Columns（7）.HeaderCell.Value = UOM 
 .Columns（8 ）.HeaderCell.Va lue =单价 
 .Columns（9）.HeaderCell.Value =总计 
 .Columns（10）.HeaderCell.Value =备注 
以
结尾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 
和Me.DataGridView1 
 .RowsDefaultCell Style.BackColor = Color.WhiteSmoke 
 .AlternatingRowsDefaultCellStyle.BackColor = Color.Lavender 
结尾为
 
 
 MsgBox（导入数据已被取消）
 
 
如果
  

TYSM以获得未来帮助

解决方案

您需要使用单元格格式数字
例如：defaultcellstyle.format = N2

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

解决方案

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

这篇关于使用VB.Net在Datagridview中导入Excel数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！