ifix報(bào)表制作文檔

1、Ifix報(bào)表的實(shí)現(xiàn)本文檔主要介紹Ifix 通過(guò)調(diào)度將報(bào)表數(shù)據(jù)按照一定要求存儲(chǔ)到Access數(shù)據(jù)庫(kù)中，并根據(jù)要求從其中提取數(shù)據(jù)，結(jié)合EXCELL的制表功能實(shí)現(xiàn)報(bào)表。Ifix時(shí)間調(diào)度觸發(fā)調(diào)度中的VB腳本Access數(shù)據(jù)庫(kù)報(bào)表顯示數(shù)據(jù)存入 1.數(shù)據(jù)庫(kù)的建立和數(shù)據(jù)關(guān)聯(lián)方法：（1）直接在Microsoft office中建立Access文件。 （2）在控制面板里ODBC數(shù)據(jù)源管理器里關(guān)聯(lián)數(shù)據(jù)庫(kù)時(shí)創(chuàng)建Access數(shù)據(jù)庫(kù)。 1.1數(shù)據(jù)庫(kù)的建立表創(chuàng)建 （1）打開新建的Access文件，點(diǎn)擊 。 （2）創(chuàng)建一個(gè)，名字為ReportData的表。在表名處右鍵鼠標(biāo)點(diǎn)擊設(shè)計(jì)視圖 ，將字段名稱，數(shù)據(jù)類型，字段屬性更改為

2、下圖所示：1.2數(shù)據(jù)庫(kù)的關(guān)聯(lián)按照開始設(shè)置控制面板管理工具數(shù)據(jù)源的操作順序進(jìn)入數(shù)據(jù)庫(kù)引用關(guān)聯(lián)定義畫面如下圖。在其中選擇點(diǎn)擊System DSN，再點(diǎn)擊ADD命令，在彈出的選擇列表中選擇Microsoft Access Driver*.mdb，再點(diǎn)擊完成按鈕，進(jìn)入下邊的窗口，在其中的數(shù)據(jù)源名(N)后的輸入?yún)^(qū)中填寫一個(gè)名字，然后在數(shù)據(jù)庫(kù)欄中點(diǎn)擊選擇(S)按鈕，選擇剛才定義的數(shù)據(jù)庫(kù)文件名即可。在以后的應(yīng)用中只使用DSN（數(shù)據(jù)源名）名，不使用數(shù)據(jù)名。2時(shí)間調(diào)度的建立2.1在ifix里新建基于時(shí)間的調(diào)度，在Schedules新建一個(gè)調(diào)度文件，在Time Based Entries中創(chuàng)建一個(gè)調(diào)度，具體設(shè)置如

3、下圖：注意：iFIX 不支持使用與本地節(jié)點(diǎn)名相同名稱的調(diào)度。 因此，不能創(chuàng)建與本地節(jié)點(diǎn)名相同的調(diào)度。觸發(fā)信息設(shè)置為continuous，間隔為1個(gè)小時(shí)，啟動(dòng)時(shí)間為第2分鐘。點(diǎn)擊Vb Editor 將以下腳本寫入Private Sub RW2REPORT_OnTimeOut(ByVal lTimerId As Long) Dim cn As ADODB.Connection '定義一個(gè)ADO方式的數(shù)據(jù)庫(kù)連接 Dim res As ADODB.Recordset '定義一個(gè)ADO方式的數(shù)據(jù)庫(kù)記錄集 Dim StrSQL As String Set cn = New ADODB.Co

4、nnection '定義cn為新的ADO數(shù)據(jù)庫(kù)連接 Set res = New ADODB.Recordset '定義res為新的ADO數(shù)據(jù)庫(kù)連接集ODBC 數(shù)據(jù)源名稱 'On Error Resume Nextcn.ConnectionString = "DSN=ReportSource;UID=;PWD=;" '定義cn的連接數(shù)據(jù)源為ReportSource 即ODBC中建立的ACCESS的數(shù)據(jù)源名 cn.Open StrSQL = "select * from ReportData Where 日期=#" &

5、 Date & "#" '使用SQL語(yǔ)句查找ReportData表中日期為Date的數(shù)據(jù) res.Open StrSQL, cn, adOpenKeyset, adLockOptimistic res.AddNew '添加一個(gè)新的記錄 res.Fields(0) = Date '在0列加入日期 res.Fields(1) = Hour(Time) '在1列加入時(shí)間res.Fields(2) = Fix32.RW2.RW_Y0GBN11AP001_ZS.f_cv '在2列加入標(biāo)簽1res.Fields(3) = Fix32.RW

