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

<%@ Page Language="C#" MasterPageFile="~/tpl/Demo.Master" AutoEventWireup="true"
    CodeBehind="pivot-table.aspx.cs" Inherits="Aspose.Cells.Demos.Pivot_Table"
    Title="Adding Pivot Table - Aspose.Cells Demos" %>

<asp:Content ID="Content" ContentPlaceHolderID="MainContent" runat="Server">
    <p class="componentDescriptionTxt">
        <span style="font-size: 10pt"></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">Pivot Table - 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>
        <font face="Arial" size="2">
            <p align="left">
                <font color="black" face="Arial" style="background-color: whitesmoke"><span style="color: black;
                    background-color: white; mso-fareast-font-family: 'Times New Roman'; mso-ansi-language: EN-US;
                    mso-fareast-language: EN-US; mso-bidi-language: AR-SA"><font size="2">This online demo
                        exhibits how to set the appearance of a Pivot Table in a worksheet</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">
                    <span style="color: #0000ff; text-decoration: underline">Aspose.Cells</span></a>
                    for .NET</font></font><span style="font-family: Arial"><font size="2">.</font></span></p>
            <p align="left">
                <span style="font-family: Arial"><font size="2">PivotTables can be added to the spreadsheets
                    using Aspose.Cells. Aspose.Cells provides some special set of classes that are used
                    to create and set the PivotTables. These classes are used to create and set PivotTable
                    Objects, which act as the building blocks of a PivotTable.</font></span></p>
            <p align="left">
            </p>
                <span style="font-family: Arial"><font size="2"></font></span>
    	Click <b>Process </b>
                to see how demo can set the appearance properties of a pivot table. </font>
        <span style="font-size: 10pt; font-family: Arial"> You can either open the resulting excel file into MS Excel
        or save directly to your disk. </span><p align="left">
            <span style="font-size: 10pt; font-family: Arial"></span>
    <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
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
122
123
124
125
126
127

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.Cells;
using Aspose.Cells.Pivot;
using System.Drawing;

namespace Aspose.Cells.Demos
{

    public partial class Pivot_Table : System.Web.UI.Page
    {
        protected System.Web.UI.WebControls.DropDownList ddlFileVersion;

        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnExecute_Click(object sender, EventArgs e)
        {
            CreateStaticReport();
        }

        public void CreateStaticReport()
        {
            //Instantiating an Workbook object
            Workbook workbook = new Workbook();

            //Obtaining the reference of the newly added worksheet
            Worksheet sheet = workbook.Worksheets[0];
            Cells cells = sheet.Cells;

            //Setting the value to the cells
            Cell cell = cells["A1"];
            cell.PutValue("Sport");
            cell = cells["B1"];
            cell.PutValue("Quarter");
            cell = cells["C1"];
            cell.PutValue("Sales");


            cell = cells["A2"];
            cell.PutValue("Golf");
            cell = cells["A3"];
            cell.PutValue("Golf");
            cell = cells["A4"];
            cell.PutValue("Tennis");
            cell = cells["A5"];
            cell.PutValue("Tennis");
            cell = cells["A6"];
            cell.PutValue("Tennis");
            cell = cells["A7"];
            cell.PutValue("Tennis");
            cell = cells["A8"];
            cell.PutValue("Golf");


            cell = cells["B2"];
            cell.PutValue("Qtr3");
            cell = cells["B3"];
            cell.PutValue("Qtr4");
            cell = cells["B4"];
            cell.PutValue("Qtr3");
            cell = cells["B5"];
            cell.PutValue("Qtr4");
            cell = cells["B6"];
            cell.PutValue("Qtr3");
            cell = cells["B7"];
            cell.PutValue("Qtr4");
            cell = cells["B8"];
            cell.PutValue("Qtr3");

            cell = cells["C2"];
            cell.PutValue(1500);
            cell = cells["C3"];
            cell.PutValue(2000);
            cell = cells["C4"];
            cell.PutValue(600);
            cell = cells["C5"];
            cell.PutValue(1500);
            cell = cells["C6"];
            cell.PutValue(4070);
            cell = cells["C7"];
            cell.PutValue(5000);
            cell = cells["C8"];
            cell.PutValue(6430);

            PivotTableCollection pivotTables = sheet.PivotTables;

            //Adding a PivotTable to the worksheet
            int index = pivotTables.Add("=A1:C8", "E20", "PivotTable1");

            //Accessing the instance of the newly added PivotTable
            PivotTable pivotTable = pivotTables[index];

            //Draging the first field to the row area.
            pivotTable.AddFieldToArea(PivotFieldType.Row, 0);

            //Draging the second field to the column area.
            pivotTable.AddFieldToArea(PivotFieldType.Column, 1);

            //Draging the third field to the data area.
            pivotTable.AddFieldToArea(PivotFieldType.Data, 2);

            if (ddlFileVersion.SelectedItem.Value == "XLS")
            {
                ////Save file and send to client browser using selected format
                workbook.Save(HttpContext.Current.Response, "PivotTable.xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
            }
            else
            {
                workbook.Save(HttpContext.Current.Response, "PivotTable.xlsx", ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsx));
            }

            //end response to avoid unneeded html
            HttpContext.Current.Response.End();   
        }
    }
}