WinCC智能报表（代替热风炉岗位工手抄日志）

MSFlexGrid控件、DTPicker控件、Text控件、按钮控件等。
WinCC项目文件夹下新建“sql”文件夹，“sql”文件夹内新建HBISCT.mdf、HBISCT_log.ldf两个数据库文件【在SQL server中建库建表建字段，数据库名称是HBISCT，表名称是ribao，字段是riqi（datatime类型）；HBISCT001,HBISCT002,HBISCT003,HBISCT004,HBISCT005,HBISCT006,HBISCT007,HBISCT008（都是float类型）将数据库文件和日志文件放到sql文件夹里面】

SQLServer中运行以下程序

USE [master]
GO

IF EXISTS(SELECT * FROM sysdatabases WHERE NAME='HBISCT')
BEGIN
    DROP DATABASE HBISCT   --如果数据库存在先删掉数据库
END
GO

CREATE DATABASE HBISCT
ON
PRIMARY  --创建主数据库文件
(
    NAME='HBISCT',
    FILENAME='E:\sql\HBISCT.mdf',
    SIZE=5MB,
    MaxSize=100MB,
    FileGrowth=10MB
)
LOG ON --创建日志文件
(
    NAME='HBISCT_log',
    FileName='E:\sql\HBISCT_log.ldf',
    Size=2MB,
    MaxSize=20MB,
    FileGrowth=1MB
)
GO
use HBISCT
--添加表
IF NOT EXISTS (SELECT * FROM sys.objects WHERE NAME='ribao')

CREATE TABLE ribao
(
    riqi DateTime NULL,
    HBISCT001 float NULL,
    HBISCT002 float NULL,
    HBISCT003 float NULL,
    HBISCT004 float NULL,
	HBISCT005 float NULL,
	HBISCT006 float NULL,
	HBISCT007 float NULL,
	HBISCT008 float NULL,
)

insert into ribao( riqi,HBISCT001,HBISCT002,HBISCT003,HBISCT004,HBISCT005,HBISCT006,HBISCT007,HBISCT008) Values(GETDATE(),1,2,3,4,5,6,7,8)

全局脚本VB

Option Explicit
Function action
Dim database
Dim strcn,cn
Dim is_SQL

Dim HBISCT001,HBISCT002,HBISCT003,HBISCT004,HBISCT005,HBISCT006,HBISCT007,HBISCT008

Set HBISCT001= HMIRuntime.Tags("HBISCT001")
HBISCT001.Read
Set HBISCT002= HMIRuntime.Tags("HBISCT002")
HBISCT002.Read
Set HBISCT003= HMIRuntime.Tags("HBISCT003")
HBISCT003.Read
Set HBISCT004= HMIRuntime.Tags("HBISCT004")
HBISCT004.Read
Set HBISCT005= HMIRuntime.Tags("HBISCT005")
HBISCT005.Read
Set HBISCT006= HMIRuntime.Tags("HBISCT006")
HBISCT006.Read
Set HBISCT007= HMIRuntime.Tags("HBISCT007")
HBISCT007.Read
Set HBISCT008= HMIRuntime.Tags("HBISCT008")
HBISCT008.Read

'连接字符串'
'strcn= "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=HBISCT;Data Source=.\WINCC"
'strcn= "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;password=123456;Initial Catalog=HBISCT;Data Source=.\WINCC"
'strcn=  "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=HBISCT;Data Source=.\WINCC"
'strcn=  "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=HBISCT;Data Source=.\WINCC"

strcn="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=HBISCT;Data Source=.\wincc"
Set cn=CreateObject("ADODB.connection")
cn.connectionString=strcn
cn.Open
'不同数据库插入系统时间函数
'https://www.cnblogs.com/wlx520/p/4579150.html
is_SQL="insert into ribao(riqi," &_ 
                         "HBISCT001," &_
                         "HBISCT002," &_
                         "HBISCT003," &_
                         "HBISCT004," &_
                         "HBISCT005," &_
                         "HBISCT006," &_
                         "HBISCT007," &_
                         "HBISCT008) Values(GETDATE()," &_
                                             "'"& HBISCT001.Value &"'," &_
                                             "'"& HBISCT002.Value &"'," &_
                                             "'"& HBISCT003.Value &"'," &_
                                             "'"& HBISCT004.Value &"'," &_
                                             "'"& HBISCT005.Value &"'," &_
                                             "'"& HBISCT006.Value &"'," &_
                                             "'"& HBISCT007.Value &"'," &_
                                             "'"& HBISCT008.Value &"')"
