WINCC7.5SP2报表练习1-增加大量数据记录,报表查询、快速导出查询结果
这是分成两篇记录的学习笔记,这是第一篇,在新浪博客刚刚记录过,那边审查有点慢,时不时还会莫名其妙的清零,在这里也记录一次。
最近现场提出要做报表功能,数据来自两种控制系统,施耐德M580和ABB AC900F,我不想在每一套控制系统上各做报表,加上ABB AC900F的上位机freelance报表功能有点弱(或许是我不精通吧),于是计划单独找一台计算机,安装wincc7.5SP2(这个软件用的多一点,相对熟悉一点),然后做报表。
这个学习笔记分成两部分,一部分是报表查询、导出功能,一部分是数据记录、通讯配置,分为两篇记录。
这个报表详细要求是每5秒记录两个数据的瞬时值到数据库,HMI可以查询某个日期两个数据瞬时值、分钟均值、小时均值、日均值,该月瞬时值、分钟均值、小时均值、日均值。查询结果导出到电子表格,电子表格末尾记录表格数据最大值、最小值、平均值。
备一台计算机(配置较新,内存要大一点),windows10专业版,wincc7.5 SP2,还安装了微软SSMS数据库管理软件。在WINCC附带SQL SERVER上建立数据库REPORT,启用sa账户,修改数据库服务器身份验证模式为sql server和windows身份验证。

前期为了测试功能,建立了测试用的表table_3,表结构如下,其中id是表示规范,增量为1:
编写下面的sql语句,给table_3添加数据记录,用于后面的测试,按照5秒一个数据,这里插入640万条数据,模拟一年以上的数据量。
declare @i int
declare @dt datetime
set @i=1
set @dt='2024-10-01 00:00:00'
while @i<=6400000
begin
insert into Table_3(dt,nh3_12,nh3_ab) values(@dt,rand()*30,rand()*30)
set @i=@i+1
set @dt=dateadd(s,5,@dt)
end
执行该sql语句需要一些时间,毕竟数据量很大。
在D盘新建一个文件夹“报表”,用于存放导出的查询结果excel文件。
打开wincc,新建一个画面,插入date and time picker控件、单选框控件、microsoft hierarchical flexgrid control6.0(SP4)、输入输出域、文本、按钮等控件,调整好各个控件位置、大小、字体等属性。这些属性使用需要权限,那个注册表授权的方法很好,这里就不贴出来了,可以网上搜索。
date and time picker控件名字设定为DTPicker、microsoft hierarchical flexgrid control6.0(SP4)名字设定为HFGrid,单选控件名字设定为SQLType,输入输出域用来显示两个测量值的当前值,这一篇学习笔记不用管它。
WINCC上新建几个内部变量

