最新wincc生成excel.docx
《最新wincc生成excel.docx》由会员分享,可在线阅读,更多相关《最新wincc生成excel.docx(9页珍藏版)》请在冰豆网上搜索。
![最新wincc生成excel.docx](https://file1.bdocx.com/fileroot1/2023-6/21/7b2e8096-92bf-480e-8ed9-2fc2726fdc5e/7b2e8096-92bf-480e-8ed9-2fc2726fdc5e1.gif)
最新wincc生成excel
给wincc中通过vbs写excel的方式做报表的初学者一点脚本
我对该帖子不做任何限制,都可以自由拷贝使用。
方便初学者学习!
//log子程序,40个数据记录,总共记录一周时间,写入excel,放到wincc全局脚本中一分钟运行一次
//如果是要整点报表也简单,判断一下当前的系统时间,t的初始值根据当前时间定义。
//本程序主要是演示数据记录的过程。
//在c:
\建立一个excel文件,该文件可以是你的报表模版
OptionExplicit
Functionaction
DimobjExcelApp,objExcelbook,objExcelSheet
Dimtagshijian,sheetname,username,zhushi
Dimtagday
Dima1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14,a15,a16,a17,a18,a19,a20,a21,a22,a23,a24,a25,a26,a27,a28,a29,a30,a31,a32,a33,a34,a35,a36,a37,a38,a39,a40
Dimi,j,t,count
Dimmsg
Dimpatch,fn
Dimd,m,y,oh,mm,ss
d=""
m="-"
y="-"
oh=":
"
mm=":
"
ss=""
msg="记录报表成功"
fn=CStr(Year(Now))&CStr(Month(Now))&CStr(Day(Now))
patch="c:
\"&fn&".xls"
Seta1=HMIRuntime.Tags("a1")
Seta2=HMIRuntime.Tags("a2")
Seta3=HMIRuntime.Tags("a3")
Seta4=HMIRuntime.Tags("a4")
Seta5=HMIRuntime.Tags("a5")
Seta6=HMIRuntime.Tags("a6")
Seta7=HMIRuntime.Tags("a7")
Seta8=HMIRuntime.Tags("a8")
Seta9=HMIRuntime.Tags("a9")
Seta10=HMIRuntime.Tags("a10")
Seta11=HMIRuntime.Tags("a11")
Seta12=HMIRuntime.Tags("a12")
Seta13=HMIRuntime.Tags("a13")
Seta14=HMIRuntime.Tags("a14")
Seta15=HMIRuntime.Tags("a15")
Seta16=HMIRuntime.Tags("a16")
Seta17=HMIRuntime.Tags("a17")
Seta18=HMIRuntime.Tags("a18")
Seta19=HMIRuntime.Tags("a19")
Seta20=HMIRuntime.Tags("a20")
Seta21=HMIRuntime.Tags("a21")
Seta22=HMIRuntime.Tags("a22")
Seta23=HMIRuntime.Tags("a23")
Seta24=HMIRuntime.Tags("a24")
Seta25=HMIRuntime.Tags("a25")
Seta26=HMIRuntime.Tags("a26")
Seta27=HMIRuntime.Tags("a27")
Seta28=HMIRuntime.Tags("a28")
Seta29=HMIRuntime.Tags("a29")
Seta30=HMIRuntime.Tags("a30")
Seta31=HMIRuntime.Tags("a31")
Seta32=HMIRuntime.Tags("a32")
Seta33=HMIRuntime.Tags("a33")
Seta34=HMIRuntime.Tags("a34")
Seta35=HMIRuntime.Tags("a35")
Seta36=HMIRuntime.Tags("a36")
Seta37=HMIRuntime.Tags("a37")
Seta38=HMIRuntime.Tags("a38")
Seta39=HMIRuntime.Tags("a39")
Seta40=HMIRuntime.Tags("a40")
Setcount=HMIRuntime.Tags("count")
sheetname="Sheet1"
tagshijian=Now
SetobjExcelApp=CreateObject("Excel.Application")
objExcelApp.visible=False
objExcelApp.workbooks.open"C:
\Table.xls"
objExcelApp.worksheets(sheetname).activate
count.Read
t=count.Value+1
Ift<4Then
count.Write4
t=4
Else
count.Writet
EndIf
Ift>10080Then
count.Write3
t=4
EndIf
Fort=tTot
WithobjExcelApp.worksheets(sheetname)
.cells(t,1).value=tagshijian
a1.Read
.cells(t,2).value=a1.Value
a2.Read
.cells(t,3).value=a2.Value
a3.Read
.cells(t,4).value=a3.Value
a4.Read
.cells(t,5).value=a4.Value
a5.Read
.cells(t,6).value=a5.Value
a6.Read
.cells(t,7).value=a6.Value
a7.Read
.cells(t,8).value=a7.Value
a8.Read
.cells(t,9).value=a8.Value
a9.Read
.cells(t,10).value=a9.Value
a10.Read
.cells(t,11).value=a10.Value
a11.Read
.cells(t,12).value=a11.Value
a12.Read
.cells(t,13).value=a12.Value
a13.Read
.cells(t,14).value=a13.Value
a14.Read
.cells(t,15).value=a14.Value
a15.Read
.cells(t,16).value=a15.Value
a16.Read
.cells(t,17).value=a16.Value
a17.Read
.cells(t,18).value=a17.Value
a18.Read
.cells(t,19).value=a18.Value
a19.Read
.cells(t,20).value=a19.Value
a20.Read
.cells(t,21).value=a20.Value
a21.Read
.cells(t,22).value=a21.Value
a22.Read
.cells(t,23).value=a22.Value
a23.Read
.cells(t,24).value=a23.Value
a24.Read
.cells(t,25).value=a24.Value
a25.Read
.cells(t,26).value=a25.Value
a26.Read
.cells(t,27).value=a26.Value
a27.Read
.cells(t,28).value=a27.Value
a28.Read
.cells(t,29).value=a28.Value
a29.Read
.cells(t,30).value=a29.Value
a30.Read
.cells(t,31).value=a30.Value
a31.Read
.cells(t,32).value=a31.Value
a32.Read
.cells(t,33).value=a32.Value
a33.Read
.cells(t,34).value=a33.Value
a34.Read
.cells(t,35).value=a34.Value
a35.Read
.cells(t,36).value=a35.Value
a36.Read
.cells(t,37).value=a36.Value
a37.Read
.cells(t,38).value=a37.Value
a38.Read
.cells(t,39).value=a38.Value
a39.Read
.cells(t,40).value=a39.Value
a40.Read
.cells(t,41).value=a40.Value
EndWith
Next
objExcelApp.ActiveWorkbook.Save
objExcelApp.ActiveWorkbook.close
objExcelApp.ActiveWorkbook.Quit
Set objExcelApp=Nothing
EndFunction
//SAVE子程序
//保存文件
//读出系统时间,把文件保存为以日期为文件名的文件
//每周日的23:
59:
10执行一次
OptionExplicit
Functionaction
DimobjExcelApp,objExcelbook,objExcelSheet
Dimsheetname,username
Dimtagday
Dimi,j,t
Dimmsg
Dimpatch,fn
fn=CStr(Year(Now))&CStr(Month(Now))&CStr(Day(Now))+CStr(Hour(Now))&CStr(Minute(Now))
patch="c:
\"&fn&".xls"
sheetname="Sheet1"
SetobjExcelApp=CreateObject("Excel.Application")
objExcelApp.visible=False
objExcelApp.workbooks.open"C:
\Table.xls"
objExcelApp.worksheets(sheetname).activate
objExcelApp.ActiveWorkbook.SaveAspatch
objExcelApp.ActiveWorkbook.Save
objExcelApp.ActiveWorkbook.close
objExcelApp.ActiveWorkbook.Quit
Set objExcelApp=Nothing
EndFunction
//delete子程序
//删除模版,拷贝空白模版到当前目录
//有人会问,为什么不用vb清空当前已经记录数据的模版文件
//问的好,如果这个模版里面已经存了几万条数据了呢?
//所以最快的方法是用vc脚本,将当前模版删除,拷贝一个空的模版到c:
//每周日的23:
59:
30执行一次
#include"apdefap.h"
intgscAction(void)
{
#pragmaoption(mbcs)
//WINCC:
TAGNAME_SECTION_START
//syntax:
#defineTagNameInAction"DMTagName"
//nextTagID:
1
//WINCC:
TAGNAME_SECTION_END
#pragmacode("kernel32.dll")
longCopyFileA(LPCTSTR,LPCTSTR,long);
longDeleteFileA(LPCTSTR);
#pragmacode()
DeleteFileA("C:
\\Table.xls");
CopyFileA("C:
\\bak\\Table.xls","C:
\\Table.xls",TRUE);
//WINCC:
PICNAME_SECTION_START
//syntax:
#definePicNameInAction"PictureName"
//nextPicID:
1
//WINCC:
PICNAME_SECTION_END
return0;
}