博图WINCC报表_wincc报表控件

• 目录
• 一、概述
• • 1.1 前言
  • 1.2 主要功能
  • 1.3 使用的软件
• 二、 SQL server建库和建表
• • 2.1、SQL server建库
  • 2.2SQL server建表
  • 三、建立wincc变量、画面
  • 3.1、新建项目：
  • 3.2添加wincc项目
  • 3.2 给wincc添加HMI应用软件和通讯模块
  • 3.3 添加内部变量
  • 3.4 添加变量
  • 3.5添加画面
  • 四、 脚本编写
  • 4.1 新建按钮和全局脚本文件夹
  • • 4.2、MyRnd全局脚本
  • 4.3 随机数生成按钮
  • 4.4 保存按钮
  • 4.5 I/O查询
• 五、脚本运行查看SQL
• • 5.1 随机数脚本运行
  • 5.2 保存按钮运行
  • 5.3 SQL数据库查看保存的数据
  • 5.4 I/O查询按钮运行
• 六、使用控件查询
• • 6.1 添加控件
  • 6.2MSHFlexGrid查询
• 七、数据导出EXCEL并保存、查询
• • 7.1、新建模板表格
  • 7.2、添加导出EXCEL按钮和脚本
• 八、运行界面显示EXCEL数据

目录

一、概述

1.1 前言

博图WINCC通过VBS脚本实现在数据库中保存和查询数据，调用Excel模板把数据保存到指定的位置！

1.2 主要功能

可以实现如下功能：
➢ 自动记录各设备的运行数据
➢ 自动统计各设备的运行时间
➢ 按设备、日期呈现报表
➢ 按日期和设备编号以EXCEL表格保存数据
➢ 可打印存有设备运行数据的表格

1.3 使用的软件

本文档所使用的各软件版本如下：
➢ 操作系统版本：Window10 专业版
➢ 博图版本: TIA V16
➢ WinCC 版本: WinCC RT Professional
➢ Office版本： Microsoft Office 2019

二、 SQL server建库和建表

这里我用的20192019版本的，其他版本的相差无几

2.1、SQL server建库

1、在开始—>所有程序，找到方框中箭头的那个单击，如下图所示：

倘若没有这个图标，请先安装SQL Server 2019。

2、然后，打开后会弹出下面图片,记住这个服务器名称 后面的值，这个等会程序中会用到，

点击链接后，需要建立我们需要的数据库，右击键“数据库” 点击 “新建数据库” 这是建库

4、然后在“数据库名称(N)”中输入数据库名字，点“确定”。这个名字用英文，我测试，所有名字随便取得

5、新建好了之后，点“数据库” ,就出现了下图信息

2.2SQL server建表

1、在库“Hong”下面 的“表” 右键—>“新建”—>点击“表（T）”，就出现下图,这是建表，

建表的意思就是建立一个小房间，这个房间专门用来存放某一个单独业务数据，比如专门存放某一个设备启停和产量等信息

2、进入下图，下面这个图是建字段，字段是用来存放设备中的属性，例如编号，时间、电能、停止电能和产量等等

列名就是填写数据英文名称，
数据类型 是字符、数字、浮点要选的类型都不一样，这个数据类型也很重要，它涉及到保存数据的长度，如果选错了，保存数据就会有问题。
不同的数据库都会详细介绍各个类型的使用场景，数据库基础知识我就不介绍了，随便找一个对于数据库的教学视频或者书都会介绍。
允许Null值意思是是否允许这一个值为空。
就是说当保存一个设备编号为1，开始电能为114KW的数据时候，允许为空的情况下，保存数据的时候，如果SQL语句中对于dev_no的值没取到，或者丢失，则这条数据也会保存，那么最终保存到数据库的信息就是编号为空，年龄为开始电能为114KW

3、其中ID需要改成标识，标识增量为1，如下图所示：

4、填好信息后，保存，就会弹出下面图片，这个是填写表的名字，就是给这个小房间弄一个名字，例如DataTableTest


到这里，数据库的建库建表就完成了，这个是鼠标操作，还可以用SQL语句来建表，或者直接拿别的地方导出的库文件直接导入到这个数据库，前提是同类型数据库。