WINCC画面打开事件写下面的VBS脚本
Sub OnOpen()
HMIRuntime.Tags("seldate").Write ""
HMIRuntime.Tags("sqltext").Write ""
hmiruntime.Tags("sqltype").Write 0
hmiruntime.Tags("tixing").Write ""
End Sub
查询按钮的鼠标单击vbs脚本为:
Sub OnClick(Byval Item)
Dim strcon
Dim objcon
Dim objcom
Dim objrs
Dim grid
Dim DTPicker
Dim pcname
Dim seldate
Dim sqltype
pcname=HMIRuntime.Tags("@LocalMachineName").Read
sqltype=ScreenItems("SQLType").process
Set grid=ScreenItems("HFGrid")
Set DTPicker=ScreenItems("DTPicker")
seldate= DateValue(DTPicker.Value)
HMIRuntime.Tags("sqltype").Write sqltype
HMIRuntime.Tags("sqldate").Write seldate
Set objcon =Createobject("ADODB.connection")
Set objrs=Createobject("ADODB.recordset")
Set objcom= Createobject("ADODB.command")
objcom.commandtype=1
strcon="Provider=SQLOLEDB;password=3390011;user id=sa;Initial Catalog=report;Data Source=" & pcname & "\WINCC"
objcon.connectionstring=strcon
objcon.Cursorlocation=3
objcon.open
If objcon.state=0 Then
Msgbox "数据库连接失败"
Else
Set objcom.ActiveConnection=objcon
If sqltype=1 Then
objcom.commandtext="SELECT dt as '日期时间',nh3_12 as '1#2#焦炉氨逃逸顺势量',nh3_ab as 'AB焦炉氨逃逸瞬时值' FROM Table_3 where cast(dt As Date)='" & seldate & "' order by id"
HMIRuntime.Tags("sqltext").Write objcom.commandtext
Set objrs=objcom.execute
grid.DataSource=objrs
grid.ColWidth(1)=5500
grid.ColWidth(2)=5500
grid.ColWidth(3)=5500
grid.TextMatrix(0,1)="日期时间"
grid.TextMatrix(0,2)="1#2#焦炉氨逃逸瞬时值"
grid.TextMatrix(0,3)="AB焦炉氨逃逸瞬时值"
End If
If sqltype=2 Then '当日分钟均值
objcom.commandtext="select cast(year1 AS varchar)+'-'+cast(month1 as varchar)+'-'+cast(day1 as varchar)+' ' +cast(hour1 As varchar)+':'+cast(minute1 as varchar) as '日期时间',convert(decimal(10,2),avg(nh3_12)) as '1#2#焦炉氨逃逸分钟均值值',convert(decimal(10,2),avg(nh3_ab)) as 'AB焦炉氨逃逸分钟均值' FROM Table_3 where cast(dt As Date)='" & seldate &"' group by year1,month1,day1,hour1,minute1" & " order by year1,month1,day1,hour1,minute1"
HMIRuntime.Tags("sqltext").Write objcom.commandtext
Set objrs=objcom.execute
grid.DataSource=objrs
grid.ColWidth(1)=5500
grid.ColWidth(2)=5500
grid.ColWidth(3)=5500
grid.TextMatrix(0,1)="日期时间"
grid.TextMatrix(0,2)="1#2#焦炉氨逃逸分钟均值值"
grid.TextMatrix(0,3)="AB焦炉氨逃逸分钟均值"
End If
If sqltype=4 Then '当日小时均值
objcom.commandtext="select cast(year1 AS varchar)+'-'+cast(month1 as varchar)+'-'+cast(day1 as varchar)+' ' +cast(hour1 As varchar)+'点' ,convert(decimal(10,2),avg(nh3_12)) as '1#2#焦炉氨逃逸小时均值值',convert(decimal(10,2),avg(nh3_ab)) as 'AB焦炉氨逃逸小时均值' FROM Table_3 where cast(dt As Date)='" & seldate &"' group by year1,month1,day1,hour1" & " order by year1,month1,day1,hour1"
HMIRuntime.Tags("sqltext").Write objcom.commandtext
Set objrs=objcom.execute
grid.DataSource=objrs
grid.ColWidth(1)=5500
grid.ColWidth(2)=6500
grid.ColWidth(3)=5500
grid.TextMatrix(0,1)="日期时间"
grid.TextMatrix(0,2)="1#2#焦炉氨逃逸小时均值值"
grid.TextMatrix(0,3)="AB焦炉氨逃逸小时均值"
End If
If sqltype=8 Then '当月瞬时值,2024-10-11晚上
objcom.commandtext="SELECT dt as '日期时间',nh3_12 as '1#2#焦炉氨逃逸瞬时值',nh3_ab as 'AB焦炉氨逃逸瞬时值' FROM Table_3 where year1='" & Year(seldate) & "' and month1='" & Month(seldate) & "' order by id"
HMIRuntime.Tags("sqltext").Write objcom.commandtext
Set objrs=objcom.execute
grid.DataSource=objrs
grid.ColWidth(1)=5500
grid.ColWidth(2)=6500
grid.ColWidth(3)=5500
grid.TextMatrix(0,1)="日期时间"
grid.TextMatrix(0,2)="1#2#焦炉氨逃逸瞬时值"
grid.TextMatrix(0,3)="AB焦炉氨逃逸瞬时值"
End If
If sqltype=16 Then '当月分钟均值
objcom.commandtext="select cast(year1 AS varchar)+'-'+cast(month1 as varchar)+'-'+cast(day1 as varchar)+' ' +cast(hour1 As varchar)+':'+cast(minute1 as varchar) as '日期时间',convert(decimal(10,2),avg(nh3_12)) as '1#2#焦炉氨逃逸分钟均值值',convert(decimal(10,2),avg(nh3_ab)) as 'AB焦炉氨逃逸分钟均值' FROM Table_3 where year1='" & Year(seldate) &"' and month1='" & Month(seldate) &"' group by year1,month1,day1,hour1,minute1" & " order by year1,month1,day1,hour1,minute1"
HMIRuntime.Tags("sqltext").Write objcom.commandtext
Set objrs=objcom.execute
grid.DataSource=objrs
grid.ColWidth(1)=5000
grid.ColWidth(2)=7500
grid.ColWidth(3)=5500
grid.TextMatrix(0,1)="日期时间"
grid.TextMatrix(0,2)="1#2#焦炉氨逃逸分钟均值值"
grid.TextMatrix(0,3)="AB焦炉氨逃逸分钟均值"
End If
If sqltype=32 Then '当月小时均值,2024-10-11晚上
objcom.commandtext="select cast(year1 AS varchar)+'-'+cast(month1 as varchar)+'-'+cast(day1 as varchar)+' ' +cast(hour1 As varchar)+'点' as '日期时间',convert(decimal(10,2),avg(nh3_12)) as '1#2#焦炉氨逃逸小时均值',convert(decimal(10,2),avg(nh3_ab)) as 'AB焦炉氨逃逸小时均值' FROM Table_3 where year1='" & Year(seldate) & "' and month1='" & Month(seldate) & "' group by year1,month1,day1,hour1" & " order by year1,month1,day1,hour1"
HMIRuntime.Tags("sqltext").Write objcom.commandtext
Set objrs=objcom.execute
'Msgbox "000"
grid.DataSource=objrs
grid.ColWidth(1)=5500
grid.ColWidth(2)=7000
grid.ColWidth(3)=5500
grid.TextMatrix(0,1)="日期时间"
grid.TextMatrix(0,2)="1#2#焦炉氨逃逸小时均值"
grid.TextMatrix(0,3)="AB焦炉氨逃逸小时均值"
End If
If sqltype=64 Then '当月日均值,2024-10-11晚上
objcom.commandtext="select cast(year1 AS varchar)+'-'+cast(month1 as varchar)+'-'+cast(day1 as varchar) as '日期时间',convert(decimal(10,2),avg(nh3_12)) as '1#2#焦炉氨逃逸小时均值',convert(decimal(10,2),avg(nh3_ab)) as 'AB焦炉氨逃逸小时均值' FROM Table_3 where year1='" & Year(seldate) & "' and month1='" & Month(seldate) & "' group by year1,month1,day1" & " order by year1,month1,day1"
HMIRuntime.Tags("sqltext").Write objcom.commandtext
Set objrs=objcom.execute
'Msgbox "000"
grid.DataSource=objrs
grid.ColWidth(1)=5500
grid.ColWidth(2)=7000
grid.ColWidth(3)=5500
grid.TextMatrix(0,1)="日期时间"
grid.TextMatrix(0,2)="1#2#焦炉氨逃逸小时均值"
grid.TextMatrix(0,3)="AB焦炉氨逃逸小时均值"
End If
'年瞬时量数量量600多万,均值数据量也非常大,取消这几个查询功能。
' If sqltype=128 Then '当年瞬时值,2024-10-11晚上
' objcom.commandtext="SELECT dt,nh3_12,nh3_ab FROM Table_3 where year1='" & Year(seldate) & "' order by id"
' HMIRuntime.Tags("sqltext").Write objcom.commandtext
' Set objrs=objcom.execute
' grid.DataSource=objrs
' grid.ColWidth(1)=5500
' grid.ColWidth(2)=6500
' grid.ColWidth(3)=5500
' grid.TextMatrix(0,1)="日期时间"
' grid.TextMatrix(0,2)="1#2#焦炉氨逃逸瞬时值"
' grid.TextMatrix(0,3)="AB焦炉氨逃逸瞬时值"
' End If
'
' If sqltype=256 Then '当年分钟均值
' objcom.commandtext="select cast(year1 AS varchar)+'-'+cast(month1 as varchar)+cast(day11 as varchar)+' ' +cast(hour1 As varchar)+':'+cast(minute1 as varchar),convert(decimal(10,2),avg(nh3_12)),convert(decimal(10,2),avg(nh3_ab)) FROM Table_3 where year1='" & Year(seldate) & "' group by year1,month1,day1,hour1,minute1" & " order by year1,month1,day1,hour1,minute1"
' HMIRuntime.Tags("sqltext").Write objcom.commandtext
' Set objrs=objcom.execute
' grid.DataSource=objrs
' grid.ColWidth(1)=5500
' grid.ColWidth(2)=5500
' grid.ColWidth(3)=5500
' grid.TextMatrix(0,1)="日期时间"
' grid.TextMatrix(0,2)="1#2#焦炉氨逃逸分钟均值值"
' grid.TextMatrix(0,3)="AB焦炉氨逃逸分钟均值"
' End If
'
' If sqltype=512 Then '当年小时均值,2024-10-11晚上
' objcom.commandtext="select cast(year1 AS varchar)+'-'+cast(month1 as varchar)+'-'+cast(day1 as varchar)+' ' +cast(hour1 As varchar)+'点',convert(decimal(10,2),avg(nh3_12)),convert(decimal(10,2),avg(nh3_ab)) FROM Table_3 where year1='" & Year(seldate) & "' group by year1,month1,day1,hour1" & " order by year1,month1,day1,hour1"
' HMIRuntime.Tags("sqltext").Write objcom.commandtext
' Set objrs=objcom.execute
' grid.DataSource=objrs
' grid.ColWidth(1)=5500
' grid.ColWidth(2)=6500
' grid.ColWidth(3)=5500
' grid.TextMatrix(0,1)="日期时间"
' grid.TextMatrix(0,2)="1#2#焦炉氨逃逸小时均值值"
' grid.TextMatrix(0,3)="AB焦炉氨逃逸小时均值"
' End If
'
' If sqltype=64 Then '当年日均值,2024-10-11晚上
' objcom.commandtext="select cast(year1 AS varchar)+'-'+cast(month1 as varchar),convert(decimal(10,2),avg(nh3_12)),convert(decimal(10,2),avg(nh3_ab)) FROM Table_3 where year1='" & Year(seldate) & "' group by year1,month1" & " order by year1,month1"
' HMIRuntime.Tags("sqltext").Write objcom.commandtext
' Set objrs=objcom.execute
' grid.DataSource=objrs
' grid.ColWidth(1)=5500
' grid.ColWidth(2)=6500
' grid.ColWidth(3)=5500
' grid.TextMatrix(0,1)="日期时间"
' grid.TextMatrix(0,2)="1#2#焦炉氨逃逸小时均值值"
' grid.TextMatrix(0,3)="AB焦炉氨逃逸小时均值"
' End If
End If
上面的脚本屏蔽了几个年度数据,因为我发现年瞬时值数据量好几百万,运行起来太慢,使用体验差。
导出按钮VBS鼠标点击脚本为:
Sub OnClick(Byval Item)
Dim strcon
Dim objcon
Dim objcom
Dim objrs
Dim rscount
Dim pcname
Dim seldate
Dim sqltype
Dim xlapp,xlwb,xlsheet,xlquery,filetype
Select Case HMIRuntime.tags("sqltype").Read
Case 1
filetype="查询日瞬时值报表"
'Msgbox "1"
Case 2
filetype="查询日分钟均值报表"
Case 4
filetype="查询日小时均值报表"
Case 8
filetype="查询月瞬时值报表"
Case 16
filetype="查询月分钟均值报表"
Case 32
filetype="查询月小时均值报表"
Case 64
filetype="查询月日值报表"
End Select
If HMIRuntime.Tags("sqltext").read="" Then
Msgbox "请先查询数据"
Else
Set objcon =Createobject("ADODB.connection")
Set objrs=Createobject("ADODB.recordset")
Set objcom= Createobject("ADODB.command")
objcom.commandtype=1
strcon="Provider=SQLOLEDB;password=3390011;user id=sa;Initial Catalog=report;Data Source=" & pcname & "\WINCC"
objcon.connectionstring=strcon
objcon.Cursorlocation=3
objcon.open
If HMIRuntime.Tags("sqltype").Read =8 Then
HMIRuntime.Tags("tixing").Write "数据量较大,可能需要一点时间"
End If
Set objcom.ActiveConnection=objcon
objcom.commandtext=HMIRuntime.Tags("sqltext").read
Set objrs=objcom.execute
rscount=objrs.recordcount
Set xlapp=Createobject("excel.application")
Set xlwb=xlapp.workbooks.add
xlapp.visible=False
xlapp.sheets.item(1).name="焦炉氨逃逸数据报表"
Set xlsheet=xlapp.sheets.item(1)
Set xlquery=xlsheet.querytables.add(objrs,xlsheet.range("a3"))
xlquery.refresh
xlapp.worksheets(1).cells(1,1)="焦炉氨逃逸数据报表"
xlapp.worksheets(1).cells(2,1)="导出日期时间:" & Now()
xlapp.worksheets(1).range("a1:c1").mergecells=True '合并单元格
xlapp.worksheets(1).range("a2:c2").mergecells=True '合并单元格
xlapp.worksheets(1).cells(1,1).horizontalalignment=3 '文字居中
xlapp.worksheets(1).range("a3:c" & CStr(6+rscount)).horizontalalignment=3 '文字居中
xlapp.worksheets(1).range("a3:a" & CStr(3+rscount)).numberformat = "yyyy/m/d h:mm:ss" '控制显示格式
'设置边框置
xlapp.worksheets(1).range("a3:c" & CStr(3+rscount+3)).borders(1).linestyle=9
xlapp.worksheets(1).range("a3:c" & CStr(3+rscount+3)).borders(1).weight=2
xlapp.worksheets(1).range("a3:c" & CStr(3+rscount+3)).borders(2).linestyle=9
xlapp.worksheets(1).range("a3:c" & CStr(3+rscount+3)).borders(2).weight=2
xlapp.worksheets(1).range("a3:c" & CStr(3+rscount+3)).borders(3).linestyle=9
xlapp.worksheets(1).range("a3:c" & CStr(3+rscount+3)).borders(3).weight=2
xlapp.worksheets(1).range("a3:c" & CStr(3+rscount+3)).borders(4).linestyle=9
xlapp.worksheets(1).range("a3:c" & CStr(3+rscount+3)).borders(4).weight=2
xlapp.worksheets(1).cells(4+rscount,1)="最大值"
xlapp.worksheets(1).cells(5+rscount,1)="最小值"
xlapp.worksheets(1).cells(6+rscount,1)="平均值"
xlapp.worksheets(1).cells(4+rscount,2)="=max(b4:b" & CStr(3+rscount) & ")"
xlapp.worksheets(1).cells(4+rscount,3)="=max(c4:c" & CStr(3+rscount) & ")"
xlapp.worksheets(1).cells(5+rscount,2)="=min(c4:c" & CStr(3+rscount) & ")"
xlapp.worksheets(1).cells(5+rscount,3)="=min(c4:c" & CStr(3+rscount) & ")"
xlapp.worksheets(1).cells(6+rscount,2)="=round(average(b4:b" & CStr(3+rscount) & "),2)"
xlapp.worksheets(1).cells(6+rscount,3)="=round(average(c4:c" & CStr(3+rscount) & "),2)"
xlapp.activeworkbook.saveas("d:\报表\"+CStr(Year(Now())) & "-" & CStr(Month(Now())) & "-" & CStr(Day(Now())) & " " & CStr(Hour(Now())) & "-" & CStr(Minute(Now())) & "-" & CStr(Second(Now())) & filetype & ".xlsx")
xlapp.workbooks.close
xlapp.quit
Msgbox "成功导出到d:\报表 文件夹"
hmiruntime.Tags("tixing").Write ""
Set xlapp=Nothing
Set xlwb=Nothing
Set xlsheet=Nothing
Set objrs=Nothing
objcon.close
Set objcon=Nothing
End If
End Sub
我曾经想给导出的excel文件单元格做数值判断,超限值把底色涂成某个颜色,后来发现数据量大的时候非常慢,使用体验很糟糕,于是放弃了。
做到这里,这一篇学习笔记就结束了。经过测试,查询和导出速度都挺快,几乎不用等待。月瞬时值数据量几十万,导出到excel也就十几秒钟,在画面上还做出了“耐心等待”的提示信息。
运行结果我就不贴出来了,等第二篇写完,我把项目程序放到网盘上共享出来。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤
2022-10-13 ABB AC900F学习笔记151:本地消息处理1
2022-10-13 ABB AC900F学习笔记 150:全局消息确认
2022-10-13 ABB AC900F学习笔记149:操作员站消息3
2022-10-13 ABB AC900F学习笔记147:操作员站消息
2022-10-13 ABB AC900F学习笔记146:web访问
2021-10-13 ABB AC800M学习笔记1:Compact Control Builder界面汉化