iFix中如何实现EXCEL报表

报表控件应用-iFix第一部分数据存储[1] 运行iFix演示程序，添加数字量输出块（DO）DataLogTrig、DataLogEnable。

[2] 工作台开发环境中新建画面DataLog，右键点击选择编辑脚本添加程序：Fix32.Fix.DataLogEnabled.F_CV = 1[3] 菜单中选择插入〉OLE对象，对象类型列表中选择hmiReportDataLog.CtrlX插入到换面中，点击控件右键菜单中选择动画〉配置〉杂项，将控件的属性DataLogEnabled链接数据块DATALOGENABLED ，属性DataLogTrig 链接数据块 DATALOGTRIG，数据转换选择”对象”。

可将控件参数TagValue001- T agValue120链接模拟量数据块的实时值（Fix32.FIX.IFIX1_BATCH_BULKFLOW.F_CV）作为采样点，控件参数TagDesc001- T agDesc120链接模拟量数据块的描述信息（Fix32.FIX.IFIX1_BATCH_BULKFLOW.A_DESC），数据块的描述信息不能为空，不能以数字开头，不能包含运算符号。

[4] 点击控件右键菜单中选择属性，将控件属性DataLogInterval 设为1-7的整数值将控制控件的采样周期为5秒钟至1小时，设为8时停止自动采样。

画面中添加一个按钮[触发手动存储]按钮，按下时将数据块DATALOGTRIG 置1，释放时置0。

[5] 保存画面并切换至运行状态，选中[显示数据]可见设置的数据点名称和iFix传入的变量数据，按控件中[创建数据表]按钮，再按[存储数据]按钮，可在数据库中建立一个名为hmiDataLog的数据表并存储一行数据。

自动状态下控件按照选定的采样周期存储数据。

画面中按[触发手动存储]按钮触发一次数据存储，可实现由外部状态控制数据存储。

[6] 报表软件程序组中打开“数据检索”工具，选择数据表名hmiDataLog，可选字段中选择时标字段TSTAMP 和其它的数据点字段，按[数据检索]按钮可从数据库查询出已存入的历史数据。

IＦＩX连接SＱL读写数据和制作eｘｃel报表图文教程ＩFIX连接ＳQL数据库、制作ｅｘcel报表详细教程一、 IＦIX连接SQL数据库读写数据IＦIX连接ＳQL数据库有多种方法,其中最为方便的两种方法是:(1)使用IFIX本身为SＱL数据库提供的接口SQT和SQＤ;（２)ＶB+ADO。

下面分别介绍着两种方法的实现步骤。

1、使用SQT和ＳＱD读写SQL数据库1.1 安装SＱＬ2００8(只列出关键步骤，其余直接点击“下一步”或“安装”) 打开SQL安装中心,点击“安装”;SQL2００8简体中文版安装包下载链接：点击“全新SＱL Ｓｅrver独立安装或向现有安装添加功能”;点击“输入产品密匙”,点击“下一步”:(根据版本选择)sqｌ server200８密钥Devｅlopeｒ: PＴTFＭ-X４67Ｇ－Ｐ7RＨ2-3Q6CG-4DＭYBEnterｐrise: JD8Y6-HQG６9－P9Ｈ８4-XDTPG-３4MBBMicｒoｓofｔSQＬ Seｒvｅr 200８Ｒ2序列号密钥开发版3２位：MC46H－JＱＲ3C-２ＪRHY-XYRKY－QWPVＭ开发版64位:FＴＭGC-Ｂ2J97－PJ４ＱG-Ｖ84ＹB-MTXX8工组版：XＱ4ＣB-VＫ９P3-４ＷＹＹＨ-４HQX3-Ｋ２Ｒ6ＱWEB版：FＰ4P７-YKＧ２2-ＷGRVK-ＭKGＭＸ-V9MTM数据中心版3２位:ＰTＴFM-Ｘ467G-P7RH2-３Q6ＣＧ－4ＤMＹB数据中心版64位:ＤＤT3B－8W６2Ｘ-P９JD6-8MX7Ｍ－HWK３8企业版3２位：R8８ＰＦ-GMＣFT-KM2KＲ-4R7GB-43K4B企业版６４位：ＧＹF3Ｔ-H２V88-GRPPＨ－HWRJP－ＱＲTYB标准版3２位:CXTFT-7４V４Y-9D48T-2DＭFW-TＸ7CＹ标准版6４位:Ｂ6８Ｑ６-ＫK２R７-89WGＢ-6Q9KＲ－QHFＤW功能选择界面，选择安装全部功能，点击“下一步”；进入实例配置界面，选择默认实例，点击“下一步”;进入服务器配置界面，启动类型均选为“自动”;点击“对所有ＳQL Server 服务使用相同的账户”,在弹出的界面中选择第一个账户,密码不用填；返回服务器配置界面点击下一步;进入数据库引擎配置界面,选择混合模式,输入自己设定的密码，点击“添加当前用户”,点击下一步，直至安装完成,关闭安装中心。