三、建立wincc变量、画面

3.1、新建项目：

3.2添加wincc项目

3.2 给wincc添加HMI应用软件和通讯模块

3.3 添加内部变量

添加一个DataTableTest变量表

3.4 添加变量

T_Datetime（时间）、T_ID（ID）、T_ID_A（查询ID）、T_Power（电能表数据）、T_Count （生产数量），五个变量

3.5添加画面

添加设备运行报表画面，并在里面添加I/O域和按钮

四、 脚本编写

4.1 新建按钮和全局脚本文件夹

4.2、MyRnd全局脚本

1、添加全局VBS函数，并命名MyRnd脚本

2、变量设置

3、脚本代码

Function MyRnd(ByVal min, ByVal max)
1

MyRnd=Rnd*(max-min+1)+min '在（min,max)之间取随机数

End Function

4、脚本展示

4.3 随机数生成按钮

1、 随机数生成按钮

2、添加按钮脚本

3、 脚本代码

Sub Random_generation()
HMIRuntime.Tags("T_Power").Write MyRnd(0,1000)
HMIRuntime.Tags("T_Count").Write MyRnd(0,1000)
1
2
3

End Sub

4、 脚本展示

5、添加脚本

4.4 保存按钮

1、保存按钮

2、添加按钮脚本

3、脚本代码

