les_cx.frm

来自「这是一个非常 好的一个程序进行体会一下学生管理系统」· FRM 代码 · 共 367 行

VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Begin VB.Form Form7 
   BackColor       =   &H00FFC0C0&
   Caption         =   "选课信息查询"
   ClientHeight    =   7065
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   11535
   LinkTopic       =   "Form7"
   MDIChild        =   -1  'True
   ScaleHeight     =   7065
   ScaleWidth      =   11535
   WindowState     =   2  'Maximized
   Begin MSFlexGridLib.MSFlexGrid myFlexGrid 
      Height          =   5175
      Left            =   3600
      TabIndex        =   11
      Top             =   600
      Width           =   7815
      _ExtentX        =   13785
      _ExtentY        =   9128
      _Version        =   393216
      Cols            =   8
      FixedCols       =   0
      BackColorBkg    =   16761024
      HighLight       =   0
      AllowUserResizing=   3
   End
   Begin VB.CommandButton Command3 
      Caption         =   "查询结果导出"
      Enabled         =   0   'False
      Height          =   495
      Left            =   6720
      TabIndex        =   10
      Top             =   6120
      Width           =   1455
   End
   Begin VB.Frame Frame1 
      BackColor       =   &H00FFC0C0&
      Caption         =   "请输入查询条件"
      BeginProperty Font 
         Name            =   "宋体"
         Size            =   10.5
         Charset         =   134
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   5175
      Left            =   240
      TabIndex        =   0
      Top             =   360
      Width           =   3135
      Begin VB.ComboBox Combo2 
         Height          =   300
         ItemData        =   "Les_cx.frx":0000
         Left            =   960
         List            =   "Les_cx.frx":0002
         TabIndex        =   13
         Top             =   2400
         Width           =   1815
      End
      Begin VB.TextBox Text1 
         Height          =   300
         Index           =   1
         Left            =   1200
         TabIndex        =   12
         Top             =   3360
         Width           =   1335
      End
      Begin VB.CommandButton Command2 
         Caption         =   "重置条件"
         Height          =   495
         Left            =   1800
         TabIndex        =   8
         Top             =   4320
         Width           =   1215
      End
      Begin VB.CommandButton Command1 
         Caption         =   "开始查询"
         Height          =   495
         Left            =   120
         TabIndex        =   7
         Top             =   4320
         Width           =   1215
      End
      Begin VB.ComboBox Combo1 
         Height          =   300
         ItemData        =   "Les_cx.frx":0004
         Left            =   960
         List            =   "Les_cx.frx":0006
         TabIndex        =   3
         Top             =   480
         Width           =   1575
      End
      Begin VB.TextBox Text1 
         Height          =   300
         Index           =   0
         Left            =   960
         TabIndex        =   2
         Top             =   1440
         Width           =   1335
      End
      Begin VB.Label Label4 
         BackColor       =   &H00FFC0C0&
         Caption         =   "教师姓名："
         BeginProperty Font 
            Name            =   "宋体"
            Size            =   10.5
            Charset         =   134
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Left            =   240
         TabIndex        =   6
         Top             =   3360
         Width           =   1095
      End
      Begin VB.Label Label3 
         BackColor       =   &H00FFC0C0&
         Caption         =   "课程名："
         BeginProperty Font 
            Name            =   "宋体"
            Size            =   10.5
            Charset         =   134
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Left            =   240
         TabIndex        =   5
         Top             =   2400
         Width           =   975
      End
      Begin VB.Label Label2 
         BackColor       =   &H00FFC0C0&
         Caption         =   "学 号："
         BeginProperty Font 
            Name            =   "宋体"
            Size            =   10.5
            Charset         =   134
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Index           =   0
         Left            =   240
         TabIndex        =   4
         Top             =   1440
         Width           =   735
      End
      Begin VB.Label Label1 
         BackColor       =   &H00FFC0C0&
         Caption         =   "学 期："
         BeginProperty Font 
            Name            =   "宋体"
            Size            =   10.5
            Charset         =   134
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   255
         Left            =   240
         TabIndex        =   1
         Top             =   480
         Width           =   735
      End
   End
   Begin VB.Label Label5 
      BackColor       =   &H00FFC0C0&
      Caption         =   "查询结果显示"
      BeginProperty Font 
         Name            =   "宋体"
         Size            =   10.5
         Charset         =   134
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   255
      Left            =   3600
      TabIndex        =   9
      Top             =   240
      Width           =   2055
   End
End
Attribute VB_Name = "Form7"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Attribute VB_Ext_KEY = "RVB_UniqueId" ,"41FF01E5022C"
Attribute VB_Ext_KEY = "RVB_ModelStereotype" ,"Form"
Dim mrc As ADODB.Recordset   '定义记录集
Dim txtSQL As String         '定义SQL字符串
Dim i As Integer             '定义循环变量

Private Sub Command1_Click() '确定按钮
'判断是否填写查询条件
    For i = 0 To 1
        If Text1(i).Text <> "" Then GoTo search
    Next i
    If Combo1.Text <> "" Or Combo2.Text <> "" Then GoTo search
    MsgBox "请输入查询条件！", 0 + 48 + 0, "注意！"
    Text1(0).SetFocus
    Exit Sub
