Calculate the Width and Height of the Cell Value and Get an Array of Values from ICustomFunction using Aspose.Cells for .NET 8.5.1

Share on FacebookTweet about this on TwitterShare on LinkedIn

Aspose.Cells for .NET logoAspose.Cells for .NET 8.5.1 has been released. This release contains some useful features and other enhancements along with some critical bug fixes. 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.

Calculate the Width and Height of the Cell Value

Aspose.Cells for .NET provides Cell.GetWidthOfValue and Cell.GetHeightOfValue methods to calculate the width & height of the cell value in the unit of pixels. These methods are useful in scenarios where application requirement is to set the width of the column and height of the row according to the size of cell value.

The following sample code explains the usage of Cell.GetWidthOfValue and Cell.GetHeightOfValue methods.


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

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

//Access cell B2 and add some value inside it
Cell cell = worksheet.Cells["B2"];
cell.PutValue("Welcome to Aspose!");

//Enlarge its font to size 16
Style style = cell.GetStyle();
style.Font.Size = 16;
cell.SetStyle(style);

//Calculate the width and height of the cell value in unit of pixels
int widthOfValue = cell.GetWidthOfValue();
int heightOfValue = cell.GetHeightOfValue();

//Print both values
Console.WriteLine("Width of Cell Value: " + widthOfValue);
Console.WriteLine("Height of Cell Value: " + heightOfValue);

//Set the row height and column width to adjust/fit the cell value inside cell
worksheet.Cells.SetColumnWidthPixel(1, widthOfValue);
worksheet.Cells.SetRowHeightPixel(1, heightOfValue);

//Save the output excel file
workbook.Save("output.xlsx");

Get a Range of Values using ICustomFunction

Aspose.Cells APIs provide the ICustomFunction interface which can be used to implement the user defined or custom functions that are not supported by Microsoft Excel as built-in functions. While implementing the ICustomFunction interface, most of the times we require to return a single cell value however, sometimes we may also need to return a range of values.

Following code snippet demonstrates how to return a range of values from ICustomFunction.


using System;
using System.Collections;
using Aspose.Cells;

namespace SimpleTest
{
    class CustomFunctionStaticValue : ICustomFunction
    {
        public object CalculateCustomFunction(string functionName, ArrayList paramsList, ArrayList contextObjects)
        {
            return new object[][] {
                new object[]{new DateTime(2015, 6, 12, 10, 6, 30), 2},
                new object[]{3.0, "Test"}
                };
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            Workbook wb = new Workbook();
            Cells cells = wb.Worksheets[0].Cells;

            Cell cell = cells[0, 0];
            cell.SetArrayFormula("=MYFUNC()", 2, 2);

            Style style = cell.GetStyle();
            style.Number = 14;
            cell.SetStyle(style);

            CalculationOptions copt = new CalculationOptions();
            copt.CustomFunction = new CustomFunctionStaticValue();
            wb.CalculateFormula(copt);

            //Save to xlsx by setting the calc mode to manual
            wb.Settings.CalcMode = CalcModeType.Manual;
            wb.Save("output.xlsx");

            //Save to pdf
            wb.Save("output.pdf");

        }
    }
}

Release Unmanaged Resources of Workbook

Aspose.Cells for .NET 8.5.1 has exposed the Workbook.Dispose() method to release the unmanaged resources of the Workbook object. The dispose pattern is used only for objects that access unmanaged resources, such as file and pipe handles, registry handles, wait handles, or pointers to blocks of unmanaged memory. This is because the garbage collector is very efficient at reclaiming unused managed objects, but it is unable to reclaim unmanaged objects.

Detect the Data Source Type of ListObject

With this revision, the API has exposed the TableDataSourceType enumeration & readonly ListObject.DataSourceType property that can be used to detect the data source type of a ListObject.

The TableDataSourceType enumeration has the following fields.

  • TableDataSourceType.QueryTable
  • TableDataSourceType.XML
  • TableDataSourceType.Worksheet
  • TableDataSourceType.SharePoint

Other Enhancements and Fixes

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

  • Provided support to link to external workbooks in the desktop grid control provided by Aspose.Cells for .NET.
  • Support Right-to-Left Display of Worksheets in desktop grid control provided by Aspose.Cells for .NET.

We have handled some exceptions when reading/writing Excel spreadsheets and copying worksheets from other workbook. We have also fixed an exception in Shape to Image rendering.

In this release, several important issues have been addressed. For example, issues around reading/ writing Microsoft Excel file formats, manipulating Pivot Tables, combining workbooks, setting conditional formatting, protecting workbooks, 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 issues in this regard.

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