Support for ActiveX Controls, Filter Data Type while Loading Spreadsheets & much more with Aspose.Cells for Android 8.9.0

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

Aspose.Cells for Android logoWe are pleased to announce the release of Aspose.Cells for Android 8.9.0. This release includes a number of new features, enhancements and bug fixes that further improve the overall stability and usability of the API. Please check the release notes in order to get an idea about what is new and what has been fixed with this revision of Aspose.Cells for Android. If you are planning to upgrade the API from any previous version, we strongly suggest you to check the Public API Changes section to know what has been changed since your current revision of the API.

While you are downloading the latest build, here is a look at the biggest features in this release.

Support for ActiveX Controls

Aspose.Cells APIs have provided the ability to add & manipulate the ActiveX Controls in spreadsheets. Aspose.Cells for Android API has exposed a number of useful classes, enumerations and methods to fully support the ActiveX Controls. The most worth mentioning addition to the public API is the ShapeCollection.addActiveXControl method which allows to add an ActiveX Control to the collection of shapes by accepting 7 parameters to specify the control type, location to place the control and size of the control. The control type can be specified using the ControlType enumeration with following possible values.

  • ControlType.CHECK_BOX
  • ControlType.COMBO_BOX
  • ControlType.COMMAND_BUTTON
  • ControlType.IMAGE
  • ControlType.LABEL
  • ControlType.LIST_BOX
  • ControlType.RADIO_BUTTON
  • ControlType.SCROLL_BAR
  • ControlType.SPIN_BUTTON
  • ControlType.TEXT_BOX
  • ControlType.TOGGLE_BUTTON
  • ControlType.UNKNOWN

Below provided code snippet demonstrates the simple usage scenario of newly exposed ShapeCollection.addActiveXControl method to add an ActiveX Control of type Toggle Button. Please check the detailed article on Adding ActiveX Controls to Worksheets if you wish to get more in-depth understanding of the aforementioned feature.

//Create an instance of Workbook
Workbook book = new Workbook();

//Access first worksheet from the collection
Worksheet sheet = book.getWorksheets().get(0);

//Add Toggle Button ActiveX Control to the ShapeCollection at specified location
Shape shape = sheet.getShapes().addActiveXControl(ControlType.TOGGLE_BUTTON, 4, 0, 4, 0, 100, 30);

//Access the ActiveX Control object and set its linked cell property
ActiveXControl control = shape.getActiveXControl();
control.setLinkedCell("A1");

//Save the result on disc
book.save(dir + "output.xlsx", SaveFormat.XLSX);

Filter the Data Type for Loading

As per default behaviour, Aspose.Cells APIs load everything from the template file into its own object model, providing access to every aspect of the spreadsheet. There could be scenarios where the user does not want to load everything but specific objects, such as charts or shapes. This can be achieved using the newly exposed LoadDataFilterOptions enumeration and LoadOptions.LoadDataFilterOptions property.

Aspose.Cells APIs have added the enumeration LoadDataFilterOptions which can be used to make the selection of data type to be loaded from the template file. Filtering data at the time of loading can improve the performance tremendously, especially when used with LightCells APIs. There are several selections that a user can make in order to customize the loading process by specifying the appropriate value from LoadDataFilterOptions.

The LoadDataFilterOptions enumeration provides the following selections.

  • ALL to load everything from the spreadsheet.
  • NONE to load nothing from the spreadsheet.
  • CELL_BLANK loads the cells whose values are blank.
  • CELL_BOOL loads cells whose values are Boolean.
  • CELL_DATA loads cells data including values, formulas and formatting.
  • CELL_ERROR loads cells whose values are error.
  • CELL_NUMERIC loads cells whose values are numeric (including Date & Time).
  • CELL_STRING loads cells whose values are text/string.
  • CELL_VALUE loads only cell values (all types).
  • CHART loads only charts.
  • CONDITIONAL_FORMATTING loads only conditional formatting rules.
  • DATA_VALIDATION loads only data validation rules.
  • DOCUMENT_PROPERTIES loads only document properties.
  • FORMULA loads formulas including defined names.
  • MERGED_AREA loads only merged cells.
  • PIVOT_TABLE loads Pivot Tables.
  • SETTINGS loads only Workbook & Worksheet settings.
  • SHAPE loads only shapes.
  • STYLE loads cells formatting.
  • TABLE loads Excel tables/List Objects.

Here is the simple usage scenario to demonstrate the Data Filtering at the time of Template Loading.

//Create an instance of LoadOptions & initialize it with type of template to be loaded
LoadOptions options = new LoadOptions(LoadFormat.XLSX);

//Set LoadDataFilterOptions to load only shapes
options.setLoadDataFilterOptions(LoadDataFilterOptions.SHAPE);

//Create an instance of Workbook from a existing spreadsheet using instance of LoadOptions
Workbook book = new Workbook(dir + "sample.xlsx", options);

Update References while Deleting Blank Rows & Columns

As per default behaviour, Aspose.Cells APIs delete the blank rows & columns without updating the cell references that may have been effected from the delete process. This could lead to undesired results in certain cases, especially when spreadsheet contains formulas or charts.