Sub Save()
Dim conn                     '定义类对象
Dim SCon                     '定义数据库连接字符串
Dim oRs1                       '定义获取到的数据集
Dim oCom
Dim strSQL1
Dim Datetime<span class="token punctuation">,</span>Count<span class="token punctuation">,</span>Power

 <span class="token string">'---------打开数据库 -----------'</span>
  SCon<span class="token operator">=</span> <span class="token string">"Provider=SQLOLEDB; Integrated Security =SSPI;Persist Security Info=False; Initial Catalog=Hong ; Data Source=DESKTOP-VFDPROG"</span>
	Set conn<span class="token operator">=</span><span class="token function">CreateObject</span><span class="token punctuation">(</span><span class="token string">"ADODB.Connection"</span><span class="token punctuation">)</span>
	  conn<span class="token punctuation">.</span>ConnectionString <span class="token operator">=</span> SCon
	  conn<span class="token punctuation">.</span>CursorLocation <span class="token operator">=</span> <span class="token number">3</span>  
	  conn<span class="token punctuation">.</span>Open
	Set oRs1 <span class="token operator">=</span> <span class="token function">CreateObject</span><span class="token punctuation">(</span><span class="token string">"ADODB.Recordset"</span><span class="token punctuation">)</span>
	Set oCom <span class="token operator">=</span> <span class="token function">CreateObject</span><span class="token punctuation">(</span><span class="token string">"ADODB.Command"</span><span class="token punctuation">)</span>
	 oCom<span class="token punctuation">.</span>CommandType <span class="token operator">=</span> <span class="token number">1</span>
  
  
   <span class="token string">'---------读取数据-----------'</span>		
	Datetime <span class="token operator">=</span> HMIRuntime<span class="token punctuation">.</span><span class="token function">Tags</span><span class="token punctuation">(</span><span class="token string">"T_Datetime"</span><span class="token punctuation">)</span><span class="token punctuation">.</span>Read
	Power <span class="token operator">=</span> HMIRuntime<span class="token punctuation">.</span><span class="token function">Tags</span><span class="token punctuation">(</span><span class="token string">"T_Power"</span><span class="token punctuation">)</span><span class="token punctuation">.</span>Read
	Count <span class="token operator">=</span> HMIRuntime<span class="token punctuation">.</span><span class="token function">Tags</span><span class="token punctuation">(</span><span class="token string">"T_Count"</span><span class="token punctuation">)</span><span class="token punctuation">.</span>Read
   <span class="token string">'---------测试是否能取到值-----------'</span>
    <span class="token function">MsgBox</span><span class="token punctuation">(</span><span class="token string">"Power="</span> <span class="token operator">&amp;</span> Power<span class="token punctuation">)</span>
    
    
    
   
   <span class="token string">'---------弹框显示测试数据是否正确-----------'</span>	 
	Dim Msg<span class="token punctuation">,</span> Style<span class="token punctuation">,</span> Title<span class="token punctuation">,</span> Help<span class="token punctuation">,</span> Ctxt<span class="token punctuation">,</span> Response<span class="token punctuation">,</span> MyString
	    Msg <span class="token operator">=</span> <span class="token string">"Do you want to continue "</span>  ' 定义信息。
	    Style <span class="token operator">=</span> vbYesNo <span class="token operator">+</span> vbQuestion <span class="token operator">+</span> vbDefaultButton2   ' 定义按钮。
	    Title <span class="token operator">=</span> <span class="token string">"是否保存"</span>   ' 定义标题。
	    Help <span class="token operator">=</span> <span class="token string">"DEMO.HLP"</span>   ' 定义帮助文件。
	    Ctxt <span class="token operator">=</span> <span class="token number">1000</span>   ' 定义标题
	  ' 上下文。
	  
	  ' 显示信息。
	    Response <span class="token operator">=</span> <span class="token function">MsgBox</span><span class="token punctuation">(</span>Msg<span class="token punctuation">,</span> Style<span class="token punctuation">,</span> Title<span class="token punctuation">,</span> Help<span class="token punctuation">,</span> Ctxt<span class="token punctuation">)</span>
	    
	    If Response <span class="token operator">=</span> vbYes Then   ' 用户按下“是”。
	        MyString <span class="token operator">=</span> <span class="token string">"Yes"</span>   ' 完成某操作。
		 	 <span class="token string">'---------弹框显示测试数据是否正确-----------'</span>    
		 	 
		 	 
			 	strSQL1 <span class="token operator">=</span> <span class="token string">"INSERT INTO [Hong].[dbo].[DataTableTest] ([Datetime], [Power], [Count])"</span>  
			'<span class="token constant">SQL</span>数据库变量
			    strSQL1 <span class="token operator">=</span> strSQL1 <span class="token operator">&amp;</span> <span class="token string">" VALUES ('"</span> <span class="token operator">&amp;</span> Datetime <span class="token operator">&amp;</span> <span class="token string">"', '"</span> <span class="token operator">&amp;</span> Power <span class="token operator">&amp;</span><span class="token string">"', '"</span> <span class="token operator">&amp;</span> Count <span class="token operator">&amp;</span> <span class="token string">"')"</span>
		    '从wincc读取的临时变量	 						
				Set oCom<span class="token punctuation">.</span>ActiveConnection <span class="token operator">=</span> conn
				    oCom<span class="token punctuation">.</span>CommandText <span class="token operator">=</span> strSQL1           
			    Set oRs1 <span class="token operator">=</span> oCom<span class="token punctuation">.</span>Execute
		
			<span class="token string">'---------关闭数据库-----------'</span>    
				Set oRs1 <span class="token operator">=</span> Nothing
				Set oCom <span class="token operator">=</span> Nothing
				conn<span class="token punctuation">.</span>Close
				Set conn <span class="token operator">=</span> Nothing
			
		<span class="token string">'---------弹框显示测试数据是否正确-----------'</span>    
	    Else   ' 用户按下“否”。
	        MyString <span class="token operator">=</span> <span class="token string">"No"</span>   ' 完成某操作。
	    End If
	<span class="token string">'---------弹框显示测试数据是否正确-----------'</span>    
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
1

End Sub

4、 脚本展示

5、按钮添加脚本

4.5 I/O查询

1、 I/O查询按钮

2、按钮脚本中添加I_O_Search脚本

3、 脚本代码

Sub I_O_Search()
1

Dim conn '定义类对象
Dim SCon '定义数据库连接字符串
Dim oRs1 '定义获取到的数据集
Dim oCom
Dim strSQL1
Dim T_ID_A

T_ID_A = HMIRuntime.Tags(“T_ID_A”).Read

‘---------------------打开数据库 --------------------’
SCon= “Provider=SQLOLEDB; Integrated Security =SSPI;Persist Security Info=False; Initial Catalog=Hong ; Data Source=DESKTOP-VFDPROG”
Set conn=CreateObject(“ADODB.Connection”)
conn.ConnectionString = SCon
conn.CursorLocation = 3
conn.Open
Set oRs1 = CreateObject(“ADODB.Recordset”)
Set oCom = CreateObject(“ADODB.Command”)
oCom.CommandType = 1

