form1.frm

一个用VB进行编写的学校成绩管理系统的一部分

VERSION 5.00
Begin VB.Form form1 
   Caption         =   "成绩单管理"
   ClientHeight    =   5400
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   6480
   LinkTopic       =   "Form1"
   ScaleHeight     =   5400
   ScaleWidth      =   6480
   StartUpPosition =   3  '窗口缺省
   Begin VB.CommandButton Command2 
      Caption         =   "打印预览"
      Height          =   615
      Left            =   2160
      TabIndex        =   5
      Top             =   4440
      Width           =   975
   End
   Begin VB.Frame Frame1 
      Caption         =   "打印预览的条件"
      Height          =   3735
      Left            =   240
      TabIndex        =   0
      Top             =   240
      Width           =   5295
      Begin VB.ComboBox Combo_speciality 
         Height          =   300
         Left            =   1800
         Style           =   2  'Dropdown List
         TabIndex        =   7
         Top             =   2040
         Width           =   1695
      End
      Begin VB.ComboBox Combo_school 
         Height          =   300
         Left            =   1800
         Style           =   2  'Dropdown List
         TabIndex        =   6
         Top             =   1320
         Width           =   1695
      End
      Begin VB.ComboBox Combo_class 
         Height          =   300
         Left            =   1800
         Style           =   2  'Dropdown List
         TabIndex        =   4
         Top             =   2760
         Width           =   1695
      End
      Begin VB.ComboBox Comrxjb 
         Height          =   300
         Left            =   1800
         Style           =   2  'Dropdown List
         TabIndex        =   2
         Top             =   480
         Width           =   1695
      End
      Begin VB.Label Label4 
         Caption         =   "专业："
         Height          =   375
         Left            =   480
         TabIndex        =   9
         Top             =   2160
         Width           =   975
      End
      Begin VB.Label Label3 
         Caption         =   "院系："
         Height          =   495
         Left            =   480
         TabIndex        =   8
         Top             =   1320
         Width           =   1095
      End
      Begin VB.Label Label2 
         Caption         =   "班级："
         Height          =   255
         Left            =   480
         TabIndex        =   3
         Top             =   2880
         Width           =   2175
      End
      Begin VB.Label Label1 
         Caption         =   "届别："
         Height          =   255
         Left            =   480
         TabIndex        =   1
         Top             =   600
         Width           =   1695
      End
   End
End
Attribute VB_Name = "form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False





Private Sub Combo_school_Click()
Dim ls_school As String
Dim ls_sqlstr As String
Dim ls_enrolled_time As String
Dim lu_res As New ADODB.Recordset


ls_school = Left(Combo_school.Text, 2)
ls_enrolled_time = Comrxjb.Text

If ls_enrolled_time <> "" Then
ls_sqlstr = "select speciality_id,speciality_name from speciality where school_id='" & ls_school & "' and enrolled_time=" & ls_enrolled_time & " order by speciality_id"
lu_res.Open ls_sqlstr, databaseModule.connectionstring, adOpenForwardOnly, adLockReadOnly, adCmdText

Combo_speciality.Clear
If Not lu_res.EOF Then
   
   While Not lu_res.EOF

   Combo_speciality.AddItem (lu_res!speciality_id & "  " & lu_res!speciality_name)
     lu_res.MoveNext
   Wend



   lu_res.Close

End If
Else
 MsgBox "入学届别为空", vbOKOnly, "系统提示"
End If
End Sub

Private Sub Combo_speciality_Click()
Dim ls_school As String
Dim ls_speciality As String
Dim ls_enrolled_time As String
Dim ls_sqlstr As String
Dim lu_res As New ADODB.Recordset


ls_school = Left(Combo_school.Text, 2)
ls_speciality = Left(Combo_speciality.Text, 2)
ls_enrolled_time = Comrxjb.Text


ls_sqlstr = "select class_id,class_name from class where school_id='" & ls_school & "' and enrolled_time=" & ls_enrolled_time & "and speciality_id ='" & ls_speciality & " ' order by speciality_id"
lu_res.Open ls_sqlstr, databaseModule.connectionstring, adOpenForwardOnly, adLockReadOnly, adCmdText

Combo_class.Clear
If Not lu_res.EOF Then
   
   While Not lu_res.EOF

   Combo_class.AddItem (lu_res!class_id & "  " & lu_res!class_name)
     lu_res.MoveNext
   Wend




   lu_res.Close

End If

End Sub

Private Sub Command2_Click()
Dim xlapp As Excel.Application
Dim l As Integer
Dim i As Integer
Dim j As Integer
Dim l1 As Integer
Dim l2 As Integer
Dim l3 As Integer
Dim l4 As Integer
Dim l5 As Integer
Dim yk As Integer
Dim qk As Integer
Dim ls_school As String
Dim ls_enrolled_time As String
Dim ls_speciality As String
Dim ls_class As String

