阳光网驿-企业信息化交流平台【DTC零售连锁全渠道解决方案】

 找回密码
 注册

QQ登录

只需一步,快速开始

扫描二维码登录本站

手机号码,快捷登录

手机号码,快捷登录

老司机
查看: 4492|回复: 2

[VB] EXCEL转SQL的vb源码EXCEL转SQL的vb源码EXCEL转SQL的vb源码EXCEL转SQL的vb源码

[复制链接]
  • TA的每日心情
    擦汗
    6 小时前
  • 签到天数: 759 天

    [LV.10]以坛为家III

    发表于 2021-1-18 22:55:58 | 显示全部楼层 |阅读模式
    本帖作者设置需要打赏1.00才可查看
    楼主热帖
    启用邀请码注册,提高发帖质量,建设交流社区
  • TA的每日心情
    奋斗
    5 天前
  • 签到天数: 578 天

    [LV.9]以坛为家II

    发表于 2021-1-19 14:23:03 | 显示全部楼层
    启用邀请码注册,提高发帖质量,建设交流社区
    启用邀请码注册,提高发帖质量,建设交流社区
  • TA的每日心情
    擦汗
    6 小时前
  • 签到天数: 759 天

    [LV.10]以坛为家III

     楼主| 发表于 2022-4-2 00:18:33 | 显示全部楼层



    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

    启用邀请码注册,提高发帖质量,建设交流社区
    您需要登录后才可以回帖 登录 | 注册

    本版积分规则

    快速回复 返回顶部 返回列表