iFix中如何实现EXCEL报表

在VBA中引用Microsoft Excel 11.0 Object Library和Microsoft ADO 6.0 Library。在画面中添加个按钮。复制以下代码:
‘----------开始复制(不包括此行)--------------
Option Explicit
Dim rsADO As ADODB.Recordset
Dim cnADO As ADODB.Connection
Private Sub Command1_Click()
Dim StrDir As String
StrDir = "E:\"

Dim i As Long
Dim Sql As String
Sql = "SELECT * FROM THISNODE"
Set cnADO = New ADODB.Connection
Set rsADO = New ADODB.Recordset
cnADO.C
cnADO.Open
rsADO.CursorLocation = adUseClient
rsADO.Open Sql, cnADO, adOpenDynamic, adLockUnspecified, -1

If rsADO.RecordCount <= 0 Then
MsgBox "无数据!", vbOKOnly + vbInformation, "信息..."
Set cnADO = Nothing
Set rsADO = Nothing
Exit Sub
End If

Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object

Set xlApp = New Excel.Application
xlApp.DisplayAlerts = False
xlApp.Visible = False
Set xlBook = xlApp.Workbooks.Open(StrDir & "\报表.xls")'需要文件(E:\报表.xls)
Set xlSheet = xlBook.Worksheets(1)

For i = 1 To rsADO.RecordCount
xlSheet.Cells(i, 1) = rsADO.Fields(1).Value & ""
xlSheet.Cells(i, 2) = rsADO.Fields(2).Value & ""
xlSheet.Cells(i, 3) = rsADO.Fields(3).Value & ""
xlSheet.Cells(i, 4) = rsADO.Fields(4).Value & ""
Next i

xlApp.Visible = True
xlApp.DisplayAlerts = False

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Set cnADO = Nothing
Set rsADO = Nothing
End Sub
‘----------结束复制(不包括次行)--------------
注意:1.iFix历史数据库只支持读90天的数据,在SQL语句中限定时间即可。
2.也可以读取其他数据源的数据,如ACCESS、SQL SERVER等,只要改一下ConnectionString

相关主题
相关文档
最新文档