导出Excel

界面

---

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

---