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

<%@ Page Language="VB" AutoEventWireup="true" CodeBehind="employee-sales-form.aspx.vb" Inherits="Aspose.Cells.Demos.VisualBasic.EmployeeSalesForm" 
MasterPageFile="~/tpl/Demo.Master" Title="Employee Sales by Country - Apose.Cells Demos"%>
<asp:Content ID="Content" ContentPlaceHolderID="MainContent" runat="server">
    <p class="componentDescriptionTxt">
        <span style="font-size: 10pt; font-family: Arial"></span>&nbsp;</p>
    <p class="componentDescriptionTxt">
        <span style="font-size: 10pt; font-family: Arial"></span>
            <table align="center" border="0" cellpadding="0" cellspacing="0" width="90%">
                <tr>
                    <td valign="top" width="19">
                        <img alt="" height="41" src="/Common/images/heading_lft.jpg" width="19" /></td>
                    <td class="demos-heading-bg" width="100%">
                        <h2 class="demos-heading-bg">
                            &nbsp;<font face="Arial" size="4">Employee Sales by Country - Aspose.Cells</font></h2>
                    </td>
                    <td valign="top" width="19">
                        <img alt="" height="41" src="/Common/images/heading_rt.jpg" width="19" /></td>
                </tr>
            </table>
    </p>
    <p class="MsoNormal">
        <font face="Arial"><span style="color: black"><font size="2">This online demo illustrates
            how to create a well formatted Employee Sales by Country report </font></span></font>
        <font size="2"><span style="font-family: Arial">using </span><font face="Arial"><a
            href="http://www.aspose.com/categories/.net-components/aspose.cells-for-.net/default.aspx">
            Aspose.Cells</a> for .NET</font><span style="font-family: Arial">.</span></font><font
                face="Arial" size="2"> </font>
    </p>
    <p class="MsoNormal">
        <font face="Arial" size="2"><span style="color: black">Aspose.Cells component gives
            you the agility to report your data in a variety of ways. Aspose.Cells component
            is fully functional for creating all types of reports. You may customize the size
            and appearance of everything on a report. You can display the information the way
            you want to see it. </span></font>
    </p>
    <p class="MsoNormal">
        <font face="Arial" size="2"><span style="color: black">The demo generates a printed
            report displaying employee </span>sales grouped by country. It also calculates subtotals,
            grand total and percents of sales. It prints range on report and prints message
            when employee's total reaches a goal. A separate sheet is generated per country.<span
                style="color: black"> ADO.NET is used to retrieve the data from the Employees table
                of Northwind database, to generate the report. </span>You can either open the resultant excel file into MS Excel
        or save directly to your disk to check the results.</font></p>
    <p class="componentDescriptionTxt">
    	Click <b>Process </b> to see how example 
        Prints sales grouped by country and employee, calculates subtotals, grand total,
        percents; prints range on report; prints message when employee's total reaches goal;
        a sheet per country. </SPAN></p>
    <p class="componentDescriptionTxt">
    <asp:Button ID="btnExecute" runat="server" Text="Process" OnClick="btnExecute_Click"/>&nbsp;</p>
</asp:Content>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

