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

<%@ Page Language="C#" MasterPageFile="~/tpl/Demo.Master" AutoEventWireup="true"
    CodeBehind="setting-pivot-table-appearance.aspx.cs" Inherits="Aspose.Cells.Demos.Setting_Pivot_Table_Appearance"
    Title="Setting Pivot Table Appearance - 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">Setting Pivot Table Appearance - 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/file-format-components/aspose.cells-for-.net-and-java/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. Also, you can set different formatting options to set / customize the pivot table appearance.</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
128
129
130
131
132
133

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

namespace Aspose.Cells.Demos
{

    public partial class Setting_Pivot_Table_Appearance : 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);

            //Setting the PivotTable report is automatically formatted
            pivotTable.IsAutoFormat = true;

            //Setting the PivotTable atuoformat type.
            pivotTable.AutoFormatType = PivotTableAutoFormatType.Report1; 

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

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