import.frm

哈哈

        Set TmpRs = New ADODB.Recordset
        sql = "insert into temp select * from import"
        TmpRs.Open sql, pubconn, adOpenKeyset, adLockOptimistic
        Set TmpRs = New ADODB.Recordset
        TmpRs.Open "delete * from import", pubconn, adOpenKeyset, adLockOptimistic
        listview1.ListItems.Clear
        listview1.ColumnHeaders.Clear
        Call showlistview
        Call viewfinish
    End If
End Sub

Private Sub menubangzhu_Click()
OLE1.DoVerb
End Sub

Private Sub menubanji_Click()
resultopen
End Sub

Private Sub menubiaoge_Click()
    resultgrid.Visible = True
End Sub

Private Sub menuclear_Click()
   setempty    '调用自定义清空数据库过程
   MsgBox "成功删除临时数据！", vbOKOnly + 64, title
End Sub

Private Sub menucreate_Click()
    On Error GoTo chuli:
    If exambiaoshi = "" Or pxfs = "" Or examstunum = 0 Or examclassnum = 0 Then
       MsgBox "考场属性设置不完整或没有被设置，请重新设置！", vbOKOnly + 48, title
       setting.Show   '验证属性设置是否正确
       Exit Sub
    End If
    '设置数据集和数据连接
    Dim personal As ADODB.Recordset   '检查人数
    Dim temprs As ADODB.Recordset
    Dim result1 As ADODB.Recordset
    Dim tempbanji As ADODB.Recordset  '检索所要考试的班级
    Dim resultcon As ADODB.Connection
    Dim stuinfor() As examstudent     '定义学生类
    Dim zrs As Integer                '存储考试学生的总数
    Dim examinfor() As kaochangclass  '将考场信息导进数组
    Dim zkcs As Integer               '存储总考场数
    Dim maxxiabiao As Integer
    
       '定义临时变量存储考号范围
    Dim tempkaochang As Integer
    Dim temprenshu As Integer
    Dim tempkc As Integer   '临时存储的增量
        tempkaochang = 1    '临时考场存储的增量
        tempkc = 1          '临时学生存储的增量
        temprenshu = 1
    '实例化连接
    Set resultcon = New ADODB.Connection
        resultcon.ConnectionString = pubconnstr
        resultcon.Open
    Dim checkrs As ADODB.Recordset
    Set checkrs = New ADODB.Recordset
        checkrs.Open "select count(*) as zongshu from temp", resultcon, adOpenDynamic, adLockPessimistic
        zrs = checkrs.Fields("zongshu")
        
        If zrs < 1 Then
           MsgBox "目前还没有参加考试的学生，请您先挑选参加考试的学生后再进行生成考号操作！", vbOKOnly + 48, title
           Exit Sub
        End If
        
          If zrs > examstunum * examclassnum Then
           MsgBox "您所设置的考场个数以及每个考场的人数，不够分配,有" & checkrs.Fields("zongshu") - examstunum * examclassnum & "个学生没有考号，请重新设置考场属性!", vbOKOnly + 48, title
            setting.Show
              Exit Sub
           End If
           jindu.Max = zrs   '设置进度条最大值
           jindu.Value = 0
    '排序方式分为"随机排号和按成绩排号"
            paixufangshi = "kaohaopaixu"
            Randomize '随机种子
            ReDim stuinfor(zrs) As examstudent
            tempkaochang = 1
            Set temprs = New ADODB.Recordset
            temprs.Open "select * from temp", resultcon, adOpenDynamic, adLockPessimistic
            While temprs.EOF = False   '将临时表中的记录导进学生类数组
                  Set stuinfor(tempkaochang) = New examstudent
                  stuinfor(tempkaochang).banji = temprs.Fields("班级")
                  stuinfor(tempkaochang).chengji = Val(temprs.Fields("成绩"))
                  stuinfor(tempkaochang).xingming = temprs.Fields("姓名")
                  stuinfor(tempkaochang).xuehao = temprs.Fields("学号")
                  stuinfor(tempkaochang).kaohao = ""
                  temprs.MoveNext
                  tempkaochang = tempkaochang + 1
            Wend
            temprs.close
            tempkaochang = 1
            Set temprs = Nothing
            Set temprs = New ADODB.Recordset
            temprs.Open "select count(*) as zs from exam", resultcon, adOpenDynamic, adLockPessimistic
            zkcs = temprs.Fields("zs")
            Set temprs = New ADODB.Recordset
            temprs.Open "select * from exam", resultcon, adOpenDynamic, adLockPessimistic
            ReDim examinfor(zkcs) As kaochangclass
            
            While temprs.EOF = False      '将考场信息导进数组
                  Set examinfor(tempkaochang) = New kaochangclass
                  examinfor(tempkaochang).didian = temprs.Fields("考场地点")
                  examinfor(tempkaochang).mingcheng = temprs.Fields("考场名称")
                  examinfor(tempkaochang).renshu = temprs.Fields("考场人数")
                  temprs.MoveNext
                  tempkaochang = tempkaochang + 1
            Wend
            temprs.close
            tempkaochang = 1
            Set temprs = Nothing
            Select Case pxfs
            
                   Case "随机排号"
                        For tempkaochang = 1 To zrs
                            suiji = Int(Rnd() * zrs) + 1
                            While stuinfor(suiji).kaohao <> ""
                                  suiji = Int(Rnd() * zrs) + 1
                            Wend
                            If temprenshu > examinfor(tempkc).renshu Then
                               tempkc = tempkc + 1
                               temprenshu = 1
                            End If
                            stuinfor(suiji).kaohao = exambiaoshi & "00" & Mid(examinfor(tempkc).mingcheng, 1, Len(examinfor(tempkc).mingcheng) - 2) & "00" & temprenshu
                            Set result1 = New ADODB.Recordset
                            result1.Open "result", resultcon, adOpenDynamic, adLockPessimistic
                            result1.AddNew
                            result1.Fields("学号") = stuinfor(suiji).xuehao
                            result1.Fields("姓名") = stuinfor(suiji).xingming
                            result1.Fields("成绩") = stuinfor(suiji).chengji
                            result1.Fields("班级") = stuinfor(suiji).banji
                            result1.Fields("考场") = examinfor(tempkc).mingcheng
                            result1.Fields("考号") = stuinfor(suiji).kaohao
                            result1.Update
                            temprenshu = temprenshu + 1     '增量自加
                            jindu.Visible = True
                            jindu.Value = jindu.Value + 1
                            If jindu.Value = jindu.Max Then
                               jindu.Visible = False
                            End If
                            result1.close
                          
                Next
               
                   Case "按成绩排号"
                                  paixufangshi = "banjipaixu"
                                  maxchengji = stuinfor(1).chengji
                                  Dim linshi As Integer
                                  linshi = 1                         '分配考场的增量
                                  temprenshu = 1                     '分配每考场人数的增量
                                  For tempkaochang = 1 To zrs        '按成绩进行排序
                                      maxchengji = stuinfor(tempkaochang).chengji
                                      maxxiabiao = tempkaochang
                                      For tempkc = tempkaochang To zrs
                                          
                                          If stuinfor(tempkc).chengji > maxchengji Then
                                             maxxiabiao = tempkc     '记录下最大值的下标
                                             maxchengji = stuinfor(maxxiabiao).chengji
                                          End If
                                      Next
                                      '用临时变量与最大的值进行交换
                                      maxchengji = stuinfor(maxxiabiao).chengji
                                      maxxingming = stuinfor(maxxiabiao).xingming
                                      maxbanji = stuinfor(maxxiabiao).banji
                                      maxkaohao = stuinfor(maxxiabiao).kaohao
                                      maxxuehao = stuinfor(maxxiabiao).xuehao
                                      stuinfor(maxxiabiao).xingming = stuinfor(tempkaochang).xingming
                                      stuinfor(maxxiabiao).banji = stuinfor(tempkaochang).banji
                                      stuinfor(maxxiabiao).kaohao = stuinfor(tempkaochang).kaohao
                                      stuinfor(maxxiabiao).xuehao = stuinfor(tempkaochang).xuehao
                                      stuinfor(maxxiabiao).chengji = stuinfor(tempkaochang).chengji
                                      stuinfor(tempkaochang).chengji = maxchengji
                                      stuinfor(tempkaochang).kaohao = maxkaohao
                                      stuinfor(tempkaochang).banji = maxbanji
                                      stuinfor(tempkaochang).xingming = maxxingming
                                      stuinfor(tempkaochang).xuehao = maxxuehao
                                        If temprenshu > examinfor(linshi).renshu Then
                                           linshi = linshi + 1
                                           temprenshu = 1
                                        End If
                                        stuinfor(tempkaochang).kaohao = exambiaoshi & "00" & Mid(examinfor(linshi).mingcheng, 1, Len(examinfor(linshi).mingcheng) - 2) & "00" & temprenshu
                                        Set result1 = New ADODB.Recordset
                                        result1.Open "result", resultcon, adOpenDynamic, adLockPessimistic
                                        result1.AddNew
                                        result1.Fields("学号") = stuinfor(tempkaochang).xuehao
                                        result1.Fields("姓名") = stuinfor(tempkaochang).xingming
                                        result1.Fields("成绩") = stuinfor(tempkaochang).chengji
                                        result1.Fields("班级") = stuinfor(tempkaochang).banji
                                        result1.Fields("考场") = examinfor(linshi).mingcheng
                                        result1.Fields("考号") = stuinfor(tempkaochang).kaohao
                                        result1.Update
                                        temprenshu = temprenshu + 1     '增量自加
                                        jindu.Visible = True
                                        jindu.Value = jindu.Value + 1
                                        If jindu.Value = jindu.Max Then
                                           jindu.Visible = False
                                        End If
                                 Next
                                        result1.close
                   End Select
                   
                   
      '尝试用DATAGRID显示数据
      
 
      
      ''展示考号生成列表
      If paixufangshi = "kaohaopaixu" Then
         kaohaopaixu
      End If
      If paixufangshi = "banjipaixu" Then
         resultopen
      End If
      Set result1 = New ADODB.Recordset
      result1.CursorLocation = adUseClient
      result1.Open "select * from result order by [学号] asc", resultcon, adOpenDynamic, adLockPessimistic
      Set resultgrid.DataSource = result1
      resultgrid.Visible = True
      Exit Sub

                                       Exit Sub
