IFIX与EXCEL的连接

IFIX与EXCEL的连接

第一步，创建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

本文来源：https://www.bwwdw.com/article/ngum.html