wincc生成excel

给wincc中通过vbs写excel的方式做报表的初学者一点脚本

我对该帖子不做任何限制，都可以自由拷贝使用。方便初学者学习！

//log子程序，40个数据记录，总共记录一周时间，写入excel，放到wincc 全局脚本中一分钟运行一次

//如果是要整点报表也简单，判断一下当前的系统时间，t的初始值根据当前时间定义。//本程序主要是演示数据记录的过程。

//在c:\建立一个excel文件，该文件可以是你的报表模版

Option Explicit

Function action

Dim objExcelApp,objExcelbook,objExcelSheet

Dim tagshijian,sheetname,username,zhushi

Dim tagday

Dim a1,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 Dim i,j,t,count

Dim msg

Dim patch,fn

Dim d,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"

Set a1=HMIRuntime.Tags("a1")

Set a2=HMIRuntime.Tags("a2")

Set a3=HMIRuntime.Tags("a3")

Set a4=HMIRuntime.Tags("a4")

Set a5=HMIRuntime.Tags("a5")

Set a6=HMIRuntime.Tags("a6")

Set a7=HMIRuntime.Tags("a7")

Set a8=HMIRuntime.Tags("a8")

Set a9=HMIRuntime.Tags("a9")

Set a10=HMIRuntime.Tags("a10")

Set a11=HMIRuntime.Tags("a11")

Set a12=HMIRuntime.Tags("a12")

Set a13=HMIRuntime.Tags("a13")

Set a14=HMIRuntime.Tags("a14")

Set a15=HMIRuntime.Tags("a15")

Set a16=HMIRuntime.Tags("a16")

Set a17=HMIRuntime.Tags("a17")

Set a18=HMIRuntime.Tags("a18")

Set a19=HMIRuntime.Tags("a19")

Set a20=HMIRuntime.Tags("a20")

Set a21=HMIRuntime.Tags("a21")

Set a22=HMIRuntime.Tags("a22")

Set a23=HMIRuntime.Tags("a23")

Set a24=HMIRuntime.Tags("a24")

Set a25=HMIRuntime.Tags("a25")

Set a26=HMIRuntime.Tags("a26")

Set a27=HMIRuntime.Tags("a27")

Set a28=HMIRuntime.Tags("a28")

Set a29=HMIRuntime.Tags("a29")

Set a30=HMIRuntime.Tags("a30")

Set a31=HMIRuntime.Tags("a31")

Set a32=HMIRuntime.Tags("a32")

Set a33=HMIRuntime.Tags("a33")

Set a34=HMIRuntime.Tags("a34")

Set a35=HMIRuntime.Tags("a35")

Set a36=HMIRuntime.Tags("a36")

Set a37=HMIRuntime.Tags("a37")

Set a38=HMIRuntime.Tags("a38")

Set a39=HMIRuntime.Tags("a39")

Set a40=HMIRuntime.Tags("a40")

Set count=HMIRuntime.Tags("count")

sheetname="Sheet1"

tagshijian=Now

Set objExcelApp=CreateObject("Excel.Application") objExcelApp.visible=False

objExcelApp.workbooks.open "C:\Table.xls"

objExcelApp.worksheets(sheetname).activate

count.Read

t=count.Value +1

If t<4 Then

count.Write 4

t=4

Else

count.Write t

End If

If t>10080 Then

count.Write 3

t=4

End If

For t=t To t

With objExcelApp.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

End With

Next

objExcelApp.ActiveWorkbook.Save

objExcelApp.ActiveWorkbook.close

objExcelApp.ActiveWorkbook.Quit

Set objExcelApp=Nothing

End Function

//SAVE子程序

//保存文件

//读出系统时间，把文件保存为以日期为文件名的文件

//每周日的23:59:10执行一次

Option Explicit

Function action

Dim objExcelApp,objExcelbook,objExcelSheet

Dim sheetname,username

Dim tagday

Dim i,j,t

Dim msg

Dim patch,fn

fn=CStr(Year(Now))&CStr(Month(Now))&CStr(Day(Now))+CStr(Hour(Now))&CStr(Minute (Now))

patch="c:\"&fn&".xls"

sheetname="Sheet1"

Set objExcelApp=CreateObject("Excel.Application")

objExcelApp.visible=False

objExcelApp.workbooks.open "C:\Table.xls"

objExcelApp.worksheets(sheetname).activate

objExcelApp.ActiveWorkbook.SaveAs patch

objExcelApp.ActiveWorkbook.Save

objExcelApp.ActiveWorkbook.close

objExcelApp.ActiveWorkbook.Quit

Set objExcelApp=Nothing

End Function

//delete子程序

//删除模版，拷贝空白模版到当前目录

//有人会问，为什么不用vb清空当前已经记录数据的模版文件

//问的好，如果这个模版里面已经存了几万条数据了呢？

//所以最快的方法是用vc脚本，将当前模版删除，拷贝一个空的模版到c://每周日的23:

59:30执行一次

#include "apdefap.h"

int gscAction( void )

{

#pragma option(mbcs)

// WINCC:TAGNAME_SECTION_START

// syntax: #define TagNameInAction "DMTagName"

// next TagID : 1

// WINCC:TAGNAME_SECTION_END

#pragma code("kernel32.dll")

long CopyFileA(LPCTSTR,LPCTSTR,long);

long DeleteFileA(LPCTSTR);

#pragma code()

DeleteFileA("C:\\Table.xls");

CopyFileA("C:\\bak\\Table.xls","C:\\Table.xls",TRUE);

// WINCC:PICNAME_SECTION_START

// syntax: #define PicNameInAction "PictureName"

// next PicID : 1

// WINCC:PICNAME_SECTION_END

return 0;

}