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

<%@ Page Language="VB" AutoEventWireup="true" Codebehind="products-by-category-form.aspx.vb"
    Inherits="Aspose.Cells.Demos.VisualBasic.ProductsByCatagoryForm" MasterPageFile="~/tpl/Demo.Master"
    Title="Products by Category - 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">Products By Category - 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 Product by Category 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 </span>products by category. It has 3 columns per page and starts
            each category in a new column..<span style="color: black"> ADO.NET is used to retrieve
                the data from the Products and Categories tables 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>Execute</b> to see how example 
        prints products by category. Output document will have 3 columns per page; each category will start in new
        column. </SPAN></p>
    <p class="componentDescriptionTxt">
        <span style="font-size: 10pt; font-family: Arial"></span>
    <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

Imports Aspose.Cells
Public Class ProductsByCatagoryForm
    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 = "ProductsByCategory"
        'Create Product By Category
        Dim ProductsByCategory As ProductsByCategory = New ProductsByCategory(path)
        workbook = ProductsByCategory.CreateProductsByCategory()
        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

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

    Public Function CreateProductsByCategory() 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 Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock, Products.Discontinued, Categories.CategoryID, Products.ProductID FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID WHERE (Products.Discontinued <> Yes) ORDER BY Categories.CategoryName, Products.ProductName"
        Me.oleDbDataAdapter1.Fill(Me.dataTable1)

        Dim sheet As Worksheet = workbook.Worksheets("Sheet7")
        sheet.Name = "Products By Category"
        Dim cells As cells = sheet.Cells
        Dim vPageBreaks As VerticalPageBreakCollection = sheet.VPageBreaks
        cells.SetRowHeight(4, 20.25)
        cells.SetRowHeight(5, 18.75)
        Dim currentRow As Integer = 4
        Dim currentColumn As Byte = 0

        Dim lastCategory As String = ""
        Dim thisCategory As String, nextCategory As String

        Dim productsCount As Integer = 0

        SetProductsByCategoryStyles(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 = 4
                If i <> 0 Then
                    currentColumn += 4
                End If
                CreateProductsByCategoryHeader(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(Me.dataTable1.Rows(i)("UnitsInStock"), Short))

            If i <> Me.dataTable1.Rows.Count - 1 Then
                nextCategory = CType(Me.dataTable1.Rows(i + 1)("CategoryName"), String)
                If thisCategory <> nextCategory Then
                    Dim style As Style = workbook.Styles("ProductsCount")
                    cells(currentRow + 1, currentColumn).PutValue("Number of Products:")
                    cells(currentRow + 1, currentColumn).Style = style

                    style = workbook.Styles("CountNumber")
                    cells(currentRow + 1, CType((currentColumn + 1), Byte)).PutValue(productsCount + 1)
                    cells(currentRow + 1, CType((currentColumn + 1), Byte)).Style = style
                    currentRow = currentRow + 1
                    productsCount = 0
                    vPageBreaks.Add(0, currentColumn + 1)
                Else
                    productsCount = productsCount + 1
                End If
            Else
                Dim style As Style = workbook.Styles("ProductsCount")
                cells(currentRow + 1, currentColumn).PutValue("Number of Products:")
                cells(currentRow + 1, currentColumn).Style = style

                style = workbook.Styles("CountNumber")
                cells(currentRow + 1, CType((currentColumn + 1), Byte)).PutValue(productsCount + 1)
                cells(currentRow + 1, CType((currentColumn + 1), Byte)).Style = style
            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 <> "Products By Category" Then
                workbook.Worksheets.RemoveAt(i)
                i = i - 1
            End If
        Next

        Return workbook

    End Function

    Private Sub SetProductsByCategoryStyles(ByVal workbook As Workbook)

        Dim styleIndex As Integer = workbook.Styles.Add()
        Dim style As style = workbook.Styles(styleIndex)
        style.Font.IsItalic = True
        style.Font.IsBold = True
        style.Font.Size = 16
        style.HorizontalAlignment = TextAlignmentType.Right
        style.Name = "Category"

        styleIndex = workbook.Styles.Add()
        style = workbook.Styles(styleIndex)
        style.Font.Size = 16
        style.Font.IsBold = True
        style.HorizontalAlignment = TextAlignmentType.Left
        style.Name = "CategoryName"

        styleIndex = workbook.Styles.Add()
        style = workbook.Styles(styleIndex)
        style.Font.Size = 14
        style.Font.IsBold = True
        style.Font.IsItalic = True
        style.HorizontalAlignment = TextAlignmentType.Left
        style.Borders(BorderType.TopBorder).LineStyle = CellBorderType.Medium
        style.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.Medium
        style.Name = "ProductName"

        styleIndex = workbook.Styles.Add()
        style = workbook.Styles(styleIndex)
        style.Font.Size = 14
        style.Font.IsBold = True
        style.Font.IsItalic = True
        style.HorizontalAlignment = TextAlignmentType.Right
        style.Borders(BorderType.TopBorder).LineStyle = CellBorderType.Medium
        style.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.Medium
        style.Name = "UnitsInStock"

        styleIndex = workbook.Styles.Add()
        style = workbook.Styles(styleIndex)
        style.Font.IsBold = True
        style.Font.IsItalic = True
        style.Borders(BorderType.TopBorder).LineStyle = CellBorderType.Thin
        style.Name = "ProductsCount"

        styleIndex = workbook.Styles.Add()
        style = workbook.Styles(styleIndex)
        style.HorizontalAlignment = TextAlignmentType.Left
        style.Borders(BorderType.TopBorder).LineStyle = CellBorderType.Thin
        style.Name = "CountNumber"

    End Sub

    Private Sub CreateProductsByCategoryHeader(ByVal workbook As Workbook, ByVal Cells As Cells, ByVal startRow As Integer, ByVal startColumn As Byte, ByVal categoryName As String)

        Dim style As style = workbook.Styles("Category")
        Cells(startRow, startColumn).PutValue("Category:")
        Cells(startRow, startColumn).Style = style

        style = workbook.Styles("CategoryName")
        Cells(startRow, CType((startColumn + 1), Byte)).PutValue(categoryName)
        Cells(startRow, CType((startColumn + 1), Byte)).Style = style

        style = workbook.Styles("ProductName")
        Cells(startRow + 1, startColumn).PutValue("Product Name")
        Cells(startRow + 1, startColumn).Style = style

        style = workbook.Styles("UnitsInStock")
        Cells(startRow + 1, CType((startColumn + 1), Byte)).PutValue("Units In Stock:")
        Cells(startRow + 1, CType((startColumn + 1), Byte)).Style = style

    End Sub


End Class