这是分成两篇记录的学习笔记，这是第一篇，在新浪博客刚刚记录过，那边审查有点慢，时不时还会莫名其妙的清零，在这里也记录一次。
最近现场提出要做报表功能，数据来自两种控制系统，施耐德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也就十几秒钟，在画面上还做出了“耐心等待”的提示信息。
运行结果我就不贴出来了，等第二篇写完，我把项目程序放到网盘上共享出来。
标签：报表,SP2,均值,objcom,查询,xlapp,cast,grid,焦炉
From： https://www.cnblogs.com/fishingsriver/p/18462842