如何实现对Excl表格进行数据库导入、导出操作?我用的SQLServer

ztchen 2002-05-20 11:50:02
如何实现对Excl表格进行数据库导入、导出操作?我用的SQLServer,并且需要导入或导出的表格要具有一定的格式,不仅仅单纯是数据行。
...全文
79 点赞 收藏 4
写回复
4 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
fraser01 2002-05-22
最直接的方法SQL语句,但容易出现目标偏离,SQL语句请参考MSDN的OpenRowSet章节.
间接方法为ADO接入,把EXCEL文件作为数据库
strTemp = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=Excel Files;Initial Catalog="
strTemp = strTemp & strFileName
adoConn.Open strTemp
这样可对其进行数据库操作了.
回复
cmsoft 2002-05-22
'*****************从Excel中导入数据到数据库********************
Public Sub FromExcel(OpenUrl As String) '将Excel中的数据导入到数据库中
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim nRows As Integer
Dim nCols As Integer
Dim k As Integer, Sql As String
Dim Rs As New ADODB.Recordset
On Error Resume Next
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(OpenUrl)
Set xlsheet = xlBook.Sheets(1)
xlApp.Visible = True
With xlsheet
nRows = .Cells(2, 1).CurrentRegion.Rows.Count
nCols = .Cells(2, 1).CurrentRegion.Columns.Count
End With
'frmSendData.Show (1)
k = 2
With xlsheet
For k = 2 To nRows
Sql = "INSERT INTO product(prd_no,prd_name,prd_price) VALUES('" & Trim(.Cells(k, "A").Value) & "','" & Trim(.Cells(k, "C").Value) & "'," & .Cells(k, "D").Value & ")"
Gadocn_app.Execute (Sql)
DoEvents
Next
End With
End Sub


'********************将数据库中的数据导出到Excel中***********************
Public Sub ToExcel(title As String, LstView As ListView)
Dim i As Integer, j As Integer
Dim MSExcel As Variant, MSXlsWB As Variant

Set MSExcel = CreateObject("Excel.Application")
Set MSXlsWB = MSExcel.Workbooks.Add
MSXlsWB.Worksheets(1).Name = "Sheet1"

MSXlsWB.Worksheets(1).Cells(1, 1) = title '标题

For i = 0 To LstView.ListItems.Count - 1 '行

For j = 0 To LstView.ColumnHeaders.Count - 1 '列
If (j + 1) Mod LstView.ColumnHeaders.Count <> 0 Then
MSXlsWB.Worksheets(1).Cells(i + 2, j + 1) = LstView.ListItems(i + 1).SubItems(j + 1)
End If
Next
Next

MSXlsWB.Application.Visible = True
MSXlsWB.Windows(1).Activate
Set MSExcel = Nothing
Set MSXlsWB = Nothing
End Sub
回复
joan_lu 2002-05-22
VBA编程
回复
ztchen 2002-05-22
有人知道吗?
回复
相关推荐
发帖

1184

社区成员

VB 数据库(包含打印,安装,报表)
申请成为版主
帖子事件
创建了帖子
2002-05-20 11:50
社区公告
暂无公告