34,576
社区成员
发帖
与我相关
我的任务
分享将EXCEL文件中的数据导入SQL Server
请问专家,在VB中如何用纯代码的方式将EXCEL中的数据导入SQL Server数据库中,我用ADO试了几种方式,都无法实现,我应该怎样实现呢? 谢谢.
...全文
请发表友善的回复…
发表回复
CDINnet 2002-01-22
- 打赏
- 举报
用DTS导入很方便
lihonggen0 2002-01-22
- 打赏
- 举报
同意： playyuer(女㊣爱)
glite 2002-01-22
- 打赏
- 举报
g Z
mmzxg 2002-01-22
- 打赏
- 举报
这个问题斑竹已经答了。
jinyefeng 2002-01-22
- 打赏
- 举报
同意女什么爱
dbcontrols 2002-01-22
- 打赏
- 举报
HOWTO: Transfer Data from an ADO Recordset to Excel with Automation
文不对题.
关注这个问题.
文不对题.
关注这个问题.
hz1101 2002-01-22
- 打赏
- 举报
全是E文
tzzxj96 2002-01-22
- 打赏
- 举报
这是微软的解决方法。
HOWTO: Transfer Data from an ADO Recordset to Excel with Automation (Q246335)
--------------------------------------------------------------------------------
The information in this article applies to:
Microsoft Excel 2002
Microsoft Excel 2000
Microsoft Excel 97 for Windows
Microsoft Visual Basic Professional Edition for Windows, versions 5.0 , 6.0
Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0 , 6.0
ActiveX Data Objects (ADO), versions 2.0 , 2.1 , 2.5
--------------------------------------------------------------------------------
SUMMARY
You can transfer the contents of an ADO recordset to a Microsoft Excel worksheet by automating Excel. The approach that you can use depends on the version of Excel you are automating. Excel 97, Excel 2000, and Excel 2002 have a CopyFromRecordset method that you can use to transfer a recordset to a range. CopyFromRecordset in Excel 2000 and 2002 can be used to copy either a DAO or an ADO recordset. However, CopyFromRecordset in Excel 97 supports only DAO recordsets. To transfer an ADO recordset to Excel 97, you can create an array from the recordset and then populate a range with the contents of that array.
This article discusses both approaches. The sample code presented illustrates how you can transfer an ADO recordset to Excel 97, Excel 2000, or Excel 2002.
MORE INFORMATION
The code sample provided below shows how to copy an ADO recordset to a Microsoft Excel worksheet using automation from Microsoft Visual Basic. The code first checks the version of Excel. If Excel 2000 or 2002 is detected, the CopyFromRecordset method is used because it is efficient and requires less code. However, if Excel 97 or earlier is detected, the recordset is first copied to an array using the GetRows method of the ADO recordset object. The array is then transposed so that records are in the first dimension (in rows), and fields are in the second dimension (in columns). Then, the array is copied to an Excel worksheet through assigning the array to a range of cells. (The array is copied in one step rather than looping through each cell in the worksheet.)
The code sample uses the Northwind sample database that is included with Microsoft Office. If you selected the default folder when you installed Microsoft Office, the database is located in:
\Program Files\Microsoft Office\Office\Samples\Northwind.mdb
If the Northwind database is located in a different folder on your computer, you need to edit the path of the database in the code provided below.
If you do not have the Northwind database installed on your system, you can use the Add/Remove option for Microsoft Office setup to install the sample databases.
Steps to Create Sample
Start Visual Basic and create a new Standard EXE project. Form1 is created by default.
Add a CommandButton to Form1.
Click References from the Project menu. Add a reference to the Microsoft ActiveX Data Objects 2.1 Library .
Paste the following code into the code section of Form1:
Private Sub Command1_Click()
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim recArray As Variant
Dim strDB As String
Dim fldCount As Integer
Dim recCount As Long
Dim iCol As Integer
Dim iRow As Integer
' Set the string to the path of your Northwind database
strDB = "c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
' Open connection to the database
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";"
' Open recordset based on Orders table
rst.Open "Select * From Orders", cnt
' Create an instance of Excel and add a workbook
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Worksheets("Sheet1")
' Display Excel and give user control of Excel's lifetime
xlApp.Visible = True
xlApp.UserControl = True
' Copy field names to the first row of the worksheet
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
Next
' Check version of Excel
If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
'EXCEL 2000 or 2002: Use CopyFromRecordset
' Copy the recordset to the worksheet, starting in cell A2
xlWs.Cells(2, 1).CopyFromRecordset rst
'Note: CopyFromRecordset will fail if the recordset
'contains an OLE object field or array data such
'as hierarchical recordsets
Else
'EXCEL 97 or earlier: Use GetRows then copy array to Excel
' Copy recordset to an array
recArray = rst.GetRows
'Note: GetRows returns a 0-based array where the first
'dimension contains fields and the second dimension
'contains records. We will transpose this array so that
'the first dimension contains records, allowing the
'data to appears properly when copied to Excel
' Determine number of records
recCount = UBound(recArray, 2) + 1 '+ 1 since 0-based array
' Check the array for contents that are not valid when
' copying the array to an Excel worksheet
For iCol = 0 To fldCount - 1
For iRow = 0 To recCount - 1
' Take care of Date fields
If IsDate(recArray(iCol, iRow)) Then
recArray(iCol, iRow) = Format(recArray(iCol, iRow))
' Take care of OLE object fields or array fields
ElseIf IsArray(recArray(iCol, iRow)) Then
recArray(iCol, iRow) = "Array Field"
End If
Next iRow 'next record
Next iCol 'next field
' Transpose and Copy the array to the worksheet,
' starting in cell A2
xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
TransposeDim(recArray)
End If
' Auto-fit the column widths and row heights
xlApp.Selection.CurrentRegion.Columns.AutoFit
xlApp.Selection.CurrentRegion.Rows.AutoFit
' Close ADO objects
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing
' Release Excel references
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing
End Sub
Function TransposeDim(v As Variant) As Variant
' Custom Function to Transpose a 0-based array (v)
Dim X As Long, Y As Long, Xupper As Long, Yupper As Long
Dim tempArray As Variant
Xupper = UBound(v, 2)
Yupper = UBound(v, 1)
ReDim tempArray(Xupper, Yupper)
For X = 0 To Xupper
For Y = 0 To Yupper
tempArray(X, Y) = v(Y, X)
Next Y
Next X
TransposeDim = tempArray
End Function
Press the F5 key to run the project. Form1 appears.
Click the CommandButton on Form1, and note that the contents of the Orders table is displayed in a new workbook in Excel.
Using CopyFromRecordset
For efficiency and performance, CopyFromRecordset is the preferred method. Because Excel 97 supports only DAO recordsets with CopyFromRecordset, if you attempt to pass an ADO recordset to CopyFromRecordset with Excel 97, you receive the following error:
Run-time error 430:
Class does not support Automation or does not support expected interface.
In the code sample, you can avoid this error by checking Excel's version so that you do not use CopyFromRecordset for the 97 version.
NOTE : When using CopyFromRecordset, you should be aware that the ADO or DAO recordset you use cannot contain OLE object fields or array data such as hierarchical recordsets. If you include fields of either type in a recordset, the CopyFromRecordset method fails with the following error:
Run-time error -2147467259:
Method CopyFromRecordset of object Range failed.
Using GetRows
If Excel 97 is detected, use the GetRows method of the ADO recordset to copy the recordset into an array. If you assign the array returned by GetRows to a range of cells in the worksheet, the data goes across the columns instead of down the rows. For example, if the recordset has two fields and 10 rows, the array appears as two rows and 10 columns. Therefore, you need to transpose the array using your TransposeDim() function before assigning the array to the range of cells. When assigning an array to a range of cells, there are some limitations to be aware of:
The following limitations apply when assigning an array to an Excel Range object:
The array cannot contain OLE object fields or array data, such as hierarchical recordsets. Notice that the code sample checks for this condition and displays "Array Field" so that the user is made aware that the field cannot be displayed in Excel.
The array cannot contain Date fields that have a date prior to the year 1900. (See the "References" section for a Microsoft Knowledge Base article link.) Note that the code sample formats Date fields as variant strings to avoid this potential problem.
Note the use of the TransposeDim() function to transpose the array before the array is copied to the Excel worksheet. Instead of creating your own function to transpose the array, you can use Excel's Transpose function by modifying the sample code to assign the array to the cells as shown below:
xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
xlApp.WorksheetFunction.Transpose(recArray)
If you decide to use Excel's Transpose method instead of the TransposeDim() function to transpose the array, you should be aware of the following limitations with the Transpose method:
The array cannot contain an element that is greater than 255 characters.
The array cannot contain Null values.
The number of elements in the array cannot exceed 5461.
If the above limitations are not taken into consideration when you copy an array to an Excel worksheet, one of the following run-time errors may occur:
Run-time Error 13: Type Mismatch
Run-time Error 5: Invalid procedure call or argument
Run-time Error 1004: Application defined or object defined error
REFERENCES
For additional information about limitations on passing arrays to various versions of Excel, click the article number below to view the article in the Microsoft Knowledge Base:
Q177991 XL: Limitations of Passing Arrays to Excel Using Automation
For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
Q146406 XL: How to Retrieve a Table from Access into Excel Using DAO
Q215965 XL2000: 12:00:00 AM Displayed for Dates Earlier Than 1900
Q243394 HOWTO: Use MFC to Copy a DAO Recordset to Excel with Automation
Q247412 INFO: Methods for Transferring Data to Excel from Visual Basic
--------------------------------------------------------------------------------
Published Dec 9 1999 10:25AM Issue Type kbhowto
Last Modifed Apr 7 2001 4:53PM Additional Query Words Transpose Mismatch
Keywords kbATM kbAutomation kbExcel kbVBp500 kbVBp600 kbGrpDSO kbDSupport
--------------------------------------------------------------------------------
COMMENTS?
If you would like to briefly comment on this article, you can enter your remarks in the space below (up to 255 characters).
Submit Comments
HOWTO: Transfer Data from an ADO Recordset to Excel with Automation (Q246335)
--------------------------------------------------------------------------------
The information in this article applies to:
Microsoft Excel 2002
Microsoft Excel 2000
Microsoft Excel 97 for Windows
Microsoft Visual Basic Professional Edition for Windows, versions 5.0 , 6.0
Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0 , 6.0
ActiveX Data Objects (ADO), versions 2.0 , 2.1 , 2.5
--------------------------------------------------------------------------------
SUMMARY
You can transfer the contents of an ADO recordset to a Microsoft Excel worksheet by automating Excel. The approach that you can use depends on the version of Excel you are automating. Excel 97, Excel 2000, and Excel 2002 have a CopyFromRecordset method that you can use to transfer a recordset to a range. CopyFromRecordset in Excel 2000 and 2002 can be used to copy either a DAO or an ADO recordset. However, CopyFromRecordset in Excel 97 supports only DAO recordsets. To transfer an ADO recordset to Excel 97, you can create an array from the recordset and then populate a range with the contents of that array.
This article discusses both approaches. The sample code presented illustrates how you can transfer an ADO recordset to Excel 97, Excel 2000, or Excel 2002.
MORE INFORMATION
The code sample provided below shows how to copy an ADO recordset to a Microsoft Excel worksheet using automation from Microsoft Visual Basic. The code first checks the version of Excel. If Excel 2000 or 2002 is detected, the CopyFromRecordset method is used because it is efficient and requires less code. However, if Excel 97 or earlier is detected, the recordset is first copied to an array using the GetRows method of the ADO recordset object. The array is then transposed so that records are in the first dimension (in rows), and fields are in the second dimension (in columns). Then, the array is copied to an Excel worksheet through assigning the array to a range of cells. (The array is copied in one step rather than looping through each cell in the worksheet.)
The code sample uses the Northwind sample database that is included with Microsoft Office. If you selected the default folder when you installed Microsoft Office, the database is located in:
\Program Files\Microsoft Office\Office\Samples\Northwind.mdb
If the Northwind database is located in a different folder on your computer, you need to edit the path of the database in the code provided below.
If you do not have the Northwind database installed on your system, you can use the Add/Remove option for Microsoft Office setup to install the sample databases.
Steps to Create Sample
Start Visual Basic and create a new Standard EXE project. Form1 is created by default.
Add a CommandButton to Form1.
Click References from the Project menu. Add a reference to the Microsoft ActiveX Data Objects 2.1 Library .
Paste the following code into the code section of Form1:
Private Sub Command1_Click()
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim recArray As Variant
Dim strDB As String
Dim fldCount As Integer
Dim recCount As Long
Dim iCol As Integer
Dim iRow As Integer
' Set the string to the path of your Northwind database
strDB = "c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
' Open connection to the database
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";"
' Open recordset based on Orders table
rst.Open "Select * From Orders", cnt
' Create an instance of Excel and add a workbook
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Worksheets("Sheet1")
' Display Excel and give user control of Excel's lifetime
xlApp.Visible = True
xlApp.UserControl = True
' Copy field names to the first row of the worksheet
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
Next
' Check version of Excel
If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
'EXCEL 2000 or 2002: Use CopyFromRecordset
' Copy the recordset to the worksheet, starting in cell A2
xlWs.Cells(2, 1).CopyFromRecordset rst
'Note: CopyFromRecordset will fail if the recordset
'contains an OLE object field or array data such
'as hierarchical recordsets
Else
'EXCEL 97 or earlier: Use GetRows then copy array to Excel
' Copy recordset to an array
recArray = rst.GetRows
'Note: GetRows returns a 0-based array where the first
'dimension contains fields and the second dimension
'contains records. We will transpose this array so that
'the first dimension contains records, allowing the
'data to appears properly when copied to Excel
' Determine number of records
recCount = UBound(recArray, 2) + 1 '+ 1 since 0-based array
' Check the array for contents that are not valid when
' copying the array to an Excel worksheet
For iCol = 0 To fldCount - 1
For iRow = 0 To recCount - 1
' Take care of Date fields
If IsDate(recArray(iCol, iRow)) Then
recArray(iCol, iRow) = Format(recArray(iCol, iRow))
' Take care of OLE object fields or array fields
ElseIf IsArray(recArray(iCol, iRow)) Then
recArray(iCol, iRow) = "Array Field"
End If
Next iRow 'next record
Next iCol 'next field
' Transpose and Copy the array to the worksheet,
' starting in cell A2
xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
TransposeDim(recArray)
End If
' Auto-fit the column widths and row heights
xlApp.Selection.CurrentRegion.Columns.AutoFit
xlApp.Selection.CurrentRegion.Rows.AutoFit
' Close ADO objects
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing
' Release Excel references
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing
End Sub
Function TransposeDim(v As Variant) As Variant
' Custom Function to Transpose a 0-based array (v)
Dim X As Long, Y As Long, Xupper As Long, Yupper As Long
Dim tempArray As Variant
Xupper = UBound(v, 2)
Yupper = UBound(v, 1)
ReDim tempArray(Xupper, Yupper)
For X = 0 To Xupper
For Y = 0 To Yupper
tempArray(X, Y) = v(Y, X)
Next Y
Next X
TransposeDim = tempArray
End Function
Press the F5 key to run the project. Form1 appears.
Click the CommandButton on Form1, and note that the contents of the Orders table is displayed in a new workbook in Excel.
Using CopyFromRecordset
For efficiency and performance, CopyFromRecordset is the preferred method. Because Excel 97 supports only DAO recordsets with CopyFromRecordset, if you attempt to pass an ADO recordset to CopyFromRecordset with Excel 97, you receive the following error:
Run-time error 430:
Class does not support Automation or does not support expected interface.
In the code sample, you can avoid this error by checking Excel's version so that you do not use CopyFromRecordset for the 97 version.
NOTE : When using CopyFromRecordset, you should be aware that the ADO or DAO recordset you use cannot contain OLE object fields or array data such as hierarchical recordsets. If you include fields of either type in a recordset, the CopyFromRecordset method fails with the following error:
Run-time error -2147467259:
Method CopyFromRecordset of object Range failed.
Using GetRows
If Excel 97 is detected, use the GetRows method of the ADO recordset to copy the recordset into an array. If you assign the array returned by GetRows to a range of cells in the worksheet, the data goes across the columns instead of down the rows. For example, if the recordset has two fields and 10 rows, the array appears as two rows and 10 columns. Therefore, you need to transpose the array using your TransposeDim() function before assigning the array to the range of cells. When assigning an array to a range of cells, there are some limitations to be aware of:
The following limitations apply when assigning an array to an Excel Range object:
The array cannot contain OLE object fields or array data, such as hierarchical recordsets. Notice that the code sample checks for this condition and displays "Array Field" so that the user is made aware that the field cannot be displayed in Excel.
The array cannot contain Date fields that have a date prior to the year 1900. (See the "References" section for a Microsoft Knowledge Base article link.) Note that the code sample formats Date fields as variant strings to avoid this potential problem.
Note the use of the TransposeDim() function to transpose the array before the array is copied to the Excel worksheet. Instead of creating your own function to transpose the array, you can use Excel's Transpose function by modifying the sample code to assign the array to the cells as shown below:
xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
xlApp.WorksheetFunction.Transpose(recArray)
If you decide to use Excel's Transpose method instead of the TransposeDim() function to transpose the array, you should be aware of the following limitations with the Transpose method:
The array cannot contain an element that is greater than 255 characters.
The array cannot contain Null values.
The number of elements in the array cannot exceed 5461.
If the above limitations are not taken into consideration when you copy an array to an Excel worksheet, one of the following run-time errors may occur:
Run-time Error 13: Type Mismatch
Run-time Error 5: Invalid procedure call or argument
Run-time Error 1004: Application defined or object defined error
REFERENCES
For additional information about limitations on passing arrays to various versions of Excel, click the article number below to view the article in the Microsoft Knowledge Base:
Q177991 XL: Limitations of Passing Arrays to Excel Using Automation
For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
Q146406 XL: How to Retrieve a Table from Access into Excel Using DAO
Q215965 XL2000: 12:00:00 AM Displayed for Dates Earlier Than 1900
Q243394 HOWTO: Use MFC to Copy a DAO Recordset to Excel with Automation
Q247412 INFO: Methods for Transferring Data to Excel from Visual Basic
--------------------------------------------------------------------------------
Published Dec 9 1999 10:25AM Issue Type kbhowto
Last Modifed Apr 7 2001 4:53PM Additional Query Words Transpose Mismatch
Keywords kbATM kbAutomation kbExcel kbVBp500 kbVBp600 kbGrpDSO kbDSupport
--------------------------------------------------------------------------------
COMMENTS?
If you would like to briefly comment on this article, you can enter your remarks in the space below (up to 255 characters).
Submit Comments
david_1978 2002-01-22
- 打赏
- 举报
小弟佩服，能得到各位指点，小弟万分感激。
hz1101 2002-01-22
- 打赏
- 举报
我已经找到原因了，主要是字段的顺序改变了，好象经过了排序
luanjiajia 2002-01-22
- 打赏
- 举报
excel是可以直接导入sql server
最主要是 把excel 看成数据库 sheet看成数据表 第一行看为列名 就可以
别的大多数基本的sql语句 都可以用 不过注意的是 数据表应该 [sheet1$] 这样写 就可以
别的 你自己研究一下
最主要是 把excel 看成数据库 sheet看成数据表 第一行看为列名 就可以
别的大多数基本的sql语句 都可以用 不过注意的是 数据表应该 [sheet1$] 这样写 就可以
别的 你自己研究一下
hz1101 2002-01-22
- 打赏
- 举报
有意思，用版主的方法，数据是倒进去了，可是数据全部乱了套。
方法如下：
一、用SQL提供的Export data工具将一张表导出为excel 5.0文件
二、用版主的方法将此excel文件再导入到此表中，数据全部乱套（全部为数字类型，若含有字符类型的字段，则根本无法导入），检查了一下，好象是字段符值时串了门。
方法如下：
一、用SQL提供的Export data工具将一张表导出为excel 5.0文件
二、用版主的方法将此excel文件再导入到此表中，数据全部乱套（全部为数字类型，若含有字符类型的字段，则根本无法导入），检查了一下，好象是字段符值时串了门。
jett 2002-01-21
- 打赏
- 举报
收了
playyuer 2002-01-21
- 打赏
- 举报
Dim adoConnection As New ADODB.Connection
adoConnection.Open "Provider=SQLOLEDB.1;Server=YUER\PSQL2K;DataBase=aa;User ID=sa;Password="
'adoConnection.Execute "SELECT * INTO [TableFromExcel97] FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source=d:\excel97.xls;User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]"
adoConnection.Execute "INSERT INTO [TableFromExcel97] SELECT * FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=d:\excel97.xls;User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]"
adoConnection.Open "Provider=SQLOLEDB.1;Server=YUER\PSQL2K;DataBase=aa;User ID=sa;Password="
'adoConnection.Execute "SELECT * INTO [TableFromExcel97] FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source=d:\excel97.xls;User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]"
adoConnection.Execute "INSERT INTO [TableFromExcel97] SELECT * FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=d:\excel97.xls;User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]"
playyuer 2002-01-21
- 打赏
- 举报
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_2be1.asp
lx_king 2002-01-21
- 打赏
- 举报
不能吧，，你先用Ado导入access，然后在导入sql Server，
照理说可以直接导入sql server的
照理说可以直接导入sql server的
