1
2
3
4
5
6
7
8
9
10
11

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SalesByCategoryForm.aspx.cs" Inherits="Aspose.Pdf.Demos.NorthwindForms.SalesByCategoryForm" MasterPageFile="~/tpl/Demo.Master"
Title="Sales by Category - Aspose.Pdf Demos"%>
<asp:Content ID="Content" ContentPlaceHolderID="MainContent" runat="server">
    <p class="componentDescriptionTxt">
    	Click <b>Process</b> to see how example 
    	generates document with sales information (by category) based on Northwind database data
        <br/>You can either open the resulting PDF file into your PDF reader
        or save directly to your disk.
    </p>
    <asp:Button ID="btnProcess" runat="server" Text="Process" OnClick="btnProcess_Click" />
</asp:Content>

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

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Aspose.Pdf.Generator;

namespace Aspose.Pdf.Demos.NorthwindForms
{
    public partial class SalesByCategoryForm : BasePage
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }

        protected void btnProcess_Click(object sender, EventArgs e)
        {
            string path = GetResource(string.Empty);    // Database is under RESOURCES
            Aspose.Pdf.Generator.Pdf pdf;
            SalesByCategory salesByCategory = new SalesByCategory(path);
            pdf = salesByCategory.GetSalesByCategory();
            pdf.Save("salesbycategory.pdf", SaveType.OpenInAcrobat, Response);
            Response.End();
        }
    }
}

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

/////////////////////////////////////////////////////////////////////////
// Copyright (C) 2002-2011 Aspose Pty Ltd.  All rights reserved.
//
// This file is part of Aspose.Pdf. The source code in this file 
// is only intended as a supplement to the documentation, and is provided 
// "as is", without warranty of any kind, either expressed or implied.
/////////////////////////////////////////////////////////////////////////

using System;
using Aspose.Pdf.Generator;
using System.Globalization;
using System.Data;
using System.Data.OleDb;


namespace Aspose.Pdf.Demos
{
	/// <summary>
	/// Summary description for SalesByCategory.
	/// </summary>
	public class SalesByCategory
	{
		private OleDbCommand oleDbSelectCommand1;
		private OleDbDataAdapter oleDbDataAdapter1;
		private OleDbConnection oleDbConnection;
		private DataTable dataTable1;
		private System.Data.OleDb.OleDbDataReader resultReader;
		string path;
		NumberFormatInfo format;

		public SalesByCategory(string curPath)
		{
			path = curPath;
			format = new NumberFormatInfo();
			format.CurrencyPositivePattern = 0;
			format.CurrencySymbol = "$";
			this.oleDbConnection = new System.Data.OleDb.OleDbConnection();
			this.oleDbConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + 
				"\\Database\\Northwind.mdb";
			this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();
			this.oleDbDataAdapter1 = new OleDbDataAdapter();
			this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1;
			this.oleDbSelectCommand1.Connection = this.oleDbConnection;
			this.oleDbConnection.Open();
		}

        public Aspose.Pdf.Generator.Pdf GetSalesByCategory()
		{
			string queryString ="SELECT DISTINCTROW Categories.CategoryID, Categories.CategoryName, "+
				"Products.ProductName as Product, Sum([Order Details Extended].ExtendedPrice) AS Sales " +
				"FROM Categories INNER JOIN (Products INNER JOIN (Orders INNER JOIN [Order Details Extended] "+
				"ON Orders.OrderID=[Order Details Extended].OrderID) ON Products.ProductID=[Order " + 
				"Details Extended].ProductID) ON Categories.CategoryID=Products.CategoryID WHERE " + 
				"(((Orders.OrderDate) Between #1/1/1997# And #12/31/1997#)) GROUP BY Categories.CategoryID, " + 
				"Categories.CategoryName, Products.ProductName ORDER BY Products.ProductName";

            Aspose.Pdf.Generator.Pdf pdf = new Aspose.Pdf.Generator.Pdf();
			pdf.IsTruetypeFontMapCached = false;

			// If you have purchased a license,
			// Set license like this: 
			// string licenseFile = MapPath("License") + "\\Aspose.Pdf.lic"; 
			// Aspose.Pdf.License lic = new Aspose.Pdf.License();
			// lic.SetLicense(licenseFile);
			
			string xmlFile = path + "\\Xml\\SalesByCategory.xml";
			pdf.BindXML(xmlFile, null);

			Section section = pdf.Sections["section1"];

            Aspose.Pdf.Generator.Text categoryName;

			int i;
			for(i=1; i<=8; i++)
			{
				this.oleDbSelectCommand1.CommandText = "SELECT Categories.CategoryName FROM (" + queryString + ") WHERE Categories.CategoryID=" + 
					i.ToString() + ";";
				resultReader = this.oleDbSelectCommand1.ExecuteReader();
				resultReader.Read();
                categoryName = new Aspose.Pdf.Generator.Text(section, Convert.ToString(resultReader.GetValue(0)));
				categoryName.Segments[0].TextInfo.FontSize = 20;
				categoryName.IsFirstParagraph = (i!=1)?true:false;
                categoryName.TextInfo.BackgroundColor = new Aspose.Pdf.Generator.Color(160);
				section.Paragraphs.Add(categoryName);
				resultReader.Close();

				this.oleDbSelectCommand1.CommandText = "SELECT Product,Sales FROM (" + queryString + 
					") WHERE Categories.CategoryID = " + i.ToString() + ";";
				dataTable1 = new DataTable();
				this.oleDbDataAdapter1.Fill(dataTable1);

				Table tab1 = new Table(section);
				section.Paragraphs.Add(tab1);
				tab1.Margin.Top = 20;
				tab1.ColumnWidths = "250 100";
				tab1.DefaultCellPadding.Top = tab1.DefaultCellPadding.Bottom = 3;
				tab1.DefaultCellTextInfo.FontSize = 14;
				tab1.ImportDataTable(dataTable1,true,0,0);

				foreach(Row curRow in tab1.Rows)
				{
					if(tab1.Rows.IndexOf(curRow) != 0)
					{
                        double d = Convert.ToDouble(((Aspose.Pdf.Generator.Text)curRow.Cells[1].Paragraphs[0]).Segments[0].Content);
                        ((Aspose.Pdf.Generator.Text)curRow.Cells[1].Paragraphs[0]).Segments[0].Content = d.ToString("C", format);
					}
				}

				Row firstRow = tab1.Rows[0];
                firstRow.Border = new BorderInfo((int)BorderSide.Bottom, new Aspose.Pdf.Generator.Color(204));
				foreach(Cell curCell in firstRow.Cells)
                    ((Aspose.Pdf.Generator.Text)curCell.Paragraphs[0]).Segments[0].TextInfo.FontSize = 18;
			}
			
			this.oleDbDataAdapter1.Dispose();
			this.oleDbConnection.Close();
			return pdf;
		}
	}
}