Imports Aspose.Cells
Public Class EmployeeSalesForm
    Inherits System.Web.UI.Page
    Protected Sub btnExecute_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim workbook As Workbook
        Dim path As String = MapPath(".")
        path = path.Substring(0, path.LastIndexOf("\"))
        Dim param As String = "EmployeeSales"
        'Create Employee Sales
        Dim EmployeeSales As EmployeeSales = New EmployeeSales(path)
        workbook = EmployeeSales.CreateEmployeeSales()
        workbook.Save(param + ".xls", SaveType.OpenInExcel, FileFormatType.Excel97To2003, Me.Response)

		' End response to avoid unneeded HTML after XLS
        Response.End()
    End Sub

End Class

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

Public Class EmployeeSales
    Inherits DbBase
    Public Sub New(ByVal path As String)
        MyBase.New(path)
    End Sub

    Public Function CreateEmployeeSales() As Workbook

        DBInit()
        Dim designerFile As String = path + "\\Designer\\Northwind.xls"

        Dim workbook As workbook = New workbook()
        workbook.Open(designerFile)


        Dim sheet As Worksheet = workbook.Worksheets("Sheet5")
        sheet.Name = "Employee Sales UK"
        Dim cells As cells = sheet.Cells

        sheet = workbook.Worksheets("Sheet6")
        cells = sheet.Cells
        sheet.Name = "Employee Sales USA"

        ReadEmployees()
        Dim dtSales() As DataTable = Me.CreateDataResult()

        Dim currentUKRow As Integer = 6
        Dim currentUSARow As Integer = 6

        Dim styleIndex As Integer
        Dim style As style

        styleIndex = workbook.Styles.Add()
        style = workbook.Styles(styleIndex)
        style.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.Double
        style.Borders(BorderType.TopBorder).LineStyle = CellBorderType.Double
        style.Borders.SetColor(Color.Black)
        style.Font.Size = 12
        style.Font.IsBold = True
        style.IsTextWrapped = True
        style.HorizontalAlignment = TextAlignmentType.Center
        style.Name = "HeaderStyle"

        Dim i As Integer
        For i = 0 To Me.dataTable1.Rows.Count - 1
            Dim employeeName As String = CType(Me.dataTable1.Rows(i)("LastName"), String) + "," _
                + CType(Me.dataTable1.Rows(i)("FirstName"), String)
            If Me.dataTable1.Rows(i)("Country").ToString() = "UK" Then
                sheet = workbook.Worksheets("Employee Sales UK")
                cells = sheet.Cells

                cells(currentUKRow - 2, 0).PutValue("Salesperson:" + employeeName)

                style = workbook.Styles(workbook.Styles.Add())
                style.Font.IsBold = True
                style.Font.Size = 12

                cells(currentUKRow - 2, 0).Style = style

                If CType(Me.dataTable1.Rows(i)("TotalSales") > 5000, Decimal) Then
                    cells(currentUKRow - 2, 3).PutValue("Exceeded Goal!")
                    style = workbook.Styles(workbook.Styles.Add())
                    Dim font As Font = style.Font()
                    font.Color = Color.Red
                    font.IsItalic = True
                    font.Size = 12
                    font.IsBold = True
                    cells(currentUKRow - 2, 3).Style = style
                End If
                cells.SetRowHeight(currentUKRow - 2, 19)
                cells.SetRowHeight(currentUKRow - 1, 4)
                cells.SetRowHeight(currentUKRow, 48)

                style = workbook.Styles("HeaderStyle")
                Dim j As Integer
                For j = 1 To 4
                    cells(currentUKRow, CType(j, Byte)).Style = style
                Next
                cells(currentUKRow, 1).PutValue("Order ID:")
                cells(currentUKRow, 2).PutValue("Sales Amount:")
                cells(currentUKRow, 3).PutValue("Percent of Salesperson's Total:")
                cells(currentUKRow, 4).PutValue("Percent of Country Total:")
                currentUKRow = currentUKRow + 1

                cells.ImportDataTable(dtSales(i), False, currentUKRow, 1)
                Dim startCellName1 As String = CellsHelper.CellIndexToName(currentUKRow, 2)
                Dim startCellName2 As String = CellsHelper.CellIndexToName(currentUKRow, 4)

                currentUKRow += dtSales(i).Rows.Count - 1
                Dim endCellName1 As String = CellsHelper.CellIndexToName(currentUKRow, 2)
                Dim endCellName2 As String = CellsHelper.CellIndexToName(currentUKRow, 4)

                cells(currentUKRow + 1, 2).Formula = "=sum(" + startCellName1 + ":" _
                    + endCellName1 + ")"

                cells(currentUKRow + 1, 4).Formula = "=sum(" + startCellName2 + ":" _
                    + endCellName2 + ")"

                currentUKRow += 4

            Else
                sheet = workbook.Worksheets("Employee Sales USA")
                cells = sheet.Cells

                cells(currentUSARow - 2, 0).PutValue("Salesperson:" + employeeName)

                style = workbook.Styles(workbook.Styles.Add())
                style.Font.IsBold = True
                style.Font.Size = 12

                cells(currentUSARow - 2, 0).Style = style

                If CType(Me.dataTable1.Rows(i)("TotalSales") > 5000, Decimal) Then
                    cells(currentUSARow - 2, 3).PutValue("Exceeded Goal!")

                    style = workbook.Styles(workbook.Styles.Add())
                    Dim font As Font = style.Font()
                    font.Color = Color.Red
                    font.IsItalic = True
                    font.Size = 12
                    font.IsBold = True

                    cells(currentUSARow - 2, 3).Style = style
                End If
                cells.SetRowHeight(currentUSARow - 2, 19)
                cells.SetRowHeight(currentUSARow - 1, 4)
                cells.SetRowHeight(currentUSARow, 48)

                style = workbook.Styles("HeaderStyle")
                Dim j As Integer
                For j = 1 To 4
                    cells(currentUSARow, CType(j, Byte)).Style = style
                Next
                cells(currentUSARow, 1).PutValue("Order ID:")
                cells(currentUSARow, 2).PutValue("Sales Amount:")
                cells(currentUSARow, 3).PutValue("Percent of Salesperson's Total:")
                cells(currentUSARow, 4).PutValue("Percent of Country Total:")
                currentUSARow = currentUSARow + 1

                cells.ImportDataTable(dtSales(i), False, currentUSARow, 1)

                Dim startCellName1 As String = CellsHelper.CellIndexToName(currentUSARow, 2)
                Dim startCellName2 As String = CellsHelper.CellIndexToName(currentUSARow, 4)

                currentUSARow += dtSales(i).Rows.Count - 1
                Dim endCellName1 As String = CellsHelper.CellIndexToName(currentUSARow, 2)
                Dim endCellName2 As String = CellsHelper.CellIndexToName(currentUSARow, 4)

                cells(currentUSARow + 1, 2).Formula = "=sum(" + startCellName1 + ":" _
                    + endCellName1 + ")"

                cells(currentUSARow + 1, 4).Formula = "=sum(" + startCellName2 + ":" _
                    + endCellName2 + ")"

                currentUSARow += 4
            End If
        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 <> "Employee Sales UK" And sheet.Name <> "Employee Sales USA" Then
                workbook.Worksheets.RemoveAt(i)
                i = i - 1
            End If
        Next

        Return workbook

    End Function

    Private Function CreateDataResult() As DataTable()

        Dim dtSales() As DataTable = New DataTable(Me.dataTable1.Rows.Count - 1) {}
        Dim totalUKSales As Decimal = 0.0
        Dim totalUSASales As Decimal = 0.0
        Dim cmdText As String = "SELECT Orders.OrderID, [Order Subtotals].Subtotal as SaleAmount FROM Employees INNER " + _
          "JOIN (Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID) " + _
          "ON Employees.EmployeeID = Orders.EmployeeID"


        Dim i As Integer
        For i = 0 To Me.dataTable1.Rows.Count - 1
            dtSales(i) = New DataTable()
            dtSales(i).Columns.Add("OrderID", Type.GetType("System.Int32"))
            dtSales(i).Columns.Add("SaleAmount", Type.GetType("System.Decimal"))
            dtSales(i).Columns.Add("PercentOfPerson", Type.GetType("System.Decimal"))
            dtSales(i).Columns.Add("PercentOfCountry", Type.GetType("System.Decimal"))

            Dim totalPersonSales As Decimal = 0.0

            Me.oleDbDataAdapter2.SelectCommand.CommandText = cmdText + " where Employees.EmployeeID =" _
                + Me.dataTable1.Rows(i)("EmployeeID").ToString()
            Me.oleDbDataAdapter2.Fill(dtSales(i))

            'Get total sales amount of a salesperson
            Dim j As Integer
            For j = 0 To dtSales(i).Rows.Count - 1
                totalPersonSales += CType(dtSales(i).Rows(j)("SaleAmount"), Decimal)
            Next

            Me.dataTable1.Rows(i)("TotalSales") = totalPersonSales

            For j = 0 To dtSales(i).Rows.Count - 1
                dtSales(i).Rows(j)("PercentOfPerson") = CType(dtSales(i).Rows(j)("SaleAmount"), Decimal) / totalPersonSales
            Next
            If Me.dataTable1.Rows(i)("Country").ToString() = "UK" Then
                totalUKSales += totalPersonSales
            Else
                totalUSASales += totalPersonSales
            End If
        Next
        For i = 0 To dtSales.Length - 1
            Dim j As Integer
            For j = 0 To dtSales(i).Rows.Count - 1
                If Me.dataTable1.Rows(i)("Country").ToString() = "UK" Then
                    dtSales(i).Rows(j)("PercentOfCountry") = CType(dtSales(i).Rows(j)("SaleAmount"), Decimal) / totalUKSales
                Else
                    dtSales(i).Rows(j)("PercentOfCountry") = CType(dtSales(i).Rows(j)("SaleAmount"), Decimal) / totalUSASales
                End If
            Next
        Next
        Return dtSales
    End Function

    Private Sub ReadEmployees()

        Me.dataTable1.Reset()

        Me.oleDbSelectCommand1.CommandText = "SELECT Country, EmployeeID, FirstName, LastName FROM Employees ORDER BY Country, " + _
             "LastName, FirstName"
        Me.oleDbDataAdapter1.Fill(Me.dataTable1)
        Me.dataTable1.Columns.Add("TotalSales", Type.GetType("System.Decimal"))

    End Sub

End Class