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="sales-by-year-subreport-form.aspx.vb"
    Inherits="Aspose.Cells.Demos.VisualBasic.SalesByYearSubreportForm" MasterPageFile="~/tpl/Demo.Master"
    Title="Sales by Year SubReport - 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">Sales By Year SubReport - 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 Sales by Year report </font></span></font><font size="2">
                <span style="font-family: Arial">using </span><font face="Arial"><a href="http://www.aspose.com/categories/file-format-components/aspose.cells-for-.net-and-java/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">
        <span style="color: black"><font face="Arial" size="2">The demo generates a printed
            report displaying </font></span><font face="Arial" size="2">quarter totals in sub-report
                in group header and optionally prints detail records. It displays page header on
                pages that don't have a group header. <span style="color: black">ADO.NET is used to
                    retrieve the data from the Orders 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 subreport for Sales by Year report.
        <br/>
        </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

Imports Aspose.Cells
Public Class SalesByYearSubreportForm
    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 = "SalesByYearSubreport"
        'Create Sales By Year Subreport
        Dim SalesByYearSubreport As SalesByYearSubreport = New SalesByYearSubreport(path)
        workbook = SalesByYearSubreport.CreateSalesByYearSubreport()
        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

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

    Public Function CreateSalesByYearSubreport() As Workbook

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

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


        Me.oleDbSelectCommand1.CommandText = "SELECT DISTINCTROW COUNT(Orders.OrderID) AS Orders, " + _
            " SUM([Order Subtotals].Subtotal) AS Sales, FORMAT(ORDERS.SHIPPEDDATE, 'yyyy/Q') AS Quarter" + _
            " FROM Orders INNER JOIN [Order Subtotals] ON" + _
            " Orders.OrderID = [Order Subtotals].OrderID WHERE " + _
            " (orders.shippeddate IS NOT NULL) GROUP BY FORMAT(ORDERS.SHIPPEDDATE ,  'yyyy/Q')"
        Me.oleDbDataAdapter1.Fill(Me.dataTable1)

        Dim sheet As Worksheet = workbook.Worksheets("Sheet11")
        sheet.Name = "Sales By Year Subreport"
        Dim cells As cells = sheet.Cells

        Dim currentRow As Integer = 0
        Dim totalOrders As Integer = 0
        Dim totalSales As Decimal = 0.0
        Dim thisYear As String = ""
        SetSalesByYearSubreportStyles(workbook)
        Dim i As Integer
        For i = 0 To Me.dataTable1.Rows.Count - 1
            If i = 0 Then
                thisYear = Me.dataTable1.Rows(0)("Quarter").ToString().Substring(0, 4)
                CreateSalesByYearSubreportHeader(workbook, cells, 0, thisYear)
                CreateData(cells, 2, 0)
                totalOrders += CType(Me.dataTable1.Rows(0)("Orders"), Integer)
                totalSales += CType(Me.dataTable1.Rows(0)("Sales"), Decimal)
                currentRow = 3
            Else
                If thisYear = Me.dataTable1.Rows(i)("Quarter").ToString().Substring(0, 4) Then
                    CreateData(cells, currentRow, i)
                    totalOrders += CType(Me.dataTable1.Rows(i)("Orders"), Integer)
                    totalSales += CType(Me.dataTable1.Rows(i)("Sales"), Decimal)
                    currentRow = currentRow + 1
                    If i = Me.dataTable1.Rows.Count - 1 Then
                        CreateFooter(workbook, cells, currentRow, totalOrders, totalSales)
                    End If
                Else
                    CreateFooter(workbook, cells, currentRow, totalOrders, totalSales)
                    totalOrders = 0
                    totalSales = 0.0
                    currentRow = currentRow + 1
                    thisYear = Me.dataTable1.Rows(i)("Quarter").ToString().Substring(0, 4)
                    If i <> Me.dataTable1.Rows.Count - 1 Then
                        CreateSalesByYearSubreportHeader(workbook, cells, currentRow, thisYear)
                        currentRow += 2
                        CreateData(cells, currentRow, i)
                        totalOrders += CType(Me.dataTable1.Rows(i)("Orders"), Integer)
                        totalSales += CType(Me.dataTable1.Rows(i)("Sales"), Decimal)
                        currentRow = currentRow + 1
                    End If
                End If
            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 <> "Sales By Year Subreport" Then
                workbook.Worksheets.RemoveAt(i)
                i = i - 1
            End If
        Next
        Return workbook

    End Function

    Private Sub CreateFooter(ByVal workbook As Workbook, ByVal Cells As Cells, ByVal startRow As Integer, ByVal totalOrders As Integer, ByVal totalSales As Decimal)
        Dim style As style = workbook.Styles("Bold")
        Cells(startRow, 1).PutValue("Totals:")
        Cells(startRow, 1).Style = style
        Cells(startRow, 2).PutValue(totalOrders)
        Cells(startRow, 3).PutValue(CType(totalSales, Double))
    End Sub

    Private Sub CreateData(ByVal Cells As Cells, ByVal startRow As Integer, ByVal index As Integer)
        Cells(startRow, 1).PutValue(Integer.Parse(Me.dataTable1.Rows(index)("Quarter").ToString().Substring(5)))
        Cells(startRow, 2).PutValue(CType(Me.dataTable1.Rows(index)("Orders"), Integer))
        Cells(startRow, 3).PutValue(CType(CType(Me.dataTable1.Rows(index)("Sales"), Decimal), Double))
    End Sub

    Private Sub SetSalesByYearSubreportStyles(ByVal workbook As Workbook)
        Dim styleIndex As Integer = workbook.Styles.Add()
        Dim style As style = workbook.Styles(styleIndex)
        style.Font.IsBold = True
        style.Name = "Bold"
    End Sub

    Private Sub CreateSalesByYearSubreportHeader(ByVal workbook As Workbook, ByVal Cells As Cells, ByVal startRow As Integer, ByVal year As String)
        Dim style As style = workbook.Styles("Bold")
        Cells(startRow, 0).PutValue(year + " Summary")
        Cells(startRow + 1, 1).PutValue("Quarter:")
        Cells(startRow + 1, 2).PutValue("Orders Shipped:")
        Cells(startRow + 1, 3).PutValue("Sales:")
        Cells(startRow, 0).Style = style
        Cells(startRow + 1, 1).Style = style
        Cells(startRow + 1, 2).Style = style
        Cells(startRow + 1, 3).Style = style
    End Sub

End Class