Aspose Demos > Java Components > Aspose.Cells for Java > Java > QuickStart > Pivot Table > Pivot Table

Pivot Table - Aspose.Cells

This online demo exhibits how to set the appearance of a Pivot Table in a worksheet using Aspose.Cells for Java .

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.

The demo sets the appearance of a pivot table in a worksheet. You can either open the resultant excel file into MS Excel or save directly to your disk.

ClickExecute to see how demo can set the appearance properties of a pivot table.

JAVA

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20


package com.aspose.cells.demos.Workbooks.PivotTable;

import com.aspose.cells.*;
import com.aspose.cells.demos.Demo;

public class AddPivotTable extends BasePivot
{
    public void execute()
    {
        try
        {
            CreatePivotTable("PivotTable.xls");
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
        }
    }
}








		


JAVA



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
134
135
136
137
138
139
140
141
142


package com.aspose.cells.demos.Workbooks.PivotTable;

import java.io.IOException;
import java.io.PrintWriter;
import java.io.OutputStream;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.Random;
import java.util.TimeZone;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.aspose.cells.*;
import com.aspose.cells.demos.Demo;

public abstract class BasePivot extends Demo
{
/*
   public void doGet(HttpServletRequest request, HttpServletResponse response)
      throws IOException, ServletException
   {
      String fileName = response.getOutputStream();
      try {
         String command = request.getParameter("_command");

         ServletContext sc = getServletContext();
         String db_url = DB_URL_P1 + sc.getRealPath(DB_URL_P2);
         String template_file_path = sc.getRealPath(TEMPLATE_FILE_PATH_PART);
         Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
         if(command.equals("ColumnChart")) {
            setResponseHeader(response, "ColumnChart.xls");
            CreateColumnChart(out);
         }else if(command.equals("LineChart")){
            setResponseHeader(response, "LineChart.xls");
            CreateLineChart(out);
         }else if(command.equals("StockChart")){
            setResponseHeader(response, "StockChart.xls");
            CreateStockChart(out);
         }else if(command.equals("PivotTable")){
            setResponseHeader(response, "PivotTable.xls");
            CreatePivotTable(out);
         }
      } catch(Exception x) {
         x.printStackTrace();

         // say sorry
         response.setContentType("text/html");
         PrintWriter writer = new PrintWriter(out);

         writer.println("<html>");
         writer.println("<head>");
         writer.println("<title>sorry</title>");
         writer.println("</head>");
         writer.println("<body>");
         writer.println("<h1>Sorry, the demo programme failed for some reason.</h1>");
         x.printStackTrace(writer);
         writer.println("</body>");
         writer.println("</html>");
         writer.close();
      }
   }
*/

 

   public void CreatePivotTable(String fileName) throws IOException
   {
      Workbook workbook = new Workbook();
      Worksheet sheet = workbook.getWorksheets().getSheet(0);
      Cells cells = sheet.getCells();
      Cell cell = cells.getCell("A1");
      cell.setValue("Sport");
      cell = cells.getCell("B1");
      cell.setValue("Quarter");
      cell = cells.getCell("C1");
      cell.setValue("Sales");


      cell = cells.getCell("A2");
      cell.setValue("Golf");
      cell = cells.getCell("A3");
      cell.setValue("Golf");
      cell = cells.getCell("A4");
      cell.setValue("Tennis");
      cell = cells.getCell("A5");
      cell.setValue("Tennis");
      cell = cells.getCell("A6");
      cell.setValue("Tennis");
      cell = cells.getCell("A7");
      cell.setValue("Tennis");
      cell = cells.getCell("A8");
      cell.setValue("Golf");

      cell = cells.getCell("B2");
      cell.setValue("Qtr3");
      cell = cells.getCell("B3");
      cell.setValue("Qtr4");
      cell = cells.getCell("B4");
      cell.setValue("Qtr3");
      cell = cells.getCell("B5");
      cell.setValue("Qtr4");
      cell = cells.getCell("B6");
      cell.setValue("Qtr3");
      cell = cells.getCell("B7");
      cell.setValue("Qtr4");
      cell = cells.getCell("B8");
      cell.setValue("Qtr3");

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

      PivotTables pivotTables = sheet.getPivotTables();
      int index = pivotTables.add("=A1:C8","E3","PivotTable1");
      PivotTable pivotTable = pivotTables.get(index);
      pivotTable.addFieldToArea(PivotFieldType.ROW,0);
      pivotTable.addFieldToArea(PivotFieldType.COLUMN,1);
      pivotTable.addFieldToArea(PivotFieldType.DATA,2);
      pivotTable.setSaveData(true);

      setResponse(fileName, workbook);
   }

    
    
}








		


