WINCC7.5SP2报表练习1-增加大量数据记录，报表查询、快速导出查询结果
2024-10-13 19:13阅读：
最近现场提出要做报表功能，数据来自两种控制系统，施耐德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焦炉氨逃逸分钟均值'
'