EXCEL在IFIX软件的报表实现巩伟;宋勇江【期刊名称】《自动化技术与应用》【年(卷),期】2011(030)006【摘要】本文介绍了一种将办公软件EXCEL与IFIX实现数据报表的方法,利用EXCEL和IFIX软件对OLE的支持,并结合VBA脚本语言,灵活的扩展了IFIX的报表功能,为在工业生产重要的抄表工作提供了一个思路.%This article introduces a method which combines office software Excel and IF 1X to complete data reports, uses EXCEL and IF1X software to support OLE. And combines with VBA Scripting language to extend IF1X function of data reports flexibelly. It provides an idea for important meter reading in industrial production.【总页数】3页(P88-90)【作者】巩伟;宋勇江【作者单位】黑龙江省科学院自动化研究所,黑龙江哈尔滨150090;黑龙江省科学院自动化研究所,黑龙江哈尔滨150090【正文语种】中文【中图分类】TP311.52【相关文献】1.利用EXCEL完善IFIX监控软件的报表功能 [J], 杨永奇2.应用Excel工具软件实现工程统计报表的自动生成 [J], 张相龙3.利用iFIX监控软件实现数据采集和输出报表 [J], 吴坚兰;崔绍文;高连艳;温艳艳;孙合明4.利用Microsfot Excel实现组态软件复杂报表方法的研究 [J], 潘礼军5.利用Microsfot Excel实现组态软件复杂报表方法的研究 [J], 潘礼军;因版权原因，仅展示原文概要，查看原文内容请购买。

iFix中如何实现EXCEL报表.txt心脏是一座有两间卧室的房子，一间住着痛苦，一间住着快乐。

人不能笑得太响，否则会吵醒隔壁的痛苦。

iFix中如何实现EXCEL报表在VBA中引用Microsoft Excel 11.0 Object Library和Microsoft ADO 6.0 Library。

在画面中添加个按钮。

复制以下代码：‘－－－－－－－－－－开始复制(不包括此行)－－－－－－－－－－－－－－Option ExplicitDim rsADO As ADODB.RecordsetDim cnADO As ADODB.ConnectionPrivate Sub Command1_Click()Dim StrDir As StringStrDir = "E:\"Dim i As LongDim Sql As StringSql = "SELECT * FROM THISNODE"Set cnADO = New ADODB.ConnectionSet rsADO = New ADODB.RecordsetcnADO.CcnADO.OpenrsADO.CursorLocation = adUseClientrsADO.Open Sql, cnADO, adOpenDynamic, adLockUnspecified, -1If rsADO.RecordCount <= 0 ThenMsgBox "无数据！", vbOKOnly + vbInformation, "信息..."Set cnADO = NothingSet rsADO = NothingExit SubEnd IfDim xlApp As ObjectDim xlBook As ObjectDim xlSheet As ObjectSet xlApp = New Excel.ApplicationxlApp.DisplayAlerts = FalsexlApp.Visible = FalseSet xlBook = xlApp.Workbooks.Open(StrDir & "\报表.xls")'需要文件(E:\报表.xls) Set xlSheet = xlBook.Worksheets(1)For i = 1 To rsADO.RecordCountxlSheet.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 ixlApp.Visible = TruexlApp.DisplayAlerts = FalseSet xlSheet = NothingSet xlBook = NothingSet xlApp = NothingSet cnADO = NothingSet rsADO = NothingEnd Sub‘－－－－－－－－－－结束复制(不包括次行)－－－－－－－－－－－－－－注意：1.iFix历史数据库只支持读90天的数据，在SQL语句中限定时间即可。

IFIX通过VB脚本调用ACCESS数据库数据制作报表作者的前一篇文章已经详细介绍了，如何通过调度将数据保存在access数据库中，今天分享一下，通过脚本调用数据库中的数据形成报表。

