IFIX与EXCEL的连接

hzjiyitech

第一步，创建ODBC数据源，具体方法：启动控制面板中的数据源ODBC，单击“系统DNS”对话框，选择所需数据源的驱动，如：“Microsoft excel driver (*.exl)”,单击“完成”按钮，弹出“ODBC Microsoft excel 安装”对话框；在数据源名中定义一个数据源名称，数据库中“选择”中选择区县数据所在的数据库，此数据库的表至少有三个字段：时间字段、数据字段、毫秒字段。单击确定按钮，新创建数据源就添加到“系统DNS”列表中。

第二步，在IFIX中创建代码

ADO—在安装了MCROWIN BASIC 6.0 等软件就有了；

Private Sub CommandButton1_Click()
'注释: 1。该程序需要安装ADO 2.0目标库并在本机注册
' 2。Microsoft ActiveX Data Objects 2.1 Library 必须被引用 (Office 2000)
' 3。Microsoft Excel 9.0 object libraries 必须被引用 (Office 2000)
' 4。划===处可根据具体报表修改

Dim strQueryAvg As String

Dim c As Integer
Dim r As Integer
Dim Intyexcel As Excel.Application

Dim MyDate, MyMonth, MyDay, MyHour, MyMinute, MySecond
Dim StartTime, EndTime, Duration, DisplayDay, DisplayMonth As String

'++===================================================================
'报表中的 TAG
Dim Tag1, Tag2, Tag3, Tag4, Tag5, Tag6, Tag7, Tag8 As String
Dim Items As Integer

Tag1 = "TEST"
Tag2 = "TEST1"
Tag3 = " "
Tag4 = " "
Tag5 = " "
Tag6 = " "
Tag7 = " "
Tag8 = " "

'从历史库中取得域项， 2 - DATATIME, VALUE, TAG 共三项
Items = 2
'--====================================================================

MyDate = Now()
MyMonth = Month(MyDate)
MyDay = Day(MyDate)
MyHour = Hour(MyDate)
MyMinute = Minute(MyDate)
MySecond = Second(MyDate)

StartTime = "2000" & "-" & MyMonth & "-" & MyDay - 1 & " " & "00:00:00"
EndTime = "2000" & "-" & MyMonth & "-" & MyDay - 1 & " " & "23:00:00"

'++==========================================================================
'查询，根据报表修改
strQueryAvg = "Select DATETIME, VALUE, TAG FROM FIX " & _
"WHERE MODE = 'AVERAGE' and (TAG='" & Tag1 & "' or TAG='" & Tag2 & "'" & _
" or TAG='" & Tag3 & "' or TAG='" & Tag4 & "' or TAG='" & Tag5 & "'" & _
" or TAG='" & Tag6 & "' or TAG='" & Tag7 & "' or TAG='" & Tag8 & "')" & _
"and INTERVAL = '01:00:00' and " & _
"(DATETIME >= and " & _
"DATETIME <= )"
'--===========================================================================


Dim cnADO As New ADODB.Connection
Dim rsADO As Recordset

Set cnADO = New ADODB.Connection
cnADO.ConnectionString = "DSN = FIX Dynamics Historical Data; UID = sa; PWD = ;"
cnADO.Open "FIX Dynamics Historical Data", "sa", ""

Set rsADO = New ADODB.Recordset

