|
Dobrý den, osměluji se přidat svůj dotaz k tomuto vláknu s inspirací, neboť farníci začali vařit pivo, aby ho měli dost a mohli lít i z oken. :) Pro potřeby onoho pivovaru vznikla potřeba sčítat hodnoty podle provozu a také v daném období, podobný postup, ale v datagridview, neboť poskytuje možnost sloupce formátovat, taky jde lépe naplnit, bohaté na události atd. a aby byla změna ;), pořád jen to listview. Vložit novou třídu s názvem Brewery do projektu, nahradit kód vzorkem níže. Ve vlasnostech projektu nastavit Brewery jako startovní formulář:
Imports System.Windows.Forms
Public Class Brewery
Inherits Form
Private bs As BindingSource
Private WithEvents dgv As DataGridView 'header and data
Private WithEvents dgf As DataGridView 'footer
Public Sub New()
Me.Text = "Brewery"
Dim tb As TextBox = New TextBox
tb.Name = "tb"
Dim dtpFrom As DateTimePicker = New DateTimePicker
With dtpFrom
.Name = "dtpFrom"
.Format = DateTimePickerFormat.Short
End With
Dim dtpTo As DateTimePicker = New DateTimePicker
With dtpTo
.Name = "dtpTo"
.Format = DateTimePickerFormat.Short
End With
Dim pnl As Panel = New Panel
With pnl
.Name = "pnl"
.Dock = DockStyle.Top
.Controls.AddRange(New Control() {tb, dtpFrom, dtpTo})
.Height = dtpFrom.Height * 2
End With
bs = New BindingSource
bs.DataSource = CreateSampleDataTable()
dgv = New DataGridView
With dgv
.Dock = DockStyle.Fill
.RowHeadersVisible = False
.AllowUserToResizeRows = False
.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
.BorderStyle = BorderStyle.FixedSingle
Me.Controls.Add(dgv)
Me.Controls.Add(pnl)
.ReadOnly = True 'False
.AllowUserToAddRows = Not .ReadOnly
.DataSource = bs
End With
End Sub
Private Sub tb_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
Dim exp As String = Nothing
Dim ColumnName As String, FilterText As String
ColumnName = "Provoz"
FilterText = DirectCast(sender, TextBox).Text
If Trim(FilterText) <> vbNullString Then
exp = String.Format("[{0}] Like '{1}%'", ColumnName, FilterText)
End If
Dim dtpFrom As DateTimePicker = DirectCast(Me.Controls("pnl").Controls("dtpFrom"), DateTimePicker)
Dim dtpTo As DateTimePicker = DirectCast(Me.Controls("pnl").Controls("dtpTo"), DateTimePicker)
Dim exp1 As String = Nothing
If Not (Date.Equals(dtpFrom.Value, dtpFrom.Tag) And Date.Equals(dtpTo.Value, dtpTo.Tag)) Then
exp1 = String.Format("([{0}] >= #{1:M/dd/yyyy}# AND [{0}] <= #{2:M/dd/yyyy}#)", _
"Datum", dtpFrom.Value, dtpTo.Value)
End If
Dim exps As String = IIf(exp = String.Empty, IIf(exp1 = String.Empty, "", exp1), exp & _
IIf(exp1 = String.Empty, "", IIf(exp = String.Empty, "", " AND ") & exp1))
bs.Filter = exps
Dim dv As DataView = DirectCast(bs.List, DataView)
Dim t As DataTable = dv.ToTable
Dim _sum As Decimal
Dim res = t.Compute(String.Format("{0}([{1}])", _
"Sum", t.Columns(2).ColumnName), Nothing)
If TypeOf (res) Is Decimal Then _sum = res
dgf.Columns(1).HeaderText = String.Format("{0} z {1}", dv.Count, _
DirectCast(bs.DataSource, DataTable).Rows.Count())
dgf.Columns(2).HeaderCell.Value = Format(_sum, dgv.Columns(2).DefaultCellStyle.Format)
End Sub
Private Sub dgv_ColumnWidthChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewColumnEventArgs) Handles dgv.ColumnWidthChanged
SetColumnWidths()
End Sub
Private Sub SetColumnWidths()
Dim tb As TextBox = _
DirectCast(Me.Controls("pnl").Controls("tb"), TextBox)
tb.Width = dgv.Columns(0).Width
Dim dtpFrom As DateTimePicker = _
DirectCast(Me.Controls("pnl").Controls("dtpFrom"), DateTimePicker)
With dtpFrom
.Width = dgv.Columns(1).Width
.Left = tb.Right
End With
Dim dtpTo As DateTimePicker = _
DirectCast(Me.Controls("pnl").Controls("dtpTo"), DateTimePicker)
With dtpTo
.Width = dgv.Columns(1).Width
.Left = tb.Right
.Top = dtpFrom.Bottom
End With
If Not dgf Is Nothing Then
For ic As Integer = 0 To dgv.Columns.Count - 1
dgf.Columns(ic).Width = dgv.Columns(ic).Width
Next
Dim ctrl As New Control
For Each ctrl In dgv.Controls
If ctrl.GetType() Is GetType(HScrollBar) Then
If ctrl.Visible = True Then
If dgf.Columns.Count = dgv.Columns.Count Then
dgf.Columns.Add("vsb", "")
End If
dgf.Columns(dgf.Columns.Count - 1).Width = ctrl.Width
Else
If dgf.Columns.Count > dgv.Columns.Count Then
dgf.Columns.RemoveAt(dgf.Columns.Count - 1)
End If
End If
Exit For
End If
Next
dgf.HorizontalScrollingOffset = dgv.HorizontalScrollingOffset
End If
End Sub
Private Sub dgv_DataSourceChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgv.DataSourceChanged
With dgv
.Sort(dgv.Columns(1), System.ComponentModel.ListSortDirection.Ascending)
.AutoResizeColumnHeadersHeight()
.AutoResizeRows(DataGridViewAutoSizeRowsMode.AllCells)
End With
Dim t As DataTable = DirectCast(bs.DataSource, DataTable)
dgf = New DataGridView
With dgf
.Dock = DockStyle.Bottom
.BorderStyle = BorderStyle.FixedSingle
.RowHeadersVisible = dgv.RowHeadersVisible
For Each c As DataGridViewColumn In dgv.Columns
.Columns.Add(c.Name, IIf(c.Index = 0, "Celkem", ""))
.Columns(dgf.Columns.Count - 1).SortMode = DataGridViewColumnSortMode.NotSortable
With t
If Not .Columns(c.DataPropertyName).ExtendedProperties("Format") Is Nothing Then
c.DefaultCellStyle.Format = .Columns(c.DataPropertyName).ExtendedProperties("Format")
End If
If .Columns(c.DataPropertyName).DataType Is GetType(Decimal) Then
c.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
dgf.Columns(c.DataPropertyName).HeaderCell.Style.Alignment = _
DataGridViewContentAlignment.MiddleRight
End If
If .Columns(c.DataPropertyName).DataType Is GetType(Date) Then
c.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
End If
End With
Next
.BackgroundColor = dgf.ColumnHeadersDefaultCellStyle.BackColor
.AllowUserToAddRows = False
.AllowUserToResizeColumns = False
.AllowUserToResizeRows = False
.Height = .ColumnHeadersHeight
.ScrollBars = ScrollBars.None
End With
Me.Controls.Add(dgf)
Dim dteMin As Date = t.Compute(String.Format("{0}([{1}])", _
"Min", t.Columns(1).ColumnName), Nothing)
Dim dteMax As Date = t.Compute(String.Format("{0}([{1}])", _
"Max", t.Columns(1).ColumnName), Nothing)
Dim dtpFrom As DateTimePicker = _
DirectCast(Me.Controls("pnl").Controls("dtpFrom"), DateTimePicker)
With dtpFrom
.Value = dteMin
.Tag = dteMin
.MinDate = dteMin
.MaxDate = dteMax
End With
Dim dtpTo As DateTimePicker = _
DirectCast(Me.Controls("pnl").Controls("dtpTo"), DateTimePicker)
With dtpTo
.Value = dteMax
.Tag = dteMax
.MinDate = dteMin
.MaxDate = dteMax
End With
Dim tb As TextBox = DirectCast(Me.Controls("pnl").Controls("tb"), TextBox)
AddHandler tb.TextChanged, AddressOf tb_TextChanged
AddHandler dtpFrom.ValueChanged, AddressOf dtp_ValueChanged
AddHandler dtpTo.ValueChanged, AddressOf dtp_ValueChanged
Dim s As New AutoCompleteStringCollection()
t = New DataView(t).ToTable(True, "Provoz")
For Each r As DataRow In t.Rows
s.Add(r(0))
Next
tb.AutoCompleteCustomSource = s
tb.AutoCompleteMode = AutoCompleteMode.Suggest
tb.AutoCompleteSource = AutoCompleteSource.CustomSource
SetColumnWidths()
tb_TextChanged(Me.Controls("pnl").Controls("tb"), EventArgs.Empty)
End Sub
Private Sub dgv_Scroll(ByVal sender As Object, ByVal e As System.Windows.Forms.ScrollEventArgs) Handles dgv.Scroll
If e.ScrollOrientation = ScrollOrientation.VerticalScroll Then Exit Sub
SetColumnWidths()
End Sub
Private Sub Brewery_Resize(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Resize
SetColumnWidths()
End Sub
Private Sub dtp_ValueChanged(ByVal sender As Object, ByVal e As System.EventArgs)
Dim dtpFrom As DateTimePicker = DirectCast(Me.Controls("pnl").Controls("dtpFrom"), DateTimePicker)
Dim dtpTo As DateTimePicker = DirectCast(Me.Controls("pnl").Controls("dtpTo"), DateTimePicker)
If dtpFrom.Value <= dtpTo.Value Then
tb_TextChanged(Me.Controls("pnl").Controls("tb"), EventArgs.Empty)
Else
dtpTo.Value = dtpFrom.Value
End If
End Sub
Private Function CreateSampleDataTable() As DataTable
Dim dt As DataTable = New DataTable
With dt
.Columns.Add("Provoz", GetType(String))
.Columns.Add("Datum", GetType(Date))
.Columns("Datum").ExtendedProperties.Add("Format", "d")
.Columns.Add("Kč", GetType(Decimal))
.Columns("Kč").ExtendedProperties.Add("Format", "N2")
End With
Dim departments() As String = New String() {"Sladovna", _
"Hvozd", "Varna", "Spilka", "Ležácký sklep", "Expedice", _
"Stáčírna", "Plnící linka", "Filtrace", "Pasterizace"}
For m As Integer = 1 To 12
For c As Integer = 0 To departments.Length - 1
dt.Rows.Add(departments(c), _
New Date(Now.Year, m, _
IIf(c < departments.Length - 1, c * 2 + 1, _
DateTime.DaysInMonth(Now.Year, m))), (c + 1) * 1000 + c / 10)
Next
Next
dt.AcceptChanges()
Return dt
End Function
End Class
Jakým způsobem lze zobrazit poslední řádek pro součet přímo v jednom jediném datagridview? S ohledem na možnost povolit přidávání řádků a zachování možnosti seřazení podle libovolného sloupce. Nezbylo, než to vzdát po marných pokusech, ale bylo by milé ,pokud někdo již řešil a měl by chuť se podělit o své řešení. Případně předem děkuji.
|