如何将一些格式固定的Excel表的数据,通过VB写的程序导入SQL server中?
有相关的源代码吗?
有相关的源代码吗?
解决方案 »
- VB ado 操作数据库问题
- 关于datagrid的使用问题!
- mediaplay6 如何获得avi总帧数?请教高手!
- 用vb自带的制作工具做安装盘,怎样将文件夹和access表打进包中?
- 望大家给我介绍几个制作界面的控件呀!!!!!!!!!
- 关于mshflexgrid的列合并~~
- 一个文本问题
- 真不好意思再麻烦大家一下,combo里的list里的选项(数值和字符串)怎样才能被提到数据库里阿??
- 搞不懂,调用一个api时,到底要那些参数!!
- 大家从奈软挑起的无谓争端中跳出来吧,还VB版块一方净土。多解决技术问题然后适当灌水:)
- crystal report8.5中的货币符号
- VB中怎样调用C写的DLL?
http://www.csdn.net/develop/read_article.asp?id=18623 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-------------------------------------------------------------------------------------------------
您好,我運行了
SELECT *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
出錯
錯誤提示Server: Msg 7403, Level 16, State 1, Line 1
Could not locate registry entry for OLE DB provider 'Provider = Microsoft.Jet.OLEDB.4.0'.
OLE DB error trace [Non-interface error: Provider not registered.].
請幫忙
Dim i, j As Integer
Dim iRows As Integer
Dim iCols As Integer
Dim jOut As Integer
Dim kOut As Integer
Dim objExcel As Excel.Application
Dim objWorkBook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Dim objRange As Excel.Range
On Error GoTo err
'将EXCEL表中的数据导入到网格控件中
Set objExcel = New Excel.Application
Set objWorkBook = objExcel.Workbooks.Open(txt_FileName.Text)
Set objSheet = objWorkBook.ActiveSheet
Set objRange = objSheet.UsedRange
iRows = objRange.Rows.Count
iCols = objRange.Columns.Count
For jOut = 1 To iRows - 1
For kOut = 1 To iCols
With MSHFlexGrid1
.TextMatrix(jOut - 1, kOut - 1) = objSheet.Cells(jOut + 1, kOut)
End With
Next
MSHFlexGrid1.Rows = MSHFlexGrid1.Rows + 1
Next
objWorkBook.Close
Set objSheet = Nothing
Set objWorkBook = Nothing
Set objExcel = Nothing
'将网格控件中的内容存入数据库
sql = "select* from Y_YSDWBH "
Set rs = ExecuteSQL(sql)
If rs.EOF Then
For i = 0 To MSHFlexGrid1.Rows - 2
rs.AddNew
For j = 0 To 6
rs.Fields(j) = MSHFlexGrid1.TextMatrix(i, j)
Next j
rs.Update
Next i
MsgBox "报表数据已导入至数据库中! ", vbInformation + vbOKOnly, "提示"
txt_FileName.Text = ""
cmd_datain.Enabled = False
Else
MsgBox "导入过程中出现错误,请重新操作! ", vbCritical + vbOKOnly, "错误"
sql = "delete from Y_YSDWBH "
Set rs = ExecuteSQL(sql)
txt_FileName.Text = ""
cmd_datain.Enabled = False
End If
err:
txt_FileName.Text = ""
cmd_datain.Enabled = False
Exit Sub
End Sub
我是这样做的给楼主参考 (引用工程里面的EXCEL9)
用下面的方法可以把excel中的数据导入到创建的数据库的表中
'將 Excel 的文件导入 Access文件
'此一模块共有四个参数:
'1、SheetName:要导出资料的文件名称 (Sheet name),例如 Sheet1
'2、ExcelPath:要导出资料的 Excel 档案路径名称 (Workbook path),例如 D:\data\把excel数据导入到access中\book1.xls
'3、AccessTable:要导入的 Access Table 名称,例如 TestTable
'4、AccessDBPath:要导入的 Access 档案路径名称,例如 C:\book1.mdb
Private Sub ExportExcelSheetToAccess(sSheetName As String, _
sExcelPath As String, sAccessTable As String, sAccessDBPath As String)
Dim db As Database
Dim rs As Recordset
Set db = OpenDatabase(sExcelPath, True, False, "Excel 5.0")
Call db.Execute("Select * into [;database=" & sAccessDBPath & "]." & _
sAccessTable & " FROM [" & sSheetName & "$]")
MsgBox "Table exported successfully.", vbInformation, "Yams"
End Sub
Private Sub Command1_Click()
'ExportExcelSheetToAccess "Sheet1", "D:\shujv.xls", a, "D:\inidata.MDB"
ExportExcelSheetToAccess "dd", "F:\book.xls", "poiss_data", "F:\data.MDB"
' MsgBox Text2.Text & Text1.Text & "已经成功转换到了" & Text4.Text & Text3.Text
End Sub
SQL server的安装盘就有这儿也有,微软网站也有
http://www.dapha.net/down/list.asp?id=1881
sql = "select* from Y_YSDWBH "
Set rs = ExecuteSQL(sql)
Set MSHFlexGrid1.DataSource = rs
With MSHFlexGrid1
.TextMatrix(0, 0) = "单位编号"
.TextMatrix(0, 1) = "单位名称"
.TextMatrix(0, 2) = "单位简称"
.TextMatrix(0, 3) = "单位类别"
.TextMatrix(0, 4) = "单位级数"
.TextMatrix(0, 5) = "单位明细"
.TextMatrix(0, 6) = "备注"
End With
Dim i, j As Integer
Dim Exl As Variant
Set Exl = CreateObject("Excel.Application")
Exl.Workbooks.Add
'由EXCEL里面的宏得来的
Range("A1:A65536").Select
Selection.NumberFormatLocal = "@"
'将网格中的数据导入到EXCEL表中
For i = 0 To MSHFlexGrid1.Rows - 1
For j = 0 To MSHFlexGrid1.Cols - 1
Exl.Cells(1 + i, j + 1) = MSHFlexGrid1.TextMatrix(i, j)
Next j
Next i
DeleteFile ("c:\Documents and Settings\Administrator\My Documents\Resume.xlw")
DeleteFile (" c:\My Documents \Resume.xlw")
Exl.SaveWorkspace
'退出Excel Application
Exl.Quit
MsgBox "数据已导出至Excel文件中,同时请注意导出的数据禁止对其操作! ", vbInformation, "提示"error:
opt_all.Value = False
opt_exit.Value = False
Exit Sub
Me.ShowEnd Sub我是这样导出的