|
第一步，創(chuàng)建ODBC數(shù)據(jù)源,，具體方法：啟動控制面板中的數(shù)據(jù)源ODBC,，單擊“系統(tǒng)DNS”對話框,，選擇所需數(shù)據(jù)源的驅(qū)動，如：“Microsoft excel driver (*.exl)”,單擊“完成”按鈕，彈出“ODBC Microsoft excel 安裝”對話框；在數(shù)據(jù)源名中定義一個數(shù)據(jù)源名稱,，數(shù)據(jù)庫中“選擇”中選擇區(qū)縣數(shù)據(jù)所在的數(shù)據(jù)庫，此數(shù)據(jù)庫的表至少有三個字段：時間字段,、數(shù)據(jù)字段,、毫秒字段。單擊確定按鈕,，新創(chuàng)建數(shù)據(jù)源就添加到“系統(tǒng)DNS”列表中,。
第二步，在IFIX中創(chuàng)建代碼 ADO—在安裝了MCROWIN BASIC 6.0 等軟件就有了,； Private Sub CommandButton1_Click() '注釋: 1,。該程序需要安裝ADO 2.0目標(biāo)庫并在本機(jī)注冊 ' 2。Microsoft ActiveX Data Objects 2.1 Library 必須被引用 (Office 2000) ' 3,。Microsoft Excel 9.0 object libraries 必須被引用 (Office 2000) ' 4,。劃===處可根據(jù)具體報表修改 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" '++========================================================================== '查詢,，根據(jù)報表修改 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 '''如果執(zhí)行上面的語句出錯的話，則最大的可能性就是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中電廠日報表的生成 總則：運(yùn)用iFIX的調(diào)度器運(yùn)行基于時間的VBA腳本來生成每天的EXCEL格式日報表,。 1) iFIX中調(diào)度設(shè)置 在iFIX中新建時間調(diào)度，設(shè)置成間隔1小時的調(diào)度項,，然后點擊VB編輯器自定義腳本. 2) 初始變量定義 Dim ReportArray As Variant Rem 存放日報中所有要顯示的參數(shù)的數(shù)組 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) 創(chuàng)建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為本地節(jié)點名 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) 執(zhí)行對數(shù)據(jù)庫的查詢： 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) 建立查詢數(shù)據(jù)庫后的記錄集，并把數(shù)據(jù)寫到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 |
|
|