6、2.RW_Y0GBN11AP002_ZS.f_cv '在3列加入標(biāo)簽2 res.Update '保存記錄 Update(當(dāng)Edit或AddNew方法完成后保存記錄集) res.Close '關(guān)閉記錄集cn.Close Set res = Nothing Set cn = NothingEnd Sub 在vb的菜單欄點(diǎn)擊工具引用，添加一下控件：2.2添加到FixBackgroundServer 任務(wù) 打開SCU,點(diǎn)擊configureTask 如圖所示：在文件名內(nèi)查找FixBackgroundServer.exe文件添加后保存SCU文件。2.3在后臺(tái)任務(wù)啟動(dòng)列表中添加調(diào)度

7、點(diǎn)擊settingsuser preferences進(jìn)入如下圖界面，添加后臺(tái)啟動(dòng)項(xiàng)3同時(shí)在Report畫面里，添加WEB控件：4 報(bào)表生成在IFIX畫面上插入一個(gè)按鈕。按鈕名稱：CommandButton1報(bào)表生成按鈕代碼如下：Private Sub CommandButton1_Click()'On Error Resume NextDim cn As ADODB.ConnectionDim res As ADODB.RecordsetDim xlApp As Excel.ApplicationDim xlBook As Excel.WorkbookDim xlsheet1 As E

8、xcel.WorksheetDim xlsheet2 As Excel.WorksheetDim strFileName As StringDim StrSQL As StringDim i As IntegerDim row As IntegerDim CreatDayReport As Boolean WebBrowser.Navigate "E:空?qǐng)?bào)表.htm"strFileName = "E:ReportView.htm" '預(yù)先設(shè)計(jì)的報(bào)表顯示模板文件,存放路徑C:Documents and SettingsAdministrator桌面

9、 實(shí)際看工程存放路徑這里是測(cè)試StrSQL = "select * from ReportData where 日期=#" & Calendar.Value & "#" '使用SQL語(yǔ)句查詢Calendar日歷上日期的數(shù)據(jù)If Dir(strFileName) = "" Then '判斷文件是否存在，不存在則退出 MsgBox "報(bào)表模版文件不存在" Exit SubEnd IfSet cn = New ADODB.Connection '新建連接ODBC數(shù)據(jù)源名稱cn.Co

10、nnectionString = "DSN=ReportSource;UID=;PWD=;" '連接到ReportSource數(shù)據(jù)源cn.Open '打開連接Set res = New ADODB.Recordset '新建記錄集res.Open StrSQL, cn, adOpenKeyset, adLockOptimisticIf res.RecordCount <= 0 Then 'RecordCount 記錄集中記錄的數(shù)量。若無(wú)法判斷記錄條數(shù)則返回-1 MsgBox "你要查詢的數(shù)據(jù)不存在，可能已被刪除",

11、vbInformation + vbOKOnly, "系統(tǒng)提示" res.Close Set res = Nothing cn.Close Set cn = Nothing Exit Sub Else res.MoveFirst '將記錄指針移動(dòng)到第一行 Set xlApp = New Excel.Application '新建Excel Set xlBook = xlApp.Workbooks.Open("E:ReportView.htm") 'Set xlBook = GetObject(strFileName) '打

12、開模版XLS文件 Set xlsheet1 = xlBook.Worksheets(1) Set xlsheet2 = xlBook.Worksheets(2) xlBook.Worksheets.Application.Visible = False ' 設(shè)置第一張表為可視 xlsheet1.Range("b4", "g27") = "" 'b4列g(shù)27行清空 xlsheet2.Range("b4", "g27") = "" 'b4列g(shù)27行清空 x