‘---------------------查询数据库 --------------------’
strSQL1 =“SELECT [ID],[Datetime],[Power], [Count] FROM [Hong].[dbo].[DataTableTest]”
strSQL1 = strSQL1 & " WHERE ID = ‘“ & T_ID_A & ”’"

Set oCom.ActiveConnection = conn
oCom.CommandText = strSQL1
Set oRs1 = oCom.Execute

‘---------------------查询的数据传送给WinCC的内部变量 --------------------’
HMIRuntime.Tags(“T_ID”).Write oRs1.fields(“ID”).value
HMIRuntime.Tags(“T_Datetime”).Write oRs1.fields(“Datetime”).value
HMIRuntime.Tags(“T_Power”).Write oRs1.fields(“Power”).value
HMIRuntime.Tags(“T_Count”).Write oRs1.fields(“Count”).value

MsgBox( “查询结束” )

‘----------------关闭数据库-----------’
Set oRs1 = Nothing
Set oCom = Nothing
conn.Close
Set conn = Nothing

End Sub

4、 脚本展示

5、按钮添加脚本

五、脚本运行查看SQL

5.1 随机数脚本运行

1、打开“运行系统设置”，点开脚本选项中的启动命令

2、对我们建立的画面右击启动仿真命令

3、点击随机数生成按钮，电能和产量下的数值会随着每次的点击，进行随机变化

5.2 保存按钮运行

1、点击保存按钮后，会显示电能的数值，会和当前显示的电能数值一至。

2、点击确定后，会弹出是否保存的弹窗，
这里如我们点击是的话，数据会保存到SQL中
点击否的话，数据不会保存。

5.3 SQL数据库查看保存的数据

使用SQL查询刚才保存按钮保存的数据
1、右击DataTableTest，选择编写表脚本为(S)–>SELECT到(S)–>新查询编辑器窗口
2、点击查询

3、这是刚才插入的数据

5.4 I/O查询按钮运行

1、选择查询ID,这里以查询数据库的第2行数据为例

2、点击I/O查询，

3、弹出查询结束的弹窗，点击确定即可

4、此时，这里显示的数据和SQL数据库的第二行数据

六、使用控件查询

6.1 添加控件

选择我的控件，在空白处右击，单击选择对象

2、弹出以下图框，在图框里选择控件，选好后点击确定

控件1：Microsoft Date and Time Picker Control 6.0 (SP4)

控件2：Microsoft FlexGrid Control , version 6.0

控件3：Microsoft Web Browser

3、添加完毕后，我的控件里会有刚才选择的三个控件

6.2MSHFlexGrid查询

1、添加按钮，命名为”MSHFlexGrid查询“

2、添加Microsoft FlexGrid Control , version 6.0控件
3、Microsoft FlexGrid Control , version 6.0控件名称修改为Grid

4、在按钮脚本中添加MSHFlexGrid_Search脚本

5、脚本代码

Sub MSHFlexGrid_Search()
1

Dim conn '定义类对象
Dim SCon '定义数据库连接字符串
Dim oRs1 '定义获取到的数据集
Dim oCom
Dim strSQL1
Dim m,i,j,k

‘---------------------打开数据库 --------------------’
SCon= “Provider=SQLOLEDB; Integrated Security =SSPI;Persist Security Info=False; Initial Catalog=Hong ; Data Source=DESKTOP-VFDPROG”
Set conn=CreateObject(“ADODB.Connection”)
conn.ConnectionString = SCon
conn.CursorLocation = 3
conn.Open
Set oRs1 = CreateObject(“ADODB.Recordset”)
Set oCom = CreateObject(“ADODB.Command”)
oCom.CommandType = 1

‘---------------------查询数据库 --------------------’
strSQL1= “SELECT * FROM [Hong].[dbo].[DataTableTest]”

Set oCom.ActiveConnection = conn
oCom.CommandText = strSQL1
Set oRs1 = oCom.Execute

m = oRs1.RecordCount
MsgBox(“查询到表格共有” & m &“行数据”)

