Modify VBA Codes, Extract Theme Data, Set PivotTable Layout and Convert DataBars to Images with Aspose.Cells for .NET 8.4.0

Aspose.Cells for .NET logoAspose.Cells for .NET 8.4.0 has been released. This release contains many useful features and other enhancements including the long awaited feature to manipulate VBA codes embedded inside 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.

Access & Modify VBA Code

Many of the Aspose.Cells users had been waiting for this feature, and now it is available with the release of Aspose.Cells for .NET 8.4.0. The latest version has exposed a new namespace named Aspose.Cells.Vba and a few classes to provide the means to access the VBA code embedded inside the macro enabled spreadsheets, and to update the code as per your requirements. The details of the newly exposed classes are as follow.

  • VbaProject class can be used to fetch the VBA project from a given spreadsheet.
  • VbaModuleCollection class represents the collection of VBA modules that are part of a given VbaProject.
  • VbaModule class represents a single module from the VbaModuleCollection.

The following code snippet shows how to dynamically modify the VBA code segments.

//Create workbook object from source Excel file
Workbook workbook = new Workbook("sample.xlsm");

//Change the VBA Module Code
foreach (VbaModule module in workbook.VbaProject.Modules)
    string code = module.Codes;

    //Replace the original message with the modified message
    if (code.Contains("This is test message."))
        code = code.Replace("This is test message.", "This is Aspose.Cells message.");
        module.Codes = code;

//Save the output Excel file

See the document on how to Modify VBA or Macro Code via Aspose.Cells for your reference.

Extract Theme Data from Excel File

Aspose.Cells allows the users to extract Theme related data from Excel file. For example, you can extract Theme Name applied to workbook and Theme Color applied to cell or borders of the cell etc. Please see the document on how to extract theme data for your reference.

Ability to Remove Pivot Table from Worksheet

Another worth mentioning feature is the support for Pivot Table removal. Aspose.Cells has provided this feature by exposing two new methods for the PivotTableCollection class that allows to delete a specific PivotTable from the collection depending upon the input parameter.

Here are a few details about the newly exposed methods whereas the code snippets are available in the detailed article as linked above.

  • PivotTableCollection.Remove method accepts an object of PivotTable, and removes it from the collection.
  • PivotTableCollection.RemoveAt method accepts a zero index based integer value and removes the particular PivotTable from the collection.

Support for Different Pivot Table Layouts

Microsoft Excel has predefined layouts for the Pivot Tables as listed below. Upon choosing any of these layouts, Microsoft Excel applies formatting to the Pivot Tables accordingly.

  • Compact Form
  • Outline Form
  • Tabular Form

Aspose.Cells for .NET 8.4.0 provides the same functionality while using the newly exposed methods for the PivotTable class that allows to dynamically set the layouts as discussed above. Detailed article and sample code snippets are available at Changing the Layout of Pivot Table.

Convert DataBars to Images

With the release of v8.4.0, the Aspose.Cells API has provided the DataBar.ToImage method to save the conditionally formatted DataBar in image format. The DataBar.ToImage method accepts two parameters as detailed below.

  • The first parameter is of type Cell on which conditional formatting has been applied.
  • The second parameter is of type ImageOrPrintOptions in order to set different parameters of the resultant image.

The following sample code demonstrates the use of DataBar.ToImage method to render the DataBar in image format.

//Create workbook object from source excel file
Workbook workbook = new Workbook("source.xlsx");

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

//Access the cell which contains conditional formatting databar
Cell cell = worksheet.Cells["C1"];

//Get the conditional formatting of the cell
FormatConditionCollection fcc = cell.GetFormatConditions();

//Access the conditional formatting databar
DataBar dbar = fcc[0].DataBar;

//Create image or print options
ImageOrPrintOptions opts = new ImageOrPrintOptions();
opts.ImageFormat = ImageFormat.Png;

//Get the image bytes of the databar
byte[] imgBytes = dbar.ToImage(cell, opts);

//Write image bytes on the disk
File.WriteAllBytes("databar.png", imgBytes);

Custom Properties for the Document Information Panel

Aspose.Cells can be used to add custom properties inside the workbook object which are visible in the Document Information Panel when accessed through Microsoft Excel using the File > Info > Properties > Show Document Panel menu.

The following code snippet uses the ContentTypePropertyCollection.Add method to add two custom properties.

//Create workbook object
Workbook workbook = new Workbook(FileFormatType.Xlsx);

//Add simple property without any type
workbook.ContentTypeProperties.Add("MK31", "Simple Data");

//Add date time property with type
workbook.ContentTypeProperties.Add("MK32", "04-Mar-2015", "DateTime");

//Save the workbook

Other Enhancements and Fixes

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

  • Customize the generated CSS file while saving Excel to HTML file format
  • Retain the Cell’s Number Format after using ImportTwoDimensionArray
  • Aspose.Pdf and other tools validates the output PDF/A-1B file by Aspose.Cells API
  • Workbook’s RefreshChartCache works with regional and language settings

We have fixed a few exceptions that occurred while reading and writing Microsoft Excel file formats and HTML file format. We have also fixed the exceptions that occurred while manipulating Pivot tables in the template file and combining workbooks.

In this release, several important issues have been addressed. For example, issues around reading/ writing Microsoft Excel file formats, combining worksheets, Smart Markers, manipulating PivotTables, applying formattings to the cells, using PageSetup options, rendering HTML file format, applying conditional formatting, manipulating 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 further and fixed a few relevant issues in this release.

We have fixed an issue regarding customize row/column headers in the web based Grid control provided by Aspose.Cells for .NET. We also figured out issues around setting background color, alignment of rows and charts in the template file not being refreshed 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.0, please visit the download page.