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
|
Imports Aspose.Cells
Imports Aspose.Cells.Charts
Partial Public Class CostPareto
Inherits System.Web.UI.Page
Protected Sub btnProcess_Click(ByVal sender As Object, ByVal e As System.EventArgs)
'Get data from xml file
Dim ds As DataSet = New DataSet()
Dim path As String = MapPath(".")
path = path.Substring(0, path.LastIndexOf("\"))
path += "\Database\CostPareto.xml"
ds.ReadXml(path, XmlReadMode.ReadSchema)
Dim workbook As Workbook = New Workbook()
'Generate first data sheet
GenerateDataSheet(workbook, ds)
'Generate second chart sheet
GenerateChartSheet(workbook, ds)
'Save file and send to client browser
workbook.Save("CostPareto.xls", SaveType.OpenInExcel, FileFormatType.Excel97To2003, Me.Response)
' End response to avoid unneeded HTML after XLS
Response.End()
End Sub
Private Sub GenerateDataSheet(ByVal workbook As Workbook, ByVal ds As DataSet)
'Write data to first data sheet
Dim sheet1 As Worksheet = workbook.Worksheets(0)
sheet1.Name = "Cost Data"
Dim cells As cells = sheet1.Cells
'Import data
cells.ImportDataTable(ds.Tables(0), True, 0, 0, ds.Tables(0).Rows.Count, ds.Tables(0).Columns.Count)
'Set header style
Dim styles As StyleCollection = workbook.Styles
Dim styleIndex As Integer = styles.Add()
Dim style As Style = styles(styleIndex)
style.Font.Size = 10
style.Font.Color = color.White
style.Font.IsBold = True
style.Font.Name = "Verdana"
style.IsLocked = True
style.VerticalAlignment = TextAlignmentType.Center
style.HorizontalAlignment = TextAlignmentType.Left
style.IndentLevel = 1
style.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.Thick
style.Borders(BorderType.TopBorder).LineStyle = CellBorderType.Thin
style.Borders(BorderType.LeftBorder).LineStyle = CellBorderType.Thin
style.Borders(BorderType.RightBorder).LineStyle = CellBorderType.Thin
workbook.ChangePalette(color.FromArgb(10, 100, 180), 50)
style.ForegroundColor = color.FromArgb(10, 100, 180)
style.Pattern = BackgroundType.Solid
cells.SetColumnWidth(0, 25)
cells.SetColumnWidth(1, 18)
cells.SetRowHeight(0, 30)
cells(0, 0).Style = style
styleIndex = styles.Add()
Dim style1 As Style = styles(styleIndex)
style1.Copy(style)
style1.HorizontalAlignment = TextAlignmentType.Right
style1.IndentLevel = 0
cells(0, 1).Style = style1
Dim currentRow As Integer = 1
Dim i As Integer
For i = 0 To ds.Tables(0).Rows.Count - 1
'Set row height
cells.SetRowHeight(currentRow, 20)
Dim color As color = color.FromArgb(255, 255, 255)
workbook.ChangePalette(color, 51)
If currentRow Mod 2 = 0 Then
color = color.FromArgb(250, 250, 200)
workbook.ChangePalette(color, 52)
End If
'Set style for the first column cells
styleIndex = styles.Add()
Dim styleCell1 As Style = styles(styleIndex)
styleCell1.Font.Size = 10
styleCell1.Font.Name = "Arial"
styleCell1.HorizontalAlignment = TextAlignmentType.Left
styleCell1.VerticalAlignment = TextAlignmentType.Center
styleCell1.IndentLevel = 1
styleCell1.Borders(BorderType.LeftBorder).LineStyle = CellBorderType.Thin
styleCell1.Borders(BorderType.RightBorder).LineStyle = CellBorderType.Dashed
styleCell1.Borders(BorderType.TopBorder).LineStyle = CellBorderType.None
styleCell1.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.None
If currentRow = ds.Tables(0).Rows.Count Then
styleCell1.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.Thin
End If
styleCell1.ForegroundColor = color
styleCell1.Pattern = BackgroundType.Solid
cells(currentRow, 0).Style = styleCell1
'Set style for the second column cells
styleIndex = styles.Add()
Dim styleCell2 As Style = styles(styleIndex)
styleCell2.Copy(styleCell1)
styleCell2.Borders(BorderType.LeftBorder).LineStyle = CellBorderType.None
styleCell2.Borders(BorderType.RightBorder).LineStyle = CellBorderType.Thin
styleCell2.HorizontalAlignment = TextAlignmentType.Right
styleCell2.IndentLevel = 0
'Set number format
styleCell2.Custom = "_(""$""* #,##0.00_);_(""$""* (#,##0.00);_(""$""* ""-""??_);_(@_)"
cells(currentRow, 1).Style = styleCell2
currentRow = currentRow + 1
Next
End Sub
Private Sub GenerateChartSheet(ByVal workbook As Workbook, ByVal ds As DataSet)
'Generate the second chart sheet
Dim sheetIndex As Integer = workbook.Worksheets.Add(SheetType.Chart)
Dim sheet2 As Worksheet = workbook.Worksheets(sheetIndex)
sheet2.Name = "Pareto Chart"
Dim chartIndex As Integer = sheet2.Charts.Add(ChartType.Column, 0, 0, 0, 0)
Dim chart As chart = sheet2.Charts(chartIndex)
chart.Title.Text = "Cost Center"
'Set chart title font
chart.Title.TextFont.IsBold = True
chart.Title.TextFont.Size = 16
'Set series
Dim series As String = "Cost Data!B2:B" & (ds.Tables(0).Rows.Count + 1)
chart.NSeries.Add(series, True)
chart.NSeries(0).Name = "Annual Cost"
'Set category
chart.NSeries.CategoryData = "Cost Data!A2:A" & (ds.Tables(0).Rows.Count + 1)
'Set chart style
'Set plot area foreground color
chart.IsLegendShown = False
workbook.ChangePalette(Color.FromArgb(255, 255, 200), 53)
chart.PlotArea.Area.ForegroundColor = Color.FromArgb(255, 255, 200)
'Set major grid line color
workbook.ChangePalette(Color.FromArgb(121, 117, 200), 54)
chart.CategoryAxis.MajorGridLines.Color = Color.FromArgb(121, 117, 200)
'Set series each point color
Dim i As Integer
For i = 0 To chart.NSeries(0).Points.Count - 1
workbook.ChangePalette(Color.FromArgb(10, 100, 180), 55)
chart.NSeries(0).Points(i).Area.ForegroundColor = Color.FromArgb(10, 100, 180)
workbook.ChangePalette(Color.FromArgb(255, 255, 200), 53)
chart.NSeries(0).Points(i).Border.Color = Color.FromArgb(255, 255, 200)
Next
End Sub
End Class
|