EXCEL转SQL的vb源码EXCEL转SQL的vb源码EXCEL转SQL的vb源码EXCEL转SQL的vb源码

interfar

本帖作者设置需要打赏1.00才可查看

现金打赏

qyhua

启用邀请码注册，提高发帖质量，建设交流社区

interfar

Public cnn As New ADODB.Connection
Public cnn2 As New ADODB.Connection
Public qy1 As New ADODB.Recordset
Public qy2 As New ADODB.Recordset
Public qy3 As New ADODB.Recordset
Public qy4 As New ADODB.Recordset





Dim NewApp As New Excel.Application
Dim NewSheet As New Excel.Worksheet
Dim NewBook As New Excel.Workbook
Dim x As Long, y As Long, z As Long
Dim n(1 To 12) As String


Private Sub Command2_Click()
Timer1.Enabled = False
NewBook.Close
NewApp.Quit
End Sub

Private Sub Command3_Click()
CommonDialog1.Filter = "Excel File|*.xls"
CommonDialog1.ShowOpen
If CommonDialog1.FileName <> "" Then Text1.Text = CommonDialog1.FileName


End Sub

Private Sub Command4_Click()
If Text1.Text = "" Then Exit Sub
Set NewApp = New Excel.Application
Set NewBook = NewApp.Workbooks.Open(Text1.Text)
'第一位为路径，第五位为密码
Set NewSheet = NewBook.Worksheets(1)

If NewSheet.Cells(1, 1) = "编号" And NewSheet.Cells(1, 2) = "货品名称" And NewSheet.Cells(1, 3) = "货品全名" And _
   NewSheet.Cells(1, 4) = "基本单位" And NewSheet.Cells(1, 5) = "规格" And NewSheet.Cells(1, 6) = "条码" And _
   NewSheet.Cells(1, 7) = "商品类型" And NewSheet.Cells(1, 8) = "主供应商" Then
   Timer1.Enabled = True
Else
   MsgBox "Excel 文档格式不合要求", vbInformation, "注意"
End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
End
End Sub

Private Sub Label1_Click()

End Sub

Private Sub Timer1_Timer()

For i = 1 To 12
n(i) = NewSheet.Cells(2 + y, i)      '获取的数据
Next i


If n(1) = "" And n(2) = "" And n(3) = "" And n(4) = "" And n(5) = "" And n(6) = "" And n(7) = "" And n(8) = "" Then
Timer1.Enabled = False
MsgBox " 共增加了 " & y & " 个货品，操作完成！", vbInformation, "注意"
x = 0: y = 0: z = 0
NewBook.Close
NewApp.Quit

Exit Sub
End If

Set qy1 = cnn.Execute("select * from B_Goods where G_BarCode='" & n(6) & "'")
If qy1.EOF = True And qy1.BOF = True Then

Else
x = x + 1
GoTo Gnext
End If


If n(1) = "" Then     '处理空编码
Set qy1 = cnn.Execute("select * from B_Goods order by log(G_ID) DESC")
n(1) = Str(Val(qy1.Fields(1)) + 1)
End If

If n(2) = "" Or n(3) = "" Or n(4) = "" Or n(6) = "" Or n(7) = "" Or n(8) = "" Then
Timer1.Enabled = False
MsgBox (2 + y) & " 行数据不完整或者无数据", vbInformation, "注意"
End If

Set qy1 = cnn.Execute("select * from B_Unit where U_Name='" & n(4) & "'")
If qy1.EOF = True And qy1.BOF = True Then
Timer1.Enabled = False
MsgBox (2 + y) & " 行，数据库中没有找到“" & n(4) & "”这个基本单位。", vbInformation, "注意"
End If

Set qy1 = cnn.Execute("select * from B_GoodsType where Name='" & n(7) & "'")
If qy1.EOF = True And qy1.BOF = True Then
Timer1.Enabled = False
MsgBox (2 + y) & " 行，数据库中没有找到“" & n(7) & "”这个商品类型。", vbInformation, "注意"
Else
n(7) = qy1.Fields(1)
End If

Set qy1 = cnn.Execute("select * from B_Vendor where V_Name='" & n(8) & "'")
If qy1.EOF = True And qy1.BOF = True Then
Timer1.Enabled = False
MsgBox (2 + y) & " 行，数据库中没有找到“" & n(8) & "”这个供应商。", vbInformation, "注意"
Else
n(8) = qy1.Fields(1)
End If

If Timer1.Enabled = True Then

Set qy1 = cnn.Execute("insert into B_Goods(G_ID,G_PinYin,G_Name,G_FullName,G_BarCode,TypeID,PriceType,G_Unit,G_UnitRate,V_ID,Cal_ID,S_ID,G_PArea,G_Manufacturer,G_Price,G_LastPrice,G_SalePrice,V_Price,isBZQ,RateNum)values('" & _
n(1) & "','" & "" & "','" & n(2) & "','" & n(3) & "','" & n(6) & "','" & n(7) & "','" & "111" & "','" & n(4) & "','" & n(5) & "','" & n(8) & "','" & 0 & "','" & "0001" & "','" & "" & "','" & "" & "'," & n(9) & "," & n(10) & "," & n(11) & "," & n(12) & ",'" & 0 & "'," & 0 & ")")

z = z + 1
End If

Gnext:
y = y + 1
Label1.Caption = "共读取商品数 " & y & " 个，其中增加商品数 " & z & " 个，重复商品数 " & x & " 个！（将不会增加到商品库中）"

End Sub