JAVA



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
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333


//////////////////////////////////////////////////////////////////////////
// Copyright 2008 Aspose Pty Ltd. All Rights Reserved.
//
// This file is part of Aspose Demos for Java. 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.
//////////////////////////////////////////////////////////////////////////
package com.aspose.cells.demos;

import java.io.*;
import java.net.URL;
import java.sql.*;

import com.aspose.cells.Workbook;
import com.aspose.cells.WorkbookDesigner;
import com.aspose.cells.License;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.faces.context.FacesContext;

///////////////////////
import java.text.SimpleDateFormat;
import com.icesoft.faces.context.ByteArrayResource;
import com.icesoft.faces.context.Resource;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.Date;
import java.util.Locale;
import java.lang.reflect.*;

/**
 * Base class for Aspose.Cells.Demos classes that execute specific demos.
 */
public abstract class Demo
{
   private static final String DB_URL_P1 = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=";
   private static final String DB_URL_P2 = "/WEB-INF/Database/Northwind.mdb";

   protected String TEMPLATE_FILE_PATH_PART = "/WEB-INF/Designer/Northwind.xls";
   protected Connection connection = null;

   public Demo() {
        FacesContext context = FacesContext.getCurrentInstance();
        HttpServletRequest request = (HttpServletRequest) context.getExternalContext().getRequest();

        // TODO: Consider using streams
        try {
            TEMPLATE_FILE_PATH_PART = getFileName(request, TEMPLATE_FILE_PATH_PART);
            initLicense(new License());
        }catch(Exception e) {
            e.printStackTrace();
        }
   }

    /**
     * Utility function that creates a connection to the Database.
     */
    private Connection createConnection()
    {
        try 
        {
            if (null!=connection) return connection;

            // Get absolute path
            FacesContext context = FacesContext.getCurrentInstance();
            HttpServletRequest request = (HttpServletRequest) context.getExternalContext().getRequest();

            String dbName = request.getRealPath(DB_URL_P2).replace("\\", "/");
            //System.out.println("db: " + dbName);

            //Load a DB driver that is used by the demos
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String connectionString = "jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" + dbName;// + ";UID=Admin"
            connection = DriverManager.getConnection(connectionString, "", "");

            //Class.forName("net.sourceforge.jtds.jdbc.Driver");
            //String connectionString = "jdbc:jtds:sqlserver://localhost:1433/northwind";
            //connection = DriverManager.getConnection(connectionString, "northwind", "northwind");
        } catch (Exception e) {
            System.out.println("openDBConnection: " + e.toString());
        }

        return connection;
    }

    /**
     * Returns SQL dialect-specific date format (MS Access vs MS SQL Server)
     */
    protected static String getDate(Integer year, Integer month, Integer day) {
        return "#" + day.toString() + "/" + month.toString() + "/" + year.toString() + "#";

        //java.text.DecimalFormat nft = new java.text.DecimalFormat("00");
        //return "'" + year.toString() + "-" + nft.format(month) + "-" + nft.format(day) + "'";
    }

    /**
     * Returns SQL dialect-specific quarter formatting method
     */
    protected static String getQuarter(String fieldName) {
        return " FORMAT(" + fieldName +  ", 'yyyy/Q') ";
        //return " (convert(nvarchar(4), datepart(yyyy, " + fieldName + ")) + '/' + convert(nvarchar(4), datepart(qq, " + fieldName + "))) ";
    }