步骤01：建立报表模板1．使用Excel根据实际报表样式建立报表模板2．将报表模板另存为（*.htm,*.html）格式文件，文件打开如下图所示：步骤02：新建报表查询画面1.通过IFIX工具箱中的插入OLE对象，插入控件Microsoft date and timecontrol，控件属性中，将customformat值修改为yyyy/MM/dd HH:mm:ss，控件名称默认为DTPicker1；2.同样通过IFIX工具箱中的插入OLE对象，插入Microsoft Web Broswer控件，用于显示报表；3.通过工具箱插入按钮，用于进行报表查询；如下图所示：步骤03：VB脚本开发，调用ACCESS数据库中的历史数据，填写到报表模板中；具体脚本如下：Private Sub CommandButton4_Click()'On Error Resume NextDim OutReportFile As String '定义打开的报表模板路径和名称变量Dim InReportFile As String '另行保存报表的路径和名称变量Dim cn As ADODB.ConnectionDim res As ADODB.RecordsetDim StrSQL As StringDim i As IntegerDim row As IntegerDim MyDateMyDate = Format(Now(), "yyyy-MM-dd")Dim msexcel As Excel.ApplicationSet msexcel = CreateObject("Excel.Application")With msexcel.Visible = False '如为FALSE，则不显示EXCEL。

写IFIX实时数据到ExcelDescription 描述Inserting FIX Dynamics data into an Excel worksheet.把FIX数据写入EXCEL工作表ResolutionThe following procedure will enable you to insert FIX Dynamics data into an Excel worksheet: 下面的程序可以让你把FIX数据写到EXCEL工作表中' Declare necessary API routines: 定义必要的APIPrivate Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As Long) As LongPrivate Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As LongSub DetectExcel()' Procedure dectects a running Excel and registers it.生成运行一EXCEL工作表并保存之Const WM_USER = 1024Dim hwnd As Long' If Excel is running this API call returns its handle.如果Excel运行这个API调用返回其句柄，hwnd = FindWindow("XLMAIN", 0)If hwnd = 0 Then ' 0 means Excel not running.0表示EXCEL没有运行Exit SubElse' Excel is running so use the SendMessage API function to enter it in the Running Object Table. EXCEL运行就用API的SendMessage功能发送到运行的工作表中SendMessage hwnd, WM_USER + 18, 0, 0End IfEnd SubPrivate Sub CommandButton2_Click()Dim msexcel As Excel.ApplicationSet msexcel = CreateObject("Excel.Application")With msexcel.Visible = True.Workbooks.Open "d:\fix32\Test1.xls", , FalseEnd WithEnd SubPrivate Sub CommandButton3_Click()Dim MyXL As Object ' Variable to hold reference to Microsoft Excel.Dim ExcelWasNotRunning As Boolean ' Flag for final release.' Test to see if there is a copy of Microsoft Excel already running.On Error Resume Next ' Defer error trapping.' Getobject function called without the first argument returns a reference to an instance of the application. If the application isn't running, an error occurs. Note the comma used as the first argument placeholder.Set MyXL = GetObject(, "Excel.Application")If Err.Number <> 0 Then ExcelWasNotRunning = TrueErr.Clear ' Clear Err object in case error occurred.' Check for Excel. If Excel is running,enter it into the Running Object table.DetectExcel'Set the object variable to reference the file you want to see.Set MyXL = GetObject("d:\fix32\test1.XLS")' Show Microsoft Excel through its Application property. Then show the actual window containing the file using the Windows collection of the MyXL object reference.MyXL.Application.Visible = TrueMyXL.Parent.Windows(1).Visible = True' Do manipulations of your file here.With MyXL.Application.ActiveWorkbook.ActiveSheet.Select.Goto reference:=.Range("D4").Selection = Fix32.Johnski2.AI1.F_CVEnd With' If this copy of Microsoft Excel was not already running when you started, close it using the Application property's Quit method. Note that when you try to quit Microsoft Excel, the Microsoft Excel title bar blinks and Microsoft Excel displays a message asking if you want to save any loaded files.If ExcelWasNotRunning = True ThenMyXL.Application.QuitEnd IfSet MyXL = Nothing ' Release reference to the application and spreadsheet.End Sub。

有关iFIX软件报表生成的说明 iFIX软件可以通过内置的VBA借助EXCEL，ACCESS，Crystal Roport等软件生成报表。

本文通过 VBA+ADO+EXCEL 做一个具体的介绍。

1．V BA(Visual Basic for Application)VBA是iFIX完全内置的一个强有力的编程工具，可以使用户快速方便的生成自动的操作任务和过程自动化的解决方案。

