Code Samples for Customizing the Appearance of Pivot Ta

Tutorials Shared by the Internet Community


  Top Tutorials     New Tutorials     Submit     Login     Register  

Code Samples for Customizing the Appearance of Pivot Ta

This technical tip shows how developers can customize the Appearance of Pivot Table Reports inside Android application using Aspose.Cells for Android
  • Total Hits: 14384

  • Average Rating :
    URL RatingURL RatingURL RatingURL RatingURL Rating


  • Total Votes: 181 votes

  • Category: Mobile Computing/Mobile Computing

  • Submitted on: 2014-02-20 01:06:41

  • Submitted By: Katherine Johanson

  • Set as Favorite


This technical tip shows how developers can customize the Appearance of Pivot Table Reports inside their Android applications. Previously they have shown how to create a simple pivot table. This article further goes and discusses how to customize the appearance of a pivot table by setting its properties, Setting Data Fields Format etc.

Setting the AutoFormat and PivotTableStyle Types

The code example that follows illustrates how to set the auto format type and the pivot table style using the AutoFormat and PivotTableStyle properties.

//Setting the PivotTable report is automatically formatted for Excel 2003 formats
//Setting the PivotTable atuoformat type.

//Setting the PivotTable's Styles for Excel 2007/2010 formatse.g XLSX.

Setting Format Options

The code sample that follows illustrates how to set a number of pivot table formatting options, including adding grand totals for rows and columns.

//Dragging the third field to the data area.

//Show grand totals for rows.

//Show grand totals for columns.

//Display a custom string in cells that contain null values.

//Setting the layout

Setting Row, Column, and Page Fields Format

The code example that follows shows how to access row fields, access a particular row, set subtotals, apply automatic sorting, and using the autoShow option.

//Accessing the row fields.
PivotFieldCollectionpivotFields = pivotTable.getRowFields();

//Accessing the first row field in the row fields.
PivotFieldpivotField = pivotFields.get(0);

//Setting Subtotals.

//Setting autosort options.
//Setting the field auto sort.

//Setting the field auto sort ascend.

//Setting the field auto sort using the field itself.

//Setting autoShow options.
//Setting the field auto show.

//Setting the field auto show ascend.

//Setting the auto show using field(data field).

Setting Data Fields Format

The following lines of code illustrate how to format data fields.

//Accessing the data fields.
PivotFieldCollectionpivotFields = pivotTable.getDataFields();

//Accessing the first data field in the data fields.
PivotFieldpivotField = pivotFields.get(0);

//Setting data display format

//Setting the base field.

//Setting the base item.

//Setting number format

Modify a Pivot Table Quick Style

The code examples that follow show how to modify the quick style applied to a pivot table.

File sdDir = Environment.getExternalStorageDirectory();
String sdPath = sdDir.getCanonicalPath();

//Open the template file containing the pivot table.
Workbook wb = new Workbook(sdPath + "/Template.xlsx");

//Add pivot table style
Style style1 = wb.createStyle();
com.aspose.cells.Font font1 = style1.getFont();
Style style2 = wb.createStyle();
com.aspose.cells.Font font2 = style2.getFont();
int i = wb.getWorksheets().getTableStyles().addPivotTableStyle("tt");

//Get and Set the table style for different categories
TableStylets = wb.getWorksheets().getTableStyles().get(i);
int index = ts.getTableStyleElements().add(TableStyleElementType.FIRST_COLUMN);
TableStyleElement e = ts.getTableStyleElements().get(index);
index = ts.getTableStyleElements().add(TableStyleElementType.GRAND_TOTAL_ROW);
e = ts.getTableStyleElements().get(index);

//Set Pivot Table style name
PivotTable pt = wb.getWorksheets().get(0).getPivotTables().get(0);
pt.setPivotTableStyleName ("tt");

//Save the file. + "/OutputFile.xlsx");

Clearing PivotFields

PivotFieldCollection has a method named clear() for the task. When user want to clear all the PivotFields in the areas e.g., page, column, row or data, user can use it.
The code sample below shows how to clear all the PivotFields in data area.

File sdDir = Environment.getExternalStorageDirectory();
String sdPath = sdDir.getCanonicalPath();

//Open the template file containing the pivot table.
Workbook workbook = new Workbook(sdPath + "/PivotTable.xlsx");

//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);

//Get the pivot tables in the sheet
PivotTableCollectionpivotTables = sheet.getPivotTables();

//Get the first PivotTable
PivotTable pivotTable = pivotTables.get(0);

//Clear all the data fields

//Add new data field
pivotTable.addFieldToArea(PivotFieldType.DATA, "BetragNetto FW");

//Set the refresh data flag on

//Refresh and calculate the pivot table data

//Save the Excel file + "/out1.xlsx");    More detail...

Rating Detail

 Stars  Percentage  Total Vote
 One star  Vote LeftVote MiddleVote Right 36.46%  66
 Two starsTwo star  Vote LeftVote MiddleVote Right 11.05%  20
 Three starsThree starsThree stars  Vote LeftVote MiddleVote Right 11.6%  21
 Four starsFour starsFour starsFour stars  Vote LeftVote MiddleVote Right 11.05%  20
 Five starsFive starsFive starsFive starsFive stars  Vote LeftVote MiddleVote Right 29.83%  54

Caste Your Vote

 Rating  Selection  Guidelines
 Poor:     Very disappointing, useless.
 Fair:     Below average, disappointing but not useless.
 Good:     About average in its class.
 Very Good:     Above average for its category.
 Excellent:     One of the best Tutorials in its category.