Filter the Type of Data while Loading, Convert Chart to PDF and Set Shadow as Text Effect for Shapes using Aspose.Cells for .NET 8.8.1

Share on FacebookTweet about this on TwitterShare on LinkedIn

Aspose.Cells for .NET logoWe are pleased to announce our next version of Aspose.Cells for .NET v8.8.1. The release contains some valuable features along with critical bug fixes and other enhancements. Please see the release notes in order to get an idea on what is new and what has been fixed with this version of Aspose.Cells for .NET. If you are planning to upgrade the Aspose.Cells for .NET API to the latest revision, we would strongly suggest you to check the complete Public API Changes section to know what APIs are changed so far. We have highlighted some important features and other enhancements here.

Filter the Data Type while Loading

As per default behaviour, Aspose.Cells APIs load everything from the template file into its document object model, providing access to every aspect of the spreadsheet. There could be a scenario 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. CellBlank loads the cells whose values are blank.
  4. CellBool loads cells whose values are Boolean.
  5. CellData loads cells data including values, formulas and formatting.
  6. CellError loads cells whose values are error.
  7. CellNumeric loads cells whose values are numeric (including Date & Time).
  8. CellString loads cells whose values are text/string.
  9. CellValue loads only cell values (all types).
  10. Chart loads only charts.
  11. ConditionalFormatting loads only conditional formatting rules.
  12. DataValidation loads only data validation rules.
  13. DocumentProperties loads only document properties.
  14. Formula loads formulas including defined names.
  15. MergedArea loads only merged cells.
  16. PivotTable 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.

//Set the load options, we only want to load shapes and do not want to load data
LoadOptions opts = new LoadOptions(LoadFormat.Xlsx);
opts.LoadDataFilterOptions = LoadDataFilterOptions.Shape;

//Create workbook object from sample excel file using load options
Workbook wb = new Workbook("sample.xlsx", opts);

//Save the output in pdf format
wb.Save("output.pdf", SaveFormat.Pdf);

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 Stream, allowing the users to save the chart’s PDF into streams.

Following code snippet converts a chart to PDF in memory.

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

//Access first worksheet containing a chart
var worksheet = workbook.Worksheets[0];

//Access first chart from the worksheet
var chart = worksheet.Charts[0];

//Save the chart to PDF as Stream
using (MemoryStream stream = new MemoryStream())
{
    chart.ToPdf(stream);
}

Setting Shadow as Text Effect for Shapes

Aspose.Cells for .NET 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
var book = new Workbook();

//Access first worksheet of the Workbook
var sheet = book.Worksheets[0];

//Add a TextBox to the ShapeCollection
var textBox = sheet.Shapes.AddTextBox(2, 0, 2, 0, 100, 400);

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

//Set shadow effect for text
for (int i = 0; i < textBox.TextBody.Count; i++)
{
    textBox.TextBody[i].ShapeFont.FillFormat.ShadowEffect.PresetType = PresetShadowType.OffsetBottom;
}

Create Data Validation for GridCell

Aspose.Cells.GridWeb 8.8.1 has exposed the GridCell.CreateValidation & GridCell.RemoveValidation methods to add and remove 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 = GridWeb1.WorkSheets[0];

//Access cell B3
GridCell cell = sheet.Cells["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.WholeNumber, true);
val.Formula1 = "=20";
val.Formula2 = "=40";
val.Operator = GridOperatorType.Between;
val.ShowError = true;
val.ShowInput = true;

Similarly, if the requirement is remove the data validation rule from a single GridCell then it’s RemoveValidation method can be used to achieve the goal.

Other Enhancements and Fixes

The most notable enhancements in this release are as follow:

We have also handled a few exceptions regarding reading/writing MS Excel files.

In this release, several important issues have been addressed. For example, issues around reading, writing and manipulating Microsoft Excel file formats, converting spreadsheets to HTML files and vice versa, manipulating and rendering charts and shapes, rendering images from Excel worksheets, rendering images files from charts and exporting Excel workbooks to PDF format have been resolved.

Aspose.Cells for .NET Resources

The resources, you may need to accomplish your tasks: