IFIX连接SQL读写数据和制作excel报表图文教程

二、 IFIX制作excel报表

1、在建好数据库和数据源（参照1.1-1.3）后，新建一个ifix画面，添加一个ole对象，选择“Microsoft Web Browser”,用来显示报表；

2、新建一张excel报表模板，放到工程目录的APP文件夹下，如“人工数据日报表.xls”； 3、新建一个按钮，为其编写脚本，粘贴如下代码（覆盖新建画面原有的代码）：

Option Explicit

Dim a As Single ‘定义中间变量，用来暂存从数据库中取出的数据 Dim b As Single

Dim rsADO As ADODB.Recordset ‘定义连接数据库的ADO变量 Dim conn As New ADODB.Connection Public CmdTruck As New ADODB.Command Public dbUpdata As New ADODB.Command Public rstUpdata As New Recordset

Private Sub CommandButton3_Click() ‘点击按钮的响应函数 Call showbb ‘调用显示报表的函数showbb Call closeDB1 ‘调用关闭数据库的函数closeDB1 End Sub

Private Sub showbb()

Dim Rs As New ADODB.Recordset '定义连接数据库的ADO变量

openDB1 ‘打开DB1数据库，该函数在后面有定义 CmdTruck.ActiveConnection = conn

CmdTruck.CommandText = \ ‘从data表读取COL1和COL2的最大值

Set Rs = CmdTruck.Execute ‘执行SQL语句 a = 0 b = 0

If Not (Rs.BOF Or Rs.EOF) Then ‘将读出来的数据传给中间变量a、b If (Rs!col1) Then a = Rs!col1 b = Rs!col2 End If End If

Dim xlApp As Object '定义报表对象 Dim xlBook As Object Dim xlSheet As Object On Error GoTo errorhandle

Set xlApp = New Excel.Application

Set xlBook = xlApp.Workbooks.Open(System.ProjectPath & \人工数据日报表.xls\ ‘打开报表模板 Set xlSheet = xlBook.Worksheets(1)

xlApp.DisplayAlerts = False '警告禁用 xlApp.Visible = False 'EXCEL隐藏

xlSheet.Cells(10, 4) = a & \ ‘将中间变量的值写到对应excel的单元格 xlSheet.Cells(11, 4) = b & \

xlSheet.SaveAs System.ProjectPath & \人工数据日报表.htm\ '工作表另存为

xlApp.DisplayAlerts = True '警告使能 xlApp.Quit

Me.WebBrowser1.Navigate System.ProjectPath & \人工数据日报表.htm\ ‘在WebBrowser控件上显示报表 xlApp.Quit

Set xlSheet = Nothing '释放内存 Set xlBook = Nothing Set xlApp = Nothing Set CmdTruck = Nothing Set Rs = Nothing Exit Sub

errorhandle:

MsgBox \报表生成错误！\信息...\

Set xlSheet = Nothing '释放内存 Set xlBook = Nothing Set xlApp = Nothing End Sub

Public Sub openDB1() '定义打开数据库的函数openDB1 If conn.State <> adStateOpen Then With conn

.ConnectionString = \pwd=123456;\ ‘连接数据源的相关信息

.Mode = adModeReadWrite .Open End With End If End Sub

Public Sub closeDB1() '定义关闭数据库的函数closeDB1

If conn.State = adStateOpen Then conn.Close End If End Sub

4、添加代码后点击“工具”-“引用”，在下图界面中，勾上

microsoft activex data objects

2.1 library和Microsoft excel 12.0 object library，否则会报错。

5、最后保存代码。