Big smiles as I am here to announce the release of the latest version of Aspose.Cells for Java. In this post, I am going to give you a glimpse of all the new exciting features for the best utilization of the API along with the improvements and fixes. Links to the relevant articles and resources will also be available for quick reference. So let us start reviewing what is there in this release.
JSON to Excel in Java
As you know that JavaScript Object Notation is a common data interchange format which is quite lightweight and can be read by human and machines easily. Now, for example, you get data from some Web API in JSON format and need it to be imported into some Excel file, then Aspose.Cells is there to provide this facility.
JSONUtility class is introduced by Aspose.Cells for this purpose having an ImportData method that not only imports data from JSON to Excel but also performs different conversions and formatting using the JsonLayoutOptions object during the import process. You may try this exciting feature by following the article Importing Data from JSON. Here is a sample code to import data from JSON to Excel in Java:
You can see that the result is cool as a fully formatted Excel file is created without using any extra code to parse the JSON string from any source like Web API. Isn’t it? For example, if you get the following string from Web API, then the output Excel file is shown in the image below:
[{“color”: “red”,”value”: “#f00”},{“color”: “green”,”value”: “#0f0”},{“color”: “blue”,”value”: “#00f”},{“color”: “cyan”,”value”: “#0ff”},{“color”: “magenta”,”value”: “#f0f”},{“color”: “yellow”,”value”:”#ff0″},{“color”: “black”,”value”: “#000”}]
Convert Table to Range with Options
Conversion of the table to a range was available earlier. However what if you want to control the formatting of destination range like formatting only the partial output. The good news is that this feature is available now and you don’t need to write extra code to format the output range. We have introduced a new class TableToRangeOptions where LastRow property is available to set the last row to which formatting is copied from the source table. Here is the view of a sample table and converted range which is formatted up to row 5.
Here is a sample code that is used to create the above range.
For detailed information on conversions follow this link.
Get Range with External Links
Microsoft Excel supports external links for fetching data from different sources. We have provided the option to retrieve these links from the Excel file using Name.GetRefferedAreas method. This method returns ReferredArea which has many useful properties as follows:
- EndColumn: The end column of the area
- EndRow: The end row of the area
- ExternalFileName: Get the external file name if this is an external reference
- IsArea: Indicates whether this is an area
- IsExternalLink: Indicates whether this is an external link
- SheetName: Indicates which sheet this reference is in
- StartColumn: The start column of the area
- StartRow: The start row of the area
In the following example, an external file having named range “Names” is linked with the Excel file. We can access this named range in the linked file and display its properties mentioned above.
Keep Separators for Blank Rows in Spreadsheet to CSV
Many times you convert the Excel sheets to CSV for using it in some other environment or applications. You may need to decide yourself about the separators for blank rows in the spreadsheet while exporting it to CSV. For example, there is a blank row in the source spreadsheet and you want either a blank row in the CSV or have a row with predefined separators. We have provided this feature now and you can get details about this feature here.
For better understanding, have a look at the following image which shows the result of this feature.
Following is a sample code which demonstrates this new feature.
Autofit Rows for Merged Cells
Autofitting rows is a very common operation that you perform while working with the Excel files. This feature was already there in Aspose.Cells API however many people asked for more control over this operation. To fulfill this requirement we have provided AutoFitMergedCellsType enumerator which contains the following options:
- NONE: Ignore merged cells.
- FIRST_LINE: Only expands the height of the first row.
- LAST_LINE: Only expands the height of the last row.
- EACH_LINE: Expands the height of each row.
Here is a snapshot of this feature where the effect of one of the options EACH_LINE is shown.
You can test this feature using following sample code.
For more details on this topic please visit here.
Other enhancements in this release
- Text extraction from cells using Cell.getDisplayStringValue() was returning different results for Ricty Diminished font. The new version returns an appropriate string now.
- Text alignment in the resultant PDF is made more accurate.
- In some cases, the black text color was changed to red in the converted HTML, that is no more an issue.
- A Scatter chart and 2D bubble chart will be rendered to PDF without any problem.
- Range.Copy() will copy the font settings and other objects now onward.
- Workbook.hasExernalLinks() will return proper results for DDE links.
Note: You may download this project from GitHub to get the template files and running examples used in this blog post.