rsADO.Open strQueryAvg, cnADO, adOpenForwardOnly, adLockBatchOptimistic
'''如果执行上面的语句出错的话，则最大的可能性就是SQL语句有错误！
r = 1
Set Intyexcel = New Excel.Application
Intyexcel.Visible = False


'++============================================================================
'打开的报表文件名
Dim OutReportFile As String
Dim InReportFile As String

InReportFile = "C:\Dynamics\App\HIST1"

Intyexcel.Workbooks.Open InReportFile & ".XLS"

Intyexcel.Sheets("Sheet2").Select
Intyexcel.Columns("A:Z").Select
Intyexcel.Selection.ClearContents
Intyexcel.Range("A1").Select

While rsADO.EOF <> True
With Intyexcel.Worksheets(2)
For c = 0 To Items
If rsADO(c) <> "" Then .Cells(r, c + 1).Value = rsADO(c)
Next c
r = r + 1
rsADO.MoveNext
End With
Wend

Intyexcel.Sheets("Sheet1").Select

' Intyexcel.ActiveSheet.PageSetup.Orientation = xlPortrait 'xlLandscape
' Intyexcel.ActiveSheet.PageSetup.PaperSize = xlPaperA4
Intyexcel.ActiveSheet.PrintOut
Intyexcel.DisplayAlerts = False
Intyexcel.ActiveWorkbook.Save
OutReportFile = InReportFile & "_00" & MyMonth & MyDay
Intyexcel.ActiveWorkbook.SaveAs OutReportFile

Intyexcel.Quit
Intyexcel.DisplayAlerts = True
Set Intyexcel = Nothing
Set cnADO = Nothing

End Sub

4.2 iFIX中电厂日报表的生成

　　总则：运用iFIX的调度器运行基于时间的VBA脚本来生成每天的EXCEL格式日报表。

　　1) iFIX中调度设置
在iFIX中新建时间调度，设置成间隔1小时的调度项，然后点击VB编辑器自定义脚本.

　　2) 初始变量定义

Dim ReportArray As Variant Rem 存放日报中所有要显示的参数的数组
Dim FirstPoint1 As Variant Rem 第一个变量
Dim tempvar As Variant Rem 中间变量
Dim strStartTime, strEndTime Rem 报表查询的时间范围
Dim Interval As Variant Rem 报表查询的间隔时间
Dim OutReportfile As Variant Rem 输出EXCEL表格的文件名
Dim TemplateName As String Rem 这个是日报表模板的文件名

　　3) 建立对EXCEL的引用，并打开报表的模板文件：

Set msExcel = CreateObject("Excel.Application")
With msExcel
.WindowState = xlMinimized
.Visible = False
.Workbooks.Open ReportTemplateName, , False Rem 打开报表的模板文件
.ActiveWorkbook.ActiveSheet.Select
.DisplayAlerts = False
.DisplayAlerts = False
.Wait (Now() + 0.00002)
End With

　　4) 创建SQL查询语句：
Dim rsADO As New ADODB.Recordset
Dim cnADO As New ADODB.Connection
Dim SQL0,SQL1 AS String
SQLO = "select FIX.value from FIX where FIX.interval=|" + Interval + "| and FIX.datetime>={ts|" + strStartTime + "|} and FIX.datetime<{ts|" + strEndTime + "|}" Rem FIX为本地节点名
SQL1 = SQLO & " and (FIX.tag=|" & FirstPoint1 & "|"
iTotalCols = 1
For Each tempvar In FirstReportPoints
iTotalCols = iTotalCols + 1
SQL1 = SQL1 & " or FIX.tag=|" & tempvar & "|"
Next tempvar
SQL1 = SQL1 + ")"

　　5) 执行对数据库的查询：
Set cnADO = New ADODB.Connection
With cnADO
Rem .CursorLocation = adUseClient
.Open "PROVIDER = Microsoft OLE DB Provider for ODBC Drivers;dsn=FIX Dynamics Historical Data;uid=sa;pwd=;"
.Execute (SQL1)
End With

　　6) 建立查询数据库后的记录集，并把数据写到EXCEL中后另存为EXCEL文件：
Set rsADO = New ADODB.Recordset
rsADO.CursorLocation = adUseClient
rsADO.Open SQL1, cnADO, adOpenForwardOnly, adLockReadOnly
If rsADO.BOF Then
rsADO.Close
cnADO.Close
With msExcel
DoEvents
.ActiveWorkbook.Close
.Quit
End With
Set msExcel = Nothing
Rem MsgBox "第一个表查询条件为空，请检查查询条件"
Exit Sub
Else
c = 1
While rsADO.EOF <> True
With msExcel.Worksheets(1)
For j = 1 To 24
If rsADO(0) <> "" Then
msExcel.Worksheets(1).Cells(j, c).Value = rsADO(0)
rsADO.MoveNext
End If
Next j
End With
c = c + 1
Wend
msExcel.ActiveWorkbook.SaveAs "d:\" & OutReportfile & ".xls"
msExcel.Quit
msExcel.DisplayAlerts = True
msExcel.Visible = True
Set msExcel = Nothing
Set cnADO = Nothing
rsADO.Close