最好的wincc报表实例.zip

Sub OnClick(ByVal Item) Dim i, n, k, m, t, e, f, n1, shi Dim Sql, oCom, conn, oCom1 Dim j, a, b, c, d, z, sql1, a1, b1, c1 Dim ylp, wdp, llp, ylx, wdx, llx, yld, wdd, lld Dim zlp, dyp, sdp, zlx, dyx, sdx, zld, dyd, sdd Dim strcn, cn Dim is_SQL Dim oRs, oRs1 Dim Byear,Eyear,Text2,MSFlexGrid2,ban2 Dim BeginDate, EndDate,BeginTime,EndTime Set Byear = ScreenItems("Byear") Set Eyear = ScreenItems("Eyear") Set Text2 = ScreenItems("Text2") Set ban2 = ScreenItems("ban2") Set MSFlexGrid2 = ScreenItems("MSFlexGrid2") If ban2.Text = "中" Then BeginTime = "08:02:00" EndTime = "16:01:00" End If If ban2.Text = "晚" Then BeginTime = "16:02:00" EndTime = "24:01:00" End If If ban2.Text = "早" Then BeginTime = "00:00:00" EndTime = "08:01:00" End If BeginDate = Byear.Text EndDate = Eyear.Text If Byear.Text > Eyear.Text Then MsgBox "输入的时间不正确", vbOK, "错误的起始时间" End If Sql = "select convert(char(7),[riqi],120) ,avg(yali) ,avg(wendu) ,avg(liuliang),avg(zhongliang),avg(dianya),avg(sudu) from (select convert(char(10),[riqi],120) as riqi ,avg(yali) as yali, avg(wendu) as wendu, avg(liuliang) as liuliang, avg(zhongliang) as zhongliang,avg(dianya) as dianya,avg(sudu) as sudu from ribao where convert(char(4),[riqi],120) between '" & BeginDate & "' and'" & EndDate & "' and CONVERT(varchar(100), [riqi], 108) between '" & BeginTime & "' and'" & EndTime & "' group by convert(char(10),[riqi],120) ) a group by convert(char(7),[riqi],120)" 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 (select top 100 percent convert(char(7),[riqi],120) as riqi ,avg(yali) as yali ,avg(wendu) as wendu ,avg(liuliang) as liuliang, avg(zhongliang) as zhongliang,avg(dianya) as dianya,avg(sudu) as sudu from (select convert(char(10),[riqi],120) as riqi ,avg(yali) as yali, avg(wendu) as wendu, avg(liuliang) as liuliang, avg(zhongliang) as zhongliang,avg(dianya) as dianya,avg(sudu) as sudu from ribao where convert(char(4),[riqi],120) between'" & BeginDate & "' and'" & EndDate & "' and CONVERT(varchar(100), [riqi], 108) between '" & BeginTime & "' and'" & EndTime & "' group by convert(char(10),[riqi],120) ) a group by convert(char(7),[riqi],120))as t" strcn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist SecurityInfo=False;Initial Catalog=baobiao1;Data Source=.\wincc" Set conn = CreateObject("ADODB.Connection") conn.ConnectionString = strcn conn.CursorLocation = 3 conn.Open '使用命令文本查询 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 Text2.Text = n Set oCom1 = CreateObject("ADODB.Command") oCom1.CommandType = 1 Set oCom1.ActiveConnection = conn oCom1.CommandText = sql1 Set oRs1 = oCom1.Execute n1 = oRs1.RecordCount ylp = 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 MsgBox "对不起,没有找到符合条件的数据", vbOK, "没有相关数据" End If oRs.Requery MSFlexGrid2.Clear MSFlexGrid2.Rows = oRs.RecordCount + 6 MSFlexGrid2.ColWidth(0) = 900 MSFlexGrid2.ColWidth(1) = 1600 MSFlexGrid2.ColWidth(2) = 1000 MSFlexGrid2.ColWidth(3) = 1000 MSFlexGrid2.ColWidth(4) = 1000 MSFlexGrid2.ColWidth(5) = 1000 MSFlexGrid2.ColWidth(6) = 1000 MSFlexGrid2.ColWidth(7) = 1000 MSFlexGrid2.Row = 0 For z = 0 To 7 MSFlexGrid2.Col = z MSFlexGrid2.Text = "R980履带式布料机年报表" Next MSFlexGrid2.MergeCells = 4 MSFlexGrid2.MergeRow(0) = True MSFlexGrid2.TextMatrix(1, 0) = "编号" MSFlexGrid2.TextMatrix(1, 1) = "日期" MSFlexGrid2.TextMatrix(1, 2) = "压力" MSFlexGrid2.TextMatrix(1, 3) = "温度" MSFlexGrid2.TextMatrix(1, 4) = "流量" MSFlexGrid2.TextMatrix(1, 5) = "重量" MSFlexGrid2.TextMatrix(1, 6) = "电压" MSFlexGrid2.TextMatrix(1, 7) = "速度" MSFlexGrid2.TextMatrix(oRs.RecordCount + 3, 0) = "最大值" MSFlexGrid2.TextMatrix(oRs.RecordCount + 4, 0) = "最小值" MSFlexGrid2.TextMatrix(oRs.RecordCount + 5, 0) = "平均值" MSFlexGrid2.ColAlignment(0) = 4 MSFlexGrid2.ColAlignment(1) = 4 MSFlexGrid2.ColAlignment(2) = 4 MSFlexGrid2.ColAlignment(3) = 4 MSFlexGrid2.ColAlignment(4) = 4 MSFlexGrid2.ColAlignment(5) = 4 MSFlexGrid2.ColAlignment(6) = 4 MSFlexGrid2.ColAlignment(7) = 4 For i = 1 To oRs.RecordCount MSFlexGrid2.TextMatrix(i + 1, 0) = i Next If (n > 0) Then oRs.MoveFirst i = 0 End If Do While Not oRs.EOF n = n + 1 yld = Int(yld * 10 ^ 3 + 0.5) / (10 ^ 3) wdd = Int(wdd * 10 ^ 3 + 0.5) / (10 ^ 3) lld = Int(lld * 10 ^ 3 + 0.5) / (10 ^ 3) zld = Int(zld * 10 ^ 3 + 0.5) / (10 ^ 3) dyd = Int(dyd * 10 ^ 3 + 0.5) / (10 ^ 3) sdd = Int(sdd * 10 ^ 3 + 0.5) / (10 ^ 3) zlx = Int(zlx * 10 ^ 3 + 0.5) / (10 ^ 3) dyx = Int(dyx * 10 ^ 3 + 0.5) / (10 ^ 3) sdx = Int(sdx * 10 ^ 3 + 0.5) / (10 ^ 3) ylx = Int(ylx * 10 ^ 3 + 0.5) / (10 ^ 3) wdx = Int(wdx * 10 ^ 3 + 0.5) / (10 ^ 3) llx = Int(llx * 10 ^ 3 + 0.5) / (10 ^ 3) ylp = 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 a = CStr(oRs.Fields(1).Value) a = Int(a * 10 ^ 3 + 0.5) / (10 ^ 3) b = CStr(oRs.Fields(2).Value) b = Int(b * 10 ^ 3 + 0.5) / (10 ^ 3) c = CStr(oRs.Fields(3).Value) c = Int(c * 10 ^ 3 + 0.5) / (10 ^ 3) '***************************** a1 = CStr(oRs.Fields(4).Value) a1 = Int(a1 * 10 ^ 3 + 0.5) / (10 ^ 3) b1 = CStr(oRs.Fields(5).Value) b1 = Int(b1 * 10 ^ 3 + 0.5) / (10 ^ 3) c1 = CStr(oRs.Fields(6).Value) c1 = Int(c1 * 10 ^ 3 + 0.5) / (10 ^ 3) '******************************** MSFlexGrid2.TextMatrix(i + 1, 1) = CStr(oRs.Fields(0).Value) MSFlexGrid2.TextMatrix(i + 1, 2) = a MSFlexGrid2.TextMatrix(i + 1, 3) = b MSFlexGrid2.TextMatrix(i + 1, 4) = c MSFlexGrid2.TextMatrix(i + 1, 5) = a1 MSFlexGrid2.TextMatrix(i + 1, 6) = b1 MSFlexGrid2.TextMatrix(i + 1, 7) = c1 MSFlexGrid2.TextMatrix(oRs.RecordCount + 3, 2) = yld MSFlexGrid2.TextMatrix(oRs.RecordCount + 4, 2) = ylx MSFlexGrid2.TextMatrix(oRs.RecordCount + 5, 2) = ylp MSFlexGrid2.TextMatrix(oRs.RecordCount + 3, 3) = wdd MSFlexGrid2.TextMatrix(oRs.RecordCount + 4, 3) = wdx MSFlexGrid2.TextMatrix(oRs.RecordCount + 5, 3) = wdp MSFlexGrid2.TextMatrix(oRs.RecordCount + 3, 4) = lld MSFlexGrid2.TextMatrix(oRs.RecordCount + 4, 4) = llx MSFlexGrid2.TextMatrix(oRs.RecordCount + 5, 4) = llp '********************************************* MSFlexGrid2.TextMatrix(oRs.RecordCount + 3, 5) = zld MSFlexGrid2.TextMatrix(oRs.RecordCount + 4, 5) = zlx MSFlexGrid2.TextMatrix(oRs.RecordCount + 5, 5) = zlp MSFlexGrid2.TextMatrix(oRs.RecordCount + 3, 6) = dyd MSFlexGrid2.TextMatrix(oRs.RecordCount + 4, 6) = dyx MSFlexGrid2.TextMatrix(oRs.RecordCount + 5, 6) = dyp MSFlexGrid2.TextMatrix(oRs.RecordCount + 3, 7) = sdd MSFlexGrid2.TextMatrix(oRs.RecordCount + 4, 7) = sdx MSFlexGrid2.TextMatrix(oRs.RecordCount + 5, 7) = sdp oRs.MoveNext Loop End Sub