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

<%@ Page Language="VB" AutoEventWireup="true" CodeBehind="catalog-form.aspx.vb" Inherits="Aspose.Cells.Demos.VisualBasic.CatalogForm" 
MasterPageFile="~/tpl/Demo.Master" Title="Catalog - 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">Catalog&nbsp; - 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 Catalog report by </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"><span style="color: black"><font size="2">The demo generates </font>
        </span><font size="2">a Catalog of products. It has a two-page report header and uses
            photos for each category. It starts each category on a new page and keeps all records
            for a category on same page. It prints an order form in the report footer on a separate
            page.</font></font><font size="2"><span style="color: black"><font face="Arial"> ADO.NET
                is used to retrieve the data from the Products table of Northwind database, to generate
                the report. </font></span></font><font face="Arial" size="2">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 a catalog of products. The document will contain two-page report header, photos for each category;
		the demo starts each category on a new page; keeps all records for a category on same page;
        prints an order form in the report footer on a separate page.</p>
    <asp:Button ID="btnExecute" runat="server" Text="Process" OnClick="btnExecute_Click"/>
</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 CatalogForm
    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 = "Catalog"
        'Create Catalog
        Dim Catalog As Catalog = New Catalog(path)
        workbook = Catalog.CreateCatalog()
        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

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

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

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

        ReadCategory()
        Dim dataTable2 As DataTable = New DataTable

        Dim sheet As Worksheet = workbook.Worksheets("Sheet2")
        sheet.Name = "Catalog"
        Dim cells As cells = sheet.Cells

        Dim currentRow As Integer = 55

        'Add LightGray color to color palette
        workbook.ChangePalette(Color.LightGray, 55)

        Dim styles As StyleCollection = workbook.Styles
        'Set CategoryName style
        Dim styleIndex As Integer = styles.Add()
        Dim styleCategoryName As Style = styles(styleIndex)
        styleCategoryName.Font.Size = 14
        styleCategoryName.Font.Color = Color.Blue
        styleCategoryName.Font.IsBold = True
        styleCategoryName.Font.Name = "Times New Roman"

        styleIndex = styles.Add()
        Dim styleDescription As Style = styles(styleIndex)
        styleDescription.Font.Name = "Times New Roman"
        styleDescription.Font.Color = Color.Blue
        styleDescription.Font.IsItalic = True

        styleIndex = styles.Add()
        Dim styleProductName As Style = styles(styleIndex)
        styleProductName.Font.IsBold = True

        styleIndex = styles.Add()
        Dim styleTitle As Style = styles(styleIndex)
        styleTitle.Font.IsBold = True
        styleTitle.Font.IsItalic = True
        styleTitle.ForegroundColor = Color.LightGray

        styleIndex = styles.Add()
        Dim styleNumber As Style = styles(styleIndex)
        styleNumber.Number = 8

        Dim hPageBreaks As HorizontalPageBreakCollection = sheet.HPageBreaks

        Dim cmdText As String = "SELECT ProductName, ProductID, QuantityPerUnit, UnitPrice FROM Products"
        Dim i As Integer
        For i = 0 To Me.dataTable1.Rows.Count - 1
            currentRow += 2
            cells.SetRowHeight(currentRow, 20)
            cells(currentRow, 1).SetStyle(styleCategoryName)
            Dim categoriesRow As DataRow = Me.dataTable1.Rows(i)

            'Write CategoryName
            cells(currentRow, 1).PutValue(CType(categoriesRow("CategoryName"), String))

            'Write Description
            currentRow = currentRow + 1
            cells(currentRow, 1).PutValue(CType(categoriesRow("Description"), String))
            cells(currentRow, 1).SetStyle(styleDescription)

            dataTable2.Clear()
            oleDbDataAdapter2.SelectCommand.CommandText = cmdText + " where categoryid = " _
                + categoriesRow("CategoryID").ToString()
            oleDbDataAdapter2.Fill(dataTable2)

            currentRow += 2
            cells.ImportDataTable(dataTable2, True, currentRow, 1)

            Dim range As range = cells.CreateRange(currentRow, 1, 1, 4)
            Dim styleFlag As styleFlag = New styleFlag
            styleFlag.All = True
            range.ApplyStyle(styleTitle, styleFlag)

            range = cells.CreateRange(currentRow + 1, 1, dataTable2.Rows.Count, 1)
            range.ApplyStyle(styleProductName, styleFlag)

            range = cells.CreateRange(currentRow + 1, 4, dataTable2.Rows.Count, 1)
            range.ApplyStyle(styleNumber, styleFlag)

            currentRow += dataTable2.Rows.Count
            hPageBreaks.Add(currentRow, 0)
        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 <> "Catalog" Then
                workbook.Worksheets.RemoveAt(i)
                i = i - 1
            End If

        Next

        Return workbook
    End Function

    Private Sub ReadCategory()
        Me.oleDbSelectCommand1.CommandText = "SELECT CategoryID, CategoryName, Description FROM Categories"
        Me.oleDbDataAdapter1.Fill(Me.dataTable1)

    End Sub

End Class