报表控件应用-iFix

报表控件应用-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 和其它的数据点字段，按[数据检索]按钮可从数据库查询出已存入的历史数据。

IFIX连接SQL读写数据和制作excel报表图文教程IFIX连接SQL数据库、制作excel报表详细教程一、 IFIX连接SQL数据库读写数据IFIX连接SQL数据库有多种方法，其中最为方便的两种方法是:(1)使用IFIX本身为SQL数据库提供的接口SQT和SQD;(2)VB+ADO。

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

1、使用SQT和SQD读写SQL数据库1.1 安装SQL2008(只列出关键步骤，其余直接点击“下一步”或“安装”) 打开SQL安装中心，点击“安装”;SQL2008简体中文版安装包下载链接:点击“全新SQL Server独立安装或向现有安装添加功能”;点击“输入产品密匙”，点击“下一步”:(根据版本选择)sql server2008密钥Developer: PTTFM-X467G-P7RH2-3Q6CG-4DMYBEnterprise: JD8Y6-HQG69-P9H84-XDTPG-34MBBMicrosoft SQL Server 2008 R2序列号密钥开发版32位:MC46H-JQR3C-2JRHY-XYRKY-QWPVM开发版64位:FTMGC-B2J97-PJ4QG-V84YB-MTXX8工组版:XQ4CB-VK9P3-4WYYH-4HQX3-K2R6QWEB版:FP4P7-YKG22-WGRVK-MKGMX-V9MTM数据中心版32位:PTTFM-X467G-P7RH2-3Q6CG-4DMYB数据中心版64位:DDT3B-8W62X-P9JD6-8MX7M-HWK38企业版32位:R88PF-GMCFT-KM2KR-4R7GB-43K4B企业版64位:GYF3T-H2V88-GRPPH-HWRJP-QRTYB标准版32位:CXTFT-74V4Y-9D48T-2DMFW-TX7CY标准版64位:B68Q6-KK2R7-89WGB-6Q9KR-QHFDW功能选择界面，选择安装全部功能，点击“下一步”;进入实例配置界面，选择默认实例，点击“下一步”;进入服务器配置界面，启动类型均选为“自动”;点击“对所有SQL Server服务使用相同的账户”，在弹出的界面中选择第一个账户，密码不用填;返回服务器配置界面点击下一步;进入数据库引擎配置界面，选择混合模式，输入自己设定的密码，点击“添加当前用户”，点击下一步，直至安装完成，关闭安装中心。

IFIX报表⼀、新建access⽇报数据库ReportData(预装office2007以上版本)表名：ReportData右键设计视图：右键打开：⽉报数据库ReportMonth右键设计视图：右键打开：年报数据库ReportYear 右键设计视图：右键打开：删除⽇期主键，索引选择“⽆”；时间：长整型；其他累计：长整型。

⼆、配置ODBC64位操作系统打开ODBC⽅法：C:/Windows/SysWOW64/odbcad32.exe完成后确定即可，⽉报，年报同上。

32位系统：开始》控制⾯板》管理⼯具》ODBC。

同上设置三、IFIX新建时间调度时间间隔设置12⼩时，开始时间是6点54分。

⼀天分为⽩班（7点到19点）夜班（19点到7点），分班累计重置时间为6点54分和18点54分。

