frmbookbrowse.frm

自考软件工程_宋瑞峰 自考软件工程_宋瑞峰

Unload Me
End Sub

'将数据导出到Excel
Private Sub CmdExportExcel_Click()
Dim rs As ADODB.Recordset
Dim appExcel As Excel.Application
Dim bookExcel As Excel.Workbook
Dim sheetExcel As Excel.Worksheet
Dim i As Integer

If SQL = "" Then
    Exit Sub
End If
Set rs = ExecuteSQL(SQL)

Set appExcel = CreateObject("excel.application")
appExcel.Visible = True

Set bookExcel = appExcel.Workbooks.Add
Set sheetExcel = bookExcel.Worksheets.Add

'开始将记录读入Excel
sheetExcel.Cells(1, 1) = "图书编号"
sheetExcel.Cells(1, 2) = "书名"
sheetExcel.Cells(1, 3) = "作者"
sheetExcel.Cells(1, 4) = "出版社"
sheetExcel.Cells(1, 5) = "图书类别"
sheetExcel.Cells(1, 6) = "价钱"
sheetExcel.Cells(1, 7) = "总库存量"
sheetExcel.Cells(1, 8) = "剩余量"
sheetExcel.Cells(1, 9) = "入库日期"
i = 2
Do While Not rs.EOF
    sheetExcel.Cells(i, 1) = rs.Fields("图书编号")
    sheetExcel.Cells(i, 2) = rs.Fields("书名")
    sheetExcel.Cells(i, 3) = rs.Fields("作者")
    sheetExcel.Cells(i, 4) = rs.Fields("出版社")
    sheetExcel.Cells(i, 5) = rs.Fields("类别")
    sheetExcel.Cells(i, 6) = rs.Fields("价钱")
    sheetExcel.Cells(i, 7) = rs.Fields("总库存量")
    sheetExcel.Cells(i, 8) = rs.Fields("剩余量")
    sheetExcel.Cells(i, 9) = rs.Fields("入库日期")
    rs.MoveNext
    i = i + 1
Loop

rs.Close
Set rs = Nothing
Set sheetExcel = Nothing
Set bookExcel = Nothing
Set appExcel = Nothing
End Sub

Private Sub CmdHomeBook_Click()

SQL = "SELECT 图书编号,书名,作者,出版社,类别,价钱,总库存量,剩余量,入库日期 FROM book WHERE 剩余量 > 0"
DisplayFlexGrid (SQL)
End Sub

Private Sub CmdNewBook_Click()
Dim tempDate As Date

tempDate = Date - 30
'还没成功
SQL = "SELECT 图书编号,书名,作者,出版社,类别,价钱,总库存量,剩余量,入库日期 FROM book WHERE 入库日期 >#" & tempDate & "#"
DisplayFlexGrid (SQL)
End Sub

Private Sub CmdOutBook_Click()
SQL = "SELECT 图书编号,书名,作者,出版社,类别,价钱,总库存量,剩余量,入库日期 FROM book WHERE 总库存量 > 剩余量"
DisplayFlexGrid (SQL)
End Sub

Private Sub CmdPrint_Click()
'打印查询结果
If DataEnvironment1.rstblSearch.State <> adStateClosed Then
    DataEnvironment1.rstblSearch.Close
End If

DataEnvironment1.rstblSearch.Open SQL
Set DataReportBook.DataSource = DataEnvironment1
DataReportBook.DataMember = "tblSearch"
DataReportBook.Show
'DataEnvironment1.rstblSearch.Open "SELECT 图书编号,书名,作者,类别,出版社,价钱,入库日期 FROM book"
End Sub

Private Sub CmdSearch_Click()

If Trim(Text1(0).Text) = "" Then
    MsgBox "对不起，你还未输入条件呢！", vbExclamation + vbOKOnly, "警告"
    Exit Sub
End If

SQL = "SELECT 图书编号,书名,作者,出版社,类别,价钱,总库存量,剩余量,入库日期 FROM book"
'如果执行模糊查询
If Combo1(0).ListIndex < 5 Then
    If Check1.Value = 1 Then
        SQL = "SELECT * FROM book WHERE " & Combo1(0).Text & " LIKE '%" & Text1(0).Text & "%'"
    Else
        SQL = "SELECT * FROM book WHERE " & Combo1(0).Text & " ='" & Text1(0).Text & "'"
    End If
Else
    SQL = "SELECT * FROM book WHERE " & Combo1(0).Text & Combo2(0).Text & Text1(0).Text
End If
If Combo3.ListIndex = 0 Then
If Combo1(1).ListIndex < 5 Then
    If Check1.Value = 1 Then
        SQL = SQL & " AND " & Combo1(1).Text & " LIKE '%" & Text1(1).Text & "%'"
    Else
        SQL = SQL & " AND " & Combo1(1).Text & " ='" & Text1(1).Text & "'"
    End If
Else
    SQL = SQL & " AND " & Combo1(1).Text & Combo2(1).Text & Text1(1).Text
End If
Else
If Combo1(1).ListIndex < 5 Then
    If Check1.Value = 1 Then
        SQL = SQL & " OR " & Combo1(1).Text & " LIKE '%" & Text1(1).Text & "%'"
    Else
        SQL = SQL & " OR " & Combo1(1).Text & " ='" & Text1(1).Text & "'"
    End If