‘---------------------设置MSHFlexGrid控件显示--------------------’
Dim olist
Set olist = ScreenItems(“报表”)
olist.clear
olist.Cols=5 '列数
olist.Rows = m+1 '行数量

For i = 0 To 2
olist.ColAlignment(i)=3'列内容居中显示
Next

'设置列宽    
olist<span class="token punctuation">.</span><span class="token function">ColWidth</span><span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token number">800</span>                 
olist<span class="token punctuation">.</span><span class="token function">ColWidth</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token number">1200</span>
olist<span class="token punctuation">.</span><span class="token function">ColWidth</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token number">1200</span>
olist<span class="token punctuation">.</span><span class="token function">ColWidth</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token number">1200</span>
olist<span class="token punctuation">.</span><span class="token function">ColWidth</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token number">1200</span>

'设置表头
olist<span class="token punctuation">.</span><span class="token function">TextMatrix</span><span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span><span class="token operator">=</span><span class="token string">"序号"</span>   
1
2
3
4
5
6
7
8
9

olist.TextMatrix(0, 1)=“ID”
olist.TextMatrix(0, 2) = “时间”
olist.TextMatrix(0, 3) = “电能”
olist.TextMatrix(0, 4) = “生产数量”

‘---------------------将数据写入表格--------------------’
oRs1.movefirst
For i = 1 To m
olist.TextMatrix(i ,0) = i
olist.TextMatrix(i ,1) = oRs1.Fields(0).Value
olist.TextMatrix(i ,2) = oRs1.Fields(1).Value
olist.TextMatrix(i ,3) = oRs1.Fields(2).Value
olist.TextMatrix(i ,4) = oRs1.Fields(3).Value
oRs1.movenext
Next
MsgBox(“查询结束” )
‘----------------关闭数据库-----------’
Set oRs1 = Nothing
Set oCom = Nothing
conn.Close
Set conn = Nothing

End Sub

6、脚本展示：

5、运行情况
5.1点击查询

5.2、弹出共查询到几行数据

5.3、查询结束后会有查询结束的弹窗，点击确定即可

5.4、数据会显示在控件中

七、数据导出EXCEL并保存、查询

7.1、新建模板表格

1、在D盘中新建DataTableTest表格

2、编辑DataTableTest表格
3、D盘新建日报表文件夹

后面的的表格都保存到这个文件夹中

7.2、添加导出EXCEL按钮和脚本

1、添加导出到EXCEL按钮脚本

2、脚本代码

Sub Export_to_Excel()
Dim conn                     '定义类对象
Dim SCon                     '定义数据库连接字符串
Dim oRs1           '定义获取到的数据集
Dim oCom
Dim strSQL1
Dim m
Dim ReportSelect
1
2
3
4
5
6
7
8

‘---------------------打开并查询数据库 --------------------’
SCon= “Provider=SQLOLEDB.1; Integrated Security =SSPI;Persist Security Info=False; Initial Catalog=Hong ; Data Source=DESKTOP-VFDPROG”
strSQL1= “SELECT * FROM [Hong].[dbo].[DataTableTest]”
Set conn=CreateObject(“ADODB.Connection”)
conn.ConnectionString = SCon
conn.CursorLocation = 3
conn.Open
Set oRs1 = CreateObject(“ADODB.Recordset”)
Set oCom = CreateObject(“ADODB.Command”)
oCom.CommandType = 1
Set oCom.ActiveConnection = conn
oCom.CommandText = strSQL1
Set oRs1 = oCom.Execute
m = oRs1.RecordCount
MsgBox(“查询到表格共有” & m &“行数据”)