chuli:
        jindu.Value = 0
        jindu.Visible = False
        If Err.Number = -2147217887 Then  '根据错误号判断相关处理程序
          If MsgBox("您已经配置了学生的考号，当前的操作将生成新的结果，您真的要放弃原来的结果吗？", vbYesNo + 48, title) = vbYes Then
            resulttree.Nodes.Clear
              Dim rstemp As ADODB.Recordset
              Dim tempconn As ADODB.Connection
              Set tempconn = New ADODB.Connection
              tempconn.ConnectionString = pubconnstr
              tempconn.Open
              Set rstemp = New ADODB.Recordset
              rstemp.Open "delete * from result", tempconn, adOpenDynamic, adLockPessimistic
              MsgBox "原始记录已经清楚，请重新进行考号生成操作！", vbOKOnly + 64, title
              
            Else
              Exit Sub
            End If
        Else
            MsgBox "错误号是：" & Err.Number & Err.Description & "不合理的操作，当前操作被取消！", vbOKOnly + 48, title
        End If
          
End Sub

Private Sub menudaochu_Click()
    If daochufangshi = "" Then
       setting1.Show
       Exit Sub
    End If
       
    Dim tempbanji As ADODB.Recordset  '存储班级的数据集
    Dim temprs As ADODB.Recordset     '用于临时存储的数据集
    Dim daochurs As ADODB.Recordset   '存储导出信息的数据集
    Dim daochuconn As ADODB.Connection '数据库连接
    Dim linshi As ADODB.Recordset '临时查找考场地点的数据集
    Set daochuconn = New ADODB.Connection
    daochuconn.ConnectionString = pubconnstr  'pubconnstr是数据连接字符串
    daochuconn.Open
    wenjianpath = App.Path & "\考号文件夹\"   '创建并命名文件夹
    fname = wenjianpath & Year(Now) & Month(Now) & Day(Now) & Hour(Now) & Minute(Now) & Second(Now)
    mulu = fname
    MkDir mulu
    'jindu.Max = zrs
    jindu.Value = 0
    If daochufangshi = "按班级导出" Or daochufangshi = "按两种方式导出" Then
       jindu.Visible = True  '进度条可见
       jindu.Value = 0
       Set tempbanji = New ADODB.Recordset
       tempbanji.Open "select distinct [班级] from result order by [班级] asc", daochuconn, adOpenDynamic, adLockPessimistic
       mulu = fname & "\按班级导出"
       MkDir mulu
       While tempbanji.EOF = False
             
             wenjianming = mulu & "\" & tempbanji.Fields("班级") & ".xls"
             FileCopy wenjianpath & "model\temp.xls", wenjianming
             Set sourceconn = New ADODB.Connection
             sourceconn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & wenjianming & ";Extended Properties=""Excel 8.0;"""
             sourceconn.Open                      '循环打开已经分配考号的班级
             Set daochurs = New ADODB.Recordset
             sql = "select * from result where [班级]='" & tempbanji.Fields("班级") & "' order by [成绩] desc"
             daochurs.Open sql, daochuconn, adOpenDynamic, adLockPessimistic
             Set linshi = New ADODB.Recordset
             sql = "select [考场地点] from exam where [考场名称]='" & daochurs.Fields("考场") & "'"
             linshi.Open sql, daochuconn, adOpenDynamic, adLockPessimistic
             
          
             Set temprs = New ADODB.Recordset
             temprs.Open "Select * from [sheet1$]", sourceconn, adOpenDynamic, adLockPessimistic
             While daochurs.EOF = False
                temprs.AddNew
                temprs.Fields("考号") = daochurs.Fields("考号")
                temprs.Fields("姓名") = daochurs.Fields("姓名")
                temprs.Fields("学号") = daochurs.Fields("学号")
                temprs.Fields("成绩") = daochurs.Fields("成绩")
                temprs.Fields("考生班级") = daochurs.Fields("班级")
                temprs.Fields("考场名称") = daochurs.Fields("考场")
                temprs.Fields("考场地点") = linshi.Fields("考场地点")
                daochurs.MoveNext
                temprs.Update
                
                If jindu.Value = jindu.Max Then
                       jindu.Visible = False
                End If
                jindu.Value = jindu.Value + 1
             Wend
                tempbanji.MoveNext
                               temprs.close
                               daochurs.close
                           Set daochurs = Nothing
                           Set temprs = Nothing
            Wend
        End If
        If daochufangshi = "按考场导出" Or daochufangshi = "按两种方式导出" Then
           jindu.Visible = True  '进度条可见
           jindu.Value = 0