ifix和excel連接上課講義

1、Good is good, but better carries it.精益求精，善益求善。ifix和excel連接IFIX與EXCEL的連接2007-02-2420:16:44作者：HYPERLINK/member/member.php?username=ifixonlinet_blankifixonline來源：HYPERLINKt_blank杭州集益科技有限公司文字大?。骸綡YPERLINKjavascript:fontZoom(16)大】【HYPERLINKjavascript:fontZoom(14)中】【HYPERLINKjavascript:fontZoom(12)小】評(píng)分等級(jí)：

2、0HYPERLINK/misc.php?action=viewratings&tid=85&pid=553o評(píng)分0第一步，創(chuàng)建ODBC數(shù)據(jù)源，具體方法：?jiǎn)?dòng)控制面板中的數(shù)據(jù)源ODBC，單擊“系統(tǒng)DNS”對(duì)話框，選擇所需數(shù)據(jù)源的驅(qū)動(dòng)，如：“Microsoftexceldriver(*.exl)”,單擊“完成”按鈕，彈出“ODBCMicrosoftexcel安裝”對(duì)話框；在數(shù)據(jù)源名中定義一個(gè)數(shù)據(jù)源名稱，數(shù)據(jù)庫中“選擇”中選擇區(qū)縣數(shù)據(jù)所在的數(shù)據(jù)庫，此數(shù)據(jù)庫的表至少有三個(gè)字段：時(shí)間字段、數(shù)據(jù)字段、毫秒字段。單擊確定按鈕，新創(chuàng)建數(shù)據(jù)源就添加到“系統(tǒng)DNS”列表中。第二步，在IFIX中創(chuàng)建代碼ADO在安裝

3、了MCROWINBASIC6.0等軟件就有了；PrivateSubCommandButton1_Click()注釋:1。該程序需要安裝ADO2.0目標(biāo)庫并在本機(jī)注冊(cè)2。MicrosoftActiveXDataObjects2.1Library必須被引用(Office2000)3。MicrosoftExcel9.0objectlibraries必須被引用(Office2000)4。劃=處可根據(jù)具體報(bào)表修改DimstrQueryAvgAsStringDimcAsIntegerDimrAsIntegerDimIntyexcelAsExcel.ApplicationDimMyDate,MyMonth,

4、MyDay,MyHour,MyMinute,MySecondDimStartTime,EndTime,Duration,DisplayDay,DisplayMonthAsString+=報(bào)表中的TAGDimTag1,Tag2,Tag3,Tag4,Tag5,Tag6,Tag7,Tag8AsStringDimItemsAsIntegerTag1=TESTTag2=TEST1Tag3=Tag4=Tag5=Tag6=Tag7=Tag8=從歷史庫中取得域項(xiàng)，2-DATATIME,VALUE,TAG共三項(xiàng)Items=2-=MyDate=Now()MyMonth=Month(MyDate)MyDay=Day

5、(MyDate)MyHour=Hour(MyDate)MyMinute=Minute(MyDate)MySecond=Second(MyDate)StartTime=2000&-&MyMonth&-&MyDay-1&00:00:00EndTime=2000&-&MyMonth&-&MyDay-1&23:00:00+=查詢，根據(jù)報(bào)表修改strQueryAvg=SelectDATETIME,VALUE,TAGFROMFIX&_WHEREMODE=AVERAGEand(TAG=&Tag1&orTAG=&Tag2&_orTAG=&Tag3&orTAG=&Tag4&orTAG=&Tag5&_orTAG=

6、&Tag6&orTAG=&Tag7&orTAG=&Tag8&)&_andINTERVAL=01:00:00and&_(DATETIME=and&_DATETIME=)-=DimcnADOAsNewADODB.ConnectionDimrsADOAsRecordsetSetcnADO=NewADODB.ConnectioncnADO.ConnectionString=DSN=FIXDynamicsHistoricalData;UID=sa;PWD=;cnADO.OpenFIXDynamicsHistoricalData,sa,SetrsADO=NewADODB.RecordsetrsADO.Op