‘---------------------打开Excel模板 --------------------’
Dim objExcelApp,objExcelBook,objExcelSheet,a,b ,i
Set objExcelApp =CreateObject(“Excel.Application”)
objExcelApp.Visible=True
Set a =objExcelApp.Workbooks.Open(“D:\DataTableTest.xlsx”)
Set b =a.Worksheets(“Sheet1”)
b.Range(“A2”) = "日期: " & CStr(Year(Now)) & “年” & CStr(Month(Now)) &“月” & CStr(Day(Now)) &“日”
objExcelApp.Worksheets(“Sheet1”).Activate
‘---------------------判断有无符合要求的数据 --------------------’
If (oRs1.EOF) Then
MsgBox(“没有符合要求的记录”)
Else
MsgBox(“符合要求的记录”)
oRs1.movefirst
For i = 4 To m+3

        With objExcelApp<span class="token punctuation">.</span><span class="token function">Worksheets</span><span class="token punctuation">(</span><span class="token string">"Sheet1"</span><span class="token punctuation">)</span>
        <span class="token punctuation">.</span><span class="token function">cells</span><span class="token punctuation">(</span>i<span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">.</span>value<span class="token operator">=</span><span class="token function">CStr</span><span class="token punctuation">(</span>oRs1<span class="token punctuation">.</span><span class="token function">Fields</span><span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">.</span>Value<span class="token punctuation">)</span>
        <span class="token punctuation">.</span><span class="token function">cells</span><span class="token punctuation">(</span>i<span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">.</span>value<span class="token operator">=</span><span class="token function">CStr</span><span class="token punctuation">(</span>oRs1<span class="token punctuation">.</span><span class="token function">Fields</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">.</span>Value<span class="token punctuation">)</span>
        <span class="token punctuation">.</span><span class="token function">cells</span><span class="token punctuation">(</span>i<span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">.</span>value<span class="token operator">=</span><span class="token function">CStr</span><span class="token punctuation">(</span>oRs1<span class="token punctuation">.</span><span class="token function">Fields</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">.</span>Value<span class="token punctuation">)</span>
        <span class="token punctuation">.</span><span class="token function">cells</span><span class="token punctuation">(</span>i<span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">)</span><span class="token punctuation">.</span>value<span class="token operator">=</span><span class="token function">CStr</span><span class="token punctuation">(</span>oRs1<span class="token punctuation">.</span><span class="token function">Fields</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">.</span>Value<span class="token punctuation">)</span>
        End With
        oRs1<span class="token punctuation">.</span>MoveNext
    Next
1
2
3
4
5
6
7
8

End If

‘---------------------以日期命名，并保存到指定文件夹 --------------------’
Dim patch,filename
filename=CStr(Year(Now))&““&CStr(Month(Now))&””&CStr(Day(Now))&““&CStr(Hour(Now))&””&CStr(Minute(Now))&“_”&CStr(Second(Now))
patch= “D:\日报表”&filename&“.xlsx”
objExcelApp.ActiveWorkbook.SaveAs patch
objExcelApp.Workbooks.Close
objExcelApp.Quit

     MsgBox <span class="token string">"成功生成数据文件!"</span>
1

‘---------------------关闭数据库 --------------------’
Set objExcelApp= Nothing
Set oRs1 = Nothing
Set oCom = Nothing
conn.Close
Set conn = Nothing

End Sub

3、脚本展示

4、添加按钮
5、按钮添加脚本

6、运行
1、点击导出EXCEL

2、弹窗点击确定

3、导出结果
数据统一至

八、运行界面显示EXCEL数据

8.1、D盘日报表文件夹内新建web文件夹

8.2、添加Web Browser 控件，并修改名称Web

8.3、添加Generate_report脚本

8.4、脚本代码

Sub Generate_report()
1

On Error Resume Next

Dim conn '定义类对象
Dim SCon '定义数据库连接字符串
Dim oRs1 '定义获取到的数据集
Dim oCom
Dim strSQL1
Dim m
Dim ReportSelect

‘---------------------打开并查询数据库 --------------------’
SCon= “Provider=SQLOLEDB.1; Integrated Security =SSPI;Persist Security Info=False; Initial Catalog=Hong ; Data Source=DESKTOP-VFDPROG”
strSQL1= “SELECT * FROM [Hong].[dbo].[DataTableTest]”
Set conn=CreateObject(“ADODB.Connection”)
conn.ConnectionString = SCon
conn.CursorLocation = 3
conn.Open
Set oRs1 = CreateObject(“ADODB.Recordset”)
Set oCom = CreateObject(“ADODB.Command”)
oCom.CommandType = 1
Set oCom.ActiveConnection = conn
oCom.CommandText = strSQL1
Set oRs1 = oCom.Execute
m = oRs1.RecordCount
MsgBox(“查询到表格共有” & m &“行数据”)

