quote:以下是引用ice_sea在2009-03-10 20:33:50的发言：
我说说我去年一个项目做的报表吧
这是个改造项目，下位机是5台S5-135U/155U，上位机用wincc，profibus fms通讯
报表我是变量记录后，用全局脚本查询sql数据库，写入excel文件中实现的。程序是我根据实例改的。

主要特点是：excel模板中一行是要查询变量的ID，可以自定义变量的ID，用户可以随意增减报表中需要的变量，至于求平均值，总和等功能就让excel实现吧
变量ID在sql server enterprise manager中，导出wincc项目的archive表格获得
Function action
Dim sPro, sDsn, sSer,sCon, sSql
Dim sVid, sVal, lRet, sRel
Dim V,Sum,Avg,Std
Dim conn, oRs, oCom, oList, oItem
Dim m,n,s,nRec
Dim strDateTime, iMs
Dim fso,myfile,daystr,dstr,fname,vaID,dstr2,dstr3
dstr = FormatDateTime(Date-19)
dstr2=FormatDateTime(Date-20)
dstr3=FormatDateTime(Date-21)
fname="D:\TD_SERVER1\excel\day\"+DSTR2+".xls"
Set fso = CreateObject("s cripting.FileSystemObject")
If (fso.FileExists(fname)) Then
Else
Set MyFile = fso.GetFile("D:\TD_SERVER1\excel\day\hDay1.xls")
MyFile.Copy (fname)
End If
Dim ObjExcelApp
Dim i,j,k,l
Dim Spreadsheet1
Set objExcelApp = CreateObject("Excel.Application")
objExCelApp.Visible = True
objExcelApp.Workbooks.Open fname
sDsn = "CC_TD_SERVE_08_09_25_14_38_20R"
sPro = "Provider=WinCCOLEDBProvider.1;"
sDsn = "Catalog=" + sDsn + ";"
sSer = "Data Source=.\WinCC"
sCon = sPro + sDsn + sSer
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = sCon
conn.CursorLocation = 3 ' 3
conn.Open
Set oRs = CreateObject("ADODB.Recordset")
Set oCom = CreateObject("ADODB.Command")
oCom.CommandType = 1
Set oCom.ActiveConnection = conn
l=3
i=6

Do While objExcelApp.worksheets ("Tabel1").Cells(i, 2).VAlUe<>""
i=i+1
Loop
Do While objExcelApp.worksheets ("Tabel1").Cells(4, l).VAlUe<>""
vaID=objExcelApp.worksheets ("Tabel1").Cells(4, l).VAluE
l=l+1
sSql="TAG:R," + CStr(vaID) + ",'" + dstr3 + " 15:50:00.000','" + dstr2 + " 15:10:00.000'"
oCom.CommandText = sSql
Set oRs = oCom.Execute
m = oRs.Fields.Count
j=i
Do While Not oRs.EOF
s=DateAdd("h",+8,oRS.Fields(1).Value)
objExcelApp.worksheets ("Tabel1").Cells(j, 2).VAluE =S
objExcelApp.worksheets ("Tabel1").Cells(j, l-1).VAluE =oRs.Fields(2).Value
j=j+1
oRs.MoveNext
Loop
loop
objExcelApp.ActiveWorkbook.Save
objExcelApp.Workbooks.Close
objExcelApp.QuiT
oRs.Close
Set oRs = Nothing
conn.Close
Set conn = Nothing
End Function

---