iFix中采用ODBC制作报表.doc

在iFix中采用ODBC制作历史报表胡 俊上海海得控制系统公司 系统网络部具体步骤如下：Step 1:打开SCU中的Task Configuration，增加HTC.EXE和WSQLODC.EXE两个任务，并将它们设置成为后台运行状态；Step 2:启动iFix，点击工具栏上的按钮打开Historical Assign，添加所需监控的数据点，并设置历史数据的采样速度。Step 3:打开任务控制，查看是否已经启动HTC。Step 4:在GlobalsUser下面添加三个字符型（vtString）变量，分别命名为strStartTime, strEndTime 和 Interval。新建一幅画面，如图如示，在画面上布置3个DATALINK分别指向strStartTime, strEndTime 和 Interval，并设置成In place（即允许修改）。IntervalstrEndTimestrStartTimeStep 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:30End Sub组态状态画面初始化Private Sub CFixPicture_InitializeConfigure()user.strEndTime.CurrentValue = 报表结束时间user.strStartTime.CurrentValue = 报表开始时间End Sub设当前时间为报表开始时间Private Sub CommandButton1_Click() Dim curTime As String curTime = Now Dim curmonth, curday, curhour, curminute, cursecond As String curmonth = 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 & : & cursecondEnd Sub设当前时间为报表结束时间Private Sub CommandButton2_Click() Dim curTime As String curTime = Now Dim curmonth, curday, curhour, curminute, cursecond As String curmonth = 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.CurrentValue = Year(curTime) & - & curmonth & - & curday & _& curhour & : & curminute & : & cursecondEnd Sub打印此前10分钟历史报表，只是设置时间值，打印仍要调用打印程序Private Sub CommandButton3_Click() Dim curTime As String curTime = Now Dim curmonth, curday, curhour, curminute, cursecond As String curmonth = 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.CurrentValue = Year(curTime) & - & curmonth & - & curday & _ & curhour & : & curminute & : & cursecondEnd Sub打印当天报表，只是设置时间值，打印仍要调用打印程序Private Sub CommandButton4_Click() Dim curTime As String curTime = Now Dim curmonth, curday, curhour, curminute, cursecond As String curmonth = 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.CurrentValue = Year(curTime) & - & curmonth & - & curday _& & 23:59:59End Sub打印当月报表，只是设置时间值，打印仍要调用打印程序Private Sub CommandButton5_Click() Dim curTime As String curTime = Now Dim curmonth, BeginofMonth, EndofMonth As String curmonth = IIf(Month(curTime) 0 and erval= + Interval + _ and (fix.datetime ts + strStartTime + ) and (fix.datetime ts _ + strEndTime + ) With cnADO .Open PROVIDER=MSDASQL;dsn=FIX Dynamics Real Time Data;uid=;pwd=; 本行为打开实时数据库，下一行为打开历史数据库。 .Open Provider=MSDASQL.1;Extended Properties=DSN=FIX Dynamics Historical Data;DBQ=;SERVER=NotTheServer .Execute SQL1 End With rsADO.Open SQL1, cnADO For i = 1 To rsADO.Fields.Count msexcel.Cells(2, i) = rsADO.Fields(i - 1).Name Next If rsADO.BOF Then msexcel.Quit Set msexcel = Nothing rsADO.Close cnADO.Close MsgBox 查询结果为空，请检查查询条件 Exit Sub Else rsADO.MoveFirst End If i = 3 While Not rsADO.EOF For j = 1 To rsADO.Fields.Count msexcel.Cells(i, j) = rsADO.Fields(j - 1) Next rsADO.MoveNext i = i + 1 Wendendandquit: rsADO.Close cnADO.Close保存和打印代码 With msexcel .Wait (Now() + 0.00012) .ActiveWorkbook.SaveAs e:saverep