IFIX的VBA工具包括以下特性：可操作所有列出的iFIX对象的属性，方法和事件支持多种数据源，包括iFIX过程数据库，任何OPC服务器，任何对象的属性和SQL数据库ODBC支持ActiveX控件支持VBA脚本生成向导及IFIX命令可以帮助你为常用任务自动生成程序代码第三方控件的安全容器下面的程序代码就是在iFIX中建立一个新的EXCEL应用程序并打开报表模版。

Dim msexcel As Excel.ApplicationSet msexcel = CreateObject("Excel.Application")With msexcel.Visible = True.Workbooks.Open "e:\histdemo\FixReports.xls", ,False.ActiveWorkbook.ActiveSheet.Select.DisplayAlerts = False.Wait (Now() + 0.00002)End With2．A DO (ActiveX Data Object)ADO 是微软推出的最新的数据库访问方式。

它综合了微软早期推出的DAO及RDO的特性并取代了DAO及RDO。

ADO可以访问更多类型的数据库，并且可以通过ODBC（Open DataBase Connectivity）访问数据，但它的结构是建立在OLE DB Providers上。

IFIX软件安装完毕以后，在控制面板的ODBC数据源中会增加两个系统DSN：FIX Dynamics Historical Data iFIX历史数据源FIX Dynamics Real Time Data iFIX实时数据源ADO可以通过访问这两个数据源来直接操作iFIX历史数据库和实时数据库。

写IFIX实时数据到ExcelDescriptionInserting FIX Dynamics data into an Excel worksheet.ResolutionThe following procedure will enable you to insert FIX Dynamics data into an Excel worksheet:' Declare necessary API routines:Private Declare Function FindWindow Lib "user32" Alias _ "FindWindowA" (ByVal lpClassName As String, _ByVal lpWindowName As Long) As LongPrivate Declare Function SendMessage Lib "user32" Alias _ "SendMessageA" (ByVal hwnd As Long, _ByVal wMsg As Long, _ByVal wParam As Long, _lParam As Any) As LongSub DetectExcel()' Procedure dectects a running Excel and registers it.Const WM_USER = 1024Dim hwnd As Long' If Excel is running this API call returns its handle.hwnd = FindWindow("XLMAIN", 0)If hwnd = 0 Then ' 0 means Excel not running.Exit SubElse' Excel is running so use the SendMessage API' function to enter it in the Running Object Table.SendMessage hwnd, WM_USER + 18, 0, 0End IfEnd SubPrivate Sub CommandButton2_Click()Dim msexcel As Excel.ApplicationSet msexcel = CreateObject("Excel.Application")With msexcel.Visible = True.Workbooks.Open "d:\fix32\Test1.xls", , FalseEnd WithEnd SubPrivate Sub CommandButton3_Click()Dim MyXL As Object ' Variable to hold reference to Microsoft Excel. Dim ExcelWasNotRunning As Boolean ' Flag for final release.' Test to see if there is a copy of Microsoft Excel already running. On Error Resume Next ' Defer error trapping.' Getobject function called without the first argument returns a' reference to an instance of the application. If the application isn't ' running, an error occurs. Note the comma used as the first argument ' placeholder.Set MyXL = GetObject(, "Excel.Application")If Err.Number <> 0 Then ExcelWasNotRunning = TrueErr.Clear ' Clear Err object in case error occurred.' Check for Excel. If Excel is running,' enter it into the Running Object table.DetectExcel'Set the object variable to reference the file you want to see.Set MyXL = GetObject("d:\fix32\test1.XLS")' Show Microsoft Excel through its Application property. Then' show the actual window containing the file using the Windows' collection of the MyXL object reference.MyXL.Application.Visible = TrueMyXL.Parent.Windows(1).Visible = True' Do manipulations of your file here.With MyXL.Application.ActiveWorkbook.ActiveSheet.Select.Goto reference:=.Range("D4").Selection = Fix32.Johnski2.AI1.F_CVEnd With' If this copy of Microsoft Excel was not already running when you' started, close it using the Application property's Quit method.' Note that when you try to quit Microsoft Excel, the Microsoft Excel ' title bar blinks and Microsoft Excel displays a message asking if you ' want to save any loaded files.If ExcelWasNotRunning = True ThenMyXL.Application.QuitEnd IfSet MyXL = Nothing ' Release reference to the application and spreadsheet. End Sub在IFIX中如何显示用户信息在FIX32产品中，FIX内含一系列系统变量，存储当前系统信息，包括当前用户的注册信息，如#GS_。