frminportdcs.frm

这是一个用VB编写的用于外贸行业的供应商信息管理系统。

VERSION 5.00
Begin VB.Form frmImportDCS 
   Caption         =   "ImportDCS"
   ClientHeight    =   3150
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   5235
   LinkTopic       =   "Form1"
   ScaleHeight     =   3150
   ScaleWidth      =   5235
   StartUpPosition =   3  '窗口缺省
   Begin VB.OptionButton Option3 
      Caption         =   "Street"
      Height          =   495
      Left            =   3360
      TabIndex        =   5
      Top             =   1440
      Width           =   1095
   End
   Begin VB.OptionButton Option2 
      Caption         =   "City"
      Height          =   495
      Left            =   2160
      TabIndex        =   4
      Top             =   1440
      Width           =   1215
   End
   Begin VB.OptionButton Option1 
      Caption         =   "District"
      Height          =   495
      Left            =   840
      TabIndex        =   3
      Top             =   1440
      Width           =   1215
   End
   Begin VB.TextBox txtFilename 
      Height          =   375
      Left            =   1440
      TabIndex        =   1
      Top             =   480
      Width           =   3135
   End
   Begin VB.CommandButton btnImport 
      Caption         =   "Import"
      Default         =   -1  'True
      Height          =   375
      Left            =   2160
      TabIndex        =   0
      Top             =   2400
      Width           =   1215
   End
   Begin VB.Label Label1 
      Caption         =   "Filename:"
      Height          =   375
      Left            =   480
      TabIndex        =   2
      Top             =   600
      Width           =   975
   End
End
Attribute VB_Name = "frmImportDCS"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Public cn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public xlApp As Excel.Application '定义EXCEL类
Public xlBook As Excel.Workbook '定义工件簿类
Public xlsheet As Excel.Worksheet '定义工作表类

Private Sub importIt(fileName As String, tabName As String)
    Dim n As Integer
    Dim district_id As String
    Dim name As String
    Dim city_id As String
    Dim street_id As String
    Set cn = New ADODB.Connection
    cn.Open strCn
    Set rs = New ADODB.Recordset
    rs.Open tabName, cn, adOpenKeyset, adLockOptimistic, adCmdTable
    Set xlApp = CreateObject("Excel.Application") '创建EXCEL应用类
    xlApp.Visible = False '设置EXCEL不可见
    Set xlBook = xlApp.Workbooks.Open(fileName) '打开EXCEL工作簿
    If tabName = "district" Then
        Set xlsheet = xlBook.Worksheets(1) '打开EXCEL工作表
    ElseIf tabName = "city" Then
        Set xlsheet = xlBook.Worksheets(2) '打开EXCEL工作表
    ElseIf tabName = "street" Then
        Set xlsheet = xlBook.Worksheets(3) '打开EXCEL工作表
    End If
    xlsheet.Activate '激活工作表
    n = 1
    While (xlsheet.Cells(n, 1) > "")
        rs.AddNew
        If tabName = "district" Then
            district_id = xlsheet.Cells(n, 1)
            name = xlsheet.Cells(n, 2)
            rs.Fields("district_id") = district_id
            rs.Fields("district_name") = name
        ElseIf tabName = "city" Then
            district_id = xlsheet.Cells(n, 1)
            city_id = xlsheet.Cells(n, 2)
            name = xlsheet.Cells(n, 3)
            rs.Fields("district_id") = district_id
            rs.Fields("city_id") = city_id
            rs.Fields("city_name") = name
        ElseIf tabName = "street" Then
            city_id = xlsheet.Cells(n, 1)
            street_id = xlsheet.Cells(n, 2)
            name = xlsheet.Cells(n, 3)
            rs.Fields("city_id") = city_id
            rs.Fields("street_id") = street_id
            rs.Fields("street_name") = name
        End If
        rs.Update
        n = n + 1
    Wend
    rs.Close
    xlBook.Close
    MsgBox "import is over."
End Sub


Private Sub btnImport_Click()
    Dim fileName As String
    fileName = txtFilename.Text
    If Dir(fileName) <> "" Then
        If Option1.Value = True Then
            importIt fileName, "district"
        ElseIf Option2.Value = True Then
            importIt fileName, "city"
        ElseIf Option3.Value = True Then
            importIt fileName, "street"
        End If
    End If
End Sub

Private Sub Form_Activate()
    txtFilename.SetFocus
    Option1.Value = True
End Sub