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