Else
    SQL = SQL & " OR " & Combo1(1).Text & Combo2(1).Text & Text1(1).Text
End If
End If
If Combo4.ListIndex = 0 Then
If Combo1(2).ListIndex < 5 Then
    If Check1.Value = 1 Then
        SQL = SQL & " AND " & Combo1(2).Text & " LIKE '%" & Text1(2).Text & "%'"
    Else
        SQL = SQL & " AND " & Combo1(2).Text & " ='" & Text1(2).Text & "'"
    End If
Else
    SQL = SQL & " AND " & Combo1(2).Text & Combo2(2).Text & Text1(2).Text
End If
Else
If Combo1(2).ListIndex < 5 Then
    If Check1.Value = 1 Then
        SQL = SQL & " AND " & Combo1(2).Text & " LIKE '%" & Text1(2).Text & "%'"
    Else
        SQL = SQL & " AND " & Combo1(2).Text & " ='" & Text1(2).Text & "'"
    End If
Else
    SQL = SQL & " AND " & Combo1(2).Text & Combo2(2).Text & Text1(2).Text
End If
End If
DisplayFlexGrid (SQL)
End Sub

Private Sub Form_Load()
Dim i As Integer
'初始化列表框控件框
For i = 0 To 2
    Combo1(i).Clear
    Combo1(i).AddItem "图书编号", 0
    Combo1(i).AddItem "书名", 1
    Combo1(i).AddItem "出版社", 2
    Combo1(i).AddItem "作者", 3
    Combo1(i).AddItem "图书类别", 4
    Combo1(i).AddItem "价钱", 5
    Combo1(i).ListIndex = 0
    
    Combo2(i).Clear
    Combo2(i).AddItem ">", 0
    Combo2(i).AddItem "=", 1
    Combo2(i).AddItem "<", 2
    Combo2(i).ListIndex = 0
    
Next i
Combo3.Clear
Combo3.AddItem "与", 0
Combo3.AddItem "或", 1
Combo3.ListIndex = 0

Combo4.Clear
Combo4.AddItem "与", 0
Combo4.AddItem "或", 1
Combo4.ListIndex = 0

With MSFlexGrid1
    .Cols = 10
    .Rows = 2
    .TextMatrix(0, 1) = "图书编号"
    .TextMatrix(0, 2) = "书名"
    .TextMatrix(0, 3) = "作者"
    .TextMatrix(0, 4) = "出版社"
    .TextMatrix(0, 5) = "图书类别"
    .TextMatrix(0, 6) = "价钱"
    .TextMatrix(0, 7) = "总库存量"
    .TextMatrix(0, 8) = "剩余量"
    .TextMatrix(0, 9) = "入库日期"
    .ColWidth(0) = 500
    .ColWidth(1) = 1000
    .ColWidth(2) = 2200
    .ColWidth(3) = 1000
     .ColWidth(4) = 2000
    .ColWidth(5) = 1500
     .ColWidth(6) = 1000
    .ColWidth(7) = 800
    .ColWidth(8) = 800
    .ColWidth(9) = 1200
End With

End Sub

Public Sub DisplayFlexGrid(SQL As String)
Dim rs As ADODB.Recordset
Dim strRel As String

Set rs = ExecuteSQL(SQL)

With MSFlexGrid1
    .Cols = 10
    .Rows = 1
    .TextMatrix(0, 1) = "图书编号"
    .TextMatrix(0, 2) = "书名"
    .TextMatrix(0, 3) = "作者"
    .TextMatrix(0, 4) = "出版社"
    .TextMatrix(0, 5) = "图书类别"
    .TextMatrix(0, 6) = "价钱"
    .TextMatrix(0, 7) = "总库存量"
    .TextMatrix(0, 8) = "剩余量"
    .TextMatrix(0, 9) = "入库日期"
    .ColWidth(0) = 500
    .ColWidth(1) = 1000
    .ColWidth(2) = 2200
    .ColWidth(3) = 1000
     .ColWidth(4) = 2000
    .ColWidth(5) = 1500
     .ColWidth(6) = 1000
    .ColWidth(7) = 800
    .ColWidth(8) = 800
    .ColWidth(9) = 1200
    
    Do While Not rs.EOF
    .Rows = .Rows + 1
    .TextMatrix(.Rows - 1, 1) = rs.Fields("图书编号")
    .TextMatrix(.Rows - 1, 2) = rs.Fields("书名")
    .TextMatrix(.Rows - 1, 3) = rs.Fields("作者")
    .TextMatrix(.Rows - 1, 4) = rs.Fields("出版社")
    .TextMatrix(.Rows - 1, 5) = rs.Fields("类别")
    .TextMatrix(.Rows - 1, 6) = rs.Fields("价钱")
    .TextMatrix(.Rows - 1, 7) = rs.Fields("总库存量")
    .TextMatrix(.Rows - 1, 8) = rs.Fields("剩余量")
    .TextMatrix(.Rows - 1, 9) = rs.Fields("入库日期")
    rs.MoveNext
    Loop
    strRel = "共查到 " & rs.RecordCount & " 条记录"
    LblResult.Caption = strRel
    rs.Close
    Set rs = Nothing
End With


End Sub