Aspose.Cells APIs have tried to overcome the aforementioned situation by exposing the overloaded versions of the Cells.deleteBlankRows & Cells.deleteBlankColumns methods with the release of Aspose.Cells for Android. The new methods can accept an instance of DeleteOptions class and can be used to overcome the situations that could arise due to the broken references in formulas, chart series data and so on. The DeleteOptions class currently has one member, a Boolean type property by the name UpdateReference. If the said property is set to true and the instance of DeleteOptions class is passed to the Cells.deleteBlankRows & Cells.deleteBlankColumns methods, the API will internally adjust the formula references (if any) to accommodate the changes.

Below provided code snippet demonstrates the simple usage scenario of newly exposed overloads. If you wish to get more understanding where these methods should be used, please check the detailed article on Updating Cell References while Deleting Blank Rows & Columns.

//Create an instance of Workbook & load an existing spreadsheet
Workbook book = new Workbook(dir + "sample.xlsx");

//Access worksheet from which blank rows/columns have to be deleted
Worksheet sheet = book.getWorksheets().get(0);

//Access cells of the desired worksheet
Cells cells = sheet.getCells();

//Create an instance of DeleteOptions class
DeleteOptions options = new DeleteOptions();
//Set UpdateReference property to true;
options.setUpdateReference(true);

//Delete all blank rows and columns
cells.deleteBlankColumns(options);
cells.deleteBlankRows(options);

Render Grid Lines to HTML

Aspose.Cells APIs provide the ability to export spreadsheet contents in HTML format as per the standards opted by Excel application. If a spreadsheet is converted to HTML using Excel application, it does not render the grid lines. Aspose.Cells APIs behave in the same way using its default settings, however, with this release of the API, the developers can now choose to render the grid lines as well. Aspose.Cells for Android API has exposed the HtmlSaveOptions.ExportGridLines property with default value of false. If the said property is set to true, the API renders the grid lines for the available data range in HTML format.

Below provided code snippet demonstrates the simple usage scenario of newly exposed HtmlSaveOptions.ExportGridLines to render the grid lines to HTML format. In order to get a more detailed understanding of this feature, please check the article on Rendering Grid Lines to HTML.

//Create an instance of Workbook and load existing spreadsheet
Workbook book = new Workbook(dir + "input.xlsx");

//Create an instance of HtmlSaveOptions
HtmlSaveOptions options = new HtmlSaveOptions();
//Set ExportGridLines to true
options.setExportGridLines(true);

//Save the result in HTML format
book.save(dir + "output.html", options);

Auto Refresh OleObject’s Image

Aspose.Cells for Android has exposed the OleObject.AutoLoad property which allows to refresh the OleObject’s image if the contents/data of the underlying object has been changed. The aforementioned property when set to true, forces the Excel application to refresh the OleObject’s image when resultant spreadsheet is loaded in Excel interface.

Below provided code snippet demonstrates the simple usage scenario of newly exposed OleObject.AutoLoad property. In order to get a more detailed understanding of this feature, please check the article on Automatically Refresh OleObect’s Image.

//Create an instance of Workbook and load an existing spreadsheet
Workbook book = new Workbook(dir + "input.xlsx");

//Access first worksheet from the collection
Worksheet sheet = book.getWorksheets().get(0);

//Access OleObjectCollection from first worksheet
OleObjectCollection oleObjects = sheet.getOleObjects();

//Access a OleObject from the collection
OleObject oleObject = oleObjects.get(0);

//Set AutoLoad to true
oleObject.setAutoLoad(true);

Add Comments for ListObjects

Aspose.Cells APIs now allow to get and set the comments for an instance of ListObject. In order to provide the aforementioned feature, the Aspose.Cells APIs have exposed the ListObject.Comment property with the release of 8.8.3.

Here is a simple usage scenario of newly exposed ListObject.Comment property. In order to get detailed understanding of this feature, please check the article on Adding Comments for ListObjects.

//Create an instance of Workbook and load existing spreadsheet
Workbook book = new Workbook(dir + "input.xlsx");

//Access first worksheet from the collection
Worksheet sheet = book.getWorksheets().get(0);

//Access first ListObject from the collection of ListObjects
ListObject listObject = sheet.getListObjects().get(0);

//Set comments for the ListObject
listObject.setComment("Comments");

//Save the result on disc
book.save(dir + "output.xlsx");

Set Default Font for Rendering Spreadsheets to Image Formats

Aspose.Cells for Android API has provided the ability to specify the default font name for rendering the spreadsheets in image formats by exposing the ImageOrPrintOptions.DefaultFont property. The DefaultFont property is of type System.string with default value as null. The said property can be used when Unicode characters in the spreadsheet have not been formatted with appropriate font in the cell’s style, consequently such characters may appear as blocks in the resultant images. It is advised to set the DefaultFont property to MingLiu or MS Gothic to properly show the Unicode characters in the resultant images. If the DefaultFont property is not set, Aspose.Cells for Android API will use the system’s default font to show the Unicode characters.

