|
A01: 设计思路
数据写入数据库：通过kepware的DataLogger写入MSSQL或者上位机软件通过脚本写入MSSQL。
A02: 目前通过kepware整点插入数据库。
首先数据库建立表单，通过
实现数据库设计。
其次建立ODBC数据源，
A03: 安装表格控件
A04:程序设计
核心程序段：
Dim strsql
Dim cn As ADODB.Connection
Dim record As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open g_conString1
Set record = New ADODB.Recordsetst = Str(d_start_date.value)
If Len(st) < 14 Then '
st = st + " 00:00:00"
End If
st1 = Str(d_end_date.value)
If Len(st1) < 14 Then '
st1 = st1 + " 000:00:00"
End If
strsql = "select tt,t1,t2,t3,t4,t5 from tt1 where TT>='" + st + "' and TT<='" + st1 + "'order by xh"
record.Open strsql, cn
j = 2 '目录列
Grid1.Rows = 1
If record.EOF = True Then
record.Close
Set record = Nothing
cn.Close
Set cn = Nothing
Exit Sub
End If
Dim maxv(6) As Single
Dim minv(6) As Single
Dim avg(6) As Single
Dim sub1(6) As Single
For i = 0 To 5
maxv(i) = -10000
minv(i) = 1000000
avg(i) = 0
Dim cxv As Single
Next i
Dim num As Integer
num = 0
Do While record.EOF = False
Grid1.Rows = j + 4
Grid1.Cell(j, 0).Text = Str(j)
st3 = Str(CDate(record.Fields(0)))
'处理时间格式显示（开始）
If Len(st3) < 14 Then
st3 = st3 + " 00:00:00"
End If
If Len(st3) = 18 Then
st3 = Left(st3, 16) + "00"
End If
If Len(st3) = 19 Then
st3 = Left(st3, 16) + ":00"
End If
'处理时间格式显示（结束）
Grid1.Cell(j, 1).Text = st3
For i = 1 To 5
If record.Fields(i) > -10000 Then
cxv = record.Fields(i)
Else
cxv = 0
End If
If i = 1 Then
'增加一行可以否则EXCEL导出没有名称）
Grid1.Cell(1, 1).Text = "日期时间"
Grid1.Cell(1, 2).Text = "进水浊度"
Grid1.Cell(1, 3).Text = "进水PH"
Grid1.Cell(1, 4).Text = "进水温度"
Grid1.Cell(1, 5).Text = "清水池1#液位"
Grid1.Cell(1, 6).Text = "清水池2#液位"
Grid1.Cell(j, i + 1).Text = Format(cxv, "0.000")
End If
If i >= 2 And i <= 5 Then
Grid1.Cell(j, i + 1).Text = Format(cxv, "0.000")
End If
If maxv(i - 1) < cxv Then
maxv(i - 1) = cxv
End If
If minv(i - 1) > cxv Then
minv(i - 1) = cxv
End If
avg(i - 1) = avg(i - 1) + cxv
Next i
num = num + 1
j = j + 1
record.MoveNext
Loop
Grid1.Cell(j, 1).Text = "平均值"
Grid1.Cell(j + 1, 1).Text = "最大值"
Grid1.Cell(j + 2, 1).Text = "最小值"
For i = 0 To 4
if i = 0 Then
Grid1.Cell(j, i + 2).Text = Format(avg(i) / num, "0.000")
Grid1.Cell(j + 1, i + 2).Text = Format(maxv(i), "0.000")
Grid1.Cell(j + 2, i + 2).Text = Format(minv(i), "0.000")
End If
If i = 1 Then
Grid1.Cell(j, i + 2).Text = Format(avg(i) / num, "0.000")
Grid1.Cell(j + 1, i + 2).Text = Format(maxv(i), "0.000")
Grid1.Cell(j + 2, i + 2).Text = Format(minv(i), "0.000")
End If
If i = 2 Then
Grid1.Cell(j, i + 2).Text = Format(avg(i) / num, "0.000")
Grid1.Cell(j + 1, i + 2).Text = Format(maxv(i), "0.000")
Grid1.Cell(j + 2, i + 2).Text = Format(minv(i), "0.000")
End If
If i = 3 Then
Grid1.Cell(j, i + 2).Text = Format(avg(i) / num, "0.000")
Grid1.Cell(j + 1, i + 2).Text = Format(maxv(i), "0.000")
Grid1.Cell(j + 2, i + 2).Text = Format(minv(i), "0.000")
End If
If i = 4 Then
Grid1.Cell(j, i + 2).Text = Format(avg(i) / num, "0.000")
Grid1.Cell(j + 1, i + 2).Text = Format(maxv(i), "0.000")
Grid1.Cell(j + 2, i + 2).Text = Format(minv(i), "0.000")
End If
Next i
record.Close
Set record = Nothing
cn.Close
Set cn = Nothing
Grid1.ScrollBars = cellScrollBarBoth
Grid1.FrozenRows = 1 '冻结第一行
Grid1.Visible = True
A05:测试效果
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！ |
