報表自動生成

報表自動生成制作方法與相關設置報表的自動生成需要從三個方面來確保其功能的實現(xiàn)：1、Excel報表模版的制作；2、配置好相應的打印機；3、在PKS服務器上對報表自動生成及打印進行相關設置。下面從這三個方面介紹詳細的制作過程。一、Excel模版的制作Excel本身就有強大的數(shù)據(jù)庫功能與數(shù)據(jù)連接功能，需要通過相應的命令程序使Excel表格按照我們的需求自動的從服務器讀取到生產數(shù)據(jù)。首先需要對Excel進行設置，打開其數(shù)據(jù)交換功能，如圖所示，具體方法略。i工具①「廝九廠…番戸?觀莎! ftE苗€亡10日t包 卜：圖1打開Excel數(shù)據(jù)交換功能后，可利用Excel支持的命令程序使其自動獲取數(shù)據(jù)。按照每兩小時整點讀取一次數(shù)據(jù)，每天早晨8點刷新上一日報表進行當日報表生成，所用的命令如附表所示。二、 打印機配置打印機連接在中控室4#操作站上，以Station4#作為連接打印機的主機在網(wǎng)內對打印機進行共享。在主機上設置共享后，服務器作為叢機需要將網(wǎng)絡打印機添加到本機上，添加好打印機后，在PKS的操作界面上進入SystemConfiguration的Printer打印機設置界面，啟用剛建立的打印機即可。注：（1）在此處添加網(wǎng)絡打印機時，在網(wǎng)內查找共享打印機，須在地址欄里輸入\\LCRWS4，將此地址下的打印機共享，而不是\\10.10.10.27。盡管二者所代表的是同一臺計算機，但名稱上的不同可能會造成一些故障導致報表打印無法完成。（2）在服務器上的Administrator用戶下添加上打印機后，還要在Mngr用戶下同樣添加打印機。Mngr用戶下添加好打印機后不用再在PKS界面上進行打印機設置。三、 PKS設置Excel表格制作是做出來一個報表的模型，要讀取哪些數(shù)據(jù)，以及何時進行讀取。PKS設置就是為了在每天早晨8點Excel報表開始當天數(shù)據(jù)錄入而清除頭一天數(shù)據(jù)之前，將前一日已生成的報表另存一個備份，放在指定的地方，并按照設定進行打印任務。設置方法如下：打開Station界面，以管理員帳戶登陸，進到報表設置頁面下,進入點擊Reportl，進入后點擊Configure，R-Gporlreport!suligeiroportlReportDetaibReporttypeNameTilleAssignedassethlicnisiuHExc>ellreportlsullgerspahl/AssetsConfigureCtR-Gporlreport!suligeiroportlReportDetaibReporttypeNameTilleAssignedassethlicnisiuHExc>ellreportlsullgerspahl/AssetsConfigureCt|'■■■.LCR_WS4,i.HPCStaticmDofauhPrinterLastRequested:22-Jid^B07:45Completed圖3按以上步驟進入報表設置界面后出現(xiàn)如下畫面，有三個標簽頁，分別按以下方法設置:Definition頁設置：圖4由上至下各項的含義為：Peporttype：報表類型，選擇MicrosoftExcelName:報表名稱，即PKS中顯示的報表名Title：標題，供自己辨識不同報表的標題Reportingonrequest：在請求下生成。選中即可人手動點擊按鈕生成報表Destination：選擇手動生成時所用的打印報表的打印機。上為默認打印機，下面為自己添加。Periodicreporting：周期性生成報表。選中即可按照設定周期自行生成報表并打印。Destination：周期性生成報表時所用的打印報表的打印機。選擇所添加的相應打印機。Nextreport:下一份報表生成的時間。時分即為每天報表生成的時間。按照Excel報表數(shù)據(jù)讀取指令，該時間應在早晨6點以后8點以前。Interval：兩份報表生成的間隔。設為一天。OperatorID：管理級別，即達到什么級別才可對報表進行設置編輯。設為MngrSecurity:選擇/Asset

