Add new VBA Modules to the Workbook and Utilize Improved Chart Creation Approach with Aspose.Cells for .NET 8.4.2

Share on FacebookTweet about this on TwitterShare on LinkedIn

Aspose.Cells for .NET logoAspose.Cells for .NET 8.4.2 has been released. This release contains some useful features and other enhancements. The most worth mentioning feature of the release is the support for adding VBA modules to the spreadsheets. 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 has been changed in the API so far. We have provided a few important features in this month’s release.

Adding VBA Modules to Spreadsheet

Starting with this release, Aspose.Cells for .NET has provided support for adding VBA modules to the spreadsheets. Aspose.Cells for .NET 8.4.2 has exposed the VbaModuleCollection.Add method to add a new VBA module to the instance of Workbook. The VbaModuleCollection.Add method accepts a parameter of type of Worksheet to add a worksheet specific module, whereas the existing VbaModule class contains all the module specific properties.

The following code snippet shows how to use the VbaModuleCollection.Add method.

//Create new workbook
Workbook workbook = new Workbook();

//Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];

//Add VBA Module
int idx = workbook.VbaProject.Modules.Add(worksheet);

//Access the VBA Module, set its name and codes
Aspose.Cells.Vba.VbaModule module = workbook.VbaProject.Modules[idx];
module.Name = "TestModule";

module.Codes = "Sub ShowMessage()" + "\r\n" +
"    MsgBox \"Welcome to Aspose!\"" + "\r\n" +
"End Sub";

//Save the workbook
workbook.Save("output.xlsm", SaveFormat.Xlsm);

Improved Chart Creation Mechanism

Aspose.Cells for .NET 8.4.2 has exposed simplified mechanism to create charts on the fly. Please note, the conventional means of creating charts involves several steps that may include, but not limited to specifying the series and category axis data ranges separately. With new mechanism, all you have to do is to specify the chart’s data range while using the Chart.SetChartDataRange method.

The SetChartDataRange method accepts two parameters, where first parameter is of type String in order to specify the cell area from which to plot the data series. The second parameter is of type Boolean that specifies the plot orientation, that is; whether to plot the chart data series from a range of cell values by row or by columns.

The following code snippet shows how to create a column chart with few lines of code assuming that the chart’s plot series data is present on the same worksheet from cell A1 to D4.

//Add a new chart of type Column to the chart collection
int idx = worksheet.Charts.Add(ChartType.Column, 6, 5, 20, 13);

//Retrieve the newly added chart instance
Chart ch = worksheet.Charts[idx];

//Specify the chart's data series from cell A1 to D4
ch.SetChartDataRange("A1:D4", true);

Other Enhancements and Fixes

In the new version, we have also provided the following new enhancements:

  • Provided support for IFNA function.
  • Added an overloaded version of Cells.CopyColumns method that can be used to repeat the source columns onto the destination while copy operation.
  • Exposed 2 new fields for the PasteType enumeration in order to mimic the Excel’s ‘All’ & ‘All except borders’ functionality for pasting range of cells.

For more details on the above enhancements, please see the document Public API Changes in Aspose.Cells 8.4.2.

We have fixed a few exceptions that occurred while reading and writing Microsoft Excel file formats, rendering Excel spreadsheets to PDF format and rendering images from worksheets in the workbook.

In this release, several important issues have been addressed. For example, issues around reading/ writing Microsoft Excel file formats, deleting rows, retrieving data validation applied on the cells, manipulating list objects/ tables, rendering to HTML file format, manipulating drawing objects and shapes, rendering images from Excel worksheets, manipulating charts with formatting, rendering images files from charts and exporting Excel workbooks to PDF format have been resolved. We have also enhanced the Aspose.Cells formula calculation engine and fixed a few relevant issues in this release.

We have also figured out a few issues posted by the users in the web based Grid control by Aspose.Cells for .NET.

To see a complete list of enhancements and fixes, and to download Aspose.Cells for .NET 8.4.2, please visit the download page.