Render Worksheet to Graphics2D Context, Access Cell by PivotField Name, Change Shape’s Adjustment Values and Consolidation Functions with Aspose.Cells for Java 8.5.2

Share on FacebookTweet about this on TwitterShare on LinkedIn

Aspose.Cells for JavaWe are pleased to announce the release of Aspose.Cells for Java 8.5.2. 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.5.2 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.

Render Worksheet to Graphics Context

Aspose.Cells for Java APIs have exposed an overloaded version of SheetRender.toImage method that can accept an instance of java.awt.Graphics2D along with usual parameters to render the Worksheet in Graphics2D context. This feature is helpful in scenario where you wish to get high (scalable) print quality by redirecting the Graphics2D context to PostScript printer and create a PDF from the PostScript.

Following code snippet demonstrates how to use the newly exposed SheetRender.toImage method to render the Worksheet in Graphics2D context.

//Create workbook object from source file
Workbook workbook = new Workbook("source.xlsx");

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

//Create empty image and fill it with blue color
int width = 800;
int height = 800;
BufferedImage image = new BufferedImage(width, height, BufferedImage.TYPE_INT_ARGB);
Graphics2D g = image.createGraphics();
g.setColor(java.awt.Color.blue);
g.fillRect(0, 0, width, height);

//Set OnePagePerSheet option to true
ImageOrPrintOptions opts = new ImageOrPrintOptions();
opts.setOnePagePerSheet(true);

//Render worksheet to graphics context
SheetRender sr = new SheetRender(worksheet, opts);
sr.toImage(0, g);

Access Cell by PivotField Name

Aspose.Cells for Java now provides the PivotTable.getCellByDisplayName method to obtain the Cell reference by PivotField display name. This method is useful in scenarios where application requirement is to format the PivotField header in the PivotTable.

The following sample code explains the usage of PivotTable.getCellByDisplayName method to style the PivotField header.

//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 inside the worksheet
PivotTable pivotTable = worksheet.getPivotTables().get(0);

//Access cell by display name of 2nd data field of the pivot table
String displayName = pivotTable.getDataFields().get(1).getDisplayName();
Cell cell = pivotTable.getCellByDisplayName(displayName);

//Access cell style and set its fill color and font color
Style style = cell.getStyle();
style.setForegroundColor(Color.getLightBlue());
style.getFont().setColor(Color.getBlack());

//Set the style of the cell
pivotTable.format(cell.getRow(), cell.getColumn(), style);

//Save workbook
workbook.save("output.xlsx");

Change Shape’s Adjustment Values

Aspose.Cells for Java 8.5.2 has exposed the Geometry.getShapeAdjustValues method that can be used to make changes to the adjustment points within the shapes. Please note, Microsoft Excel application displays the adjustments points as yellow diamond nodes while allowing to change the arc for rounded rectangle, point location for triangle, top width of a trapezoid and shape of head & tail for arrows.

ConsolidationFunction for the DataFields of PivotTable

Aspose.Cells APIs provide the means to apply Consolidation Function to DataFields (or value fields) of the PivotTable. In Microsoft Excel, you can right click the value field and then select Value Field Settings… option and then select the tab Summarize Values By. From there, you can select any Consolidation Function of your choice like Sum, Count, Average, Max, Min, Product, Distinct Count etc.

Aspose.Cells provides ConsolidationFunction enumeration to support the following consolidation functions.

  • ConsolidationFunction.AVERAGE
  • ConsolidationFunction.COUNT
  • ConsolidationFunction.COUNT_NUMS
  • ConsolidationFunction.DISTINCT_COUNT
  • ConsolidationFunction.MAX
  • ConsolidationFunction.MIN
  • ConsolidationFunction.PRODUCT
  • ConsolidationFunction.STD_DEV
  • ConsolidationFunction.STD_DEVP
  • ConsolidationFunction.SUM
  • ConsolidationFunction.VAR
  • ConsolidationFunction.VARP

The following code applies Average consolidation function to first DataField and DistinctCount consolidation function to second DataField of a sample PivotTable.

//Create workbook from source excel file
Workbook workbook = new Workbook("source.xlsx");

//Access the first worksheet of the workbook
Worksheet worksheet = workbook.getWorksheets().get(0);

//Access the first pivot table of the worksheet
PivotTable pivotTable = worksheet.getPivotTables().get(0);

//Apply Average consolidation function to first data field
pivotTable.getDataFields().get(0).setFunction(ConsolidationFunction.AVERAGE);

//Apply DistinctCount consolidation function to second data field
pivotTable.getDataFields().get(1).setFunction(ConsolidationFunction.DISTINCT_COUNT);

//Calculate the data to make changes affect
pivotTable.calculateData();

//Save the workbook
workbook.save("output.xlsx");

Other Enhancements & Improvements

The most notable  enhancements in this release are as follow:

  • Exposed SaveOptions.MergeAreas property to handle scenarios if a spreadsheet has too many individual cells with validation applied, there are chances that the resultant spreadsheet may get corrupted. One possible solution is to merge the cells with identical validation rules or you can now use the SaveOptions.MergeAreas property to direct the API to auto merge the CellAreas before save operation.
  • Improved the HTML & PDF rendering for alignment and layout.
  • Improved overall memory consumption.

Aspose.Cells for Java Resources

The resources, you may need to accomplish your tasks: