grade_tj.frm

这是一个非常 好的一个程序进行体会一下学生管理系统

End Sub

Private Sub Command10_Click() '统计信息导出（单个学生）
    Dim colnum As Integer '存字段数量
    Dim row As Integer  '用来记录excel表的当前行
    Dim Fieldlen() '存字段长度值
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets(1)
    If mrc.RecordCount = 0 Then
        MsgBox "查询结果为空，请重新查询！", 0 + 48, "注意！"
        Command2.SetFocus
        Exit Sub
    End If
    '往表内写入标题
    xlSheet.Cells(1, 2).Value = "成绩信息统计表（单个学生）"
    xlSheet.Cells(3, 1).Value = "学号：" & Text1.Text
    xlSheet.Cells(3, 2).Value = "姓名：" & Text2.Text
    If Check4.Value = 1 Then
        colnum = myFlexGrid1.Cols
        ReDim Fieldlen(colnum)
        '往表内写入字段名
        For i = 0 To colnum - 1
            xlSheet.Cells(4, i + 1).Value = myFlexGrid1.TextMatrix(0, i)
            Fieldlen(i) = LenB(myFlexGrid1.TextMatrix(0, i))
        Next i
        '开始往表内写查询结果
        mrc.MoveFirst
        row = 5
        While Not mrc.EOF
            For i = 0 To colnum - 1
                If IsNull(mrc.Fields(i)) = False Then
                    xlSheet.Cells(row, i + 1).Value = mrc.Fields(i)
                    If LenB(mrc.Fields(i)) > Fieldlen(i) Then  '将字段的最大长度保存在Fieldlen()数组中
                        Fieldlen(i) = LenB(mrc.Fields(i))
                        xlSheet.Columns(i + 1).ColumnWidth = Fieldlen(i)
                    End If
                End If
            Next i
            mrc.MoveNext
            row = row + 1
        Wend
    Else: row = 5
    End If
    With xlSheet
        .Cells(1, 2).Font.Name = "黑体" '设标题为黑体字
        .Cells(1, 2).Font.Size = 24   '标题字体大小为24
        If Check4.Value = 1 Then
            .Range(.Cells(4, 1), .Cells(row - 1, colnum)).Borders.LineStyle = xlContinuous '设表格边框样式
        End If
        row = row + 1
        .Cells(row, 1).Value = "统计结果："
        '导出统计结果项目
        For i = 0 To myFlexGrid2.Cols - 1
            .Cells(row, i + 1).Value = myFlexGrid2.TextMatrix(0, i)
        Next i
        For i = 1 To myFlexGrid2.Rows - 1
            row = row + 1
            For j = 0 To myFlexGrid2.Cols - 1
                .Cells(row, j + 1).Value = myFlexGrid2.TextMatrix(i, j)
            Next j
       Next i
     End With
    xlApp.Visible = True '显示表格
   ' xlBook.Save '保存'
    Set xlApp = Nothing '交还控制给Excel
    Command10.Enabled = False
End Sub

Private Sub Command11_Click() '统计信息导出按钮（选课整体）
    Dim colnum As Integer '存字段数量
    Dim row As Integer  '用来记录excel表的当前行
    Dim Fieldlen() '存字段长度值
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets(1)
    If mrc.RecordCount = 0 Then
        MsgBox "查询结果为空，请重新查询！", 0 + 48, "注意！"
        Command5.SetFocus
        Command11.Enabled = False
        Exit Sub
    End If
    '往表内写入标题
    xlSheet.Cells(1, 2).Value = "成绩信息统计表（选课整体）"
    xlSheet.Cells(3, 1).Value = "学期：" & Combo2(0).Text
    xlSheet.Cells(3, 2).Value = "课程名：" & Combo2(1).Text
    xlSheet.Cells(3, 4).Value = "教师：" & Combo2(2).Text
    If Check5.Value = 1 Then
        colnum = myFlexGrid3.Cols
        ReDim Fieldlen(colnum)
        '往表内写入字段名
        For i = 0 To colnum - 1
            xlSheet.Cells(4, i + 1).Value = myFlexGrid1.TextMatrix(0, i)
            Fieldlen(i) = LenB(myFlexGrid1.TextMatrix(0, i))
        Next i
        '开始往表内写查询结果
        mrc.MoveFirst
        row = 5
        While Not mrc.EOF
            For i = 0 To colnum - 1
                If IsNull(mrc.Fields(i)) = False Then
                    xlSheet.Cells(row, i + 1).Value = mrc.Fields(i)
                    If LenB(mrc.Fields(i)) > Fieldlen(i) Then  '将字段的最大长度保存在Fieldlen()数组中
                        Fieldlen(i) = LenB(mrc.Fields(i))
                        xlSheet.Columns(i + 1).ColumnWidth = Fieldlen(i)
                    End If
                End If
            Next i
            mrc.MoveNext
            row = row + 1
        Wend
    Else: row = 5
    End If
    With xlSheet
        .Cells(1, 2).Font.Name = "黑体" '设标题为黑体字
        .Cells(1, 2).Font.Size = 24   '标题字体大小为24
        If Check5.Value = 1 Then
            .Range(.Cells(4, 1), .Cells(row - 1, colnum)).Borders.LineStyle = xlContinuous '设表格边框样式
        End If
        row = row + 1
        .Cells(row, 1).Value = "统计结果："
        '导出统计结果项目
        For i = 1 To List1.ListCount
            .Cells(row + i, 1).Value = List1.List(i - 1)
        Next i
     End With
    xlApp.Visible = True '显示表格
   ' xlBook.Save '保存'
    Set xlApp = Nothing '交还控制给Excel
    Command11.Enabled = False
End Sub

Private Sub Command12_Click() '统计信息导出（班级整体）
    Dim colnum As Integer '存字段数量
    Dim row As Integer  '用来记录excel表的当前行
    Dim Fieldlen() '存字段长度值
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets(1)
    If mrc.RecordCount = 0 Then
        MsgBox "查询结果为空，请重新查询！", 0 + 48, "注意！"
        Command8.SetFocus
        Command12.Enabled = False
        Exit Sub
    End If
    '往表内写入标题
    xlSheet.Cells(1, 2).Value = "成绩信息统计表（班级整体）"
    xlSheet.Cells(3, 1).Value = "班级：" & Text3.Text
    xlSheet.Cells(3, 2).Value = "学期：" & Combo3(0).Text
    xlSheet.Cells(3, 3).Value = "课程名：" & Combo3(1).Text
    If Check6.Value = 1 Then
        colnum = myFlexGrid4.Cols
        ReDim Fieldlen(colnum)
        '往表内写入字段名
        For i = 0 To colnum - 1
            xlSheet.Cells(4, i + 1).Value = myFlexGrid1.TextMatrix(0, i)
            Fieldlen(i) = LenB(myFlexGrid1.TextMatrix(0, i))
        Next i
        '开始往表内写查询结果
        mrc.MoveFirst
        row = 5
        While Not mrc.EOF
            For i = 0 To colnum - 1
                If IsNull(mrc.Fields(i)) = False Then
                    xlSheet.Cells(row, i + 1).Value = mrc.Fields(i)
                    If LenB(mrc.Fields(i)) > Fieldlen(i) Then  '将字段的最大长度保存在Fieldlen()数组中
                        Fieldlen(i) = LenB(mrc.Fields(i))
                        xlSheet.Columns(i + 1).ColumnWidth = Fieldlen(i)
                    End If
                End If
            Next i
            mrc.MoveNext
            row = row + 1
        Wend
    Else: row = 5
    End If
    With xlSheet
        .Cells(1, 2).Font.Name = "黑体" '设标题为黑体字
        .Cells(1, 2).Font.Size = 24   '标题字体大小为24
        If Check6.Value = 1 Then
            .Range(.Cells(4, 1), .Cells(row - 1, colnum)).Borders.LineStyle = xlContinuous '设表格边框样式
        End If
        row = row + 1
        .Cells(row, 1).Value = "统计结果："
        '导出统计结果项目
        For i = 1 To List2.ListCount
            .Cells(row + i, 1).Value = List2.List(i - 1)
        Next i
     End With
    xlApp.Visible = True '显示表格
   ' xlBook.Save '保存'
    Set xlApp = Nothing '交还控制给Excel
    Command12.Enabled = False
End Sub

Private Sub Command2_Click() '确定按钮（选课整体）
    If Combo2(0).Text = "" Then
       MsgBox "请选定学期！", 0 + 48, "注意！"
       Combo2(0).SetFocus
       Exit Sub
    End If
    If Combo2(1).Text = "" Then
       MsgBox "请填写课程名！", 0 + 48, "注意！"
       Combo2(1).SetFocus
       Exit Sub
    End If
    If Combo2(2).Text = "" Then
       MsgBox "请填写教师姓名！", 0 + 48, "注意！"
       Combo2(2).SetFocus
       Exit Sub
    End If
    '三个表联合查询，获取选课学生的学号，姓名，班级，成绩
    txtSQL = "select Les_info.Stu_no,Stu_info.Stu_name,Stu_info.Stu_class,Les_info.Les_cj" & _
             " from Stu_info,Les_info,Cou_info where Les_info.Stu_no=Stu_info.Stu_no" & _
             " and Les_info.Cou_no=Cou_info.Cou_no" & " and Les_info.Les_tna= " & _
             "'" & Trim(Combo2(2).Text) & "'" & " and Cou_info.Cou_name=" & _
             "'" & Trim(Combo2(1).Text) & "'" & " and Les_info.Les_term=" & _
             "'" & Trim(Combo2(0).Text) & "'"
    Set mrc = ExecuteSQL(txtSQL, MsgText) '建立并打开返回字符集
    If mrc.EOF Then
        MsgBox "没有符合条件的记录！", 0 + 48, "注意！"
        Combo2(0).SetFocus
        mrc.Close
        Set mrc = Nothing
        Exit Sub
    End If
    myFlexGrid3.Rows = 1
    While Not mrc.EOF
        With myFlexGrid3
             .Rows = .Rows + 1
             Dim i As Integer
             For i = 0 To 3
                If Not IsNull(mrc.Fields(i)) Then
                    .TextMatrix(.Rows - 1, i) = mrc.Fields(i)
                End If
             Next i
        End With
        mrc.MoveNext
    Wend
    Frame5.Visible = True
    Command2.Enabled = False
