listview虚拟列表加载上万数据并进行统计
这几天在搞数据统计,就一个收入列表,一个月下来有上万条数据,要列表出来,晕菜菜,原来用FOR NEXT
KAO,他一千多条就给我加了几分钟,真是神仙啊~~~~~~
干脆不要了,换成虚拟列表的形式算了,与其说统计,应该说成是向DATATABLE新增加一行自行统计的信息并显示出来,呵呵~
以下附代码:
- Private Sub listView1_RetrieveVirtualItem(ByVal sender As Object, ByVal e As RetrieveVirtualItemEventArgs) Handles ListView1.RetrieveVirtualItem
- Dim myvalues(9) As String
- With EchoDS.Tables("收入报表").Rows(e.ItemIndex)
- myvalues(0) = .Item("收费时间").ToString
- myvalues(1) = .Item("用户编号").ToString
- myvalues(2) = .Item("用户名称").ToString
- myvalues(3) = .Item("水费").ToString
- myvalues(4) = .Item("附加费").ToString
- myvalues(5) = .Item("滞纳金").ToString
- myvalues(6) = .Item("总金额").ToString
- myvalues(7) = .Item("收费人员").ToString
- myvalues(8) = .Item("收费方式").ToString
- myvalues(9) = .Item("发票号码").ToString
- End With
- e.Item = New ListViewItem(myvalues)
- If e.ItemIndex Mod 2 = 0 Then
- e.Item.BackColor = Color.AliceBlue
- ElseIf EchoDS.Tables("收入报表").Rows.Count - e.ItemIndex = 1 Then
- e.Item.BackColor = Color.AntiqueWhite
- End If
- End Sub
- Private Sub SimpleButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SimpleButton1.Click
- If ListView1.Items.Count > 0 Then
- ListView1.Clear()
- End If
- EchoDS.Reset()
- Dim up, down As String
- up = DateTimePicker1.Text
- down = DateTimePicker2.Text
- Dim downday, upday As String
- upday = Replace(Replace(Replace(up, "年", "-"), "月", "-"), "日", "")
- downday = Replace(Replace(Replace(down, "年", "-"), "月", "-"), "日", "")
- If DateDiff("d", upday, downday) < 0 Then
- MsgBox("上期时间不能小于本期时间!", MsgBoxStyle.Exclamation)
- Return
- End If
- Dim wait As New Wait
- wait.Label1.Text = "正在加载数据,请稍候..."
- wait.ProgressBar1.Visible = False
- wait.Show()
- wait.Refresh()
- Dim sql As String
- sql = "SELECT 水费收入表.收费时间, 水费收入表.用户编号, 水费收入表.用户名称, 水费收入表.水费, 水费收入表.附加费, 水费收入表.滞纳金, 水费收入表.总金额, 系统用户.用户名称 AS 收费人员, 水费收入表.收费方式, 水费收入表.发票号码 FROM 水费收入表 INNER JOIN 系统用户 ON 水费收入表.收费员 = 系统用户.id WHERE (水费收入表.收费时间 > CONVERT(DATETIME, '" & upday & " 00:00:00', 102)) AND (水费收入表.收费时间 < CONVERT(DATETIME, '" & downday & " 23:59:59', 102))"
- Select Case ComboBox2.Text.Trim
- Case "全部"
- sql = sql
- Case Else
- sql += " AND 水费收入表.收费方式 = '" & ComboBox2.Text.Trim & "'"
- End Select
- Select Case ComboBox1.Text.Trim
- Case "全部"
- sql = sql
- Case Else
- Dim j As Object = ComboBox1.Text.Trim
- j = Split(j, "、")
- sql += " AND 水费收入表.收费员 = " & j(0) & ""
- End Select
- sql += " ORDER BY 水费收入表.收费时间 DESC"
- sqlread(sql, "收入报表")
- sql = "SELECT sum(水费) as 总水费金额,sum(附加费) as 总附加费,sum(滞纳金) as 总滞纳金,sum(总金额) as 总总金额 FROM 水费收入表 WHERE (收费时间 > CONVERT(DATETIME, '" & upday & " 00:00:00', 102)) AND (收费时间 < CONVERT(DATETIME, '" & downday & " 23:59:59', 102))"
- Select Case ComboBox2.Text.Trim
- Case "全部"
- sql = sql
- Case Else
- sql += " AND 收费方式 = '" & ComboBox2.Text.Trim & "'"
- End Select
- Select Case ComboBox1.Text.Trim
- Case "全部"
- sql = sql
- Case Else
- Dim j As Object = ComboBox1.Text.Trim
- j = Split(j, "、")
- sql += " AND 收费员 = " & j(0) & ""
- End Select
- sqlread(sql, "补")
- Dim dr As DataRow = EchoDS.Tables("收入报表").NewRow()
- dr("用户名称") = "合计:"
- dr("水费") = EchoDS.Tables("补").Rows(0).Item("总水费金额")
- dr("附加费") = EchoDS.Tables("补").Rows(0).Item("总附加费")
- dr("滞纳金") = EchoDS.Tables("补").Rows(0).Item("总滞纳金")
- dr("总金额") = EchoDS.Tables("补").Rows(0).Item("总总金额")
- EchoDS.Tables("收入报表").Rows.Add(dr)
- EchoDS.Tables("补").Reset()
- If EchoDS.Tables("收入报表").Rows.Count <= 0 Then
- wait.Dispose()
- ListView1.VirtualMode = False
- MsgBox("暂无当前时间段的收入报表!", MsgBoxStyle.Information)
- Return
- Else
- ListView1.VirtualMode = True
- ListView1.VirtualListSize = EchoDS.Tables("收入报表").Rows.Count
- ListView1.Columns.Add("交费时间")
- ListView1.Columns.Add("用户编号")
- ListView1.Columns.Add("用户名称")
- ListView1.Columns.Add("水费金额")
- ListView1.Columns.Add("附加费")
- ListView1.Columns.Add("滞纳金")
- ListView1.Columns.Add("总金额")
- ListView1.Columns.Add("收费员")
- ListView1.Columns.Add("交费方式")
- ListView1.Columns.Add("发票号码")
- ListView1.Columns.Item(0).Width = 150
- ListView1.Columns.Item(1).Width = 80
- ListView1.Columns.Item(2).Width = 150
- ListView1.Columns.Item(3).Width = 80
- ListView1.Columns.Item(4).Width = 80
- ListView1.Columns.Item(5).Width = 80
- ListView1.Columns.Item(6).Width = 80
- ListView1.Columns.Item(7).Width = 80
- ListView1.Columns.Item(8).Width = 80
- ListView1.Columns.Item(9).Width = 150
- End If
- wait.Dispose()
- End Sub