13、lsheet1.Cells(2, "A") = CDate(res.Fields(0) '獲取日期 CDate轉(zhuǎn)換為幼小日期 xlsheet2.Cells(2, "A") = CDate(res.Fields(0) '獲取日期 CDate轉(zhuǎn)換為幼小日期 i = 0 While i < res.RecordCount row = res.Fields(1) + 4 '利用時(shí)間排序 xlsheet1.Cells(row, "b") = res.Fields(2) xlsheet1.Cells(row, &qu

14、ot;c") = res.Fields(3) i = i + 1 res.MoveNext Wend xlApp.DisplayAlerts = False xlBook.SaveAs "E:ReportView.htm" '保存 以日?qǐng)?bào)表.htm格式來(lái)保存報(bào)表 xlApp.DisplayAlerts = True '將日?qǐng)?bào)表顯示出來(lái) xlApp.Quit Set xlsheet1 = Nothing Set xlsheet2 = Nothing Set xlBook = Nothing Set xlApp = Nothing CreatDayRe

15、port = True If CreatDayReport = True Then WebBrowser.Navigate "e:ReportView.htm" Else WebBrowser.Navigate "e:空?qǐng)?bào)表.htm" End If End If End Sub以上對(duì)于數(shù)據(jù)代碼部分，添加的數(shù)據(jù)名稱、數(shù)量要以本身項(xiàng)目所對(duì)數(shù)據(jù)庫(kù)為基準(zhǔn)。5保存報(bào)表在IFIX中插入一個(gè)按鈕，命名為：CommandButton2保存報(bào)表按鈕代碼如下：Private Sub CommandButton2_Click()Dim FileName As StringOn

16、 Error GoTo errHandler Dim strReport As String 'Dim mydate As Date 'mydate = Date strReport = "日?qǐng)?bào)表" 'If Me.DayControl = True Then strReport = "日?qǐng)?bào)表" 'If Me.MonthControl = True Then strReport = "月報(bào)表"On Error Resume Next Set xlApp = GetObject(, "Excel.

17、Application") If Err.Number <> 0 Then Set xlApp = CreateObject("Excel.Application") Else Set xlApp = GetObject(, "Excel.Application") xlApp.ScreenUpdating = True End If Err.ClearOn Error GoTo errHandler 'Set xlApp = getobjece(, "excel.application") 'x

18、lApp.ScreenUpdating = True xlApp.DisplayAlerts = False Set xlBook = xlApp.Workbooks.Open("E:ReportView.htm") xlApp.Visible = False ' dlgSaveAs.CancelError = True ' dlgSaveAs.Flags = cdlOFNHideReadOnly ' dlgSaveAs.Filter = "HTML 文件(*.HTM)|*.HTM|EXCEL 文件(*.xls)|*.xls" &

19、#39; dlgSaveAs.FileName = "C:Documents and SettingsAdministrator桌面" & Format(Calendar.Value, "yyyy-MM-dd") & strReport & ".xls" FileName = "E:" & Format(Calendar.Value, "yyyy-MM-dd") & strReport & ".xlsx" 'dlgSav

20、eAs.Filename = strReport ' dlgSaveAs.FilterIndex = 2 'dlgSaveAs.ShowSave 'xlBook.SaveAs Filename:=dlgSaveAs.Filename, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False xlBook.SaveAs FileName xlApp.DisplayAlerts =

21、 True Set xlsheet1 = Nothing Set xlsheet2 = Nothing Set xlBook = Nothing xlApp.Quit' xlApp.Visible = True Set xlApp = Nothing Exit SuberrHandler: xlApp.DisplayAlerts = True Set xlsheet1 = Nothing Set xlsheet2 = Nothing Set xlBook = Nothing xlApp.Quit' xlApp.Visible = True Set xlApp = Nothing

22、 MsgBox "保存出錯(cuò)！"End Sub6 報(bào)表日期查詢功能添加打開IFIX報(bào)表畫面，點(diǎn)擊工具箱TOOLBOX，單擊OLE，,選擇日歷控件：Calendar Control 11.0；日歷控件必須初始化，初始化代碼如下：Private Sub CFixPicture_Initialize() '對(duì)日歷控件初始化Calendar.Value = DateEnd Sub至此IFIX報(bào)表ODBC已經(jīng)組態(tài)完成，若需要打印報(bào)表，則添加打印按鈕：打印按鈕代碼如下：Private Sub cmdPrint_Click()On Error Resume Next '日?qǐng)?bào)

23、頁(yè)面設(shè)置 If Calendar.Value = True Then PrintSet "0.5", "0.5", "0.5", "0.5" End If WebBrowser.ExecWB 6, OLECMDEXECOPT_PROMPTUSEREnd Sub打印頁(yè)面設(shè)置代碼如下：Private Sub PrintSet(sBottom As String, strTop As String, sLeft As String, sMargin_right As String) Dim hkey_root, hkey_path, hkey_key As String Dim RegWsh As Object hkey_root = "HKEY_CURRENT_USER" hkey_path = "Soft