7、enstrQueryAvg,cnADO,adOpenForwardOnly,adLockBatchOptimistic如果執(zhí)行上面的語句出錯(cuò)的話，則最大的可能性就是SQL語句有錯(cuò)誤！r=1SetIntyexcel=NewExcel.ApplicationIntyexcel.Visible=False+=打開的報(bào)表文件名DimOutReportFileAsStringDimInReportFileAsStringInReportFile=C:DynamicsAppHIST1Intyexcel.Workbooks.OpenInReportFile&.XLSIntyexcel.Sheets(Shee

8、t2).SelectIntyexcel.Columns(A:Z).SelectIntyexcel.Selection.ClearContentsIntyexcel.Range(A1).SelectWhilersADO.EOFTrueWithIntyexcel.Worksheets(2)Forc=0ToItemsIfrsADO(c)Then.Cells(r,c+1).Value=rsADO(c)Nextcr=r+1rsADO.MoveNextEndWithWendIntyexcel.Sheets(Sheet1).SelectIntyexcel.ActiveSheet.PageSetup.Orie

9、ntation=xlPortraitxlLandscapeIntyexcel.ActiveSheet.PageSetup.PaperSize=xlPaperA4Intyexcel.ActiveSheet.PrintOutIntyexcel.DisplayAlerts=FalseIntyexcel.ActiveWorkbook.SaveOutReportFile=InReportFile&_00&MyMonth&MyDayIntyexcel.ActiveWorkbook.SaveAsOutReportFileIntyexcel.QuitIntyexcel.DisplayAlerts=TrueSe

10、tIntyexcel=NothingSetcnADO=NothingEndSub4.2iFIX中電廠日?qǐng)?bào)表的生成總則：運(yùn)用iFIX的調(diào)度器運(yùn)行基于時(shí)間的VBA腳本來生成每天的EXCEL格式日?qǐng)?bào)表。1)iFIX中調(diào)度設(shè)置在iFIX中新建時(shí)間調(diào)度，設(shè)置成間隔1小時(shí)的調(diào)度項(xiàng)，然后點(diǎn)擊VB編輯器自定義腳本.2)初始變量定義DimReportArrayAsVariantRem存放日?qǐng)?bào)中所有要顯示的參數(shù)的數(shù)組DimFirstPoint1AsVariantRem第一個(gè)變量DimtempvarAsVariantRem中間變量DimstrStartTime,strEndTimeRem報(bào)表查詢的時(shí)間范圍DimIn

11、tervalAsVariantRem報(bào)表查詢的間隔時(shí)間DimOutReportfileAsVariantRem輸出EXCEL表格的文件名DimTemplateNameAsStringRem這個(gè)是日?qǐng)?bào)表模板的文件名3)建立對(duì)EXCEL的引用，并打開報(bào)表的模板文件：SetmsExcel=CreateObject(Excel.Application)WithmsExcel.WindowState=xlMinimized.Visible=False.Workbooks.OpenReportTemplateName,FalseRem打開報(bào)表的模板文件.ActiveWorkbook.ActiveSheet

12、.Select.DisplayAlerts=False.DisplayAlerts=False.Wait(Now()+0.00002)EndWith4)創(chuàng)建SQL查詢語句：DimrsADOAsNewADODB.RecordsetDimcnADOAsNewADODB.ConnectionDimSQL0,SQL1ASStringSQLO=selectFIX.valuefromFIXwhereFIX.interval=|+Interval+|andFIX.datetime=ts|+strStartTime+|andFIX.datetimets|+strEndTime+|RemFIX為本地節(jié)點(diǎn)名SQL

13、1=SQLO&and(FIX.tag=|&FirstPoint1&|iTotalCols=1ForEachtempvarInFirstReportPointsiTotalCols=iTotalCols+1SQL1=SQL1&orFIX.tag=|&tempvar&|NexttempvarSQL1=SQL1+)5)執(zhí)行對(duì)數(shù)據(jù)庫的查詢：SetcnADO=NewADODB.ConnectionWithcnADORem.CursorLocation=adUseClient.OpenPROVIDER=MicrosoftOLEDBProviderforODBCDrivers;dsn=FIXDynamics

14、HistoricalData;uid=sa;pwd=;.Execute(SQL1)EndWith6)建立查詢數(shù)據(jù)庫后的記錄集，并把數(shù)據(jù)寫到EXCEL中后另存為EXCEL文件：SetrsADO=NewADODB.RecordsetrsADO.CursorLocation=adUseClientrsADO.OpenSQL1,cnADO,adOpenForwardOnly,adLockReadOnlyIfrsADO.BOFThenrsADO.ClosecnADO.CloseWithmsExcelDoEvents.ActiveWorkbook.Close.QuitEndWithSetmsExcel=NothingRemMsgBox第一個(gè)表查詢條件為空，請(qǐng)檢查查詢條件ExitSubElsec=1WhilersADO.EOFTrueWithmsExcel.Worksheets(1)Forj=1To24IfrsADO(0)ThenmsExcel.Works