前段時(shí)間的一個(gè)項(xiàng)目用SE的vba做了一套報(bào)表，運(yùn)用的是時(shí)間觸發(fā)計(jì)數(shù)，每小時(shí)記一次，一個(gè)班記8次數(shù)據(jù)，本次這次項(xiàng)目需要用WINCC做報(bào)表，觸發(fā)條件是事件觸發(fā)，WINCC自帶報(bào)表功能可以滿足，但是WINCC對(duì)數(shù)據(jù)歸檔的數(shù)量有限制，而且記錄的數(shù)據(jù)會(huì)出現(xiàn)一次數(shù)據(jù)分兩行或者三行記錄，數(shù)據(jù)拆分。因此就自學(xué)用VBS做報(bào)表，大概流程是：先用全局腳本把需要記錄的數(shù)據(jù)寫(xiě)入SQL數(shù)據(jù)庫(kù)，然后再用按鈕腳本把數(shù)據(jù)庫(kù)需要的數(shù)據(jù)提取出來(lái)，在MSFlexGrid控件中顯示出來(lái)，再用一個(gè)按鈕腳本把MSFlexGrid控件的數(shù)據(jù)通過(guò)excel進(jìn)行打印，這種方法還有一個(gè)好處就是能對(duì)記錄的數(shù)據(jù)進(jìn)行處理和篩選再進(jìn)行打印。

下面先看一下記錄數(shù)據(jù)的全局腳本：
Option Explicit
Function action
Dim cn
Dim is_SQL
Dim riqi
Dim mw1,mw2,mw3,mw4,mw5,mw6
Dim y,m,d,h,mi,s
y = CStr(Year(Now))
m = CStr(Month(Now))
d = CStr(Day(Now))
h = CStr(Hour(Now))
mi= CStr(Minute(Now))
s = CStr(Second(Now))
riqi = y  & '-' & m & '-' & d  & ' ' & h & ':'  & mi & ':'  & s
'riqi=Now'獲取系統(tǒng)日期時(shí)間；
a001=HMIRuntime.Tags('SL3_11#_One').Read
b001=HMIRuntime.Tags('11b').Read

Set cn=CreateObject('ADODB.connection')
If  a001= 1 And b001=0 Then
Set mw1= HMIRuntime.Tags('001')'通過(guò)Set語(yǔ)句將mw1對(duì)象指向建立的通訊變量。
mw1.Read
Set mw2= HMIRuntime.Tags('002')'通過(guò)Set語(yǔ)句將mw2對(duì)象指向建立的通訊變量。
mw2.Read
Set mw3= HMIRuntime.Tags('003')'通過(guò)Set語(yǔ)句將mw3對(duì)象指向建立的通訊變量。
mw3.Read
Set mw4= HMIRuntime.Tags('004')'通過(guò)Set語(yǔ)句將mw4對(duì)象指向建立的通訊變量。
mw4.Read
Set mw5= HMIRuntime.Tags('005')'通過(guò)Set語(yǔ)句將mw5對(duì)象指向建立的通訊變量。
mw5.Read
Set mw6= HMIRuntime.Tags('006')'通過(guò)Set語(yǔ)句將mw6對(duì)象指向建立的通訊變量。
mw6.Read

cn.connectionString= 'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DATA;Data Source=.\wincc'
'拼寫(xiě)訪問(wèn)數(shù)據(jù)庫(kù)的字符串，包括驅(qū)動(dòng)名稱、數(shù)據(jù)庫(kù)、訪問(wèn)權(quán)限等
cn.Open
'執(zhí)行打開(kāi)數(shù)據(jù)庫(kù)指令
is_SQL='INSERT INTO ribaoVALUES(''&riqi&'',''&mw1.Value &'',''&mw2.Value &'',''&mw3.Value &'',''&mw4.Value &'',''&mw5.Value &'',''&mw6.Value &'')'     
'拼寫(xiě)存儲(chǔ)到數(shù)據(jù)庫(kù)的SQL的語(yǔ)句
cn.Executeis_SQL'執(zhí)行此操作
cn.Close'結(jié)束訪問(wèn)數(shù)據(jù)庫(kù)
End If
HMIRuntime.Tags('11b').Write a001
End Function

以上是記錄數(shù)據(jù)的程序，在記錄數(shù)據(jù)前需要先在SQL里面建一個(gè)DATA數(shù)據(jù)庫(kù)，并在DATA數(shù)據(jù)庫(kù)建一個(gè)riqi表。

接下來(lái)需要往MSFlexGrid控件提取某個(gè)時(shí)間段的數(shù)據(jù)記錄，需要用到DTpicker時(shí)間控件，即以下程序：

Sub OnClick(Byval Item)      

Dim Text1,Date1,Date2,MSFlexGrid1
Dim By, Bm, Bd,Ny, Nm, Nd
Dim Sql,strcn,conn,oRs,oCom, n
Dim BeginDate
Dim EndDate

Dim z,i, t        
Dim a1, b1, c1, d1, e1, f1

Set Text1 = ScreenItems('Text1')
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 '輸入的時(shí)間不正確', vbOK, '錯(cuò)誤的起始時(shí)間'
'End If

'建立連接
'Sql = 'SELECT CONVERT(char(19), riqi, 20) AS riqi, mw1, mw2, mw3, mw4, mw5, mw6 FROM ribao WHERE riqi BETWEEN '' &BeginDate& '' and'' &EndDate& ''ORDER BY riqi'
Sql = 'SELECT * FROM ribao WHERE riqi BETWEEN '' &BeginDate& '' and'' &EndDate& ''ORDER BY riqi'
'Sql = 'SELECT * FROM ribao ORDER BY riqi'
'Sql = 'SELECT * FROM ribao'

