Filter the Type of Data for Loading, Convert Chart to PDF, Create & Remove Data Validation for GridCell with Aspose.Cells for Java 8.8.1

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.8.1. This month’s release includes new features, enhancements and bug fixes that further improve the overall stability and usability of the API. Please check the release notes in order to get an idea about what is new and what has been fixed with this revision of Aspose.Cells for Java. 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.

Filter the Data Type for Loading

As per default behaviour, Aspose.Cells APIs load everything from the template file into its own object model, providing access to every aspect of the spreadsheet. There could be scenarios where the user does not want to load everything but specific objects, such as charts or shapes. This can be achieved using the newly exposed LoadDataFilterOptions enumeration and LoadOptions.LoadDataFilterOptions property.

Aspose.Cells APIs have added the enumeration LoadDataFilterOptions which can be used to make the selection of data type to be loaded from the template file. Filtering data at the time of loading can improve the performance tremendously, especially when used with LightCells APIs. There are several selections that a user can make in order to customize the loading process by specifying the appropriate value from LoadDataFilterOptions.

The LoadDataFilterOptions enumeration provides the following selections.

  1. ALL to load everything from the spreadsheet.
  2. NONE to load nothing from the spreadsheet.
  3. CELL_BLANK loads the cells whose values are blank.
  4. CELL_BOOL loads cells whose values are Boolean.
  5. CELL_DATA loads cells data including values, formulas and formatting.
  6. CELL_ERROR loads cells whose values are error.
  7. CELL_NUMERIC loads cells whose values are numeric (including Date & Time).
  8. CELL_STRING loads cells whose values are text/string.
  9. CELL_VALUE loads only cell values (all types).
  10. CHART loads only charts.
  11. CONDITIONAL_FORMATTING loads only conditional formatting rules.
  12. DATA_VALIDATION loads only data validation rules.
  13. DOCUMENT_PROPERTIES loads only document properties.
  14. FORMULA loads formulas including defined names.
  15. MERGED_AREA loads only merged cells.
  16. PIVOT_TABLE loads Pivot Tables.
  17. SETTINGS loads only Workbook & Worksheet settings.
  18. SHAPE loads only shapes.
  19. STYLE loads cells formatting.
  20. TABLE loads Excel tables/List Objects.

Here is the simple usage scenario to demonstrate the data filtering at the time of template loading.

//Create an instance of LoadOptions & initialize it with type of template to be loaded
LoadOptions options = new LoadOptions(LoadFormat.XLSX);

//Set LoadDataFilterOptions to load only shapes
options.setLoadDataFilterOptions(LoadDataFilterOptions.SHAPE);

//Create an instance of Workbook from a existing spreadsheet using instance of LoadOptions
Workbook book = new Workbook(filePath, options);

Convert Chart to PDF

Aspose.Cells APIs have already provided the facility to render charts to PDF while using the Chart.toPdf method. With this release, the API has exposed another overloaded version of the said method that could accept an instance of OutputStream, allowing the users to save the chart’s PDF in an instance of ByteArrayOutputStream.

Following code snippet converts a chart to PDF in memory.

//Create an instance of Workbook and load an existing spreadsheet with a chart
Workbook workbook = new Workbook(filePath);

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

//Access first chart from the worksheet
Chart chart = worksheet.getCharts().get(0);

//Save the chart to PDF as Stream
ByteArrayOutputStream outStream = new ByteArrayOutputStream();
chart.toPdf(outStream);

Setting Shadow as Text Effect for Shapes

Aspose.Cells for Java 8.8.1 provides the ability to set the shadow as text effects for any shape such as TextBox. The said feature has been provided by exposing the Shape.TextBody property. This property presents the settings of the shape’s text and returns FontSetting objects which in turn can be used to set the shadow via ShadowEffect.PresetType property.

Here is the simple usage scenario demonstrating how to set the shadow effect for the text in a TextBox.

//Create an instance of Workbook
Workbook book = new Workbook();

//Access first worksheet of the Workbook
Worksheet sheet = book.getWorksheets().get(0);

//Add a TextBox to the ShapeCollection
int index = sheet.getTextBoxes().add(2, 2, 100, 400);
TextBox textBox = sheet.getTextBoxes().get(index);

//Set the text of the TextBox
textBox.setText("This text has the following settings.\n\nText Effects > Shadow > Offset Bottom");

//Set shadow effect for text
for (int i = 0; i < textBox.getTextBody().getCount(); i++)
{
  textBox.getTextBody().get(i).getShapeFont().getFillFormat().getShadowEffect().setPresetType(PresetShadowType.OFFSET_BOTTOM);
}

Create Data Validation for GridCell

Aspose.Cells.GridWeb for Java 8.8.1 has exposed the GridCell.createValidation & GridCell.removeValidation methods to add and remove the data validation from the GridCell. Please note, the Aspose.Cells.GridWeb also provides the facility to add data validation rules while using the ValidationCollection.add method, however, this approach requires to specify the cell range on which data validation rule has to be applied. If the requirement is to specify the data validation rule for just one cell then the GridCell.createValidation method can be used without specifying the cell range.

Here is the simple usage scenario of GridCell.createValidation method to add a data validation rule to single GridCell.

//Access first worksheet
GridWorksheet sheet = gridweb.getWorkSheets().get(0);

//Access cell B3
GridCell cell = sheet.getCells().get("B3");

//Add validation inside the GridCell
//Any value which is not between 20 and 40 will cause error in a GridCell
GridValidation val = cell.createValidation(GridValidationType.WHOLE_NUMBER, true);
val.setFormula1("=20");
val.setFormula2("=40");
val.setOperator(OperatorType.BETWEEN);
val.setShowError(true);
val.setShowInput(true);

Similarly, if the requirement is remove the data validation rule from a single GridCell then its removeValidation method can be used to achieve the goal.

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: