產品目錄

當前位置：首頁 >>> 技術文章 >

技術文章

IFIX與EXCEL的連接

IFIX與EXCEL的連接

**步，創建ODBC數據源，具體方法：啟動控制面板中的數據源ODBC，單擊“系統DNS”對話框，選擇所需數據源的驅動，如：“Microsoftexcel 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, DisplayMonthAs String

'++===================================================================
'報表中的 TAG
Dim Tag1, Tag2, Tag3, Tag4, Tag5, Tag6, Tag7, Tag8 AsString
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 HistoricalData; 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 + "|} andFIX.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 ODBCDrivers;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

上一篇：iFIX_excel報表實現的方法？
下一篇：IFIX的定時器使用方法

若網站內容侵犯到您的權益，請通過網站上的聯系方式及時聯系我們修改或刪除

日本天堂视频在线观看_国内精自线一二区_日日操狠狠干_亚洲欧美日本综合

產品目錄

技術文章

IFIX與EXCEL的連接