private people as new ADODB.Recordset'向Excle中写入数据 Private Function LoadExcel() As String LoadExcel = "" On Error GoTo label:If Not people.EOF Then
Set xlsApp = New Excel.Application Set xlsBook = xlsApp.Workbooks.Open(App.Path & "\book1.xls") Set xlsSheet1 = xlsBook.Worksheets(1) Set xlsSheet2 = xlsBook.Worksheets(2)
people.MoveFirst i = 2 Do While Not people.EOF i = i + 1 xlsSheet2.Cells(i, 1) = people("name") xlsSheet1.Cells(i, 1) = people("name")
If people("sex") = 1 Then xlsSheet1.Cells(i, 2) = "男" Else xlsSheet1.Cells(i, 2) = "女" End If xlsSheet1.Cells(i, 3) = people("nation") xlsSheet1.Cells(i, 4) = Format(people("bdate"), "yyyy-mm-dd") xlsSheet1.Cells(i, 5) = Format(people("cdate"), "yyyy-mm-dd") xlsSheet1.Cells(i, 6) = people("card_id") xlsSheet1.Cells(i, 7) = people("power") xlsSheet1.Cells(i, 8) = people("zz") xlsSheet1.Cells(i, 9) = people("address") people.MoveNext Loop xlsApp.Visible = TrueEnd IfExit Functionlabel: LoadExcel = "发生错误,创建不成功,请重试!" End Function
希望这些对你有用 SQL SERVER 和EXCEL的数据导入导出 1、在SQL SERVER里查询Excel数据:-- ======================================================SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] 下面是个查询的示例,它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。SELECT * FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions ------------------------------------------------------------------------------------------------- 2、将Excel的数据导入SQL server :-- ======================================================SELECT * into newtableFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] 实例:SELECT * into newtableFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
Private Function LoadExcel() As String
LoadExcel = ""
On Error GoTo label:If Not people.EOF Then
Set xlsApp = New Excel.Application
Set xlsBook = xlsApp.Workbooks.Open(App.Path & "\book1.xls")
Set xlsSheet1 = xlsBook.Worksheets(1)
Set xlsSheet2 = xlsBook.Worksheets(2)
people.MoveFirst
i = 2
Do While Not people.EOF
i = i + 1
xlsSheet2.Cells(i, 1) = people("name")
xlsSheet1.Cells(i, 1) = people("name")
If people("sex") = 1 Then
xlsSheet1.Cells(i, 2) = "男"
Else
xlsSheet1.Cells(i, 2) = "女"
End If
xlsSheet1.Cells(i, 3) = people("nation")
xlsSheet1.Cells(i, 4) = Format(people("bdate"), "yyyy-mm-dd")
xlsSheet1.Cells(i, 5) = Format(people("cdate"), "yyyy-mm-dd")
xlsSheet1.Cells(i, 6) = people("card_id")
xlsSheet1.Cells(i, 7) = people("power")
xlsSheet1.Cells(i, 8) = people("zz")
xlsSheet1.Cells(i, 9) = people("address")
people.MoveNext
Loop xlsApp.Visible = TrueEnd IfExit Functionlabel:
LoadExcel = "发生错误,创建不成功,请重试!"
End Function
SQL SERVER 和EXCEL的数据导入导出 1、在SQL SERVER里查询Excel数据:-- ======================================================SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] 下面是个查询的示例,它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。SELECT *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
------------------------------------------------------------------------------------------------- 2、将Excel的数据导入SQL server :-- ======================================================SELECT * into newtableFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] 实例:SELECT * into newtableFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions