搬家第二天-35.Wincc V7.3 OWC控件读取SQL Server数据表并后台导出到excel文件生成统计图表

本文阐述在Wincc V7.3中，如何利用OWC控件读取数据表，以及如何后台导出excel文件，同时声称统计图表、

一 准备工作

假设sql server有这样一张数据表

在Wincc页面中放一个OWC控件，名字修改为OWC，放置一个按钮钮，按钮脚本为：

Sub OnClick(ByVal Item)                          
Dim OWC
Dim PCName
Dim scon,ssql,conn,ors,ocom
Dim rscount
Dim InsertRowCount,i,j,k,m,SourceData
Dim xlapp,fileName,objsheet,objchart
Dim web
Set OWC=ScreenItems("OWC")

'以下代码首先计算记录数
PCName=HMIRuntime.Tags("@LocalMachineName").Read
scon="Provider = SQLOLEDB.1;Integrated Security=SSPI;Persist SecurityInfo=False;Initial Catalog =MyDB;Data Source = " &PCName & "\WINCC"
Set conn=CreateObject("ADODB.Connection")
ssql="select * from charttable"
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
HMIRuntime.Tags("OWCRecordCount").Write ors.recordcount
'定义表格的数据来源
OWC.ActiveSheet.ConnectionString=scon
OWC.ActiveSheet.CommandText =ssql
'日期时间格式的字段处理
OWC.Range("a2:a" & CStr(ors.recordcount+1)).NumberFormat="yyyy/mm/dd h:mm:ss"
'插入若干行，行数由InsertRowCount定义，表头排版可能有好几行
InsertRowCount=1
rscount=InsertRowCount+1+ors.recordcount '判断最终表格有多少行，便于绘制表格线
For i=1 To InsertRowCount
  OWC.ActiveSheet.Rows("1:1").Insert
Next
'合并单元格,并写表头,字体大小,加粗
OWC.ActiveSheet.Range("a1:e1").Merge
OWC.ActiveSheet.Cells(1,1)="***报表"
OWC.ActiveSheet.Cells(1,1).Font.Size=20
OWC.ActiveSheet.cells(1,1).Font.Bold=True
'加边框
OWC.ActiveWorkbook.Sheets(1).Range("a2:e" & rscount).Borders(1).LineStyle=1
OWC.ActiveWorkbook.Sheets(1).Range("a2:e" & rscount).Borders(2).LineStyle=1
OWC.ActiveWorkbook.Sheets(1).Range("a2:e" & rscount).Borders(3).LineStyle=1
OWC.ActiveWorkbook.Sheets(1).Range("a2:e" & rscount).Borders(4).LineStyle=1
'居中显示
OWC.ActiveSheet.Cells(1,1).HorizontalAlignment =-4108

'开始导出到Excel
Set xlapp=CreateObject("Excel.Application")
xlapp.visible=False
xlapp.workbooks.add
Set objsheet=xlapp.worksheets(1)
For k=1 To ors.fields.count
 objsheet.cells(2,k)=OWC.ActiveSheet.cells(2,k)
Next
objsheet.cells(1,1)="XX装置生产工艺参数报表"
m=ors.recordcount
For i=1 To m
  For j= 1 To ors.fields.count
    objsheet.cells(i+1,j)=OWC.ActiveSheet.cells(i+1,j)
  Next
Next

'以下代码处理日期时间数据格式以及表格边框线、标题合并单元格等排版
  objsheet.range("a1:e1").mergecells=True
   objsheet.range("a3:a" & CStr(1+ors.recordcount)).NumberFormat="yyyy/mm/dd hh:mm:ss"
  objsheet.range("a1").ColumnWidth =20
   objsheet.cells(1,1).HorizontalAlignment = 3
   objsheet.range("a2:e" & CStr(1+ors.recordcount)).borders(1).linestyle=9
   objsheet.range("a2:e" & CStr(1+ors.recordcount)).borders(1).weight=2
   objsheet.range("a2:e" & CStr(1+ors.recordcount)).borders(2).linestyle=9
   objsheet.range("a2:e" & CStr(1+ors.recordcount)).borders(2).weight=2
   objsheet.range("a2:e" & CStr(1+ors.recordcount)).borders(3).linestyle=9
   objsheet.range("a2:e" & CStr(1+ors.recordcount)).borders(3).weight=2
   objsheet.range("a2:e" & CStr(1+ors.recordcount)).borders(4).linestyle=9
   objsheet.range("a2:e" & CStr(1+ors.recordcount)).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("a2:e" & CStr(1+ors.recordcount)),True  '设置数据来源范围
  xlapp.ActiveChart.PlotArea.Select
   'xlapp.ActiveChart.ChartType = "Excel.XlChartType.xlXYScatterSmooth"
   xlapp.ActiveChart.ChartType =72 '平滑散点图
  'charttype各个类型的对应数值不是连续的，详细信息参考http://www.exceltip.net/thread-7361-1-1.html
   '在各条曲线上显示数值
   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 Sub

 

保存后运行，OWC查询数据表结果是这样子

导出的excel文件和统计图表是这个样子：