搬家第二天-28.Wincc V7.3使用Microsoft Hierarchical Flexgrid控件显示SQL Server数据表并导出到excel文件图表
我们在使用Wincc的时候，有时候需要把后台数据库数值读取上来，导出到excel文件，同时根据数据自动生成柱状图/折线图/散点图等等图表，这里面写代码生成图表相对麻烦一些，下面介绍一下整个功能的实现方法。
一 准备工作
1. 数据库准备工作
假设数据库里面有这样一张表
2. wincc准备工作
wincc中要使用Microsoft Hierarchical Flexgrid控件，这个控件默认没有显示在控件列表，需要在Activex控件右键“添加/删除”勾选出来。页面添加控件之后，名字修改为"MSHFGrid"
二 VBS脚本
1. 读取数据表的脚本
Sub OnClick(ByVal Item)
Dim conn
Dim ssql
Dim ors
Dim ocom
Dim scon
Dim MSHFGrid
Dim ADODC
Dim PCName
PCName=HMIRuntime.Tags("@LocalMachineName").Read
scon="Provider = SQLOLEDB.1;Integrated Security=SSPI;Persist SecurityInfo=False;Initial Catalog =MyDB;Data Source = " &PCName & "\WINCC"
ssql="select * from charttable"
Set conn=CreateObject("ADODB.Connection")
conn.ConnectionString=scon
conn.Cursorlocation=3
conn.open
Set ors=CreateObject("ADODB.RecordSet")
Set ocom=CreateObject("ADODB.Command")
ocom.commandtype=1
Set ocom.ActiveConnection=conn
ocom.CommandText=ssql
Set ors=ocom.Execute
Set MSHFGrid=ScreenItems("MSHFGrid")
Set MSHFGrid.DataSource=ors
MSHFGrid.Refresh
MSHFGrid.colwidth(1)=2500 '调整第一列的宽度
Set ors=Nothing
conn.close
Set conn=Nothing
End Sub
2. 导出到excel文件和自动生成图表脚本
Sub OnClick(ByVal Item)
Dim i,j,k,m,n,filename
Dim xlapp
Dim MSHFGrid
Dim ors
Dim objsheet,objchart,SourceData
Set MSHFGrid=ScreenItems("MSHFGrid")
If MSHFGrid.rows>1 Then
Set xlapp=CreateObject("Excel.Application")
xlapp.visible=False
xlapp.workbooks.add
Set objsheet=xlapp.worksheets(1)
For k=1 To MSHFGrid.cols-1
objsheet.cells(3,k)=MSHFGrid.TextMatrix(0,k)
Next
objsheet.cells(1,1)="XX装置生产工艺参数报表"
m=MSHFGrid.rows
For i=1 To m-1
For j= 1 To MSHFGrid.cols-1
objsheet.cells(i+3,j)=MSHFGrid.TextMatrix(i,j)
Next
Next
'以下代码处理日期时间数据格式以及表格边框线、标题合并单元格等排版
objsheet.range("a1:e1").mergecells=True
objsheet.range("a4:a" & CStr(2+MSHFGrid.rows)).NumberFormat="yyyy/mm/dd hh:mm:ss"
objsheet.range("a1").ColumnWidth =20
objsheet.cells(2,1)="生成时间："
objsheet.cells(2,2)=Year(Now) & "年" & Month(Now) & "月" & Day(Now) & "日"
objsheet.cells(1,1).HorizontalAlignment = 3
objsheet.range("a3:e" & CStr(2+MSHFGrid.rows)).borders(1).linestyle=9
objsheet.range("a3:e" & CStr(2+MSHFGrid.rows)).borders(1).weight=2
objsheet.range("a3:e" & CStr(2+MSHFGrid.rows)).borders(2).linestyle=9
objsheet.range("a3:e" & CStr(2+MSHFGrid.rows)).borders(2).weight=2
objsheet.range("a3:e" & CStr(2+MSHFGrid.rows)).borders(3).linestyle=9
objsheet.range("a3:e" & CStr(2+MSHFGrid.rows)).borders(3).weight=2
objsheet.range("a3:e" & CStr(2+MSHFGrid.rows)).borders(4).linestyle=9
objsheet.range("a3:e" & CStr(2+MSHFGrid.rows)).borders(4).weight=2
'以下代码用于生成散点图,在2013版本office测试，其他版本可能无效，此处仅做思路示范
'添加图表以及修改位置和大小
xlapp.worksheets.add
xlapp.sheets("sheet2").Select
xlapp.activesheet.Shapes.AddChart2 240,"xlXYScatterSmooth"
'Msgbox "sheet2上面图标个数为" & CStr(xlapp.activesheet.Shapes.count)
xlapp.activesheet.Shapes.item(1).Select '选中当前图表
xlapp.activesheet.Shapes.item(1).Height=400
xlapp.activesheet.Shapes.item(1).Width=600
xlapp.activesheet.Shapes.item(1).Top=30
xlapp.activesheet.Shapes.item(1).Left=30
'添加数据
Set SourceData=xlapp.ActiveChart.SeriesCollection
SourceData.add objsheet.range("a3:e" & CStr(2+MSHFGrid.rows)),True '设置数据来源范围
xlapp.ActiveChart.PlotArea.Select
'xlapp.ActiveChart.ChartType = "Excel.XlChartType.xlXYScatterSmooth"
xlapp.ActiveChart.ChartType =72 '平滑散点图
'在各条曲线上显示数值
For i=1 To 4
xlapp.ActiveChart.FullSeriesCollection(i).Select
xlapp.ActiveChart.FullSeriesCollection(i).ApplyDataLabels
Next
'设置图表标题
xlapp.ActiveChart.HasTitle=True
xlapp.ActiveChart.ChartTitle.Characters.Text="**装置温度压力流量液位曲线图"
xlapp.ActiveChart.ChartTitle.font.size=20
'以下代码用于后台保存
filename= "c:\" & Year(Now) & "年" & Month(Now) & "月" & Day(Now) & "日-" & Hour(Now) & "点" & Minute(Now) & "分" & Second(Now) & "秒生成生产报表.xlsx"
xlapp.Activeworkbook.saveas (filename)
xlapp.workbooks.close
xlapp.quit
Msgbox "成功导出到C:\"
End If
End Sub
做完之后，查询按钮点击，效果如下：
导出excel生成的图表效果如下：
