使用OLEDB将数据插入对应的EXCEL表格中

 Public Sub ExportDataGridview(ByVal gridView As Object, ByVal isShowExcele As Boolean)

        On Error GoTo showerr

        Dim Mytable As New DataTable

        Mytable = CType(gridView.DataSource, DataTable )

        If Mytable Is Nothing Then

            MessageBox.Show("没有记录不能导出数据" , "错误", MessageBoxButtons.OK, MessageBoxIcon.Warning)

            Exit Sub

        End If

        If Mytable.Rows.Count > 0 Then

            Dim MyFileName As String

            Dim FileName As String = String.Empty

            With SaveFileDialog1

                .AddExtension = True '如果用户忘记添加扩展名，将自动家上

                .DefaultExt = "xlsx" '默认扩展名

                .Filter = "Excel2003文件(*.xlsx)|*.xlsx"

                .Title = "导出文件保存到..."

                If .ShowDialog = Windows.Forms.DialogResult .OK Then

                    FileName = .FileName

                Else

                    Exit Sub

                End If

            End With

            MyFileName = Mid(FileName, InStr(FileName, "."), FileName.Length())

            If MyFileName = "" Then

                Exit Sub

            End If

            MyFileName = LCase(MyFileName)

            If MyFileName = ".xlsx" Then

                If Not File.Exists(FileName) Then

                    Dim excel As Object

                    excel = CreateObject( "Excel.application")

                    excel.Workbooks.add.SaveAs(FileName)

                    excel.quit()

                    excel = Nothing

                End If

                SaveToExcel(FileName)

                MessageBox.Show("数据导出成功！" , "成功", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)

            Else

                Exit Sub

            End If

        Else

            MessageBox.Show("没有记录不能导出数据" , "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error)

        End If

        Exit Sub

showerr:

        MsgBox( "ERROR，错误代码：" & Err.Description)

    End Sub

    Public Sub SaveToExcel(ByVal Path As String )

        Try

            Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = " + Path + ";Extended Properties =Excel 12.0"

            Dim conn As New OleDbConnection(strConn)

            conn.Open()

            Dim cmd As New OleDbCommand

            cmd.Connection = conn

            cmd.CommandText = "CREATE TABLE Sheetone (备注 String,销售订单号 String,订单日期 String,iSOsID String,客户编码 String,客户名称 String,业务员 String,收货地址 String,收货人 String,收货联系电话 String," & _

"制单人 String,图书编码 String,图书名称 String,要求到货仓库 String,实际到货仓库 String,仓库情况 String,订单关闭情况 String,订单数量 String,定价 String,单价 String,金额 String,发货数量 String,出库数量 String,未出库数量 String,发货待出库数量 String,发货出库匹配 String," & _

"出库次数 String,快递单号 String,出库日期 String,采购次数 String,出库仓库 String,总现存量 String,库存预留量 String,采购不到数量 String,上海总仓现存量 String,北京仓库现存量 String,深圳仓库现存量 String,门店结存量 String,入库次数 String,入库仓库一 String," & _

"入库仓库二 String,入库日期 String,入库数量 String,采购订单号 String,供应商 String,采购订单数量 String,预计入库时间 String,在途数量 String,货运状态 String,承运人 String)"

            cmd.ExecuteNonQuery()

            Dim i As Long

            For i = 0 To UltraGrid1.Rows.Count - 1

                cmd.CommandText = "INSERT INTO  Sheetone(备注,销售订单号,订单日期,iSOsID,客户编码,客户名称,业务员,收货地址,收货人,收货联系电话," & _

"制单人,图书编码,图书名称,要求到货仓库,实际到货仓库,仓库情况,订单关闭情况,订单数量,定价,单价,金额,发货数量,出库数量,未出库数量,发货待出库数量,发货出库匹配," & _

"出库次数,快递单号,出库日期,采购次数,出库仓库,总现存量,库存预留量,采购不到数量,上海总仓现存量,北京仓库现存量,深圳仓库现存量,门店结存量,入库次数,入库仓库一," & _

"入库仓库二,入库日期,入库数量,采购订单号,供应商,采购订单数量,预计入库时间,在途数量,货运状态,承运人) Values('" & UltraGrid1.Rows(i).Cells(0).Value.ToString() & "','" &

                UltraGrid1.Rows(i).Cells(1).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(2).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(3).Value.ToString() & "','" &

                UltraGrid1.Rows(i).Cells(4).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(5).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(6).Value.ToString() & "','" &

                UltraGrid1.Rows(i).Cells(7).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(8).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(9).Value.ToString() & "','" &

                UltraGrid1.Rows(i).Cells(10).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(11).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(12).Value.ToString() & "','" &

                UltraGrid1.Rows(i).Cells(13).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(14).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(15).Value.ToString() & "','" &

                UltraGrid1.Rows(i).Cells(16).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(17).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(18).Value.ToString() & "','" &

                UltraGrid1.Rows(i).Cells(19).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(20).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(21).Value.ToString() & "','" &

                UltraGrid1.Rows(i).Cells(22).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(23).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(24).Value.ToString() & "','" &

                UltraGrid1.Rows(i).Cells(25).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(26).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(27).Value.ToString() & "','" &

                UltraGrid1.Rows(i).Cells(28).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(29).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(30).Value.ToString() & "','" &

                UltraGrid1.Rows(i).Cells(31).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(32).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(33).Value.ToString() & "','" &

                UltraGrid1.Rows(i).Cells(34).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(35).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(36).Value.ToString() & "','" &

                UltraGrid1.Rows(i).Cells(37).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(38).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(39).Value.ToString() & "','" &

                UltraGrid1.Rows(i).Cells(40).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(41).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(42).Value.ToString() & "','" &

                UltraGrid1.Rows(i).Cells(43).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(44).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(45).Value.ToString() & "','" &

                UltraGrid1.Rows(i).Cells(46).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(47).Value.ToString() & "','" & UltraGrid1.Rows(i).Cells(48).Value.ToString() &

                "','" & UltraGrid1.Rows(i).Cells(49).Value.ToString() & "')"

                cmd.ExecuteNonQuery()

            Next

            conn.Close()

        Catch ex As Exception

            MessageBox.Show(ex.Message)

        End Try

    End Sub