Dim ls_sqlstr As String
Dim lu_res As New ADODB.Recordset

ls_school = Left(Combo_school.Text, 2)
ls_speciality = Left(Combo_speciality.Text, 2)
ls_enrolled_time = Comrxjb.Text
ls_class = Left(Combo_class.Text, 2)

ls_sqlstr = "select a.student_id,a.student_name,b.score from student_natural as a,xscj as b where a.school_id='" & ls_school & "' and a.[current_time]=" & ls_enrolled_time & " and a.speciality_id ='" & ls_speciality & "' and a.class_id='" & ls_class & "' and a.student_id=b.student_id order by right(a.student_id,2)"
lu_res.Open ls_sqlstr, databaseModule.connectionstring, adOpenForwardOnly, adLockReadOnly, adCmdText

Set xlapp = New Excel.Application
xlapp.Workbooks.Open App.Path & "\cjd.xlt"
 i = 9
 j = 1

  While Not lu_res.EOF
  
  If i < 39 Then
xlapp.Cells(i, 3) = lu_res.Fields(j).Value
xlapp.Cells(i, 4) = lu_res.Fields(j + 1).Value
Select Case xlapp.Cells(i, 4)
 Case 90 To 100
  l1 = l1 + 1
  Case 80 To 89
  l2 = l2 + 1
 Case 70 To 79
  l3 = l3 + 1
   Case 60 To 69
  l4 = l4 + 1
   Case 50 To 59
  l5 = l5 + 1
  Case 0
  qk = qk + 1
  End Select
yk = yk + 1
i = i + 1
Else
xlapp.Cells(i - 30, 9) = lu_res.Fields(j).Value
xlapp.Cells(i - 30, 10) = lu_res.Fields(j + 1).Value
Select Case xlapp.Cells(i - 30, 10)
 Case 90 To 100
  l1 = l1 + 1
  Case 80 To 89
  l2 = l2 + 1
 Case 70 To 79
  l3 = l3 + 1
   Case 60 To 69
  l4 = l4 + 1
   Case 50 To 59
  l5 = l5 + 1
  Case 0
  qk = qk + 1
  End Select
yk = yk + 1
i = i + 1
End If
lu_res.MoveNext
Wend
lu_res.Close

xlapp.Range("A2:B2:C2:D2") = Mid(Combo_school.Text, 3)
xlapp.Range("J2:G2:H2:I2") = Mid(Combo_speciality.Text, 3)
 xlapp.Range("L2:M2") = Mid(Combo_class.Text, 3)
 xlapp.Range("E40") = l1
 xlapp.Range("E42") = l2
 xlapp.Range("E44") = l3
 xlapp.Range("E46") = l4
 xlapp.Range("E48") = l5
  xlapp.Range("L41:L40") = yk
 xlapp.Range("L44:L43") = yk - qk
 xlapp.Range("l46") = qk
xlapp.Range("K48:L48") = Date

xlapp.Range("C1") = Left(Date, 4) + "~"
xlapp.Range("D1") = CStr(Left(Date, 4) + 1)
If Mid(Date, 7, 1) <> "- " Then
  xlapp.Range("H1") = " 一"
  l = 1
Else
  If Mid(Date, 7, 1) < 8 Then
  xlapp.Range("H1") = " 二"
  l = 2
   Else
   xlapp.Range("H1") = " 一"
   l = 1
   End If
   End If
   ls_sqlstr = "select teacher_name from jsskqk where class_name='" & Trim(Mid(Combo_class.Text, 3)) & "' and left(term,2)='" & Mid(Date, 3, 2) & "' and right(term,1)='" & l & "'"
lu_res.Open ls_sqlstr, databaseModule.connectionstring, adOpenForwardOnly, adLockReadOnly, adCmdText
If Not lu_res.EOF Then
xlapp.Range("L3:L4") = lu_res!teacher_name
Else
MsgBox "无教师授课 ", vbOKOnly, "系统提示"
End If
lu_res.Close
xlapp.Visible = True


End Sub



Private Sub Form_Load()
Dim rs As New ADODB.Recordset
Dim sqlstr As String
Dim i As Integer
sqlstr = "select school_id,school_name from School order by school_id"
rs.Open sqlstr, databaseModule.connectionstring, adOpenForwardOnly, adLockReadOnly, adCmdText
While Not rs.EOF
Combo_school.AddItem (rs!school_id & "  " & rs!school_name)
rs.MoveNext
Wend
rs.Close

For i = 1997 To 2010
  Comrxjb.AddItem (CStr(i))
  
Next

End Sub