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="summary-by-quarter-form.aspx.vb"
    Inherits="Aspose.Cells.Demos.VisualBasic.SummaryByQuarterForm" MasterPageFile="~/tpl/Demo.Master"
    Title="Summary of Sales by Quarter - 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">Summary By Quarter - 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>
        <font face="Arial"><span style="color: black"><font size="2">This online demo illustrates
            how to create a well formatted Summary by Quarter 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>
        <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>
        <span style="color: black"><font face="Arial" size="2">The demo generates a </font></span>
        <font size="2"><font face="Arial">summary report showing sales from multiple years for
            each quarter</font><span style="color: black"><font face="Arial">. ADO.NET is used to
                retrieve the data from the Orders 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 summary report showing sales from multiple years for each quarter.
        <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 SummaryByQuarterForm
    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 = "SummaryByQuarter"
        'Create Summary By Quarter
        Dim SummaryByQuarter As SummaryByQuarter = New SummaryByQuarter(path)
        workbook = SummaryByQuarter.CreateSummaryByQuarter()
        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

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

    Public Function CreateSummaryByQuarter() As Workbook

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

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


        Me.oleDbSelectCommand1.CommandText = "SELECT 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("Sheet13")
        sheet.Name = "Summary By Quarter"
        Dim cells As cells = sheet.Cells

        Dim quarterSummary() As DataTable = New DataTable(4) {}
        Dim i As Integer
        For i = 0 To 3
            quarterSummary(i) = New DataTable()
            quarterSummary(i).Columns.Add("YearOrQuarter", Type.GetType("System.Int32"))
            quarterSummary(i).Columns.Add("Orders", Type.GetType("System.Int32"))
            quarterSummary(i).Columns.Add("Sales", Type.GetType("System.Decimal"))
        Next

        For i = 0 To Me.dataTable1.Rows.Count - 1
            Dim strQuarter As String = CType(Me.dataTable1.Rows(i)("Quarter"), String)
            Dim quarter As Integer = Integer.Parse(strQuarter.Substring(strQuarter.Length - 1))
            Dim row As DataRow = quarterSummary(quarter - 1).NewRow()
            row("YearOrQuarter") = Integer.Parse(strQuarter.Substring(0, 4))
            row("Sales") = Me.dataTable1.Rows(i)("Sales")
            row("Orders") = Me.dataTable1.Rows(i)("Orders")
            quarterSummary(quarter - 1).Rows.Add(row)
        Next

        For i = 0 To 3
            workbook.Replace("&summary" + (i + 1).ToString(), quarterSummary(i))
        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 <> "Summary By Quarter" Then
                workbook.Worksheets.RemoveAt(i)
                i = i - 1
            End If
        Next

        Return workbook

    End Function
End Class