Private Sub sjdiaodu_OnTimeOut(ByVal lTimerId As Long)Dim cn As ADODB.Connection '定义⼀个ADO⽅式的数据库连接Dim res As ADODB.Recordset '定义⼀个ADO⽅式的数据库记录集Dim StrSQL As StringSet cn = New ADODB.Connection '定义cn为新的ADO数据连接Set res = New ADODB.Recordset '定义res为新的ADO数据库记录集'On Error Resume Nextcn.ConnectionString = "DSN=ReportData;UID=;PWD=;" '定义cn的连接数据源为ReportData 即ODBC中建⽴的ACCESS的数据源名cn.OpenStrSQL = "select * from ReportData Where ⽇期=#" & Date & "#" '使⽤SQl语"句查找ReportData表中⽇期为Data的数据res.Open StrSQL, cn, adOpenKeyset, adLockOptimisticres.ADDNew '添加⼀个新的记录If Hour(Time) = 6 Thenres.Fields(1) = 2 '在1列加⼊⽩班计数res.Fields(0) = Date -1'在0列加⼊⽇期，早上6点54分记得数算成前⼀天⽇累计ElseIf Hour(Time) = 18 Thenres.Fields(1) = 1 '在1列加⼊夜班计数res.Fields(0) = Date '在0列加⼊⽇期ElseEnd IfEnd Ifres.Fields(2) = Fix32.Fix.R0126.F_CV '在2列加⼊标签1res.Fields(3) = Fix32.Fix.R0127.F_CV '在3列加⼊标签2res.Fields(4) = Fix32.Fix.R0128.F_CV '在4列加⼊标签3res.Fields(5) = Fix32.Fix.R0129.F_CV '在5列加⼊标签4res.Fields(6) = Fix32.Fix.R0130.F_CV '在6列加⼊标签5res.Update '保持记录res.Close '关闭记录集cn.CloseSet res = NothingSet cn = NothingEnd Sub⽉调度Private Sub sjmonth_OnTimeOut(ByVal lTimerId As Long)Dim Mcn As ADODB.Connection '定义⼀个ADO⽅式的数据库连接Dim Mres As ADODB.Recordset '定义⼀个ADO⽅式的数据库记录集Dim MStrSQL As StringSet Mcn = New ADODB.Connection '定义cn为新的ADO数据连接Set Mres = New ADODB.Recordset '定义res为新的ADO数据库记录集'On Error Resume NextMcn.ConnectionString = "DSN=ReportMonth;UID=;PWD=;" '定义cn的连接数据源为ReportData 即ODBC中建⽴的ACCESS 的数据源名Mcn.OpenMStrSQL = "select * from ReportMonth Where ⽇期=#" & Date & "#" '使⽤SQl语"句查找ReportData表中⽇期为Data的数据Mres.Open MStrSQL, Mcn, adOpenKeyset, adLockOptimisticMres.ADDNew '添加⼀个新的记录Mres.Fields(0) = DateAdd("y", -1, Date) '在0列加⼊⽇期-1Mres.Fields(1) = Day(Date) - 1 '在1列加⼊天数-1Mres.Fields(2) = Fix32.Fix.R0141.F_CV '在2列加⼊标签1Mres.Fields(3) = Fix32.Fix.R0142.F_CV '在3列加⼊标签2Mres.Fields(4) = Fix32.Fix.R0143.F_CV '在4列加⼊标签3Mres.Fields(5) = Fix32.Fix.R0144.F_CV '在5列加⼊标签4Mres.Fields(6) = Fix32.Fix.R0145.F_CV '在6列加⼊标签5Mres.Fields(7) = DatePart("m", Date) '在7列加⼊标签6Mres.Fields(8) = DatePart("yyyy", Date) '在8列加⼊标签7Mres.Update '保持记录Mres.Close '关闭记录集Mcn.CloseSet Mres = NothingSet Mcn = NothingEnd Sub年调度（⽤于⽉数据记录，年数据不做显⽰）Private Sub Year_OnTimeOut(ByVal lTimerId As Long)Dim Ycn As ADODB.Connection '定义⼀个ADO⽅式的数据库连接Dim Yres As ADODB.Recordset '定义⼀个ADO⽅式的数据库记录集Dim YStrSQL As StringSet Ycn = New ADODB.Connection '定义cn为新的ADO数据连接Set Yres = New ADODB.Recordset '定义res为新的ADO数据库记录集'On Error Resume NextYcn.ConnectionString = "DSN=ReportYear;UID=;PWD=;" '定义cn的连接数据源为ReportData 即ODBC中建⽴的ACCESS的数据源名Ycn.OpenYStrSQL = "select * from ReportYear Where ⽇期=#" & Date & "#" '使⽤SQl语"句查找ReportData表中⽇期为Data的数据Yres.Open YStrSQL, Ycn, adOpenKeyset, adLockOptimisticYres.ADDNew '添加⼀个新的记录Yres.Fields(0) = DateAdd("y", -1, Date) '在0列加⼊⽇期-1Yres.Fields(1) = DatePart("m", Date) - 1 '在1列加⼊⽉份-1Yres.Fields(2) = Fix32.Fix.R0135.F_CV '在2列加⼊标签1Yres.Fields(3) = Fix32.Fix.R0136.F_CV '在3列加⼊标签2Yres.Fields(4) = Fix32.Fix.R0137.F_CV '在4列加⼊标签3Yres.Fields(5) = Fix32.Fix.R0138.F_CV '在5列加⼊标签4Yres.Fields(6) = Fix32.Fix.R0139.F_CV '在6列加⼊标签5Yres.Fields(7) = DatePart("yyyy", Date) '在7列加⼊标签6，有bug，懒得改了。

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中采用ODBC制作历史报表具体步骤如下：Step 1: 打开SCU中的Task Configuration，增加HTC.EXE和WSQLODC.EXE两个任务，并将它们设置成为后台运行状态；Step 2: 启动iFix，点击工具栏上的按钮打开Historical Assign，添加所需监控的数据点，并设置历史数据的采样速度。

