| |
 |
Summary of Sales by Year - Aspose.Cells
|
 |
This online demo illustrates
how to create a well formatted Summary of Sales by Year report
using
Aspose.Cells for .NET.
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.
The demo generates a
summary report showing quarterly sales for each year. ADO.NET is used to retrieve the data from the
Orders table of Northwind database, to generate the report.
You can either open the resultant excel file into MS Excel
or save directly to your disk to check the results.
| ASP.NET |
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="summary-by-year-form.aspx.vb"
Inherits="Aspose.Cells.Demos.VisualBasic.SummaryByYearForm" MasterPageFile="~/tpl/Demo.Master"
Title="Summary of Sales by Year - Apose.Cells Demos" %>
<asp:Content ID="Content" ContentPlaceHolderID="MainContent" runat="server">
<p class="componentDescriptionTxt">
<span style="font-size: 10pt; font-family: Arial"></span> </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" valign="top" width="100%">
<h2 class="demos-heading-bg">
<font face="Arial" size="4">Summary of Sales by Year - 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 of 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/.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 quarterly sales for each year.</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></SPAN></p>
<p>
<span style="font-size: 10pt; font-family: Arial"><font face="Arial" size="2"></font>
</span>
<asp:Button ID="btnExecute" runat="server" Text="Process" OnClick="btnExecute_Click" /> </p>
</asp:Content>
|
| Visual Basic |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
Imports Aspose.Cells
Public Class SummaryByYearForm
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 = "SummaryByYear"
'Create Summary By Year
Dim SummaryByYear As SummaryByYear = New SummaryByYear(path)
workbook = SummaryByYear.CreateSummaryByYear()
workbook.Save(param + ".xls", SaveType.OpenInExcel, FileFormatType.Excel97To2003, Me.Response)
' End response to avoid unneeded HTML after XLS
Response.End()
End Sub
End Class
|
| Visual Basic |
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
|
Public Class SummaryByYear
Inherits DbBase
Public Sub New(ByVal path As String)
MyBase.New(path)
End Sub
Public Function CreateSummaryByYear() 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("Sheet14")
sheet.Name = "Summary By Year"
Dim cells As cells = sheet.Cells
Dim yearSummary() As DataTable = New DataTable(3) {}
Dim i As Integer
For i = 0 To 2
yearSummary(i) = New DataTable()
yearSummary(i).Columns.Add("YearOrQuarter", Type.GetType("System.Int32"))
yearSummary(i).Columns.Add("Orders", Type.GetType("System.Int32"))
yearSummary(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 year As Integer = Integer.Parse(strQuarter.Substring(0, 4)) - 1994
Dim row As DataRow = yearSummary(year).NewRow()
row("YearOrQuarter") = Integer.Parse(strQuarter.Substring(strQuarter.Length - 1))
row("Sales") = Me.dataTable1.Rows(i)("Sales")
row("Orders") = Me.dataTable1.Rows(i)("Orders")
yearSummary(year).Rows.Add(row)
Next
For i = 0 To 2
workbook.Replace("&summary" + (i + 1).ToString(), yearSummary(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 Year" Then
workbook.Worksheets.RemoveAt(i)
i = i - 1
End If
Next
Return workbook
End Function
End Class
|
|
|
|