界面
VB导出Excel的优点:
个人感觉将查询到的内容以表格形式导出,更直观,也很方便,方便信息的整理。
代码:
Dim i As Integer
Dim j As Integer
Dim xlApp As Object '必须这样定义
Dim xlBook As Object
Dim xlSheet As Object
MSHFlexGrid.Redraw = False '关闭表格重画,加快运行速度
Set xlApp = CreateObject("Excel.Application") '创建EXCEL对象
'打开已经存在的EXCEL工件簿文件
Set xlBook = xlApp.Workbooks.Open(App.Path & "\学生充值记录查询.xlsx")
xlApp.Visible = True '设置EXCEL对象可见
Set xlSheet = xlBook.Worksheets("Sheet1") '设置活动工作表
For i = 0 To MSHFlexGrid.Rows - 1 '行循环
For j = 0 To MSHFlexGrid.Cols - 1 '列循环
MSHFlexGrid.Row = i
MSHFlexGrid.Col = j
'保存到EXCEL
xlBook.Worksheets("Sheet1").Cells(i + 1, j + 1) = MSHFlexGrid.Text
Next j
Next i
MSHFlexGrid.Redraw = True
End Sub`
查询:
Private Sub cmdInquire_Click()
Dim txtSQL As String
Dim MsgText As String
Dim vbExcalmation As String
Dim mrc As ADODB.Recordset
If Trim(txtCard.Text) = "" Then
MsgBox "卡号不能为空", vbOKOnly + vbExclamation, "警告"
txtCard.SetFocus
End If
'组合SQL语句
txtSQL = "select * from money_Info where "
'组合查询语句
txtSQL = txtSQL & " money_Card = '" & Trim(txtCard.Text) & "'"
'查询所有满足条件的内容
txtSQL = txtSQL & " order by money_Card "
Set mrc = ExecuteSQL(txtSQL, MsgText)
'将查询内容显示在表格控件中
With MSHFlexGrid
.Rows = 2
.CellAlignment = 4
.TextMatrix(1, 0) = "卡号"
.TextMatrix(1, 1) = "姓名"
.TextMatrix(1, 2) = "充值金额"
.TextMatrix(1, 3) = "账户余额"
.TextMatrix(1, 4) = "充值日期"
.TextMatrix(1, 5) = "充值时间"
'判断是否移动到数据集对象的最后一条记录
Do While Not mrc.EOF
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(.Rows - 1, 0) = mrc.Fields(0)
.TextMatrix(.Rows - 1, 1) = mrc.Fields(1)
.TextMatrix(.Rows - 1, 2) = mrc.Fields(2)
.TextMatrix(.Rows - 1, 3) = mrc.Fields(3)
.TextMatrix(.Rows - 1, 4) = mrc.Fields(4)
.TextMatrix(.Rows - 1, 5) = mrc.Fields(5)
'移动到下一条记录
mrc.MoveNext
Loop
End With
mrc.Close
End Sub
----------------------------------------------------------
Private Sub Form_Load()
With MSHFlexGrid '初始化flexgrid控件的行标题
.CellAlignment = 4 'flexAlignCenter
.TextMatrix(1, 0) = "卡号"
.TextMatrix(1, 0) = "卡号"
.TextMatrix(1, 1) = "姓名"
.TextMatrix(1, 2) = "充值金额"
.TextMatrix(1, 3) = "账户余额"
.TextMatrix(1, 4) = "充值日期"
.TextMatrix(1, 5) = "充值时间"
End With
Me.Width = Screen.Width * 0.5
Me.Height = Screen.Height * 0.75
Left = 0 ' 在水平方向上居中显示。
Top = 0 ' 在垂直方向上居中显示。
End Sub