1180
社区成员
- 主页
如何实现对Excl表格进行数据库导入、导出操作？我用的SQLServer
如何实现对Excl表格进行数据库导入、导出操作？我用的SQLServer，并且需要导入或导出的表格要具有一定的格式，不仅仅单纯是数据行。
...全文
请发表友善的回复…
发表回复
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
这样可对其进行数据库操作了.
间接方法为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
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
有人知道吗？