Encrypted Excel 2013 Files Reading & Justify Distributed Text Alignment in Android

What’s new in this release?

Aspose development team is pleased to announce the new release of Aspose.Cells for Android v5.0.0. This new release supports reading password protected Microsoft Excel 2013 files. We provide the Style.setJustifyDistributed() method so you can apply the justify distributed setting while applying text alignment settings on cells. We also provide support for the B3 (13.9 x 19.7 inches) and Business Card (90 mm x 55 mm) PaperSizeType options. We have added a number of enhancements and fixes in the new release. We have improved the chart to image and sheet to image modules. A few other improvements are also made to Aspose.Cells for Android’s formula calculation engine. We have fixed a few exceptions when reading and writing Microsoft Excel file formats too. This release includes plenty of new & enhanced features as listed below

• Support opening password protected MS Excel 2013 files
• Print comments on an Excel document
• Support Justify Distributed Alignment Setting
• Remove hardcode usage of com.sun.image.codec.jpeg.JPEGCodec
• Unhide Row with previously assigned height
• Axis.setTickLabelSpacing() is now working for XLS version
• Cell borders were rendered on opaque cells
• Less than equals to calculation error is fixed
• Problem with wildcard parsing in formula calculation is fixed
• Some columns are narrow to contain text properly is now fixed
• IStreamProvider.setCustomPath now have effect to file tabstrip.htm
• Extracting formatted Chinese Date issue is resolved
• getStringValue() returns #VALUE!
• Validation now working after converting to XLS format
• XLSX to PDF: Underline below the hyperlink is getting spread out to adjacent character positions is now fixed

Other most recent bug fixes are also included in this release.

Overview: Aspose.Cells for Android

Aspose.Cells for Android is a MS Excel spreadsheet component that allows programmer to develop android applications for reading, writing & manipulate Excel spreadsheets (XLS, XLSX, XLSM, SpreadsheetML, CSV, tab delimited) and HTML file formats without needing to rely on Microsoft Excel. It supports robust formula calculation engine, pivot tables, VBA, workbook encryption, named ranges, custom charts, spreadsheet formatting, drawing objects like images, OLE objects & importing or creating charts.

• Homepage of Aspose.Cells for Android
• DownloadAspose.Cells for Android

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.  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.

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 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);

Setting Data Fields Format

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");

Overview: Aspose.Cells for Android

Aspose.Cells for Android is a MS Excel spreadsheet component that allows programmer to develop android applications for reading, writing & manipulate Excel spreadsheets (XLS, XLSX, XLSM, SpreadsheetML, CSV, tab delimited) and HTML file formats without needing to rely on Microsoft Excel. It supports robust formula calculation engine, pivot tables, VBA, workbook encryption, named ranges, custom charts, spreadsheet formatting, drawing objects like images, OLE objects & importing or creating charts.

• Homepage of Aspose.Cells for Android
• Download Aspose.Cells for Android