Step 3: 打开任务控制，查看是否已经启动HTC。

Step 4: 在Globals\User下面添加三个字符型（vtString）变量，分别命名为strStartTime, strEndTime 和Interval。

新建一幅画面，如图如示，在画面上布置3个DATALINK分别指向strStartTime, strEndTime 和Interval，并设置成In place（即允许修改）。

strStartTimestrEndTimeIntervalStep 5：为画面上的各个按钮写代码，最好先将各按钮注册——即将鼠标移到按钮上方，按右键弹出菜单，选择Edit Script。

在VB Editor的工具菜单中的Reference(引用)中选中Microsoft Excel 9.0 Object Library和Microsoft ActiveX Data Objects，如下图所示。

然后在各按钮的处理程序中添加相应代码。

内容如下（可将下列代码直接COPY到你的VB编辑器中，覆盖因为注册产生的空代码）：‘运行状态画面初始化Private Sub CFixPicture_Initialize()CommandButton1_ClickCommandButton2_Clickuser.Interval.CurrentValue = "00:00:30"End Sub‘组态状态画面初始化Private Sub CFixPicture_InitializeConfigure()user.strEndTime.CurrentV alue = "报表结束时间"user.strStartTime.CurrentValue = "报表开始时间"End Sub'设当前时间为报表开始时间Private Sub CommandButton1_Click()Dim curTime As StringcurTime = NowDim curmonth, curday, curhour, curminute, cursecond As Stringcurmonth = IIf(Month(curTime) < 10, "0" & Month(curTime), Month(curTime)) curday = IIf(Day(curTime) < 10, "0" & Day(curTime), Day(curTime))curhour = IIf(Hour(curTime) < 10, "0" & Hour(curTime), Hour(curTime))curminute = IIf(Minute(curTime) < 10, "0" & Minute(curTime), Minute(curTime)) cursecond = IIf(Second(curTime) < 10, "0" & Second(curTime), Second(curTime)) user.strStartTime.CurrentValue = Year(curTime) & "-" & curmonth & "-" & curday _& " " & curhour & ":" & curminute & ":" & cursecond End Sub'设当前时间为报表结束时间Private Sub CommandButton2_Click()Dim curTime As StringcurTime = NowDim curmonth, curday, curhour, curminute, cursecond As Stringcurmonth = IIf(Month(curTime) < 10, "0" & Month(curTime), Month(curTime))curday = IIf(Day(curTime) < 10, "0" & Day(curTime), Day(curTime))curhour = IIf(Hour(curTime) < 10, "0" & Hour(curTime), Hour(curTime))curminute = IIf(Minute(curTime) < 10, "0" & Minute(curTime), Minute(curTime)) cursecond = IIf(Second(curTime) < 10, "0" & Second(curTime), Second(curTime)) user.strEndTime.CurrentV alue = Year(curTime) & "-" & curmonth & "-" & curday & " " _& curhour & ":" & curminute & ":" & cursecondEnd Sub'打印此前10分钟历史报表，只是设置时间值，打印仍要调用打印程序Private Sub CommandButton3_Click()Dim curTime As StringcurTime = NowDim curmonth, curday, curhour, curminute, cursecond As Stringcurmonth = IIf(Month(curTime) < 10, "0" & Month(curTime), Month(curTime))curday = IIf(Day(curTime) < 10, "0" & Day(curTime), Day(curTime))curhour = IIf(Hour(curTime) < 10, "0" & Hour(curTime), Hour(curTime))curminute = IIf(Minute(DateAdd("n", -10, curTime)) < 10, "0" & _Minute(DateAdd("n", -10, curTime)), Minute(DateAdd("n", -10, curTime))) cursecond = IIf(Second(curTime) < 10, "0" & Second(curTime), Second(curTime)) user.strStartTime.CurrentValue = Year(curTime) & "-" & curmonth & "-" & curday & _" " & curhour & ":" & curminute & ":" & cursecond curminute = IIf(Minute(curTime) < 10, "0" & Minute(curTime), Minute(curTime)) user.strEndTime.CurrentV alue = Year(curTime) & "-" & curmonth & "-" & curday & _" " & curhour & ":" & curminute & ":" & cursecond End Sub'打印当天报表，只是设置时间值，打印仍要调用打印程序Private Sub CommandButton4_Click()Dim curTime As StringcurTime = NowDim curmonth, curday, curhour, curminute, cursecond As Stringcurmonth = IIf(Month(curTime) < 10, "0" & Month(curTime), Month(curTime))curday = IIf(Day(curTime) < 10, "0" & Day(curTime), Day(curTime))user.strStartTime.CurrentValue = Year(curTime) & "-" & curmonth & "-" & curday _& " " & "00:00:00"user.strEndTime.CurrentV alue = Year(curTime) & "-" & curmonth & "-" & curday _& " " & "23:59:59"End Sub'打印当月报表，只是设置时间值，打印仍要调用打印程序Private Sub CommandButton5_Click()Dim curTime As StringcurTime = NowDim curmonth, BeginofMonth, EndofMonth As Stringcurmonth = IIf(Month(curTime) < 10, "0" & Month(curTime), Month(curTime)) BeginofMonth = Year(curTime) & "-" & curmonth & "-01" & " " & "00:00:00"user.strStartTime.CurrentValue = BeginofMonthEndofMonth = Day(DateAdd("s", -1, DateAdd("m", 1, CDate(BeginofMonth))))user.strEndTime.CurrentV alue = Year(curTime) & "-" & curmonth & "-" _& EndofMonth & " " & "23:59:59"End Sub‘根据输入条件，保存/打印报表到文件Private Sub CommandButton6_Click()'运行EXCEL，打开报表模板文件Dim msexcel As Excel.ApplicationSet msexcel = CreateObject("Excel.Application")With msexcel.Visible = True ‘如为FALSE，则不显示EXCEL。