search:
    myFlexGrid.Rows = 1 ' 清空前一次查询结果
    txtSQL = "select Les_info.Stu_no,Stu_info.Stu_name,Les_info.Cou_no," & _
             "Cou_info.Cou_name,Les_info.Les_tna,Les_info.Les_cj,Les_info.Les_jd," & _
             "Les_info.Les_term from Stu_info,Les_info,Cou_info  " & _
             " where Stu_info.Stu_no=Les_info.Stu_no" & _
             " and Les_info.Cou_no=Cou_info.Cou_no  "
    '以下将查询进行复合
    If Combo1.Text <> "" Then
      txtSQL = txtSQL + " and Les_info.Les_term=" & "'" & Trim(Combo1.Text) & "'"
    End If
    If Text1(0).Text <> "" Then
      txtSQL = txtSQL + " and Les_info.Stu_no=" & "'" & Trim(Text1(0).Text) & "'"
    End If
    If Combo2.Text <> "" Then
      txtSQL = txtSQL + " and Cou_info.Cou_name=" & "'" & Trim(Combo2.Text) & "'"
    End If
    If Text1(1).Text <> "" Then
      txtSQL = txtSQL + " and Les_info.Les_tna=" & "'" & Trim(Text1(1).Text) & "'"
    End If
    txtSQL = txtSQL + " order by Les_info.Cou_no,Les_info.Stu_no " '记录集按课程号,学号升序排列
    Set mrc = ExecuteSQL(txtSQL, MsgText)
    If mrc.EOF Then
       MsgBox "没有符合条件的记录！", 0 + 48 + 0, "注意！"
       mrc.Close
       Exit Sub
    End If
    With myFlexGrid  '将查询结果显示出来
         Do While Not mrc.EOF
            .Rows = .Rows + 1
            .CellAlignment = 2
             For i = 0 To 7
               If Not IsNull(mrc.Fields(i)) Then '如果字段非空则显示字段值
               .TextMatrix(.Rows - 1, i) = mrc.Fields(i)
               End If
             Next i
            mrc.MoveNext
         Loop
    End With
    Command3.Enabled = True
End Sub

Private Sub Command2_Click() ' 重置按钮
    For i = 0 To 1
      Text1(i).Text = ""
    Next i
    Combo1.Text = ""
    Combo2.Text = ""
    Command3.Enabled = False
    myFlexGrid.Rows = 1
End Sub

Private Sub Command3_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
    colnum = myFlexGrid.Cols
    ReDim Fieldlen(colnum)
    '往表内写入标题
    xlSheet.Cells(1, 2).Value = "选课信息查询结果表 "
    '往表内写入字段名
    For i = 0 To colnum - 1
        xlSheet.Cells(3, i + 1).Value = myFlexGrid.TextMatrix(0, i)
        Fieldlen(i) = LenB(myFlexGrid.TextMatrix(0, i))
    Next i
    '开始往表内写查询结果
    mrc.MoveFirst
    row = 4
    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
    With xlSheet
        .Cells(1, 2).Font.Name = "黑体" '设标题为黑体字
        .Cells(1, 2).Font.Size = 24   '标题字体大小为24
        .Range(.Cells(3, 1), .Cells(row - 1, colnum)).Borders.LineStyle = xlContinuous
        '设表格边框样式
    End With
    xlApp.Visible = True '显示表格
   ' xlBook.Save '保存'
    Set xlApp = Nothing '交还控制给Excel
End Sub

Private Sub Form_Load()
    With myFlexGrid '初始化显示查询结果的控件
         .Rows = 1
         .CellAlignment = 2
         .TextMatrix(0, 0) = "学生学号"
         .TextMatrix(0, 1) = "学生姓名"
         .TextMatrix(0, 2) = "课程号"
         .TextMatrix(0, 3) = "课程名称"
         .TextMatrix(0, 4) = "授课教师"
         .TextMatrix(0, 5) = "成绩"
         .TextMatrix(0, 6) = "绩点"
         .TextMatrix(0, 7) = "开设学期"
         For i = 0 To 7
            .ColAlignment(i) = 3
         Next i
         .ColWidth(3) = 1500
    End With
    '初始化学期选择框
    txtSQL = "select distinct Les_term from Les_info order by Les_term "
    Set mrc = ExecuteSQL(txtSQL, MsgText)
    While Not mrc.EOF
        Combo1.AddItem mrc.Fields(0)
        mrc.MoveNext
    Wend
    mrc.Close
    '初始化课程名选择框
    txtSQL = "select Cou_name from Cou_info order by Cou_no "
    Set mrc = ExecuteSQL(txtSQL, MsgText)
    While Not mrc.EOF
        Combo2.AddItem mrc.Fields(0)
        mrc.MoveNext
    Wend
    mrc.Close
    Set mrc = Nothing
End Sub

Private Sub Form_Unload(Cancel As Integer)
Set mrc = Nothing
End Sub

Private Sub Text1_KeyDown(Index As Integer, KeyCode As Integer, Shift As Integer)
    If KeyCode = 13 Then
        Call Command1_Click
    End If
End Sub