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

[ztchen]

如何实现对Excl表格进行数据库导入、导出操作？我用的SQLServer，并且需要导入或导出的表格要具有一定的格式，不仅仅单纯是数据行。

[ztchen]

有人知道吗？

[joan_lu]

VBA编程

[cmsoft]

'*****************从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

[fraser01]

最直接的方法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
这样可对其进行数据库操作了.