End Sub

Private Sub Command3_Click() '重置按钮（单个学生）
    Text1.Text = ""
    Text1.SetFocus
    myFlexGrid1.Rows = 1
    Frame3.Visible = False
    myFlexGrid2.Visible = False
    Command1.Enabled = True
    For i = 0 To 2
        Check1(i).Value = 0
    Next i
    Command10.Visible = False
    Check4.Visible = False
End Sub

Private Sub Command4_Click() '开始统计按钮（单个学生）
    myFlexGrid2.Visible = True
    Call Term_type '统计学期数
    myFlexGrid2.Cols = termtype + 1
    With myFlexGrid2
        .Rows = 1
        For i = 0 To termtype - 1
            .TextMatrix(0, i + 1) = term(i)
            .ColAlignment(i + 1) = 3
        Next i
    End With
    checknum1 = 0
    '如果“平均绩点”框被选中
    If Check1(0).Value = 1 Then
        checknum1 = checknum1 + 1
        myFlexGrid2.Rows = myFlexGrid2.Rows + 1
        myFlexGrid2.TextMatrix(checknum1, 0) = "平均绩点"
        Call Aver_jd
    End If
    '如果“累计学分”框被选中
    If Check1(1).Value = 1 Then
        checknum1 = checknum1 + 1
        myFlexGrid2.Rows = myFlexGrid2.Rows + 1
        myFlexGrid2.TextMatrix(checknum1, 0) = "累计学分"
        Call Term_sum
    End If
    '如果“所获总学分”框被选中
    If Check1(2).Value = 1 Then
        checknum1 = checknum1 + 1
        myFlexGrid2.Rows = myFlexGrid2.Rows + 1
        myFlexGrid2.TextMatrix(checknum1, 0) = "所获总学分"
        Call Total_sum
    End If
    If checknum1 = 0 Then
        MsgBox "请现选择统计项目！", 0 + 48, "注意！"
        Check1(0).SetFocus
        Exit Sub
    End If
    Command10.Visible = True
    Check4.Visible = True
    Command10.Enabled = True
End Sub

Private Sub Command5_Click() '重置按钮（选课整体）
    For i = 0 To 2
        Combo2(i).Text = ""
    Next i
    Combo2(0).SetFocus
    Command2.Enabled = True
    Frame5.Visible = False '隐藏选择统计项目的frame
    List1.Visible = False '隐藏结果显示框
    List1.Clear           '清空结果显示框的内容
    myFlexGrid3.Rows = 1
    For i = 0 To 5
        Check2(i).Value = 0
    Next i
    Command11.Visible = False
    Check5.Visible = False
End Sub

Private Sub Command6_Click() '开始统计按钮（选课整体）
    checknum2 = 0
    List1.Clear
    If Check2(0).Value = 1 Then
        checknum2 = checknum2 + 1
        Call Average(1)
    End If
    If Check2(1).Value = 1 Then
        checknum2 = checknum2 + 1
        Call Highest(1)
    End If
    If Check2(2).Value = 1 Then
        checknum2 = checknum2 + 1
        Call Lowest(1)
    End If
    If Check2(3).Value = 1 Then
        checknum2 = checknum2 + 1
        Call Qualified(1)
    End If
    If Check2(4).Value = 1 Then
        checknum2 = checknum2 + 1
        Call Excellent(1)
    End If
    If Check2(5).Value = 1 Then
        checknum2 = checknum2 + 1
        MsgBox "要统计分段人数，请先设置分数段！", 0 + 48, "注意！"
        tj_type = 1
        Form6.Show
    End If
    If checknum2 = 0 Then
        MsgBox "请现选择统计项目！", 0 + 48, "注意！"
        Check2(0).SetFocus
        Exit Sub
    End If
    List1.Visible = True
    Command11.Visible = True
    Command11.Enabled = True
    Check5.Visible = True
End Sub

Private Sub Command7_Click() '开始统计按钮（班级整体）
    List2.Clear
    checknum3 = 0
    If Check3(0).Value = 1 Then
        checknum3 = checknum3 + 1
        Call Average(2)
    End If
    If Check3(1).Value = 1 Then
        checknum3 = checknum3 + 1
        Call Highest(2)
    End If
    If Check3(2).Value = 1 Then
        checknum2 = checknum2 + 1
        Call Lowest(2)
    End If
    If Check3(3).Value = 1 Then
        checknum3 = checknum3 + 1
        Call Qualified(2)
    End If
    If Check3(4).Value = 1 Then
        checknum3 = checknum3 + 1
        Call Excellent(2)
    End If
    If Check3(5).Value = 1 Then
        checknum3 = checknum3 + 1
        MsgBox "要统计分段人数，请先设置分数段！", 0 + 48, "注意！"
        tj_type = 2