目录
1 <概述>
在工业生产中报表一直占有非常重要的部分,它一般用来记录现场的工艺参数、能耗信息和统计信息,作为分析企业运营情况的依据。
本文以Wincc V7.5 SP1 为例,介绍Wincc如何获取设备运行数据统计并通过报表的形式展现统计数据,如图1所示:
可实现如下功能:
- 自动记录设备的运行数据(整点统计)
- 定时存储日报表(excel格式)
- 利用Web控件显示htm报表文件
2 <创建模板>
在Wincc项目文件夹下创建report文件夹,如图2、图3所示:
3 < Wincc变量>
本例中需要用到两种Wincc变量。一种是和设备运行数据相关的Wincc外部变量,包括运行数据、能耗数据等,根据项目实际情况创建。另外一种是用于整点存储相关的变量,这里将介绍利用Wincc系统变量读取当前时间(H值)。如图4所示
4 <创建全局动作>
全局动作有两个文件:
- 动作1:
- 利用模版创建临时存储文件
- 每天00:00:02秒触发动作
- 按“文件名-日期”格式copy数据到指定报表路径下
- 动作2:
- 整点读取读取数据,并存入临时存储文件
4.1 动作1脚本:创建文件及拷贝报表
全局动作脚本如下:
Option Explicit
Function action
Dim objexcelapp,objexcelbook,objexcelsheet
Dim sheetname
Dim dstr,path,fn,cow,fso
Dim MyFile
Dim TempFileName,TemplateFileName,DestFile
TemplateFileName = HMIRuntime.ActiveProject.Path & "\report\日报表\智慧空压站运行日报表_模版.xlsx"
TempFileName = HMIRuntime.ActiveProject.Path & "\report\日报表\智慧空压站运行日报表.xlsx"
DestFile = "E:\报表"
dstr = CStr(Year(Now)) & "-" & CStr(Month(Now)) & "-" & CStr(Day(Now)) & " " & CStr(Hour(Now)) & ":" & CStr(Minute(Now))
fn = CStr(Year(Now)) & CStr(Month(Now)) & CStr(Day(Now)) & CStr(Hour(Now)) & CStr(Minute(Now))
sheetname = "空压机"
Set fso = CreateObject("scripting.FileSystemObject")
Set objexcelapp = CreateObject("Excel.Application")
Set MyFile = fso.GetFile(TemplateFileName)
objexcelapp.visible = False
objexcelapp.Workbooks.open TempFileName
objexcelapp.Worksheets(sheetname).Activate
objexcelapp.ActiveWorkbook.SaveAs(DestFile & fn & ".xlsx")
objexcelapp.ActiveWorkbook.SaveAs(DestFile &"\web\"& fn & ".htm"),44
MyFile.Copy (TempFileName),True
objexcelapp.Workbooks.Close
objexcelapp.Quit
Set objexcelapp = Nothing
End Function
动作触发周期为每日的00:00:02,如下图5所示:
4.2 全局动作2:整点存储数据
全局动作2脚本如下,该部分代码根据项目实际情况进行修改
Option Explicit
Function action
Dim objexcelapp,objexcelbook,objexcelsheet
Dim a,b,c,d,sheetname
Dim dstr,path,fn,cow
Dim TempFileName
TempFileName = HMIRuntime.ActiveProject.Path & "\report\日报表\智慧空压站运行日报表.xlsx"
cow = 4
dstr = CStr(Year(Now)) & "-" & CStr(Month(Now)) & "-" & CStr(Day(Now)) & " " & CStr(Hour(Now)) & ":" & CStr(Minute(Now))
sheetname = "空压机"
Set objexcelapp = CreateObject("Excel.Application")
objexcelapp.visible = False
objexcelapp.Workbooks.open TempFileName '打开临时存储文件
objexcelapp.Worksheets(sheetname).Activate
a = HMIRuntime.Tags("IntHour").Read
With objexcelapp.worksheets(sheetname)
.cells(a + cow,1) = dstr
.cells(a + cow,2) = HMIRuntime.Tags("整数转浮点数_PLC_SD").Read
.cells(a + cow,3) = HMIRuntime.Tags("整数转浮点数_PLC_WD").Read
.cells(a + cow,4) = HMIRuntime.Tags("ZLAN1_1_ZGSSLL1").Read
.cells(a + cow,5) = HMIRuntime.Tags("AI_2_AI_CQG_PRESS1").Read
.cells(a + cow,6) = HMIRuntime.Tags("AI_AI_WATER_TEMP_IN").Read
.cells(a + cow,7) = HMIRuntime.Tags("AI_AI_WATER_IN_PRESS").Read
.cells(a + cow,8) = HMIRuntime.Tags("ZLAN1_3_SYSTEM_PRE_1").Read
.cells(a + cow,9) = HMIRuntime.Tags("ZLAN1_3_KYSSLL_1").Read
.cells(a + cow,10) = HMIRuntime.Tags("ZLAN1_3_ZD1_1").Read
.cells(a + cow,11) = HMIRuntime.Tags("ZLAN1_3_ZD2_1").Read
.cells(a + cow,12) = HMIRuntime.Tags("ZLAN1_3_ZD3_1").Read
.cells(a + cow,13) = HMIRuntime.Tags("ZLAN1_3_OIL_SUPPLY_TMP_1").Read
.cells(a + cow,14) = HMIRuntime.Tags("ZLAN1_3_MOTER_DE_TMP_1").Read
.cells(a + cow,15) = HMIRuntime.Tags("AI_2_AI_KYJ_WATER_PRESS_IN1").Read
.cells(a + cow,16) = HMIRuntime.Tags("ZLAN1_3_SYSTEM_PRE_2").Read
.cells(a + cow,17) = HMIRuntime.Tags("ZLAN1_3_KYSSLL_2").Read
.cells(a + cow,18) = HMIRuntime.Tags("ZLAN1_3_ZD1_2").Read
.cells(a + cow,19) = HMIRuntime.Tags("ZLAN1_3_ZD2_2").Read
.cells(a + cow,20) = HMIRuntime.Tags("ZLAN1_3_ZD3_2").Read
.cells(a + cow,21) = HMIRuntime.Tags("ZLAN1_3_OIL_SUPPLY_TMP_2").Read
.cells(a + cow,22) = HMIRuntime.Tags("ZLAN1_3_MOTER_DE_TMP_2").Read
.cells(a + cow,23) = HMIRuntime.Tags("AI_2_AI_KYJ_WATER_PRESS_IN2").Read
.cells(a + cow,24) = HMIRuntime.Tags("ZLAN1_3_SYSTEM_PRE_3").Read
.cells(a + cow,25) = HMIRuntime.Tags("ZLAN1_3_KYSSLL_3").Read
.cells(a + cow,26) = HMIRuntime.Tags("ZLAN1_3_ZD1_3").Read
.cells(a + cow,27) = HMIRuntime.Tags("ZLAN1_3_ZD2_3").Read
.cells(a + cow,28) = HMIRuntime.Tags("ZLAN1_3_ZD3_3").Read
.cells(a + cow,29) = HMIRuntime.Tags("ZLAN1_3_OIL_SUPPLY_TMP_3").Read
.cells(a + cow,30) = HMIRuntime.Tags("ZLAN1_3_MOTER_DE_TMP_3").Read
.cells(a + cow,31) = HMIRuntime.Tags("AI_2_AI_KYJ_WATER_PRESS_IN3").Read
.cells(a + cow,32) = HMIRuntime.Tags("ZLAN1_3_SYSTEM_PRE_4").Read
.cells(a + cow,33) = HMIRuntime.Tags("ZLAN1_4_LTLS_4").Read
.cells(a + cow,34) = HMIRuntime.Tags("ZLAN1_3_ZD1_4").Read
.cells(a + cow,35) = HMIRuntime.Tags("ZLAN1_3_ZD2_4").Read
.cells(a + cow,36) = HMIRuntime.Tags("ZLAN1_3_ZD3_4").Read
.cells(a + cow,37) = HMIRuntime.Tags("ZLAN1_3_OIL_SUPPLY_TMP_4").Read
.cells(a + cow,38) = HMIRuntime.Tags("ZLAN1_3_MOTER_DE_TMP_4").Read
.cells(a + cow,39) = HMIRuntime.Tags("AI_2_AI_KYJ_WATER_PRESS_IN4").Read
.cells(a + cow,40) = HMIRuntime.Tags("ZLAN1_3_SYSTEM_PRE_8").Read
.cells(a + cow,41) = HMIRuntime.Tags("ZLAN1_3_KYSSLL_8").Read
.cells(a + cow,42) = HMIRuntime.Tags("ZLAN1_3_ZD1_8").Read
.cells(a + cow,43) = HMIRuntime.Tags("ZLAN1_3_ZD2_8").Read
.cells(a + cow,44) = HMIRuntime.Tags("ZLAN1_3_ZD3_8").Read
.cells(a + cow,45) = HMIRuntime.Tags("ZLAN1_3_OIL_SUPPLY_TMP_8").Read
.cells(a + cow,46) = HMIRuntime.Tags("ZLAN1_3_MOTER_DE_TMP_8").Read
.cells(a + cow,47) = HMIRuntime.Tags("AI_2_AI_KYJ_WATER_PRESS_IN8").Read
End With
objexcelapp.ActiveWorkbook.Save
objexcelapp.Workbooks.Close
objexcelapp.Quit
Set objexcelapp = Nothing
End Function
动作触发周期为每小时的00:05秒,如下图6所示:
5 <制作报表画面>
Wincc报表画面分几项功能:
- 点击“实时报表”按钮,展示今日运行数据
- 遍历文件夹下所有报表文件(htm格式)到列表框中,选中后点击“历史报表查询”按钮
5.1 控件添加
- **Web控件:**Microsoft Web Browser,“myweb”
- **列表框控件:**Microsoft Lisview Control,“listbox1”
- **按钮控件:**Button(“实时报表”、“历史报表查询”)
控件添加方式
在 ActiveX 控件上右键,选择“添加/删除”,然后在 OCX 控件列表中选择
Microsoft Lisview Control 和 Microsoft Web Browser。如下图7所
示:
5.2 脚本
- 窗体脚本
窗体打开后,执行web控件加载“实时报表”,同时向列表框循环添加文件夹下遍历到所有报表名称。
“窗体对象”——“其他”——“打开画面”,执行VB脚本,如下所示:
Sub OnOpen()
On Error Resume Next
Dim fso, f, f1, fc, N
Dim lst,wbCtrl
Set lst = ScreenItems("listbox1")
Set wbCtrl = ScreenItems("myweb")
Set fso = CreateObject("scripting.FileSystemObject")
Set f = fso.GetFolder("E:\报表\web格式")
Set fc = f.Files
N = 0
For Each f1 In fc
N = N + 1
lst.Index N
lst.Text f1.name
Next
lst.NumberLines =N
lst.SelIndex ="1"
lst.Sort = 1
wbCtrl.Navigate HMIRuntime.ActiveProject.Path & "\report\日报表\web\智慧空压站运行日报表.htm"
End Sub
- 按钮“实时报表”VB脚本
Sub OnClick(Byval Item)
On Error Resume Next
item.Enabled = False
Dim lst,wbCtrl
Dim Value1
Set wbCtrl = ScreenItems("myweb")
Set lst = ScreenItems("listbox1")
Value1 = lst.SelText
wbCtrl.Navigate HMIRuntime.ActiveProject.Path & "\report\日报表\web\智慧空压站运行日报表.htm"
item.Enabled = True
End Sub
- 按钮“历史报表查询”VB脚本
Sub OnClick(Byval Item)
On Error Resume Next
item.Enabled = False
Dim lst,wbCtrl
Dim Value1
Set wbCtrl = ScreenItems("myweb")
Set lst = ScreenItems("listbox1")
Value1 = lst.SelText
wbCtrl.Navigate "E:\报表\web格式\" & Value1
item.Enabled = True
End Sub