日报表解释说明

1全局脚本在全局脚本里建立起与数据库的连接，通过定时整点触发脚本，把变量写到数据库。

WINCC里全局脚本触发定时器设定是每小时整点触发。

Option Explicit 变量声明Function action 功能操作Dim database 定义数据库Dim strcn,cnDim is_SQLDim riqiDim yali,wendu,liuliang,zhongliang,dianya,sudu 定义压力、温度、、、、Dim DateTime 定义日期时间riqi=NowSet yali= HMIRuntime.Tags("yali") 人机界面运行时间标签yali.ReadSet wendu= HMIRuntime.Tags("wendu")wendu.ReadSet liuliang= HMIRuntime.Tags("liuliang")liuliang.ReadSet zhongliang= HMIRuntime.Tags("zhongliang")zhongliang.ReadSet dianya= HMIRuntime.Tags("dianya")dianya.ReadSet sudu= HMIRuntime.Tags("sudu")sudu.Readstrcn="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=baobiao1;Data Source=.\wincc"Set cn=CreateObject("ADODB.connection")cn.connectionString=strcncn.Openis_SQL="insert into ribao(riqi,yali,wendu,liuliang,zhongliang,dianya,sudu) Values('"& riqi &"','"&yali.Value &"','"&wendu.Value &"','"&liuliang.Value &"','"&zhongliang.Value &"','"&dianya.Value &"','"&sudu.Value &"')"cn.Execute is_SQLcn.CloseEnd Functi2查询按钮Sub OnClick(ByVal Item)Dim i, n, k, n1, a1, b1, c1, d1, e1, f1Dim MSFlexGrid1Dim Sql, oCom, conn, sql1, oCom1Dim j, b, zDim ylp, wdp, llp, ylx, wdx, llx, yld, wdd, lldDim zlp, dyp, sdp, zlx, dyx, sdx, zld, dyd, sdd【压力、温度、流量、重力、电压、速度的平均值、最小值、最大值】Dim strcn, cnDim a, shi, t, xDim oRs, oRs1Dim Text2Dim 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")【ScreenItems为画面对象集合，把定义的函数与画面中的控件进行映射】By = Year(Date1.Value)Bm = Month(Date1.Value)Bd = Day(Date1.Value) 【BY BM BD 为date1的年月日定义,B为begin开始时间】Ny = Year(Date2.Value)Nm = Month(Date2.Value)Nd = Day(Date2.Value) 【NY NM ND 为date2的年月日定义，N的end结束时间】BeginDate = By & "-" & Bm & "-" & Bd & " " & "00:00:00"EndDate = Ny & "-" & Nm & "-" & Nd & " " & "23:59:59"e = By & "-" & Bm & "-" & Bdf = Ny & "-" & Nm & "-" & NdIf By > Ny Or By = Ny And Bm > Nm Or By = Ny And Bm = Nm And Bd > Nd Then MsgBox "输入的时间不正确", vbOK, "错误的起始时间" 【MsgBox为弹出的对话框控件】End If'以上程序为日期查询2.1 '建立连接Sql = "SELECT CONVERT(char(19), riqi, 20) AS riqi, yali, wendu, liuliang, zhongliang, dianya, sudu FROM ribao WHERE riqi BETWEEN '" & BeginDate & "' and'" & EndDate & "'ORDER BY riqi"【CONVERT 在SQL（结构化语言）中的一个系统函数一般用法convert 函数用来转换数据类型例子：SELECT CONVERT (VARCHAR(5),12345)返回：字符串 '12345'/sql/sql_orderby.asp】sql1 = "select avg(yali)as ylp,avg(wendu)as wdp,avg(liuliang)as llp,avg(zhongliang)as zlp,avg(dianya)as dyp,avg(sudu)as sdp,min(yali)as ylx,min(wendu)as wdx,min(liuliang)As llx,min(zhongliang)As zlx,min(dianya)As dyx,min(sudu)As sdx,max(yali)As yld,max(wendu)As wdd,max(liuliang)As lld,max(zhongliang)As zld,max(dianya)As dyd,max(sudu)As sdd from ribao where riqi between '" & BeginDate & "' and'" & EndDate & "'"【/sql/sql_func_avg.asp】strcn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist SecurityInfo=False;Initial Catalog=baobiao1;Data Source=.\wincc"Set conn = CreateObject("ADODB.Connection")conn.ConnectionString = strcnconn.CursorLocation = 3conn.Open'使用命令文本查询Set oRs = CreateObject("ADODB.Recordset")Set oCom = CreateObject("mand")mandType = 1Set oCom.ActiveConnection = connmandText = SqlSet oRs = oCom.Executen = oRs.RecordCount 【RecordCount指示Recordset对象中记录的当前数目】Text2.Text = n 【Text2 为日报表中静态文本的查询到的记录数的文本内容为n】 Set oCom1 = CreateObject("mand")mandType = 1Set oCom1.ActiveConnection = connmandText = sql1Set oRs1 = oCom1.Executen1 = oRs1.RecordCountylp = oRs1("ylp"): wdp = oRs1("wdp"): llp = oRs1("llp"): ylx = oRs1("ylx"): wdx = oRs1("wdx"): llx = oRs1("llx"): yld = oRs1("yld"): wdd = oRs1("wdd"): lld = oRs1("lld")zlp = oRs1("zlp"): dyp = oRs1("dyp"): sdp = oRs1("sdp"): zlx = oRs1("zlx"): dyx = oRs1("dyx"): sdx = oRs1("sdx"): zld = oRs1("zld"): dyd = oRs1("dyd"): sdd = oRs1("sdd")If n = 0 Then 【查询的结果数为0时显示内容】MsgBox "对不起,没有找到符合条件的数据", vbOK, "没有相关数据"End IfoRs.RequeryMSFlexGrid1.Clear【使用FlexGrid ActiveX控件可以在Microsoft Visual Basic的窗体中创建一个电子数据表格，也可称之为网格，/link?url=gdGZm1tyQhFpO4MlKlEiFPvRS990aO0n5y9PFHPR1a edWB4XLJL05K68bL84l6aa】MSFlexGrid1.Rows = oRs.RecordCount + 6MSFlexGrid1.ColWidth(0) = 800MSFlexGrid1.ColWidth(1) = 2100MSFlexGrid1.ColWidth(2) = 1000MSFlexGrid1.ColWidth(3) = 1000MSFlexGrid1.ColWidth(4) = 1000MSFlexGrid1.ColWidth(5) = 1000MSFlexGrid1.ColWidth(6) = 1000MSFlexGrid1.ColWidth(7) = 1000'合并单元格，并把单元格的内容写为R980履带式布料机日报表MSFlexGrid1.Row = 0For z = 0 To 7MSFlexGrid1.Col = zMSFlexGrid1.Text = "R980履带式布料机日报表"NextMSFlexGrid1.MergeCells = 4 【Merge合并】MSFlexGrid1.MergeRow(0) = True'合并单元格MSFlexGrid1.TextMatrix(1, 0) = "编号" 【Matrix矩阵，（行，列）】MSFlexGrid1.TextMatrix(1, 1) = "日期"MSFlexGrid1.TextMatrix(1, 2) = "压力"MSFlexGrid1.TextMatrix(1, 3) = "温度"MSFlexGrid1.TextMatrix(1, 4) = "流量"MSFlexGrid1.TextMatrix(1, 5) = "重量"MSFlexGrid1.TextMatrix(1, 6) = "电压"MSFlexGrid1.TextMatrix(1, 7) = "速度"'MSFlexGrid1.TextMatrix(oRs.RecordCount+2, 0) = "总和"MSFlexGrid1.TextMatrix(oRs.RecordCount + 3, 0) = "最大值"MSFlexGrid1.TextMatrix(oRs.RecordCount + 4, 0) = "最小值"MSFlexGrid1.TextMatrix(oRs.RecordCount + 5, 0) = "平均值" MSFlexGrid1.ColAlignment(0) = 4MSFlexGrid1.ColAlignment(1) = 4MSFlexGrid1.ColAlignment(2) = 4MSFlexGrid1.ColAlignment(3) = 4MSFlexGrid1.ColAlignment(4) = 4MSFlexGrid1.ColAlignment(5) = 4MSFlexGrid1.ColAlignment(6) = 4MSFlexGrid1.ColAlignment(7) = 4 【对其方式】For i = 1 To oRs.RecordCountMSFlexGrid1.TextMatrix(i + 1, 0) = iNextIf (n > 0) ThenoRs.MoveFirsti = 0End If 【显示编号下方的信息为从第2行、第0列为1，第3行第0列为2，i的取值为从1到查询到的数目oRs.RecordCount】Do While Not oRs.EOFn = n + 1ylp = Int(ylp * 10 ^ 3 + 0.5) / (10 ^ 3)wdp = Int(wdp * 10 ^ 3 + 0.5) / (10 ^ 3)llp = Int(llp * 10 ^ 3 + 0.5) / (10 ^ 3)zlp = Int(zlp * 10 ^ 3 + 0.5) / (10 ^ 3)dyp = Int(dyp * 10 ^ 3 + 0.5) / (10 ^ 3)sdp = Int(sdp * 10 ^ 3 + 0.5) / (10 ^ 3)i = i + 1'MSFlexGrid1.AddItem i,i+1t = CStr(oRs.Fields(0).Value)If e = f ThenMSFlexGrid1.TextMatrix(i + 1, 1) = Mid(t, 11, 16) End IfIf e <> f ThenMSFlexGrid1.TextMatrix(i + 1, 1) = tEnd Ifa1 = 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)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, 2) = a1MSFlexGrid1.TextMatrix(i + 1, 3) = b1MSFlexGrid1.TextMatrix(i + 1, 4) = c1MSFlexGrid1.TextMatrix(i + 1, 5) = d1MSFlexGrid1.TextMatrix(i + 1, 6) = e1MSFlexGrid1.TextMatrix(i + 1, 7) = f1MSFlexGrid1.TextMatrix(oRs.RecordCount + 3, 2) = yld MSFlexGrid1.TextMatrix(oRs.RecordCount + 4, 2) = ylx MSFlexGrid1.TextMatrix(oRs.RecordCount + 5, 2) = ylp MSFlexGrid1.TextMatrix(oRs.RecordCount + 3, 3) = wdd MSFlexGrid1.TextMatrix(oRs.RecordCount + 4, 3) = wdx MSFlexGrid1.TextMatrix(oRs.RecordCount + 5, 3) = wdp MSFlexGrid1.TextMatrix(oRs.RecordCount + 3, 4) = lld MSFlexGrid1.TextMatrix(oRs.RecordCount + 4, 4) = llx MSFlexGrid1.TextMatrix(oRs.RecordCount + 5, 4) = llp'************************************************* MSFlexGrid1.TextMatrix(oRs.RecordCount + 3, 5) = zld MSFlexGrid1.TextMatrix(oRs.RecordCount + 4, 5) = zlx MSFlexGrid1.TextMatrix(oRs.RecordCount + 5, 5) = zlp MSFlexGrid1.TextMatrix(oRs.RecordCount + 3, 6) = dyd MSFlexGrid1.TextMatrix(oRs.RecordCount + 4, 6) = dyx MSFlexGrid1.TextMatrix(oRs.RecordCount + 5, 6) = dyp MSFlexGrid1.TextMatrix(oRs.RecordCount + 3, 7) = sdd MSFlexGrid1.TextMatrix(oRs.RecordCount + 4, 7) = sdx MSFlexGrid1.TextMatrix(oRs.RecordCount + 5, 7) = sdp '*****************************************************oRs.MoveNextLoopEnd Sub3打印按钮Sub OnClick(ByVal Item)Dim ExcelAppDim ExcelBookDim ExcelSheetDim MSFlexGrid1Dim i,irow,ICOLDim z,kSet MSFlexGrid1 = ScreenItems("MSFlexGrid1")Set ExcelApp = CreateObject("Excel.Application")Set ExcelBook = ExcelApp.Workbooks.AddSet ExcelSheet = ExcelBook.Worksheets(1)ExcelApp.Visible = TrueExcelSheet.Range("A1:H1").MergeFor irow = 0 To MSFlexGrid1.Rows - 1For ICOL = 0 To MSFlexGrid1.Cols - 1z=MSFlexGrid1.RowsExcelSheet.Cells(irow + 1,icol+1)=Trim(MSFlexGrid1.TextMatrix(irow, icol)) NextNextExcelSheet.Range( "A1:H"&z&"").Borders(1).Weight =2ExcelSheet.Range( "A1:H"&z&"").Borders(2).Weight =2ExcelSheet.Range( "A1:H"&z&"").Borders(3).Weight =2ExcelSheet.Range( "A1:H"&z&"").Borders(4).Weight =2ExcelSheet.Rows(1).RowHeight = 0.75/0.035ExcelSheet.Cells.EntireColumn.AutoFitExcelSheet.Rows(1) = "宋体"ExcelSheet.Rows(1).Font.Bold = TrueExcelSheet.Rows(1).Font.Size = 16ExcelSheet.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.035ExcelSheet.PageSetup.CenterHorizontally = 2/0.035ExcelSheet.printpreview'打印阅览'ExcelSheet.PrintOut'打印时用此句ExcelBook.CloseExcelApp.QuitSet ExcelApp = NothingEnd Sub4画面函数打开画面Sub OnOpen()Dim Text1,Text2Set Text1 = ScreenItems("Text1")Set Text2 = ScreenItems("Text2")Text1.Text=NowText2.Text=0End Sub。