Public API Changes in Aspose.Cells 8.4.0

Added APIs

Mechanism to Modify the VBA/Macro Code in Spreadsheets

In order to provide the feature of VBA/Macro Code Manipulation, the Aspose.Cells for Java 8.4.0 has exposed a series of new classes and properties in the com.aspose.cells.Vba package. A few of the important details of these new classes are as follows.

  • VbaProject class can be used to fetch the VBA project from a given spreadsheet.
  • VbaModuleCollection class represents the collection of VBA modules that are part of a given VbaProject.
  • VbaModule class represents a single module from the VbaModuleCollection.

The following code snippet shows how to dynamically modify the VBA code segments.

Java

 Workbook workbook = new Workbook("source.xlsm");

//Change the VBA Module Code

VbaModuleCollection modules = workbook.getVbaProject().getModules();

for(int i=0; i < modules.getCount(); i++)

{

	VbaModule module = modules.get(i);

    String code = module.getCodes();

    //Replace the original message with the modified message

    if (code.contains("This is test message."))

    {

        code = code.replace("This is test message.", "This is Aspose.Cells message.");

        module.setCodes(code);

    }

}

//Save the output Excel file

workbook.save("output.xlsm");

Ability to Remove Pivot Table

Aspose.Cells for Java 8.4.0 has exposed two methods for the PivotTableCollection to provide the feature of Pivot Table removal from a given spreadsheet. The details of the aforesaid methods are as follows.

  • PivotTableCollection.remove method accepts an object of PivotTable and removes it from the collection.
  • PivotTableCollection.removeAt method accepts a zero‑index‑based integer value and removes the particular PivotTable from the collection.

The following code snippet shows how to remove the PivotTable using both the above‑mentioned methods.

Java

 //Create workbook object from source Excel file

Workbook workbook = new Workbook("source.xlsx");

//Access the first worksheet

Worksheet worksheet = workbook.getWorksheets().get(0);

//Access the first pivot table object

PivotTable pivotTable = worksheet.getPivotTables().get(0);

//Remove pivot table using pivot table object

worksheet.getPivotTables().remove(pivotTable);

//Remove pivot table using pivot table position

worksheet.getPivotTables().removeAt(0);

//Save the workbook

workbook.save("output.xlsx");

Support for Different Pivot Table Layouts

Aspose.Cells for Java 8.4.0 provides support for different predefined layouts for Pivot Tables. In order to provide this feature, the Aspose.Cells APIs have exposed three methods for the PivotTable class as detailed below.

  • PivotTable.showInCompactForm method renders the Pivot Table in Compact layout.
  • PivotTable.showInOutlineForm method renders the Pivot Table in Outline layout.
  • PivotTable.showInTabularForm method renders the Pivot Table in Tabular layout.

The following sample code sets different layouts for a Pivot Table and stores the result on disc.

Java

 //Create workbook object from source excel file

Workbook workbook = new Workbook("source.xlsx");

//Access first worksheet

Worksheet worksheet = workbook.getWorksheets().get(0);

//Access first pivot table

PivotTable pivotTable = worksheet.getPivotTables().get(0);

//1 - Show the pivot table in compact form

pivotTable.showInCompactForm();

//Refresh the pivot table

pivotTable.refreshData();

pivotTable.calculateData();

//Save the output

workbook.save("CompactForm.xlsx");

//2 - Show the pivot table in outline form

pivotTable.showInOutlineForm();

//Refresh the pivot table

pivotTable.refreshData();

pivotTable.calculateData();

//Save the output

workbook.save("OutlineForm.xlsx");

//3 - Show the pivot table in tabular form

pivotTable.showInTabularForm();

//Refresh the pivot table

pivotTable.refreshData();

pivotTable.calculateData();

//Save the output

workbook.save("TabularForm.xlsx");

Class TxtLoadStyleStrategy & Property TxtLoadOptions.LoadStyleStrategy Added

Aspose.Cells for Java 8.4.0 has exposed the TxtLoadStyleStrategy class and TxtLoadOptions.LoadStyleStrategy property in order to specify the strategy to format the parsed values while converting string value to number or date‑time.

Added DataBar.ToImage Method

With the release of v8.4.0, the Aspose.Cells API has provided the DataBar.toImage method to save the conditionally formatted DataBar in image format. The DataBar.toImage method accepts two parameters as detailed below.

  • The first parameter is of type com.aspose.cells.Cell on which conditional formatting has been applied.
  • The second parameter is of type com.aspose.cells.rendering.ImageOrPrintOptions in order to set different parameters of the resultant image.

The following sample code demonstrates the use of DataBar.toImage method to render the DataBar in image format.

Java

 //Create workbook object from source excel file

Workbook workbook = new Workbook("source.xlsx");

//Access first worksheet

Worksheet worksheet = workbook.getWorksheets().get(0);

//Access the cell which contains conditional formatting databar

Cell cell = worksheet.getCells().get("C1");

//Get the conditional formatting of the cell

FormatConditionCollection fcc = cell.getFormatConditions();

//Access the conditional formatting databar

DataBar dbar = fcc.get(0).getDataBar();

//Create image or print options

ImageOrPrintOptions opts = new ImageOrPrintOptions();

opts.setImageFormat(ImageFormat.getPng());

//Get the image bytes of the databar

byte[] imgBytes = dbar.toImage(cell, opts);

//Write image bytes on the disk

FileOutputStream out = new FileOutputStream("databar.png");

out.write(imgBytes);

out.close();

Added Border.ThemeColor Property

Aspose.Cells APIs allow extraction of theme‑related data from spreadsheets. With the release of Aspose.Cells for Java 8.4.0, the API has exposed the Border.ThemeColor property that can be used to retrieve the theme‑color attributes of cell borders.

Added DrawObject.ImageBytes Property

Aspose.Cells for Java 8.4.0 has exposed the DrawObject.ImageBytes property to get the image data from a chart or shape.

Added HtmlSaveOptions.ExportBogusRowData Property

Aspose.Cells for Java 8.4.0 has provided the HtmlSaveOptions.ExportBogusRowData property. The Boolean‑type property determines if the API will inject bogus bottom‑row data while exporting a spreadsheet to HTML format.

The following sample code illustrates the use of the aforesaid property.

Java

 //Create an object of HtmlSaveOptions class

HtmlSaveOptions options = new HtmlSaveOptions();

//Set the ExportBogusRowData to true

options.setExportBogusRowData(true);

//Create workbook object from source excel file

Workbook workbook = new Workbook("source.xlsx");

//Save the workbook

workbook.save("output.xlsx");

Added HtmlSaveOptions.CellCssPrefix Property

The newly added property HtmlSaveOptions.CellCssPrefix allows setting the prefix for the CSS files while exporting spreadsheets to HTML format.

Obsolete APIs

Obsoleted Cells.getCellByIndex & Row.getCellByIndex Methods

Use the getEnumerator method to iterate over all cells instead.

Obsoleted DrawObject.Image Property

Use the DrawObject.ImageBytes property to get image data instead.

Public API Changes in Aspise.Cells 8.3.2 Public API Changes in Aspose.Cells 8.4.1