Code Samples for Customizing the Appearance of Pivot Ta

Tutorials Shared by the Internet Community

Advertisements

  Top Tutorials     New Tutorials     Submit     Login     Register  

Tutorial Detail

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
http://www.aspose.com/docs/display/cellsandroid/Customizing+the+Appearance+of+Pivot+Table+...
  • Total Hits: 11639

  • Average Rating :
    URL RatingURL RatingURL RatingURL RatingURL Rating

  •  

  • Total Votes: 175 votes

  • Category: Mobile Computing/Mobile Computing

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

  • Submitted By: Katherine Johanson

  • Set as Favorite

Description:

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
pivotTable.setAutoFormat(true);
//Setting the PivotTable atuoformat type.
pivotTable.setAutoFormatType(PivotTableAutoFormatType.CLASSIC);

//Setting the PivotTable's Styles for Excel 2007/2010 formatse.g XLSX.
pivotTable.setPivotTableStyleType(PivotTableStyleType.PIVOT_TABLE_STYLE_LIGHT_1);


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.
pivotTable.addFieldToArea(PivotFieldType.DATA,2);

//Show grand totals for rows.
pivotTable.setRowGrand(true);

//Show grand totals for columns.
pivotTable.setColumnGrand(true);

//Display a custom string in cells that contain null values.
pivotTable.setDisplayNullString(true);
pivotTable.setNullString("null");

//Setting the layout
pivotTable.setPageFieldOrder(PrintOrderType.DOWN_THEN_OVER);


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.
pivotField.setSubtotals(PivotFieldSubtotalType.SUM,true);
pivotField.setSubtotals(PivotFieldSubtotalType.COUNT,true);

//Setting autosort options.
//Setting the field auto sort.
pivotField.setAutoSort(true);

//Setting the field auto sort ascend.
pivotField.setAscendSort(true);

//Setting the field auto sort using the field itself.
pivotField.setAutoSortField(-1);

//Setting autoShow options.
//Setting the field auto show.
pivotField.setAutoShow(true);

//Setting the field auto show ascend.
pivotField.setAscendShow(false);

//Setting the auto show using field(data field).
pivotField.setAutoShowField(0);



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
pivotField.setDataDisplayFormat(PivotFieldDataDisplayFormat.PERCENTAGE_OF);

//Setting the base field.
pivotField.setBaseField(1);

//Setting the base item.
pivotField.setBaseItem(PivotItemPosition.NEXT);

//Setting number format
pivotField.setNumber(10);


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();
font1.setColor(Color.getRed());
Style style2 = wb.createStyle();
com.aspose.cells.Font font2 = style2.getFont();
font2.setColor(Color.getBlue());
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);
e.setElementStyle(style1);
index = ts.getTableStyleElements().add(TableStyleElementType.GRAND_TOTAL_ROW);
e = ts.getTableStyleElements().get(index);
e.setElementStyle(style2);

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

//Save the file.
wb.save(sdPath + "/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
pivotTable.getDataFields().clear();

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

//Set the refresh data flag on
pivotTable.setRefreshDataFlag(false);

//Refresh and calculate the pivot table data
pivotTable.refreshData();
pivotTable.calculateData();

//Save the Excel file
workbook.save(sdPath + "/out1.xlsx");    More detail...

Rating Detail

 Stars  Percentage  Total Vote
 One star  Vote LeftVote MiddleVote Right 36.57%  64
 Two starsTwo star  Vote LeftVote MiddleVote Right 10.86%  19
 Three starsThree starsThree stars  Vote LeftVote MiddleVote Right 11.43%  20
 Four starsFour starsFour starsFour stars  Vote LeftVote MiddleVote Right 10.86%  19
 Five starsFive starsFive starsFive starsFive stars  Vote LeftVote MiddleVote Right 30.29%  53

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.






Advertisements