‘---------------------打开Excel模板 --------------------’
Dim objExcelApp,objExcelBook,objExcelSheet,a,b ,i
Set objExcelApp =CreateObject(“Excel.Application”)
objExcelApp.Visible=False
objExcelApp.DisplayAlerts=False
Set a =objExcelApp.Workbooks.Open(“D:\DataTableTest.xlsx”)
Set b =a.Worksheets(“Sheet1”)
b.Range(“A2”) = "日期: " & CStr(Year(Now)) & “年” & CStr(Month(Now)) &“月” & CStr(Day(Now)) &“日”
objExcelApp.Worksheets(“Sheet1”).Activate
‘---------------------判断有无符合要求的数据 --------------------’
If (oRs1.EOF) Then
MsgBox(“没有符合要求的记录”)
Else
MsgBox(“符合要求的记录”)
oRs1.movefirst
For i = 4 To m+3

        With objExcelApp<span class="token punctuation">.</span><span class="token function">Worksheets</span><span class="token punctuation">(</span><span class="token string">"Sheet1"</span><span class="token punctuation">)</span>
        <span class="token punctuation">.</span><span class="token function">cells</span><span class="token punctuation">(</span>i<span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">.</span>value<span class="token operator">=</span><span class="token function">CStr</span><span class="token punctuation">(</span>oRs1<span class="token punctuation">.</span><span class="token function">Fields</span><span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">.</span>Value<span class="token punctuation">)</span>
        <span class="token punctuation">.</span><span class="token function">cells</span><span class="token punctuation">(</span>i<span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">.</span>value<span class="token operator">=</span><span class="token function">CStr</span><span class="token punctuation">(</span>oRs1<span class="token punctuation">.</span><span class="token function">Fields</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">.</span>Value<span class="token punctuation">)</span>
        <span class="token punctuation">.</span><span class="token function">cells</span><span class="token punctuation">(</span>i<span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">.</span>value<span class="token operator">=</span><span class="token function">CStr</span><span class="token punctuation">(</span>oRs1<span class="token punctuation">.</span><span class="token function">Fields</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">.</span>Value<span class="token punctuation">)</span>
        <span class="token punctuation">.</span><span class="token function">cells</span><span class="token punctuation">(</span>i<span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">)</span><span class="token punctuation">.</span>value<span class="token operator">=</span><span class="token function">CStr</span><span class="token punctuation">(</span>oRs1<span class="token punctuation">.</span><span class="token function">Fields</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">.</span>Value<span class="token punctuation">)</span>
        End With
        oRs1<span class="token punctuation">.</span>MoveNext
    Next
    <span class="token string">'---------------------文件同时保存到D:\日报表\web文件夹中--------------------'</span>        
1
2
3
4
5
6
7
8
9

a.SaveAs “D:\日报表\web\日报表.htm”,44

‘---------------------以日期命名，并保存到指定文件夹 --------------------’
Dim patch,filename
filename=CStr(Year(Now))&““&CStr(Month(Now))&””&CStr(Day(Now))&““&CStr(Hour(Now))&””&CStr(Minute(Now))&“_”&CStr(Second(Now))
patch= “D:\日报表”&filename&“.xlsx”
objExcelApp.ActiveWorkbook.SaveAs patch
objExcelApp.Workbooks.Close
objExcelApp.Quit

     MsgBox <span class="token string">"成功生成数据文件!"</span>         
1

‘---------------------关闭数据库 --------------------’
Set objExcelApp= Nothing
Set objExcelBook= Nothing
Set objExcelSheet= Nothing
Set oRs1 = Nothing
Set oCom = Nothing
conn.Close
Set conn = Nothing

End If

‘---------------------报表显示 --------------------’
Dim wbCtrl
Set wbCtrl = ScreenItems(“Web”) '“Web”为Web控件名称
wbCtrl.Navigate “D:\日报表\web\日报表.htm”

End Sub

8.5、脚本展示

8.6 添加按钮

8.7、添加脚本

8.8 运行结果