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
|
Imports Aspose.Cells.Charts
Imports Aspose.Cells.Drawing
Public Class SalesByCategory
Inherits DbBase
Public Sub New(ByVal path As String)
MyBase.New(path)
End Sub
Public Function CreateSalesByCategory() As Workbook
DBInit()
Dim designerFile As String = path + "\\Designer\\Northwind.xls"
Dim workbook As Workbook = New Workbook()
workbook.Open(designerFile)
Me.oleDbDataAdapter1.SelectCommand.CommandText = "SELECT DISTINCTROW Categories.CategoryID, " + _
" Categories.CategoryName, Products.ProductName, SUM([Order Details Extended].ExtendedPrice) AS ProductSales " + _
" FROM Categories INNER JOIN (Products INNER JOIN (Orders INNER JOIN [Order Details Extended] ON" + _
" Orders.OrderID = [Order Details Extended].OrderID) ON Products.ProductID = [Order Details Extended].ProductID) " + _
" ON Categories.CategoryID = Products.CategoryID WHERE " + _
" (((Orders.OrderDate) BETWEEN #1/1/1995# AND #12/31/1995#))" + _
" GROUP BY Categories.CategoryID , Categories.CategoryName , Products.ProductName ORDER BY Categories.CategoryName"
Me.oleDbDataAdapter1.Fill(Me.dataTable1)
Dim sheet As Worksheet = workbook.Worksheets("Sheet8")
sheet.Name = "Sales By Category"
Dim cells As Cells = sheet.Cells
Dim vPageBreaks As VerticalPageBreakCollection = sheet.VPageBreaks
Dim currentRow As Integer = 2
Dim currentColumn As Byte = 0
Dim lastCategory As String = ""
Dim thisCategory As String, nextCategory As String
SetSalesByCategoryStyles(workbook)
Dim i As Integer
For i = 0 To Me.dataTable1.Rows.Count - 1
thisCategory = CType(Me.dataTable1.Rows(i)("CategoryName"), String)
If thisCategory <> lastCategory Then
currentRow = 2
If i <> 0 Then
currentColumn += 15
End If
CreateSalesByCategoryHeader(workbook, cells, currentRow, currentColumn, thisCategory)
lastCategory = thisCategory
currentRow += 2
End If
cells(currentRow, currentColumn).PutValue(CType(Me.dataTable1.Rows(i)("ProductName"), String))
cells(currentRow, CType((currentColumn + 1), Byte)).PutValue(CType(CType(Me.dataTable1.Rows(i)("ProductSales"), Decimal), Double))
cells(currentRow, CType((currentColumn + 1), Byte)).Style = workbook.Styles("Sales")
cells.SetColumnWidth(currentColumn, 27)
cells.SetColumnWidth(CType((currentColumn + 1), Byte), 15)
If i <> Me.dataTable1.Rows.Count - 1 Then
nextCategory = CType(Me.dataTable1.Rows(i + 1)("CategoryName"), String)
If thisCategory <> nextCategory Then
vPageBreaks.Add(0, currentColumn + 1)
CreateChart(workbook, sheet, currentRow, currentColumn)
End If
Else
CreateChart(workbook, sheet, currentRow, currentColumn)
End If
currentRow = currentRow + 1
Next
For i = 0 To workbook.Worksheets.Count - 1
If (i >= workbook.Worksheets.Count) Then
Exit For
End If
sheet = workbook.Worksheets(i)
If sheet.Name <> "Sales By Category" Then
workbook.Worksheets.RemoveAt(i)
i = i - 1
End If
Next
Return workbook
End Function
Private Sub CreateChart(ByVal workbook As Workbook, ByVal sheet As Worksheet, ByVal currentRow As Integer, ByVal currentColumn As Integer)
Dim chartIndex As Integer = sheet.Charts.Add(ChartType.Bar, 4, currentColumn + 3, 26, currentColumn + 14)
Dim chart As chart = sheet.Charts(chartIndex)
chart.IsLegendShown = False
Dim startCell As String = CellsHelper.CellIndexToName(4, currentColumn + 1)
Dim endCell As String = CellsHelper.CellIndexToName(currentRow, currentColumn + 1)
chart.NSeries.Add(startCell + ":" + endCell, True)
Dim fillFormat As fillFormat = chart.PlotArea.Area.FillFormat
fillFormat.SetPresetColorGradient(GradientPresetType.Daybreak, GradientStyleType.Vertical, 1)
startCell = CellsHelper.CellIndexToName(4, currentColumn)
endCell = CellsHelper.CellIndexToName(currentRow, currentColumn)
chart.NSeries.CategoryData = startCell + ":" + endCell
End Sub
Private Sub CreateSalesByCategoryHeader(ByVal workbook As Workbook, ByVal Cells As Cells, ByVal currentRow As Integer, _
ByVal currentColumn As Byte, ByVal categoryName As String)
Cells(currentRow, currentColumn).PutValue(categoryName)
Cells(currentRow, currentColumn).Style = workbook.Styles("Header")
End Sub
Private Sub SetSalesByCategoryStyles(ByVal workbook As Workbook)
Dim styleIndex As Integer = workbook.Styles.Add()
Dim style As Style = workbook.Styles(styleIndex)
style.Number = 7
style.Name = "Sales"
styleIndex = workbook.Styles.Add()
style = workbook.Styles(styleIndex)
style.Font.Size = 14
style.Font.IsBold = True
style.Font.IsItalic = True
style.Font.Color = Color.Yellow
style.ForegroundColor = Color.Blue
style.Name = "Header"
End Sub
End Class
|