如何在vb中将execl数据导入到SQL中
在execl表格中填写内容
在VB中进行操作
把execl中数据导入到SQL数据库的相应表格中
请问如何实现上述操作
请举例说明
[解决办法]
excel如果是一个有规则的数据，可以把期当作数据源导入
select * into Table FROM openrowset( 'Microsoft.Jet.OLEDB.4.0',
'EXCEL 5.0;HDR=YES;IMEX=1; DATABASE=yourPath',Sheet1$)
如果不规则的，就需要按实际的需求一个个写了.可以随便找个VB如何使用Excel的例子来看看,就行了
[解决办法]
首先是EXCEL的格式与数据库的表的格式一样，然后选择EXCEL，IMPORT去SQL
Private Sub cmdImport_Click()
Dim sExcelPath As String
Dim sExcelCustomerNumber As String
Dim sExcelID As String
Dim sExcelCustomerName As String
Dim iExcelCompanyName As String
Dim cExcelOpenDate As Date
Dim cExcelLastDate As Date
Dim cExcelBirthday As Date
Dim cExcelExpiryDate As Date
Dim sExcelAddress As String
Dim sExcelPostalCode As String
Dim sExcelCity As String
Dim sExcelStateOrProvince As String
Dim sExcelCountry As String
Dim sExcelTel1 As String
Dim sExcelTel2 As String
Dim sExcelMobile As String
Dim iExcelX As Integer
Dim iExcelY As Integer
Dim xlsApplication As Object
Dim xlsWorkBook As Object
Dim xlsWorkSheet As Object
On Error GoTo FileError
If txtExcelPath.Text = "" Then Exit Sub
sExcelPath = txtExcelPath.Text
Set xlsApplication = CreateObject("Excel.Application")
Set xlsWorkBook = xlsApplication.Workbooks.Open(sExcelPath)
Set xlsWorkSheet = xlsWorkBook.Worksheets(1)
iExcelY = 2
sExcelCustomerNumber = xlsWorkSheet.Cells(iExcelY, 1).Value
Do While sExcelCustomerNumber <> ""
sExcelCustomerNumber = xlsWorkSheet.Cells(iExcelY, 1).Value
sExcelID = xlsWorkSheet.Cells(iExcelY, 2).Value
sExcelCustomerName = xlsWorkSheet.Cells(iExcelY, 3).Value
iExcelCompanyName = xlsWorkSheet.Cells(iExcelY, 4).Value
cExcelOpenDate = xlsWorkSheet.Cells(iExcelY, 5).Value
cExcelLastDate = xlsWorkSheet.Cells(iExcelY, 6).Value
cExcelBirthday = xlsWorkSheet.Cells(iExcelY, 7).Value
cExcelExpiryDate = xlsWorkSheet.Cells(iExcelY, 8).Value
sExcelAddress = xlsWorkSheet.Cells(iExcelY, 9).Value
sExcelPostalCode = xlsWorkSheet.Cells(iExcelY, 10).Value
sExcelCity = xlsWorkSheet.Cells(iExcelY, 11).Value
sExcelStateOrProvince = xlsWorkSheet.Cells(iExcelY, 12).Value
sExcelCountry = xlsWorkSheet.Cells(iExcelY, 13).Value
sExcelTel1 = xlsWorkSheet.Cells(iExcelY, 14).Value
sExcelTel2 = xlsWorkSheet.Cells(iExcelY, 15).Value
sExcelMobile = xlsWorkSheet.Cells(iExcelY, 17).Value
If sExcelCustomerNumber = "" Then Exit Do
If CheckCustomerNumber(sExcelCustomerNumber) Then
If CheckID(sExcelID) Then
'Insert
SaveCustomerProc sExcelCustomerNumber, sExcelID, sExcelCustomerName, iExcelCompanyName, cExcelOpenDate, cExcelLastDate, cExcelBirthday, cExcelExpiryDate, sExcelAddress, sExcelPostalCode, sExcelCity, sExcelStateOrProvince, sExcelCountry, sExcelTel1, sExcelTel2, sExcelMobile
End If
Else
If CheckID(sExcelID) Then
'Update
UpdateCustomerProc sExcelCustomerNumber, sExcelID, sExcelCustomerName, iExcelCompanyName, cExcelOpenDate, cExcelLastDate, cExcelBirthday, cExcelExpiryDate, sExcelAddress, sExcelPostalCode, sExcelCity, sExcelStateOrProvince, sExcelCountry, sExcelTel1, sExcelTel2, sExcelMobile
End If
End If
iExcelY = iExcelY + 1
Loop
xlsWorkBook.Close
xlsApplication.Quit
MsgBox "Import date successfully!"
Exit Sub
FileError:
xlsWorkBook.Close
xlsApplication.Quit
MsgBox "File Error!"
End Sub
[解决办法]
找一下这样Code 还是很多的
[解决办法]
网上这样的代码很多的，可以找一下，如果真的需要可以找我，我给你个例子
[解决办法]
CreateObject("Excel.Application")
[解决办法]