Private Sub CFixPicture_Initialize()dtpTime.Value = NowEnd SubPrivate Sub CFixPicture_KeyDown(ByVal KeyCode As Long, ByVal Shift As Long, ContinueProcessing As Boolean)End SubPrivate Sub CommandButton1_Click()Dim strDate As StringDim strMonth As StringstrDate = Format(dtpTime, "yyyy-MM-dd")strMonth = Format(dtpTime, "yyyy-MM")'日报If Me.optMonth.Value = False ThenIf Me.optDay.Value = True ThenWebReport.Navigate "D:\REPORT\日报表\日报表" & strDate & ".htm" '显示ElseWebReport.Navigate "D:\REPORT\日报表\空白报表" & strDate & ".htm" '显示End IfEnd If'月报If Me.optDay.Value = False ThenIf Me.optMonth.Value = True ThenWebReport.Navigate "D:\REPORT\月报表\月报表" & strMonth & ".htm" '显示ElseWebReport.Navigate "D:\REPORT\月报表\空白报表" & strMonth & ".htm" '显示End IfEnd IfEnd SubPrivate Sub CommandButton2_Click()On Error Resume Next'日报页面设置If Me.optDay.Value = True ThenPrintSet "0.5", "0.5", "0.5", "0.5"End If'月报页面设置If Me.optMonth.Value = True ThenPrintSet "0.5", "0.5", "0.5", "0.5"End IfMe.WebReport.ExecWB 6, OLECMDEXECOPT_PROMPTUSEREnd SubPrivate Sub optDay_Click()Me.optDay.Value = True ' 日报Me.optMonth.Value = False '月报Me.dtpTime.CustomFormat = "yyyy-MM-dd" '修改时间格式dtpTime.Value = NowEnd SubPrivate Sub optMonth_Click()Me.optDay.Value = False ' 日报Me.optMonth.Value = True '月报Me.dtpTime.CustomFormat = "yyyy-MM" '修改时间格式dtpTime.Value = NowEnd Sub打印设置Private Sub PrintSet(sBottom As String, strTop As String, sLeft As String, sMargin_right As String)Dim hkey_root, hkey_path, hkey_key As StringDim RegWsh As Objecthkey_root = "HKEY_CURRENT_USER"hkey_path = "\Software\Microsoft\Internet Explorer\PageSetup"'//设置网页打印的页眉页脚为空Set RegWsh = CreateObject("WScript.Shell")hkey_key = "\header" '页眉RegWsh.RegWrite hkey_root + hkey_path + hkey_key, ""hkey_key = "\footer" '页脚RegWsh.RegWrite hkey_root + hkey_path + hkey_key, ""hkey_key = "\margin_bottom" '下页边距RegWsh.RegWrite hkey_root + hkey_path + hkey_key, sBottom 'hkey_key = "\margin_top" '上页边距RegWsh.RegWrite hkey_root + hkey_path + hkey_key, strTop 'hkey_key = "\margin_left" ' 左页边距RegWsh.RegWrite hkey_root + hkey_path + hkey_key, sLeft 'hkey_key = "\margin_right" ' 右页边距RegWsh.RegWrite hkey_root + hkey_path + hkey_key, sMargin_right ''打印End Sub其中日报和月报为控件报表时间为控件WebReport控件新建调度ReportPrivate Sub report_OnTimeOut(ByVal lTimerId As Long)On Error Resume NextDim strDateTime As StringDim strDate As StringDim strHourMinute As StringDim iRow As IntegerDim strTemplateName As StringstrDateTime = Format(Now(), "yyyy-MM-dd hh:mm:ss")strDate = Left(strDateTime, 10)strHourMinute = Right(Left(strDateTime, 13), 2) & ":00"iRow = Right(Left(strDateTime, 13), 2)Dim isCreate As BooleanSet fs = CreateObject("Scripting.FileSystemObject")isCreate = fs.FileExists("D:\REPORT\日报表\日报表" & strDate & ".htm") Set fs = NothingIf isCreate ThenstrTemplateName = ("D:\REPORT\日报表\日报表" & strDate & ".htm") ElsestrTemplateName = "D:\REPORT\日报表" & ".htm"End IfSet msExcel = CreateObject("Excel.Application")With msExcel.visible = True.Workbooks.Open strTemplateName, , False.ActiveWorkbook.ActiveSheet.Select.DisplayAlerts = False.Wait (Now() + 0.00002)End With'写日期msExcel.Worksheets(1).Cells(1, 8).Value = strDate'写数据msExcel.Worksheets(1).Cells(iRow + 6, 2).Value = readvalue("DAYJSLJ1")msExcel.Worksheets(1).Cells(iRow + 6, 4).Value = readvalue("DAYJSLJ2")msExcel.Worksheets(1).Cells(iRow + 6, 6).Value = readvalue("DAYJSLJ3")msExcel.Worksheets(1).Cells(iRow + 6, 8).Value = readvalue("DAYJSLJ4")msExcel.Worksheets(1).Cells(iRow + 6, 10).Value = readvalue("DAYJSLJ1") + readvalue("DAYJSLJ2") + readvalue("DAYJSLJ3") + readvalue("DAYJSLJ4")'保存报表文件Dim strSaveName As StringstrSaveName = "D:\REPORT\日报表\日报表" & strDate & ".htm"msExcel.ActiveWorkbook.SaveAs ("D:\REPORT\日报表\日报表" & strDate & ".htm") msExcel.QuitEnd SubReport1Private Sub report1_OnTimeOut(ByVal lTimerId As Long)On Error Resume NextDim strDateTime As StringDim strMonth As StringDim strHourMinute As StringDim iRow As IntegerDim strTemplateName As StringstrDateTime = Format(Now(), "yyyy-MM-dd hh:mm:ss")strMonth = Left(strDateTime, 7)strHourMinute = Right(Left(strDateTime, 13), 2) & ":00"iRow = Right(Left(strDateTime, 10), 2)Dim isCreate As BooleanSet fs = CreateObject("Scripting.FileSystemObject")isCreate = fs.FileExists("D:\REPORT\月报表\月报表" & strMonth & ".htm")Set fs = NothingIf isCreate ThenstrTemplateName = ("D:\REPORT\月报表\月报表" & strMonth & ".htm")ElsestrTemplateName = "D:\REPORT\月报表" & ".htm"End IfSet msExcel = CreateObject("Excel.Application")With msExcel.visible = False.Workbooks.Open strTemplateName, , False.ActiveWorkbook.ActiveSheet.Select.DisplayAlerts = False.Wait (Now() + 0.00002)End With'写日期msExcel.Worksheets(1).Cells(1, 8).Value = strMonth'写数据msExcel.Worksheets(1).Cells(iRow + 2, 2).Value = readvalue("DAYJSLJ1")msExcel.Worksheets(1).Cells(iRow + 2, 4).Value = readvalue("DAYJSLJ2")msExcel.Worksheets(1).Cells(iRow + 2, 6).Value = readvalue("DAYJSLJ3")msExcel.Worksheets(1).Cells(iRow + 2, 8).Value = readvalue("DAYJSLJ4")msExcel.Worksheets(1).Cells(iRow + 2, 10).Value = readvalue("DAYJSLJ4") + readvalue("DAYJSLJ1") + readvalue("DAYJSLJ2") + readvalue("DAYJSLJ3")Dim strtemp As Integerstrtemp = 3msExcel.Worksheets(1).Cells(34, 2).Value = 0msExcel.Worksheets(1).Cells(34, 4).Value = 0msExcel.Worksheets(1).Cells(34, 6).Value = 0msExcel.Worksheets(1).Cells(34, 8).Value = 0msExcel.Worksheets(1).Cells(34, 10).Value = 0Do While strtemp <= 33msExcel.Worksheets(1).Cells(34, 2).Value = msExcel.Worksheets(1).Cells(34, 2).Value + msExcel.Worksheets(1).Cells(strtemp, 2).ValuemsExcel.Worksheets(1).Cells(34, 4).Value = msExcel.Worksheets(1).Cells(34, 4).Value + msExcel.Worksheets(1).Cells(strtemp, 4).ValuemsExcel.Worksheets(1).Cells(34, 6).Value = msExcel.Worksheets(1).Cells(34, 6).Value + msExcel.Worksheets(1).Cells(strtemp, 6).ValuemsExcel.Worksheets(1).Cells(34, 8).Value = msExcel.Worksheets(1).Cells(34, 8).Value + msExcel.Worksheets(1).Cells(strtemp, 8).ValuemsExcel.Worksheets(1).Cells(34, 10).Value = msExcel.Worksheets(1).Cells(34, 10).Value + msExcel.Worksheets(1).Cells(strtemp, 10).Valuestrtemp = strtemp + 1Loop'保存报表文件Dim strSaveName As StringstrSaveName = "D:\REPORT\月报表\月报表" & strMonth & ".htm"msExcel.ActiveWorkbook.SaveAs ("D:\REPORT\月报表\月报表" & strMonth & ".htm") msExcel.QuitEnd SubResetPrivate Sub RESET_OnTimeOut(ByVal lTimerId As Long) Dim PauseTimePauseTime = 1Start = TimerCloseDigitalPoint "Fix32.FIX.RESET_DAYJSLJ.F_CV" Do While Timer < Start + PauseTimeDoEventsLoopOpenDigitalPoint "Fix32.FIX.RESET_DAYJSLJ.F_CV" End Sub。

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。