cn.Execute is_SQL

cn.Close

End Function

NewPdl0.Pdl画面—>右键—>属性—>事件—>画面对象—>其它—>打开画面—>VB

Sub OnOpen() 
   
Dim i,Byear,Bmon,n,Eyear,Emon,p,Text1,Text2
Set Text1 = ScreenItems("Text1")
Set Text2 = ScreenItems("Text2")
Set Byear = ScreenItems("Byear")
Set Bmon = ScreenItems("Bmon")
Set Eyear = ScreenItems("Eyear")
Set Emon = ScreenItems("Emon")
Text1.Text=Now
Text2.Text=0
Byear.ListIndex = 0
Bmon.ListIndex = 0
Eyear.ListIndex = 0
Emon.ListIndex = 0
For i = 2021 To 2031
Byear.AddItem i
Eyear.AddItem i
Next

For i = 1 To 12
If i<10 Then 
Bmon.AddItem 0&i
Emon.AddItem 0&i
End If 
If i>=10 Then 
Bmon.AddItem i
Emon.AddItem i
End If
Next

End Sub

查询按钮

Sub OnClick(ByVal Item)                                                                     
Dim i, n, k, n1, a1, b1, c1, d1, e1, f1, g1, h1
Dim MSFlexGrid1
Dim Sql, oCom, conn
Dim j, b, z

Dim strcn, cn
Dim t
Dim oRs
Dim  Text2
Dim BeginDate 
Dim EndDate  
Dim By, Bm, Bd
Dim Ny, Nm, Nd, c, e, f
Dim Date1,Date2

Set Text2 = ScreenItems("Text2")
Set Date1 = ScreenItems("Date1")
Set Date2 = ScreenItems("Date2")
Set MSFlexGrid1 = ScreenItems("MSFlexGrid1")

 By = Year(Date1.Value)
 Bm = Month(Date1.Value)
 Bd = Day(Date1.Value)
 Ny = Year(Date2.Value)
 Nm = Month(Date2.Value)
 Nd = Day(Date2.Value)
BeginDate = By & "-" & Bm & "-" & Bd & "  " & "00:00:00"
EndDate = Ny & "-" & Nm & "-" & Nd & " " & "23:59:59"
e = By & "-" & Bm & "-" & Bd
f = Ny & "-" & Nm & "-" & Nd
If By > Ny Or By = Ny And Bm > Nm Or By = Ny And Bm = Nm And Bd > Nd Then
MsgBox "输入的时间不正确", vbOK, "错误的起始时间"
End If
'RiQichaxun
'建立连接     
'https://blog.csdn.net/baiduandxunlei/article/details/9180075                                
 Sql = "SELECT CONVERT(char(19), riqi, 20) as riqi," &_
 "HBISCT001," &_ 
 "HBISCT002," &_
 "HBISCT003," &_ 
 "HBISCT004," &_ 
 "HBISCT005," &_ 
 "HBISCT006," &_
 "HBISCT007," &_ 
 "HBISCT008 " &_
 "FROM ribao WHERE riqi BETWEEN '" & BeginDate & "' and'" & EndDate & "'ORDER BY riqi"


 strcn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist SecurityInfo=False;Initial Catalog=HBISCT;Data Source=.\wincc"
   Set conn = CreateObject("ADODB.Connection")
    conn.ConnectionString = strcn
    conn.CursorLocation = 3
    conn.Open
'使用命令文本查询
    Set oRs = CreateObject("ADODB.Recordset")
    
    Set oCom = CreateObject("ADODB.Command")
    oCom.CommandType = 1
    Set oCom.ActiveConnection = conn
    oCom.CommandText = Sql
   
    Set oRs = oCom.Execute
    n = oRs.RecordCount
    Text2.Text = n
    
   