strcn = 'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DATA;Data Source=.\wincc'
'創(chuàng)建一個(gè)ADO連接，連接運(yùn)行數(shù)據(jù)庫(kù)DATA
Set conn = CreateObject('ADODB.Connection')
'創(chuàng)建ADO連接對(duì)象，對(duì)數(shù)據(jù)庫(kù)操作
conn.ConnectionString = strcn
conn.CursorLocation = 3
conn.Open' 打開(kāi)數(shù)據(jù)庫(kù)連接

'使用命令文本查詢
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
    Text1.Text = n 
If n = 0 Then
MsgBox '對(duì)不起,沒(méi)有找到符合條件的數(shù)據(jù)', vbOK, '沒(méi)有相關(guān)數(shù)據(jù)'
End If
oRs.Requery

MSFlexGrid1.Clear
MSFlexGrid1.Rows = oRs.RecordCount+2'顯示的行數(shù)為查詢到數(shù)據(jù)數(shù)目+6
MSFlexGrid1.ColWidth(0) = 800'第1列寬度
MSFlexGrid1.ColWidth(1) = 2100'第2列寬度
MSFlexGrid1.ColWidth(2) = 1200'第3列寬度
MSFlexGrid1.ColWidth(3) = 1200'第4列寬度
MSFlexGrid1.ColWidth(4) = 1200'第5列寬度
MSFlexGrid1.ColWidth(5) = 1200'第6列寬度
MSFlexGrid1.ColWidth(6) = 1200'第7列寬度
MSFlexGrid1.ColWidth(7) = 1200'第8列寬度

'合并單元格，顯示標(biāo)題
MSFlexGrid1.Row = 0'選中第一行。注意第一行，或者是第一列都從0開(kāi)始
For z = 0 To 7
MSFlexGrid1.Col = z
MSFlexGrid1.Text = '南大傲拓NA400采集日?qǐng)?bào)表''通過(guò)FOR,NEXT操作，將8列內(nèi)容都顯示為'NA400數(shù)據(jù)采集日?qǐng)?bào)表'
Next
MSFlexGrid1.MergeCells = 4'設(shè)置一個(gè)值4表明如何及何時(shí)將有相同內(nèi)容的記錄進(jìn)行合并
MSFlexGrid1.MergeRow(0) = True'設(shè)置將0行內(nèi)容合并

'顯示屬性
MSFlexGrid1.TextMatrix(1, 0) = 'id'
MSFlexGrid1.TextMatrix(1, 1) = 'riqi'
MSFlexGrid1.TextMatrix(1, 2) = 'MW1'
MSFlexGrid1.TextMatrix(1, 3) = 'MW2'
MSFlexGrid1.TextMatrix(1, 4) = 'MW3'
MSFlexGrid1.TextMatrix(1, 5) = 'MW4'
MSFlexGrid1.TextMatrix(1, 6) = 'MW5'
MSFlexGrid1.TextMatrix(1, 7) = 'MW6'

MSFlexGrid1.ColAlignment(0) = 4'設(shè)置當(dāng)前列的對(duì)齊方式
MSFlexGrid1.ColAlignment(1) = 4'設(shè)置當(dāng)前列的對(duì)齊方式
MSFlexGrid1.ColAlignment(2) = 4'設(shè)置當(dāng)前列的對(duì)齊方式
MSFlexGrid1.ColAlignment(3) = 4'設(shè)置當(dāng)前列的對(duì)齊方式
MSFlexGrid1.ColAlignment(4) = 4'設(shè)置當(dāng)前列的對(duì)齊方式
MSFlexGrid1.ColAlignment(5) = 4'設(shè)置當(dāng)前列的對(duì)齊方式
MSFlexGrid1.ColAlignment(6) = 4'設(shè)置當(dāng)前列的對(duì)齊方式
MSFlexGrid1.ColAlignment(7) = 4'設(shè)置當(dāng)前列的對(duì)齊方式

'oRs.MoveFirst
For i = 1 TooRs.RecordCount
MSFlexGrid1.TextMatrix(i + 1, 0) = i'第一列顯示每條記錄編號(hào)
'Next
'If (n > 0) Then
'        oRs.MoveFirst
'        i = 0     
'End If
'Do While Not oRs.EOF
'i = i + 1
t = CStr(oRs.Fields(0).Value)
a1 =CStr(oRs.Fields(1).Value)'取第2條記錄的值。ADO Field 對(duì)象包含有關(guān)Recordset對(duì)象中某一列的信息。Recordset中的每一列對(duì)應(yīng)一個(gè) Field 對(duì)象
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)
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)
MSFlexGrid1.TextMatrix(i + 1, 1) = t
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
oRs.MoveNext
Next
oRs.close
conn.close'退出程序前，關(guān)閉與數(shù)據(jù)庫(kù)，記錄集的連接
End Sub

接下來(lái)是從控件數(shù)據(jù)打印功能腳本：

ubOnClick(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'打印時(shí)用此句
ExcelBook.Close
ExcelApp.Quit
Set ExcelApp = Nothing

End Sub

一個(gè)完整的報(bào)表就做完了，希望對(duì)大家有所幫助。

作者介紹：
申培源  微信3群成員  申培源，鄭州輕冶科技股份有限公司，電氣工程師