Optimize PDF File Size, Re-size Chart’s Data Label Shape to Fit Text & Enhancements to Protection Module with Aspose.Cells for Java 8.7.0

Share on FacebookTweet about this on TwitterShare on LinkedIn

Aspose.Cells for JavaAspose.Cells for Java API has been upgraded to 8.7.0, and we are pleased to announce, this month’s release contains many useful features and improvements along with some critical bug fixes. Please refer to the release notes of Aspose.Cells for Java 8.7.0 for a full list of enhancements. 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.

Optimize PDF File Size

Aspose.Cells APIs have provided an enhancement for the PDF rendering engine in order to reduce the resultant PDF file size. Aspose.Cells for Java 8.7.0 has exposed the PdfSaveOptions.OptimizationType property along with an enumeration by the name PdfOptimizationType in order to facilitate the application developers to control the resultant PDF file size to some extent.

The PdfOptimizationType enumeration has 2 constants at the moment exhibiting the following behaviour.

  1. PdfOptimizationType.MINIMUM_SIZE: API tries to optimize the cell text and cell border records in order to reduce the resultant PDF file size.
  2. PdfOptimizationType.STANDARD: No optimization is done therefore the resultant file size will be large as compared to PdfOptimizationType.MINIMUM_SIZE selection.

The following piece of code demonstrates the usage of PdfSaveOptions.OptimizationType property to minimize the resultant PDF file size while exporting spreadsheets to PDF format.

//Create an instance of PdfSaveOptions
PdfSaveOptions pdfSaveOptions = new PdfSaveOptions();

//Set the OptimizationType property to desired value

//Create an instance of Workbook
//Optionally load an existing spreadsheet
Workbook book = new Workbook(inFilePath);

//Save the spreadsheet in PDF format while passing the instance of PdfSaveOptions
book.save(outFilePath, pdfSaveOptions);

Re-size Chart’s Data Label Shape to Fit Text

Aspose.Cells for Java 8.7.0 has exposed the boolean type DataLabels.ResizeShapeToFitText property in order to mimic the Excel’s feature of re-sizing data label shapes to fit the text.

Please note, Excel application provides the Resize shape to fit text option for chart’s data labels to increase the size of the shape so that the text fits inside of it. This option can be accessed on Excel interface by selecting any of the data labels on the chart. Right click and select Format DataLabels menu. On Size & Properties tab, expand Alignment node to reveal the related properties including the Resize shape to fix text option as highlighted below.

The following piece of code shows the simple usage scenario of DataLabels.ResizeShapeToFitText property to re-size the chart’s data label shapes to fit text.

//Create an instance of Workbook containing the Chart
Workbook book = new Workbook(inFilePath);

//Access the Worksheet that contains the Chart
Worksheet sheet = book.getWorksheets().get(0);

//Access ChartCollection from Worksheet
ChartCollection charts = sheet.getCharts();

//Loop over each chart in collection
for (int chartIndex = 0; chartIndex < charts.getCount(); chartIndex++)
	//Access indexed chart from the collection
	Chart chart = charts.get(chartIndex);

	for (int seriesIndex = 0; seriesIndex < chart.getNSeries().getCount(); seriesIndex++)
	    //Access the DataLabels of indexed NSeries
		DataLabels labels = chart.getNSeries().get(seriesIndex).getDataLabels();

	    //Set ResizeShapeToFitText property to true

	//Calculate Chart

//Save the result

Here is a snapshot showing a simple bar chart before & after executing the above code.

Enhancements to Protection Module

Aspose.Cells APIs have enhanced the Protection class by introducing some useful properties & methods. Two of the most worth mentioning enhancements are as follow.

Detect if Worksheet is Password Protected

It is possible to protect the workbooks and worksheets separately. For instance, a spreadsheet may contain one or more worksheets that are password protected, however, the spreadsheet itself may or may not be protected. Aspose.Cells API for Java has provided the Protection.isProtectedWithPassword field to detect if a worksheet is password protected. Boolean type Protection.isProtectedWithPassword field returns true if Worksheet is password protected and false if not.

Verify Password Used to Protect the Worksheet

Aspose.Cells for Java 8.7.0 has exposed the Protection.verifyPassword method which allows to specify a password as an instance of String and verifies if same password has been used to protect the worksheet. The Protection.verifyPassword method returns true if the specified password matches with the password used to protect the given Worksheet, false if specified password does not match.

Following piece of code uses the Protection.verifyPassword method in conjunction with Protection.isProtectedWithPassword field to detect the password protection and verifies the password.

//Create an instance of Workbook and load a spreadsheet
Workbook book = new Workbook(inFilePath);

//Access the protected Worksheet
Worksheet sheet = book.getWorksheets().get(0);

//Check if Worksheet is password protected
if (sheet.getProtection().isProtectedWithPassword())
  //Verify the password used to protect the Worksheet
  if (sheet.getProtection().verifyPassword("password"))
	  System.out.println("Specified password has matched");
	  System.out.println("Specified password has not matched");

Load or Import Delimited File Containing Formulas

Aspose.Cells for Java has provided support to identify & parse the formulas while loading CSV/TXT files having delimited plain data. Newly exposed TxtLoadOptions.HasFormula property when set to true directs the API to parse the formulas from the input delimited file and set them to relevant cells without requiring any additional processing.

The following piece of code demonstrates the usage of TxtLoadOptions.HasFormula property to load and import the CSV with formulas in it.

TxtLoadOptions opts = new TxtLoadOptions();

//Load your CSV file with formulas in a Workbook object
Workbook workbook = new Workbook(csvFile, opts);

//You can also import your CSV file by importing it onto the Worksheet cells
//The code below is importing CSV file starting from cell D4
Worksheet worksheet = workbook.getWorksheets().get(0);
worksheet.getCells().importCSV(csvFile, opts, 3, 3);

Create 2-Color Scale Conditional Format

This release of the API has has exposed the ColorScale.Is3ColorScale property that can be used to create 2-Color Scale conditional format. The said property is of type boolean with default value of true which means that the conditional format will be of 3-Color Scale by default. However, switching the ColorScale.Is3ColorScale property to false will generate a 2-Color Scale conditional format.

Following is the simple usage scenario to generate 2-color scale conditional format. Please also review the detailed article on Adding 2 & 3-Color Scale Conditional Formatting.

//Create an instance of Workbook
//Optionally load an existing spreadsheet
Workbook book = new Workbook();

//Access the Worksheet to which conditional formatting rule has to be added
Worksheet sheet = book.getWorksheets().get(0);

//Add FormatConditions to the collection
int index = sheet.getConditionalFormattings().add();

//Access newly added formatConditionCollection via its index
FormatConditionCollection formatConditionCollection = sheet.getConditionalFormattings().get(index);

//Create a CellArea on which conditional formatting rule will be applied
CellArea cellArea = CellArea.createCellArea("A1", "A5");

//Add conditional formatted cell range

//Add format condition of type ColorScale
index = formatConditionCollection.addCondition(FormatConditionType.COLOR_SCALE);

//Access newly added format condition via its index
FormatCondition formatCondition = formatConditionCollection.get(index);

//Set Is3ColorScale to false in order to generate a 2-Color Scale format

Set other necessary properties

Other Enhancements & Improvements

The most notable enhancements in this release are as follow:

Aspose.Cells for Java Resources

The resources, you may need to accomplish your tasks: