Aspose.Cells for Java v19.11 is released which contains many new features and upgrades. Pivot tables processing is enhanced by providing the sorting and hiding data based upon some criteria to get compact information for reporting and presentations. Reading and writing to tables with QueryTable as a data source is available now. This is not the end of this release but the beginning of a long list of new features like modifying the existing validation by adding CellArea, retrieving OData connection information and getting text width of cell value for better layouts. All the details of this release can be found in the release notes published with every new release. Let us have a look at these new exciting features one by one.
Sort and Hide Data in Excel Pivot Table using Java
There are cases where crisp information is required in the pivot table. We may require some features to sort data in the pivot table and then hide rows against certain criteria. Following sample code demonstrates this feature
The following image shows the pivot table before and after running this code on a sample data.
Visit the following article for more information and sample files.
Read and Write Table with Query Table Data Source
Tables having a query table as a data source are very common. We may require to read such tables and modify them like showing the total etc. Earlier this feature was available however support for XLS file is provided. Here is an example code which reads a table and then changes it to show the total at the end.
The following image shows how setShowTotals() function works:
You may get the sample files and more details in the following article:
Fetch OData Connection Information
OData can be used to get the feed or data from RESTful API which can be used in Excel file as well. This information can be retrieved from the Excel file using Aspose.Cells API using DataMashup class of Workbook. Get the desired information from PowerQueryFormulas property having PowerQueryFormula and PowerQueryFormulaItem.
The following code snippet demonstrates the use of these classes to retrieve the OData information.
The following image shows a sample file that is used in the above code sample to fetch the OData connection information.
Here is the program output using the sample file:
This article contains a sample file and more details to check this feature:
Add CellArea to Existing Validation
Suppose that you have an Excel file that has validation which is working fine. Aspose.Cells always performs a check to verify if the area is already there or not whenever a cell area is added to validation. You may face performance hit during this check if there are a large number of validations in your Excel file. To handle this issue, there is a need to configure the API to get control over performing this check. Validation.AddAreaCellArea cellArea, bool checkIntersection, bool checkEdge)) method is there to get this control as the parameter checkIntersection directs the API to check the intersection of this newly added area with existing areas. If you set this parameter to false, the checking for other areas will be disabled. If you want to check the applied areas, set the _checkEdge _parameter to true. Note that internal settings are rebuilt if the top-left area is reset to the new area. However, if you are sure that new area is not the top-left area, then set the value of the property checkEdge to false.
You may give a try to the following sample code which demonstrates adding new CellArea to a validation which already exists there in the Excel file.
Have a look at the following article for more details and sample file to test this feature.
Get Text Width of Cell Value
Better data presentation layout is the target of every developer. For this support is required in the API to calculate the cell width when formatting the reports/presentations. To assist the developers, Aspose.Cells provides a function GetTextWidth() in the CellsHelper class. Using this function developer will get the text width in a cell, based upon the text length and style. Following sample code demonstrates the usage of CellsHelper.GetTextWidth)by getting the text width of the value in a cell.
The following sample code demonstrates this feature:
You may download the sample file here.
Other Enhancements
Following are a few enhancements and improvements which are made in this release:
- Cells.importCSV function is improved to avoid exception “string value cannot exceed 255 characters”
- Improvement is made in the performance of Cells.removeDupilcates for large dataset
- Radial graph rendering to HTML is improved
- More accuracy is added to scaling of the axis during rendition to PNG
- Enhancements are made to avoid corruption after updating the source data for a pivot table.
- Get more accurate Russian (custom) date format output using this release
- LoadFilter is made error-free to consider the required sheet
- Proper borders are maintained while converting Excel file to EMF
- SheetRender improved to provide a more accurate page count
- Rendering of the chart to an image is enhanced. You will get Data Labels with the same style and proper values in the output image
- Cell.setHtmlString() function improved for processing hyperlinks and HTML tags/scripts.
Following are few important resources which can be referred to while testing the new release: