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杭州集益科技有限公司文字大小：【HYPERLINKjavascript:fontZoom(16)大】【HYPERLINKjavascript:fontZoom(14)中】【HYPERLINKjavascript:fontZoom(12)小】评分等级：

2、0HYPERLINK/misc.php?action=viewratings&tid=85&pid=553o评分0第一步，创建ODBC数据源，具体方法：启动控制面板中的数据源ODBC，单击“系统DNS”对话框，选择所需数据源的驱动，如：“Microsoftexceldriver(*.exl)”,单击“完成”按钮，弹出“ODBCMicrosoftexcel安装”对话框；在数据源名中定义一个数据源名称，数据库中“选择”中选择区县数据所在的数据库，此数据库的表至少有三个字段：时间字段、数据字段、毫秒字段。单击确定按钮，新创建数据源就添加到“系统DNS”列表中。第二步，在IFIX中创建代码ADO在安装

3、了MCROWINBASIC6.0等软件就有了；PrivateSubCommandButton1_Click()注释:1。该程序需要安装ADO2.0目标库并在本机注册2。MicrosoftActiveXDataObjects2.1Library必须被引用(Office2000)3。MicrosoftExcel9.0objectlibraries必须被引用(Office2000)4。划=处可根据具体报表修改DimstrQueryAvgAsStringDimcAsIntegerDimrAsIntegerDimIntyexcelAsExcel.ApplicationDimMyDate,MyMonth,

4、MyDay,MyHour,MyMinute,MySecondDimStartTime,EndTime,Duration,DisplayDay,DisplayMonthAsString+=报表中的TAGDimTag1,Tag2,Tag3,Tag4,Tag5,Tag6,Tag7,Tag8AsStringDimItemsAsIntegerTag1=TESTTag2=TEST1Tag3=Tag4=Tag5=Tag6=Tag7=Tag8=从历史库中取得域项，2-DATATIME,VALUE,TAG共三项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+=查询，根据报表修改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如果执行上面的语句出错的话，则最大的可能性就是SQL语句有错误！r=1SetIntyexcel=NewExcel.ApplicationIntyexcel.Visible=False+=打开的报表文件名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中电厂日报表的生成总则：运用iFIX的调度器运行基于时间的VBA脚本来生成每天的EXCEL格式日报表。1)iFIX中调度设置在iFIX中新建时间调度，设置成间隔1小时的调度项，然后点击VB编辑器自定义脚本.2)初始变量定义DimReportArrayAsVariantRem存放日报中所有要显示的参数的数组DimFirstPoint1AsVariantRem第一个变量DimtempvarAsVariantRem中间变量DimstrStartTime,strEndTimeRem报表查询的时间范围DimIn

11、tervalAsVariantRem报表查询的间隔时间DimOutReportfileAsVariantRem输出EXCEL表格的文件名DimTemplateNameAsStringRem这个是日报表模板的文件名3)建立对EXCEL的引用，并打开报表的模板文件：SetmsExcel=CreateObject(Excel.Application)WithmsExcel.WindowState=xlMinimized.Visible=False.Workbooks.OpenReportTemplateName,FalseRem打开报表的模板文件.ActiveWorkbook.ActiveSheet

12、.Select.DisplayAlerts=False.DisplayAlerts=False.Wait(Now()+0.00002)EndWith4)创建SQL查询语句：DimrsADOAsNewADODB.RecordsetDimcnADOAsNewADODB.ConnectionDimSQL0,SQL1ASStringSQLO=selectFIX.valuefromFIXwhereFIX.interval=|+Interval+|andFIX.datetime=ts|+strStartTime+|andFIX.datetimets|+strEndTime+|RemFIX为本地节点名SQL

13、1=SQLO&and(FIX.tag=|&FirstPoint1&|iTotalCols=1ForEachtempvarInFirstReportPointsiTotalCols=iTotalCols+1SQL1=SQL1&orFIX.tag=|&tempvar&|NexttempvarSQL1=SQL1+)5)执行对数据库的查询：SetcnADO=NewADODB.ConnectionWithcnADORem.CursorLocation=adUseClient.OpenPROVIDER=MicrosoftOLEDBProviderforODBCDrivers;dsn=FIXDynamics

14、HistoricalData;uid=sa;pwd=;.Execute(SQL1)EndWith6)建立查询数据库后的记录集，并把数据写到EXCEL中后另存为EXCEL文件：SetrsADO=NewADODB.RecordsetrsADO.CursorLocation=adUseClientrsADO.OpenSQL1,cnADO,adOpenForwardOnly,adLockReadOnlyIfrsADO.BOFThenrsADO.ClosecnADO.CloseWithmsExcelDoEvents.ActiveWorkbook.Close.QuitEndWithSetmsExcel=NothingRemMsgBox第一个表查询条件为空，请检查查询条件ExitSubElsec=1WhilersADO.EOFTrueWithmsExcel.Worksheets(1)Forj=1To24IfrsADO(0)ThenmsExcel.Works