Customize the Appearance of Pivot Table Reports inside Android Apps

This technical tip shows how developers can customize the Appearance of Pivot Table Reports  inside their Android applications using Aspose.Cells for Android. Previously we 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 like Setting the AutoFormat and PivotTableStyle Types, Setting Format Options, Setting Row Column and Page Fields Format, Modify a Pivot Table Quick Style and Clearing PivotFields etc.

//Setting the AutoFormat and PivotTableStyle Type

//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 formats e.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.
PivotFieldCollection pivotFields = pivotTable.getRowFields();

//Accessing the first row field in the row fields.
PivotField pivotField = 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);

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

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

//Accessing the first data field in the data fields.
PivotField pivotField = 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
TableStyle ts = 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 you want to clear all the PivotFields in the areas e.g., page, column, row or data, you 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
PivotTableCollection pivotTables = 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, "Betrag Netto 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");