If n = 0 Then
MsgBox "对不起,没有找到符合条件的数据", vbOK, "没有相关数据"
End If
oRs.Requery


MSFlexGrid1.Clear
MSFlexGrid1.Rows = oRs.RecordCount + 7 '数字7与字段个数变量个数无关，表示做任何操作行数大于7行
MSFlexGrid1.ColWidth(0) = 800
MSFlexGrid1.ColWidth(1) = 2200
MSFlexGrid1.ColWidth(2) = 1000
MSFlexGrid1.ColWidth(3) = 1000
MSFlexGrid1.ColWidth(4) = 1000
MSFlexGrid1.ColWidth(5) = 1000
MSFlexGrid1.ColWidth(6) = 1000
MSFlexGrid1.ColWidth(7) = 1000
MSFlexGrid1.ColWidth(8) = 1000
MSFlexGrid1.ColWidth(9) = 1000

'合并单元格
MSFlexGrid1.Row = 0
For z = 0 To 9
MSFlexGrid1.Col = z
MSFlexGrid1.Text = "SQL Server 2014 WinCC 7.4 日报表"

Next
MSFlexGrid1.MergeCells = 4
MSFlexGrid1.MergeRow(0) = True


MSFlexGrid1.TextMatrix(1, 0) = "编号"
 MSFlexGrid1.TextMatrix(1, 1) = "日期"
  MSFlexGrid1.TextMatrix(1, 2) = "HBISCT001"
    MSFlexGrid1.TextMatrix(1, 3) = "HBISCT002"
     MSFlexGrid1.TextMatrix(1, 4) = "HBISCT003"
      MSFlexGrid1.TextMatrix(1, 5) = "HBISCT004"
       MSFlexGrid1.TextMatrix(1, 6) = "HBISCT005"
        MSFlexGrid1.TextMatrix(1, 7) = "HBISCT006"
          MSFlexGrid1.TextMatrix(1, 8) = "HBISCT007"
        	MSFlexGrid1.TextMatrix(1, 9) = "HBISCT008"       
        
MSFlexGrid1.ColAlignment(0) = 4
MSFlexGrid1.ColAlignment(1) = 4
MSFlexGrid1.ColAlignment(2) = 4
MSFlexGrid1.ColAlignment(3) = 4
MSFlexGrid1.ColAlignment(4) = 4
MSFlexGrid1.ColAlignment(5) = 4
MSFlexGrid1.ColAlignment(6) = 4
MSFlexGrid1.ColAlignment(7) = 4
MSFlexGrid1.ColAlignment(8) = 4
MSFlexGrid1.ColAlignment(9) = 4

For i = 1 To oRs.RecordCount
	MSFlexGrid1.TextMatrix(i + 1, 0) = i
Next

If (n > 0) Then
        oRs.MoveFirst    
        i = 0     
End If

Do While Not oRs.EOF
	n = n + 1

  	i = i + 1
	t = CStr(oRs.Fields(0).Value)
	MSFlexGrid1.TextMatrix(i + 1, 1) = t


	a1 = CStr(oRs.Fields(1).Value)
	b1 = CStr(oRs.Fields(2).Value)
	c1 = CStr(oRs.Fields(3).Value)
	d1 = CStr(oRs.Fields(4).Value)
	e1 = CStr(oRs.Fields(5).Value)
	f1 = CStr(oRs.Fields(6).Value)
	g1 = CStr(oRs.Fields(7).Value)
	h1 = CStr(oRs.Fields(8).Value)

	a1 = Int(a1 * 10 ^ 3 + 0.5) / (10 ^ 3)
	b1 = Int(b1 * 10 ^ 3 + 0.5) / (10 ^ 3)
	c1 = Int(c1 * 10 ^ 3 + 0.5) / (10 ^ 3)
	d1 = Int(d1 * 10 ^ 3 + 0.5) / (10 ^ 3)
	e1 = Int(e1 * 10 ^ 3 + 0.5) / (10 ^ 3)
	f1 = Int(f1 * 10 ^ 3 + 0.5) / (10 ^ 3)
	g1 = Int(g1 * 10 ^ 3 + 0.5) / (10 ^ 3)
	h1 = Int(h1 * 10 ^ 3 + 0.5) / (10 ^ 3)

	MSFlexGrid1.TextMatrix(i + 1, 2) = a1
	MSFlexGrid1.TextMatrix(i + 1, 3) = b1
	MSFlexGrid1.TextMatrix(i + 1, 4) = c1
	MSFlexGrid1.TextMatrix(i + 1, 5) = d1
	MSFlexGrid1.TextMatrix(i + 1, 6) = e1
	MSFlexGrid1.TextMatrix(i + 1, 7) = f1
	MSFlexGrid1.TextMatrix(i + 1, 8) = g1
	MSFlexGrid1.TextMatrix(i + 1, 9) = h1

 	oRs.MoveNext
Loop
  
End Sub

打印按钮

Sub OnClick(ByVal Item)  
Dim ExcelApp 
Dim ExcelBook 
Dim ExcelSheet
Dim MSFlexGrid1 
Dim i,irow,ICOL
Dim z,k
Set MSFlexGrid1 = ScreenItems("MSFlexGrid1")
Set ExcelApp = CreateObject("Excel.Application")
Set ExcelBook = ExcelApp.Workbooks.Add
Set ExcelSheet = ExcelBook.Worksheets(1)
ExcelApp.Visible = True
ExcelSheet.Range("A1:H1").Merge
For irow = 0 To MSFlexGrid1.Rows - 1 
For ICOL = 0 To MSFlexGrid1.Cols - 1 
z=MSFlexGrid1.Rows
ExcelSheet.Cells(irow + 1,icol+1)=Trim(MSFlexGrid1.TextMatrix(irow, icol)) 
Next 
Next 
ExcelSheet.Range( "A1:H"&z&"").Borders(1).Weight =2
ExcelSheet.Range( "A1:H"&z&"").Borders(2).Weight =2
ExcelSheet.Range( "A1:H"&z&"").Borders(3).Weight =2
ExcelSheet.Range( "A1:H"&z&"").Borders(4).Weight =2
ExcelSheet.Rows(1).RowHeight = 0.75/0.035
ExcelSheet.Cells.EntireColumn.AutoFit
ExcelSheet.Rows(1).Font.Name = "宋体"
ExcelSheet.Rows(1).Font.Bold = True
ExcelSheet.Rows(1).Font.Size = 16 
ExcelSheet.Cells.HorizontalAlignment =3
'ExcelSheet.PageSetup.TopMargin = 2/0.035
'ExcelSheet.PageSetup.BottomMargin = 2/0.035
'ExcelSheet.PageSetup.LeftMargin = 2/0.035
'ExcelSheet.PageSetup.RightMargin = 2/0.035
ExcelSheet.PageSetup.CenterHorizontally = 2/0.035
ExcelSheet.printpreview'打印阅览
'ExcelSheet.PrintOut'打印时用此句
ExcelBook.Close 
ExcelApp.Quit
Set ExcelApp = Nothing
End Sub

添加库按钮

Sub OnClick(Byval Item)                                              
Dim t,c,d,strcn,sql,conn
t= HMIRuntime.ActiveProject.Path
c=t&"\sql\HBISCT.mdf"
d=t&"\sql\HBISCT_log.ldf"
sql = "exec sp_attach_db 'HBISCT','"& c &"','"& d &"'"
strcn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist SecurityInfo=False;Initial Catalog=master;Data Source=.\wincc"
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = strcn
conn.CursorLocation = 3
conn.Open
conn.Execute sql
MsgBox "报表数据库文件添加成功", vbOK, "提示"

End Sub

分离库按钮

Sub OnClick(ByVal Item)                          
Dim t,c,d,strcn,sql,conn,m
m=MsgBox("你确定要把数据库文件从服务器上分离吗", vbOK, "提示")
If m=0 Then 
Exit Sub
Else
sql = "exec sp_detach_db 'HBISCT'"
strcn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist SecurityInfo=False;Initial Catalog=master;Data Source=.\wincc"
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = strcn
conn.CursorLocation = 3
conn.Open
conn.Execute sql
MsgBox "报表数据库文件分离成功", vbOK, "提示"
End if
End Sub