    /**
     * Returns SQL dialect-specific boolean formatting method
     */
    protected static String getBool(boolean flag) {
        return flag ? "Yes" : "No";
        //return flag ? "1" : "0";
    }

    /**
     * Returns SQL dialect-specific boolean formatting method
     */
    protected static String getDistinct() {
        return "DISTINCTROW";
        //return "DISTINCT";
    }

    /**
     * Utility function that creates a connection to the Database.
     */
    protected Statement createStatement()
    {
        return createStatement(true);
    }

    /**
     * Utility function that creates a connection to the Database.
     */
    protected Statement createStatement(boolean Scrollable)
    {
        try 
        {
            // Get absolute path
            Connection conn = createConnection();
            //if (Scrollable)
            //{
            //  return conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            //}else
            Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            //System.out.println("createStatement - returning: " + stmt);
            return stmt;

        } catch (Exception e) {
            System.out.println("createStatement: " + e.toString());
        }

        return null;
    }

    /**
     * Utility function that creates a connection to the Database.
     */
    protected PreparedStatement prepareStatement(String stmt)
    {
        try 
        {
            Connection conn = createConnection();
            PreparedStatement pstmt = conn.prepareStatement(stmt
            //ResultSet.TYPE_SCROLL_INSENSITIVE
                ,ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            //System.out.println("prepareStatement - returning: " + pstmt);
            return pstmt;

        } catch (Exception e) {
            System.out.println("prepareStatement: " + e.toString());
        }

        return null;
    }

    protected void closeConnection() throws SQLException
    {
        if (null!=connection) {
             connection.close();
             connection = null;
        }
    }

    protected String getFileName(HttpServletRequest request, String absoluteName)
        throws java.io.FileNotFoundException
    {
        URL url = getClass().getClassLoader().getResource(absoluteName);
        if (null==url && null!=request)
        {
            String path =
                request.getRealPath(absoluteName).replace("\\", "/");

            //System.err.println("Failed to load " + absoluteName);
            //System.err.println("Loading file from " + path);

            return path;
        }

        String path = new File(url.getPath()).getAbsolutePath();
        //return url.toString();
        return path;
    }

    protected InputStream getFile(HttpServletRequest request, String absoluteName)
        throws java.io.FileNotFoundException
    {
        InputStream is = getClass().getClassLoader().getResourceAsStream(absoluteName);
        if (null==is && null!=request)
        {
            String path =
                request.getRealPath(absoluteName).replace("\\", "/");

            System.err.println("Failed to load " + absoluteName);
            System.err.println("Loading file from " + path);
            is = new FileInputStream(path);
        }

        return is;
    }

    /**
     * Executes the demo and returns a generated document. Implement in the derived classes.
     */
    public abstract void execute() throws Exception;

    protected final String FILE_NAME = "result.xls";

    public Resource getGeneratedfile() {
        byte[] res = getResult();
        if (null==res || res.length<1) {
            return null;
        }
        return new ByteArrayResource(res);
    }

    private String fileName = FILE_NAME;
    public String getFilename() {
        return fileName;
    }

    private String respType = "application/vnd.ms-excel";
    public String getResptype() {
        return respType;
    }
    public void setResptype(String v) {
        respType = v;
    }

    public void setFilename(String v) {

        // NOTE that demos like Pivot Table demo just don't work with such file names
        // so we don't use date(time) stamp for cells demos

        //SimpleDateFormat formatter = new SimpleDateFormat ("hh-mm-ss-", Locale.US);
        //String curDate = formatter.format(new Date());
        //this.fileName = curDate + v;
        this.fileName = v;

        //System.out.println("Response: " + this.fileName + " (" + v + ")");
    }

    public  byte[] getResult()
    {
        return resp;
    }

    protected void setResponse(String fName, ByteArrayOutputStream out) throws IOException {
        setFilename(fName);
        resp = out.toByteArray();
        out.close();
        // outputStream.write(this.response);
    }
    protected void setResponse(String fName,byte[] out) throws IOException {
        setFilename(fName);
        resp = out;

    }

    protected byte[] resp;

    protected void setResponse(String fileName, Workbook w) throws IOException
    {
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        w.save(out);
        setResponse(fileName, out);
    }

    protected void setResponse(String fileName, WorkbookDesigner w) throws IOException
    {
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        w.save(out);
        setResponse(fileName, out);
    }

    /**
     *
     * Initializes license (if demo license is available).
     * you may insert your licensing code here that would use obj's
     * setLicense based on InputStream rather than passing it on
     * to 3rd-party class.
     *
     */
    public static void initLicense(Object obj) {
        try {

            Class myclass = Class.forName("com.aspose.demos.Common");
            if (null==myclass) {
                System.out.println("initLicense not found");
                return ;
            }

            //Use reflection to list methods and invoke them
            Method[] methods = myclass.getMethods();
            Object object = myclass.newInstance();

            Class partypes[] = new Class[1];
            partypes[0] = Object.class;
           
            Method meth = myclass.getMethod("initLicense", partypes);
            if (null!=meth) {

                    //System.out.println("Calling " + meth.getName());
                    Object arglist[] = new Object[1];
                    arglist[0] = obj;

                    meth.invoke(object, arglist);
            }
        } catch (ClassNotFoundException cnfe) {
            // Ignore
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}








		


XHTML



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


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:ice="http://www.icesoft.com/icefaces/component"
      xmlns:ui="http://java.sun.com/jsf/facelets"
      xmlns:h="http://java.sun.com/jsf/html"
      xmlns:f="http://java.sun.com/jsf/core"
      xmlns:c="http://java.sun.com/jstl/core"
      >

<ui:composition template="/WEB-INF/includes/templates/page-template.xhtml">
                        
    <ui:define name="pageTitle">
        Pivot Table - Aspose.Cells Demos
    </ui:define>

    <ui:define name="page-content">
        <ui:decorate template="/WEB-INF/includes/templates/tabbed_container.xhtml">
            <ui:define name="example">
              <table width="90%" align="center" cellspacing="0" cellpadding="0" border="0">
                <tr>
        <td width="19" valign="top"><img src="/Common/images/heading_lft.jpg" alt="" width="19" height="41" /></td>
        <td width="100%" class="demos-heading-bg"><h2 class="demos-heading-bg">&nbsp;<font face="Arial" size="4">Pivot 
          Table - Aspose.Cells</font></h2></td>
        <td width="19" valign="top"><img src="/Common/images/heading_rt.jpg" alt="" width="19" height="41" /></td>
      </tr>
    </table>
    <p align="left"> <font color="black" face="Arial" style="background-color: whitesmoke"> <span style="color: black; mso-fareast-font-family: 'Times New Roman';
                                        mso-ansi-language: EN-US; mso-fareast-language: EN-US; mso-bidi-language: AR-SA; background-color:white"> <font size="2">This online demo exhibits how to 
      set the appearance of&nbsp; 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"> Aspose.Cells</a> for Java</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"> <font color="black" face="Arial" style="background-color: whitesmoke" size="2"> <span style="color: black; mso-fareast-font-family: 'Times New Roman';
                                                    mso-ansi-language: EN-US; mso-fareast-language: EN-US; mso-bidi-language: AR-SA; background-color:white"> The demo sets the appearance of a pivot table in a worksheet. You can either open the resultant excel
      file into MS Excel or save directly to your disk.</span></font></p>
    <p class="componentDescriptionTxt">Click <b>Execute</b> to see how demo can  set the appearance properties of a pivot table.
    </p>
                <h:commandButton id="generate" action="#{pivotpivot.execute}" value="Execute" />

                <ice:outputResource id="outResource"
                    mimeType="application/vnd.ms-excel"
                    resource="#{pivotpivot.generatedfile}"
                    fileName="#{pivotpivot.filename}"
                    shared="false" />
            </ui:define>
        </ui:decorate>
    </ui:define>
        </ui:composition>
</html>