DefinitionReportort1suligereportlContentScriptingMicrosuftEi-kCelReportDefinitionfileTimeoutPeriod(seconds)『f呂reportG'besnotmanagetouompbtewii/wthetimeoutperiodDefinitionReportort1suligereportlContentScriptingMicrosuftEi-kCelReportDefinitionfileTimeoutPeriod(seconds)『f呂reportG'besnotmanagetouompbtewii/wthetimeoutperiod0曲stop^dgene^te耶errorreportfi/eAreportwi)7mtcompeteifthereportLoniigfj^tiun匚aus&s亍 boxtobedi申4yed.Ensureth話/snott/?ecasebeforehcrGQsingth&tirn^c^tp&riod.A pohodof0陽里卅占m抉噲detaufof30secondsfee.?usedLastRequeeted:i18-Aug-09 i07:45 !Compleled iContent頁設置:該頁設置的內容是每天生成的報表從什么地址讀取模版，讀取哪個模版及超時時間設置。本報表的模版放置在D:\reportl\DailyReport.xls路徑下。注：此文檔切勿隨意改動。Scripting頁設置本頁是寫入一段腳本。報表自動生成的程序是：Excel表格按照頁面程序設置定時讀取生產數(shù)據(jù)，每到第二天早6點，生成一份前24小時內的生產報表，8點進行報表刷新，開始生成又一個循環(huán)的報表°PKS的功能是從D:\report1\DailyReport.xls路徑下獲得報表模版,并自動地、實時地將報表以report001為文件名，保存至路徑C:\ProgramFiles\Honeywell\ExperionPKS\Server\Report\rpt001.xls下。注：此文檔切勿改動。本頁所寫入的腳本的功能就是每天定時將report001以當天日期為文件名另存一份到指定文件夾中。本報表制定的存放報表的文件夾為D:\slg_report_backup。寫入腳本后需要啟動PKS服務器中的腳本引擎。腳本程序可見附表。報表自動生成可能遇到的問題:打印中可能會提示由于內存不足，網(wǎng)絡打印機設置等問題而導致的打印出錯，遇到這種情況，需將本機Administrator帳戶下所共享的WS4的打印機刪除，重新將LCR_WS4的打印機共享到本機即可。即重新添加一下共享打印機。打印機共享設置可問丁坤。若需要暫時停用每日的報表打印功能，只需在PKS的設置頁面下的Definition頁面下，將循環(huán)生成打印的目的打印機選為空即可。即如圖4所示的紅色方框項目，選擇為空項。如要恢復報表打印功能，則選擇圖4所示的打印機即可。報表模版在設置時，由于不同報表格式的行高列寬限制，無法順序排放，只能將不同的報表依對角線排列，這使得在打印時會出現(xiàn)一些空白頁。以當前設置，每天的報表一共會打印出9頁，其中有3張報表，6張空白頁。中控崗人員要確保打印機每天早晨7點到8點是開機狀態(tài)，紙盒內有至少9張A3紙。報表打印時間設置為7：45,若超過7：50報表還未打印，則需及時上報。附表1：數(shù)據(jù)庫鏈接指令8點=IF(IF(HOUR(NOW())<8,HOUR(NOW())+24-8+1,IF(HOUR(NOW())<8,0,HOUR(NOW())-8+1))=0,"",GetHistValArray_Offset(1,"localhost","PI_3101","PV","H1HE",IF(HOUR(NOW())<8,HOUR(NOW())+24-8+1,IF(HOUR(NOW())<8,0,HOUR(NOW())-8+1)),1,"V",""))10點=IF(IF(HOUR(NOW())<8,HOUR(NOW())+24-10+1,IF(HOUR(NOW())<10,0,HOUR(NOW())-10+1))=0,"",GetHistValArray_Offset(1,"localhost","PI_3101","PV","H1HE",IF(HOUR(NOW())<8,HOUR(NOW())+24-10+1,IF(HOUR(NOW())<10,0,HOUR(NOW())-10+1)),1,"V",""))12點=IF(IF(HOUR(NOW())<8,HOUR(NOW())+24-12+1,IF(HOUR(NOW())<12,0,HOUR(NOW())-12+1))=0,"",GetHistValArray_Offset(1,"localhost","PI_3101","PV","H1HE",IF(HOUR(NOW())<8,HOUR(NOW())+24-12+1,IF(HOUR(NOW())<12,0,HOUR(NOW())-12+1)),1,"V",""))14點=IF(IF(HOUR(NOW())<8,HOUR(NOW())+24-14+1,IF(HOUR(NOW())<14,0,HOUR(NOW())-14+1))=0,"",GetHistValArray_Offset(1,"localhost","PI_3101","PV","H1HE",IF(HOUR(NOW())<8,HOUR(NOW())+24-14+1,IF(HOUR(NOW())<14,0,HOUR(NOW())-14+1)),1,"V",""))16點=IF(IF(HOUR(NOW())<8,HOUR(NOW())+24-16+1,IF(HOUR(NOW())<16,0,HOUR(NOW())-16+1))=0,"",GetHistValArray_Offset(1,"localhost","PI_3101","PV","H1HE",IF(HOUR(NOW())<8,HOUR(NOW())+24-16+1,IF(HOUR(NOW())<16,0,HOUR(NOW())-16+1)),1,"V",""))18點=IF(IF(HOUR(NOW())<8,HOUR(NOW())+24-18+1,IF(HOUR(NOW())<18,0,HOUR(NOW())-18+1))=0,"",GetHistValArray_Offset(1,"localhost","PI_3101","PV","H1HE",IF(HOUR(NOW())<8,HOUR(NOW())+24-18+1,IF(HOUR(NOW())<18,0,HOUR(NOW())-18+1)),1,"V",""))20點=IF(IF(HOUR(NOW())<8,HOUR(NOW())+24-20+1,IF(HOUR(NOW())<20,0,HOUR(NOW())-20+1))=0,"",GetHistValArray_Offset(1,"localhost","PI_3101","PV","H1HE",IF(HOUR(NOW())<8,HOUR(NOW())+24-20+1,IF(HOUR(NOW())<20,0,HOUR(NOW())-20+1)),1,"V",""))22點=IF(IF(HOUR(NOW())<8,HOUR(NOW())+24-22+1,IF(HOUR(NOW())<22,0,HOUR(NOW())-22+1))=0,"",GetHistValArray_Offset(1,"localhost","PI_3101","PV","H1HE",IF(HOUR(NOW())<8,HOUR(NOW())+24-22+1,IF(HOUR(NOW())<22,0,HOUR(NOW())-22+1)),1,"V",""))0點=IF(IF(HOUR(NOW())<8,IF(HOUR(NOW())>=0,HOUR(NOW())-0+1,0),0)=0,"",GetHistValArray_Offset(1,"localhost","PI_3101","PV","H1HE",IF(HOUR(NOW())<8,IF(HOUR(NOW())>=0,HOUR(NOW())-0+1,0),0),1,"V",""))2點厶八、、=IF(IF(HOUR(NOW())<8,IF(HOUR(NOW())>=0,HOUR(NOW())-2+1,0),0)=0,"",GetHistValArray_Offset(1,"localhost","PI_3101","PV","H1HE",IF(HOUR(NOW())<8,IF(HOUR(NOW())>=0,HOUR(NOW())-2+1,0),0),1,"V",""))4點' 八、、=IF(IF(HOUR(NOW())<8,IF(HOUR(NOW())>=0,HOUR(NOW())-4+1,0),0)=0,"",GetHistValArray_Offset(1,"localhost","PI_3101","PV","H1HE",IF(HOUR(NOW())<8,IF(HOUR(NOW())>=0,HOUR(NOW())-4+1,0),0),1,"V",""))6點=IF(IF(HOUR(NOW())<8,IF(HOUR(NOW())>=0,HOUR(NOW())-6+1,0),0)=0,"",GetHistValArray_Offset(1,"localhost","PI_3101","PV","H1HE",IF(HOUR(NOW())<8,IF(HOUR(NOW())>=0,HOUR(NOW())-6+1,0),0),1,"V",""))附表2：Dimm_excel1Files\Honeywell\E