Below provided code snippet demonstrates the simple usage scenario of newly exposed ImageOrPrintOptions.DefaultFont property. Please check the detailed article on Setting Default Font for Rendering Spreadsheets in Image Formats if you wish to get more in-depth understanding of the aforementioned feature.

//Create an instance of ImageOrPrintOptions
ImageOrPrintOptions options = new ImageOrPrintOptions();
//Set default font name for image rendering
options.setDefaultFont("MS Gothic");

//Load a spreadsheet in an instance of Workbook
Workbook book = new Workbook(dir + "sample.xlsx");
//Access the worksheet to be rendered
Worksheet sheet = book.getWorksheets().get(0);

//Create an instance of SheetRender
SheetRender render = new SheetRender(sheet, options);
//Save spreadsheet to image
render.toImage(0, dir + "output.png");

Set Default Font for Rendering Spreadsheets to HTML

Aspose.Cells for Android 8.9.0 has exposed the DefaultFontName property for the HtmlSaveOptions class that allows to specify the default font name while rendering spreadsheets to HTML format. The default font will be used only when a particular font used to style some contents in the spreadsheet does not exist on the machine where conversion process has to take place. The default value of HtmlSaveOptions.DefaultFontName property is null that means, Aspose.Cells for Android API will use the universal font which has the same family with the original font.

Below provided code snippet demonstrates the simple usage scenario of newly exposed HtmlSaveOptions.DefaultFontName. In order to get a more in-depth  understanding of this feature, please check the article on Setting Default Font for Rendering Spreadsheets to HTML Format.

//Create an instance of HtmlSaveOptions
HtmlSaveOptions options = new HtmlSaveOptions();
//Set default font name for Html rendering
options.setDefaultFontName("Arial");

//Load a spreadsheet in an instance of Workbook
Workbook book = new Workbook(dir + "sample.xlsx");
//Save the spreadsheet in Html format while passing instance of HtmlSaveOptions
book.save(dir + "output.html", options);

Impose Restrictions of Excel 2003 while Refreshing Pivot Table

Aspose.Cells for Android API has exposed the Boolean type IsExcel2003Compatible property for the PivotTable class which allows to impose or remove the Excel 2003 restrictions for refreshing Pivot Tables. The default value of IsExcel2003Compatible property is true, that means a string must be less than or equal to 255 characters. If the string is greater than 255 characters, it will be truncated. If false, the aforementioned restriction will not be imposed.

Below provided code snippet demonstrates the simple usage scenario of PivotTable.IsExcel2003Compatible property. In order to get a more detailed understanding of this feature, please check the article on Compatibility for Excel 2003 while Refreshing Pivot Tables.

//Load a spreadsheet in an instance of Workbook
Workbook book = new Workbook(dir + "sample.xlsx");

//Access the desired Pivot Table from the spreadsheet
PivotTable pivot = book.getWorksheets().get(0).getPivotTables().get(0);

//Set Excel 2003 compatibility to false
pivot.setExcel2003Compatible(false);

//Refresh & recalculate Pivot Table
pivot.refreshData();
pivot.calculateData();

Convert Chart to PDF

Aspose.Cells APIs have already provided the facility to render charts to PDF while using the Chart.toPdf method. With this release, the API has exposed another overloaded version of the said method that could accept an instance of OutputStream, allowing the users to save the chart’s PDF in an instance of ByteArrayOutputStream.

Following code snippet converts a chart to PDF in memory.

//Create an instance of Workbook and load an existing spreadsheet with a chart
Workbook workbook = new Workbook(dir + "sample.xlsx");

//Access first worksheet containing a chart
Worksheet worksheet = workbook.getWorksheets().get(0);

//Access first chart from the worksheet
Chart chart = worksheet.getCharts().get(0);

//Save the chart to PDF as Stream
ByteArrayOutputStream outStream = new ByteArrayOutputStream();
chart.toPdf(outStream);

Setting Shadow as Text Effect for Shapes

Aspose.Cells for Android now provides the ability to set the shadow as text effects for any shape such as TextBox. The said feature has been provided by exposing the Shape.TextBody property. This property presents the settings of the shape’s text and returns FontSetting objects which in turn can be used to set the shadow via ShadowEffect.PresetType property.

Here is the simple usage scenario demonstrating how to set the shadow effect for the text in a TextBox.

//Create an instance of Workbook
Workbook book = new Workbook();

//Access first worksheet of the Workbook
Worksheet sheet = book.getWorksheets().get(0);

//Add a TextBox to the ShapeCollection
int index = sheet.getTextBoxes().add(2, 2, 100, 400);
TextBox textBox = sheet.getTextBoxes().get(index);

//Set the text of the TextBox
textBox.setText("This text has the following settings.\n\nText Effects > Shadow > Offset Bottom");

//Set shadow effect for text
for (int i = 0; i < textBox.getTextBody().getCount(); i++)
{
  textBox.getTextBody().get(i).getShapeFont().getFillFormat().getShadowEffect().setPresetType(PresetShadowType.OFFSET_BOTTOM);
}

Other Enhancements & Improvements

The most notable enhancements in this release are as follow:


To keep up with our news, you can follow us on Twitter or follow our Facebook page.