/
PivotReportGenerator.vb
314 lines (279 loc) · 17.1 KB
/
PivotReportGenerator.vb
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Drawing
Imports System.Windows.Forms
Imports DevExpress.XtraReports.UI
Imports DevExpress.XtraPivotGrid
Imports DevExpress.XtraEditors
Imports System.Text
Imports DevExpress.Drawing
Imports DevExpress.XtraPrinting
Imports System.Runtime.InteropServices
Public Module PivotReportGenerator
Public Function GenerateReport(ByVal pivot As DevExpress.XtraPivotGrid.PivotGridControl, ByVal kind As ReportGeneratorType, ByVal columnWidth As Integer, ByVal repeatRowHeader As Boolean) As XtraReport
Dim rep As DevExpress.XtraReports.UI.XtraReport = New DevExpress.XtraReports.UI.XtraReport()
rep.Landscape = True
rep.DataSource = PivotReportGenerator.FillDataset(pivot)
rep.DataMember = CType(rep.DataSource, System.Data.DataSet).Tables(CInt((0))).TableName
PivotReportGenerator.InitBands(rep)
PivotReportGenerator.InitStyles(rep)
PivotReportGenerator.InitDetailsBasedonXRTable(rep, kind, columnWidth, repeatRowHeader)
Return rep
End Function
Public Function FillDataset(ByVal pivot As DevExpress.XtraPivotGrid.PivotGridControl) As DataSet
Dim dataSet1 As System.Data.DataSet = New System.Data.DataSet()
dataSet1.DataSetName = "PivotGridColumns"
Dim dataTable1 As System.Data.DataTable = New System.Data.DataTable()
dataSet1.Tables.Add(dataTable1)
PivotReportGenerator.FillDatasetColumns(pivot, dataTable1)
PivotReportGenerator.FillDatasetExtracted(pivot, dataTable1)
Return dataSet1
End Function
#Region "PreparingDataSet"
Private Sub FillDatasetExtracted(ByVal pivot As DevExpress.XtraPivotGrid.PivotGridControl, ByVal dataTable1 As System.Data.DataTable)
Dim rowvalues As System.Collections.Generic.List(Of Object) = New System.Collections.Generic.List(Of Object)()
Dim tempRowText As String = ""
Dim fieldsInRowArea As System.Collections.Generic.List(Of DevExpress.XtraPivotGrid.PivotGridField) = PivotReportGenerator.GetFieldsInArea(pivot, DevExpress.XtraPivotGrid.PivotArea.RowArea)
For i As Integer = 0 To pivot.Cells.RowCount - 1
Dim pcea As DevExpress.XtraPivotGrid.PivotCellEventArgs = pivot.Cells.GetCellInfo(0, i)
If pcea.RowValueType = DevExpress.XtraPivotGrid.PivotGridValueType.Value Then
For Each item As DevExpress.XtraPivotGrid.PivotGridField In fieldsInRowArea
tempRowText += pcea.GetFieldValue(CType((item), DevExpress.XtraPivotGrid.PivotGridField)).ToString() & " | "
Next 'add formatting if it's necessary
tempRowText = tempRowText.Remove(tempRowText.Length - 3, 3)
Else
tempRowText = pcea.RowValueType.ToString()
End If
rowvalues.Clear()
rowvalues.Add(tempRowText)
tempRowText = ""
For j As Integer = 0 To pivot.Cells.ColumnCount - 1
pcea = pivot.Cells.GetCellInfo(j, i)
If pcea.Value IsNot Nothing Then
rowvalues.Add(pcea.Value)
Else
rowvalues.Add(System.DBNull.Value)
End If
Next
dataTable1.Rows.Add(rowvalues.ToArray())
Next
End Sub
Private Sub FillDatasetColumns(ByVal pivot As DevExpress.XtraPivotGrid.PivotGridControl, ByVal dataTable1 As System.Data.DataTable)
dataTable1.Columns.Add("RowFields", GetType(String))
Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder()
Dim fieldsInColumnArea As System.Collections.Generic.List(Of DevExpress.XtraPivotGrid.PivotGridField) = PivotReportGenerator.GetFieldsInArea(pivot, DevExpress.XtraPivotGrid.PivotArea.ColumnArea)
Dim multipleDataField As Boolean = pivot.GetFieldsByArea(CType((DevExpress.XtraPivotGrid.PivotArea.DataArea), DevExpress.XtraPivotGrid.PivotArea)).Count > 1
For i As Integer = 0 To pivot.Cells.ColumnCount - 1
Dim pcea As DevExpress.XtraPivotGrid.PivotCellEventArgs = pivot.Cells.GetCellInfo(i, 0)
For Each field As DevExpress.XtraPivotGrid.PivotGridField In pcea.GetColumnFields()
sb.AppendFormat("{0} | ", field.GetDisplayText(pcea.GetFieldValue(field)))
Next 'add formatting if it's necessary
If multipleDataField Then sb.AppendFormat("{0} | ", pcea.DataField)
If pcea.ColumnValueType = DevExpress.XtraPivotGrid.PivotGridValueType.Value Then
sb.Remove(sb.Length - 3, 3)
Else
sb.Append(pcea.ColumnValueType.ToString())
End If
dataTable1.Columns.Add(sb.ToString(), GetType(Object))
sb.Clear()
Next
End Sub
Private Function GetFieldsInArea(ByVal pivot As DevExpress.XtraPivotGrid.PivotGridControl, ByVal area As DevExpress.XtraPivotGrid.PivotArea) As List(Of DevExpress.XtraPivotGrid.PivotGridField)
Dim fields As System.Collections.Generic.List(Of DevExpress.XtraPivotGrid.PivotGridField) = New System.Collections.Generic.List(Of DevExpress.XtraPivotGrid.PivotGridField)()
For i As Integer = 0 To pivot.Fields.Count - 1
If pivot.Fields(CInt((i))).Area = area Then fields.Add(pivot.Fields(i))
Next
Return fields
End Function
#End Region
Public Sub InitBands(ByVal rep As DevExpress.XtraReports.UI.XtraReport)
' Create bands
Dim detail As DevExpress.XtraReports.UI.DetailBand = New DevExpress.XtraReports.UI.DetailBand()
Dim pageHeader As DevExpress.XtraReports.UI.PageHeaderBand = New DevExpress.XtraReports.UI.PageHeaderBand()
Dim reportFooter As DevExpress.XtraReports.UI.ReportFooterBand = New DevExpress.XtraReports.UI.ReportFooterBand()
detail.Height = 20
reportFooter.Height = 380
pageHeader.Height = 20
' Place the bands onto a report
rep.Bands.AddRange(New DevExpress.XtraReports.UI.Band() {detail, pageHeader, reportFooter})
End Sub
Public Sub InitStyles(ByVal rep As DevExpress.XtraReports.UI.XtraReport)
' Create different odd and even styles
Dim oddStyle As DevExpress.XtraReports.UI.XRControlStyle = New DevExpress.XtraReports.UI.XRControlStyle()
Dim evenStyle As DevExpress.XtraReports.UI.XRControlStyle = New DevExpress.XtraReports.UI.XRControlStyle()
' Specify the odd style appearance
oddStyle.BackColor = System.Drawing.Color.LightBlue
oddStyle.StyleUsing.UseBackColor = True
oddStyle.StyleUsing.UseBorders = False
oddStyle.Name = "OddStyle"
' Specify the even style appearance
evenStyle.BackColor = System.Drawing.Color.LightPink
evenStyle.StyleUsing.UseBackColor = True
evenStyle.StyleUsing.UseBorders = False
evenStyle.Name = "EvenStyle"
' Add styles to report's style sheet
rep.StyleSheet.AddRange(New DevExpress.XtraReports.UI.XRControlStyle() {oddStyle, evenStyle})
End Sub
Public Sub InitDetailsBasedonXRTable(ByVal rep As DevExpress.XtraReports.UI.XtraReport, ByVal kind As ReportGeneratorType, ByVal columnWidth As Single, ByVal repeatRowHeader As Boolean)
If Not repeatRowHeader OrElse kind = ReportGeneratorType.SinglePage Then
PivotReportGenerator.InitDetailsBasedonXRTableWithoutRepeatingRowHeader(rep, kind, columnWidth)
Else
PivotReportGenerator.InitDetailsBasedonXRTableRepeatingRowHeader(rep, kind, columnWidth)
End If
End Sub
Private Sub InitDetailsBasedonXRTableRepeatingRowHeader(ByVal rep As DevExpress.XtraReports.UI.XtraReport, ByVal kind As ReportGeneratorType, ByVal columnWidth As Single)
Dim font As DevExpress.Drawing.DXFont = New DevExpress.Drawing.DXFont("Tahoma", 9.75F)
Dim dataTable As System.Data.DataTable = CType(rep.DataSource, System.Data.DataSet).Tables(0)
Dim processedPage As Integer = 0
Dim usablePageWidth As Single = rep.PageWidth - (rep.Margins.Left + rep.Margins.Right)
Dim columnsWidth As System.Collections.Generic.List(Of Single) = Nothing
If kind = ReportGeneratorType.FixedColumnWidth Then
columnsWidth = PivotReportGenerator.DefineColumnsWidth(columnWidth, dataTable.Columns.Count)
Else
columnsWidth = PivotReportGenerator.GetColumnsBestFitWidth(dataTable, font, rep.ReportUnit)
End If
Dim tableHeader As DevExpress.XtraReports.UI.XRTable = Nothing
Dim tableDetail As DevExpress.XtraReports.UI.XRTable = Nothing
PivotReportGenerator.InitNewTableInstancesAt(rep, font, tableHeader, tableDetail, New System.Drawing.PointF(0, 0))
tableHeader.BeginInit()
tableDetail.BeginInit()
Dim i As Integer = 1
PivotReportGenerator.AddCellsToTables(tableHeader, tableDetail, dataTable.Columns(0), columnsWidth(0), True)
Dim remainingSpace As Single = usablePageWidth - columnsWidth(0)
Do
If columnsWidth(i) > remainingSpace Then
processedPage += 1
tableHeader.WidthF = usablePageWidth - remainingSpace
tableDetail.WidthF = usablePageWidth - remainingSpace
tableHeader.EndInit()
tableDetail.EndInit()
PivotReportGenerator.InitNewTableInstancesAt(rep, font, tableHeader, tableDetail, New System.Drawing.PointF(usablePageWidth * processedPage, 0))
tableHeader.BeginInit()
tableDetail.BeginInit()
PivotReportGenerator.AddCellsToTables(tableHeader, tableDetail, dataTable.Columns(0), columnsWidth(0), True)
remainingSpace = usablePageWidth - columnsWidth(0)
Else
PivotReportGenerator.AddCellsToTables(tableHeader, tableDetail, dataTable.Columns(i), columnsWidth(i), False)
remainingSpace -= columnsWidth(i)
i += 1
End If
Loop While i < columnsWidth.Count
tableHeader.WidthF = usablePageWidth - remainingSpace
tableDetail.WidthF = usablePageWidth - remainingSpace
tableHeader.EndInit()
tableDetail.EndInit()
End Sub
Public Sub AddCellsToTables(ByVal header As DevExpress.XtraReports.UI.XRTable, ByVal detail As DevExpress.XtraReports.UI.XRTable, ByVal dc As System.Data.DataColumn, ByVal columnWidth As Single, ByVal isFirstColumnInTable As Boolean)
Dim headerCell As DevExpress.XtraReports.UI.XRTableCell = New DevExpress.XtraReports.UI.XRTableCell()
headerCell.Text = dc.Caption
Dim detailCell As DevExpress.XtraReports.UI.XRTableCell = New DevExpress.XtraReports.UI.XRTableCell()
detailCell.DataBindings.Add("Text", Nothing, dc.Caption)
headerCell.WidthF = columnWidth
detailCell.WidthF = columnWidth
If isFirstColumnInTable Then
headerCell.Borders = DevExpress.XtraPrinting.BorderSide.Left Or DevExpress.XtraPrinting.BorderSide.Top Or DevExpress.XtraPrinting.BorderSide.Bottom
detailCell.Borders = DevExpress.XtraPrinting.BorderSide.Left Or DevExpress.XtraPrinting.BorderSide.Top Or DevExpress.XtraPrinting.BorderSide.Bottom
Else
headerCell.Borders = DevExpress.XtraPrinting.BorderSide.All
detailCell.Borders = DevExpress.XtraPrinting.BorderSide.All
End If
' Place the cells into the corresponding tables
header.Rows(CInt((0))).Cells.Add(headerCell)
detail.Rows(CInt((0))).Cells.Add(detailCell)
End Sub
Public Sub InitNewTableInstancesAt(ByVal report As DevExpress.XtraReports.UI.XtraReport, ByVal font As DevExpress.Drawing.DXFont, <Out> ByRef header As DevExpress.XtraReports.UI.XRTable, <Out> ByRef detail As DevExpress.XtraReports.UI.XRTable, ByVal location As System.Drawing.PointF)
header = PivotReportGenerator.InitXRTable(font, False)
detail = PivotReportGenerator.InitXRTable(font, True)
header.LocationF = location
detail.LocationF = location
Dim headerRow As DevExpress.XtraReports.UI.XRTableRow = New DevExpress.XtraReports.UI.XRTableRow()
header.Rows.Add(headerRow)
Dim detailRow As DevExpress.XtraReports.UI.XRTableRow = New DevExpress.XtraReports.UI.XRTableRow()
detail.Rows.Add(detailRow)
report.Bands(CType((DevExpress.XtraReports.UI.BandKind.PageHeader), DevExpress.XtraReports.UI.BandKind)).Controls.Add(header)
report.Bands(CType((DevExpress.XtraReports.UI.BandKind.Detail), DevExpress.XtraReports.UI.BandKind)).Controls.Add(detail)
End Sub
Private Function InitXRTable(ByVal font As DevExpress.Drawing.DXFont, ByVal withStyles As Boolean) As XRTable
Dim table As DevExpress.XtraReports.UI.XRTable = New DevExpress.XtraReports.UI.XRTable()
table.Font = font
table.Height = 20
If withStyles Then
table.EvenStyleName = "EvenStyle"
table.OddStyleName = "OddStyle"
End If
Return table
End Function
Private Function DefineColumnsWidth(ByVal columnWidth As Single, ByVal count As Integer) As List(Of Single)
Dim columnsWidth As System.Collections.Generic.List(Of Single) = New System.Collections.Generic.List(Of Single)()
For i As Integer = 0 To count - 1
columnsWidth.Add(columnWidth)
Next
Return columnsWidth
End Function
Private Sub InitDetailsBasedonXRTableWithoutRepeatingRowHeader(ByVal rep As DevExpress.XtraReports.UI.XtraReport, ByVal kind As ReportGeneratorType, ByVal columnWidth As Single)
Dim font As DevExpress.Drawing.DXFont = New DevExpress.Drawing.DXFont("Tahoma", 9.75F)
Dim ds As System.Data.DataSet = CType(rep.DataSource, System.Data.DataSet)
Dim colCount As Integer = ds.Tables(CInt((0))).Columns.Count
Dim colWidth As Single = 0
Dim tableHeader As DevExpress.XtraReports.UI.XRTable = Nothing
Dim tableDetail As DevExpress.XtraReports.UI.XRTable = Nothing
PivotReportGenerator.InitNewTableInstancesAt(rep, font, tableHeader, tableDetail, New System.Drawing.PointF(0, 0))
Dim columnsWidth As System.Collections.Generic.List(Of Single) = Nothing
Select Case kind
Case ReportGeneratorType.FixedColumnWidth
colWidth = columnWidth
tableHeader.WidthF = columnWidth * colCount
tableDetail.WidthF = columnWidth * colCount
Case ReportGeneratorType.BestFitColumns
columnsWidth = PivotReportGenerator.GetColumnsBestFitWidth(ds.Tables(0), font, rep.ReportUnit)
colWidth = 0
tableHeader.WidthF = PivotReportGenerator.GetTotalWidth(columnsWidth)
tableDetail.WidthF = tableHeader.Width
Case Else
colWidth =(rep.PageWidth - (rep.Margins.Left + rep.Margins.Right)) / colCount
tableHeader.WidthF =(rep.PageWidth - (rep.Margins.Left + rep.Margins.Right))
tableDetail.WidthF =(rep.PageWidth - (rep.Margins.Left + rep.Margins.Right))
End Select
tableHeader.BeginInit()
tableDetail.BeginInit()
' Create table cells, fill the header cells with text, bind the cells to data
For i As Integer = 0 To colCount - 1
PivotReportGenerator.AddCellsToTables(tableHeader, tableDetail, ds.Tables(CInt((0))).Columns(i), If(kind = ReportGeneratorType.BestFitColumns, columnsWidth(i), colWidth), If(i = 0, True, False))
Next
tableDetail.EndInit()
tableHeader.EndInit()
' Place the table onto a report's Detail band
End Sub
Private Function GetTotalWidth(ByVal columnsWidth As System.Collections.Generic.List(Of Single)) As Single
Dim i As Single = 0
For Each colWidth As Single In columnsWidth
i += colWidth
Next
Return i
End Function
Private Function GetColumnsBestFitWidth(ByVal dataTable As System.Data.DataTable, ByVal font As DevExpress.Drawing.DXFont, ByVal unit As DevExpress.XtraReports.UI.ReportUnit) As List(Of Single)
Dim optimalColumnWidth As System.Collections.Generic.List(Of Single) = New System.Collections.Generic.List(Of Single)()
Dim maxWidth As Single = 0
Dim tempWidth As Single = 0
For i As Integer = 1 To dataTable.Rows.Count - 1
tempWidth = PivotReportGenerator.MeasureWidth(dataTable.Rows(CInt((i)))(CInt((0))).ToString(), font, unit)
maxWidth = If(maxWidth > tempWidth, maxWidth, tempWidth)
Next
optimalColumnWidth.Add(maxWidth)
For i As Integer = 1 To dataTable.Columns.Count - 1
tempWidth = PivotReportGenerator.MeasureWidth(dataTable.Columns(CInt((i))).ColumnName.ToString(), font, unit)
maxWidth = If(50 > tempWidth, 50, tempWidth)
optimalColumnWidth.Add(maxWidth)
Next
Return optimalColumnWidth
End Function
Private Function MeasureWidth(ByVal candidate As String, ByVal font As DevExpress.Drawing.DXFont, ByVal unit As DevExpress.XtraReports.UI.ReportUnit) As Single
Return DevExpress.XtraReports.UI.BestSizeEstimator.GetBoundsToFitText(CStr((candidate)), CType((New DevExpress.XtraPrinting.BrickStyle() With {.Font = font}), DevExpress.XtraPrinting.BrickStyle), CType((unit), DevExpress.XtraReports.UI.ReportUnit)).Width
End Function
End Module
Public Enum ReportGeneratorType
SinglePage
FixedColumnWidth
BestFitColumns
End Enum