| Wincc 7.5 SP1使用VBS创建Excel日报表并显示在画面 | 您所在的位置：网站首页 › Excel周报表如何做 › Wincc 7.5 SP1使用VBS创建Excel日报表并显示在画面 |
Wincc 7.5 SP1使用VBS创建Excel日报表并显示在画面
|
目录
1 2 3 < Wincc变量>4 4.1 动作1脚本：创建文件及拷贝报表4.2 全局动作2：整点存储数据
5 5.1 控件添加5.2 脚本
6
1
在工业生产中报表一直占有非常重要的部分，它一般用来记录现场的工艺参数、能耗信息和统计信息，作为分析企业运营情况的依据。 本文以Wincc V7.5 SP1 为例，介绍Wincc如何获取设备运行数据统计并通过报表的形式展现统计数据，如图1所示： 可实现如下功能： 自动记录设备的运行数据（整点统计）定时存储日报表(excel格式)利用Web控件显示htm报表文件 2在Wincc项目文件夹下创建report文件夹，如图2、图3所示：
本例中需要用到两种Wincc变量。一种是和设备运行数据相关的Wincc外部变量，包括运行数据、能耗数据等，根据项目实际情况创建。另外一种是用于整点存储相关的变量，这里将介绍利用Wincc系统变量读取当前时间（H值）。如图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所示： 全局动作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所示： 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
6
|
【本文地址】
公司简介
联系我们