Amjad Sahi November 17, 20162 Comments

Create Custom Subtotal Labels & Data Label of Pie Chart, Create Style object using CellsFactory and many more with Aspose.Cells for .NET 16.11.0

Create Custom Subtotal Labels & Data Label of Pie Chart, Create Style object using CellsFactory and many more with Aspose.Cells for .NET 16.11.0

November 17, 2016
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 v16.11.0. The new release includes some features and other important enhancements with some critical bug fixes. Please see the release notes in order to know what is new and what has been changed/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 valuable features and other enhancements here.

Create Custom Subtotal Labels

Aspose.Cells for .NET 16.11.0 has added the support for the creation of custom subtotal labels allowing the application developers to customize the labels according to the region or personal preferences. In order to provide this feature, the latest version of the Aspose.Cells for .NET has exposed the GlobalizationSettings class offering the following 2 methods which can be overridden in a custom class to get desired labels for the Subtotals.

  • GlobalizationSettings.GetTotalName: Gets the total name of the function.
  • GlobalizationSettings.GetGrandTotalName: Gets the grand total name of the function.

The GlobalizationSettings class can be used to customize the Subtotal labels by overriding the GlobalizationSettings.GetTotalName & GlobalizationSettings.GetGrandTotalName methods as demonstrated below.

// Defines a custom class derived from GlobalizationSettings class
class CustomSettings : GlobalizationSettings
{
    // Overrides the GetTotalName method
    public override string GetTotalName(ConsolidationFunction functionType)
    {
        // Checks the function type used to add the subtotals
        switch (functionType)
        {
            // Returns custom value based on the function type used to add the subtotals
            case ConsolidationFunction.Average:
                return "AVG";

            // Handle other cases as per requirement

            default:
                return base.GetTotalName(functionType);
        }
    }

    // Overrides the GetGrandTotalName method
    public override string GetGrandTotalName(ConsolidationFunction functionType)
    {
        // Checks the function type used to add the subtotals
        switch (functionType)
        {
            // Returns custom value based on the function type used to add the subtotals
            case ConsolidationFunction.Average:
                return "GRD AVG";

            // Handle other cases as per requirement

            default:
                return base.GetGrandTotalName(functionType);
        }
    }
}

In order to inject custom labels, it is required to assign the WorkbookSettings.GlobalizationSettings property to an instance of the CustomSettings class defined above, before adding the Subtotals to the worksheet

// Loads an existing spreadsheet containing some data
Workbook book = new Workbook(dir + "sample.xlsx");

// Assigns the GlobalizationSettings property of the WorkbookSettings class
// to the class created in first step
book.Settings.GlobalizationSettings = new CustomSettings();

// Accesses the 1st worksheet from the collection which contains data
// Data resides in the cell range A2:B9
Worksheet sheet = book.Worksheets[0];

// Adds Subtotal of type Average to the worksheet
sheet.Cells.Subtotal(CellArea.CreateCellArea("A2", "B9"), 0, ConsolidationFunction.Average, new int[] { 1 });

// Calculates Formulas
book.CalculateFormula();

// Auto fits all columns
sheet.AutoFitColumns();

// Saves the workbook on disc
book.Save(dir + "output.xlsx");

Render Other Custom Label for Pie Chart

The GlobalizationSettings class also offers the GetOtherName method which is useful to give the “Other” label of Pie charts a custom value. The following snippet defines a custom class and overrides the GetOtherName method to get a custom label based on the system’s regional (culture) settings.

// Defines a custom class inherited by GlobalizationSettings class
class CustomSettings : GlobalizationSettings
{
    // Overrides the GetOtherName method
    public override string GetOtherName()
    {
        // Gets the culture identifier for the current system
        int lcid = System.Globalization.CultureInfo.CurrentCulture.LCID;
        switch (lcid)
        {
            // Handles case for English
            case 1033:
                return "Other";
            // Handles case for French
            case 1036:
                return "Autre";
            // Handles case for German
            case 1031:
                return "Andere";

            // Handle other cases

            default:
                return base.GetOtherName();
        }
    }
}

The following snippet loads an existing spreadsheet containing a Pie chart, and renders the chart to image while utilizing the CustomSettings class created above.

// Loads an existing spreadsheet containing a pie chart
Workbook book = new Workbook(dir + "sample.xlsx");

// Assigns the GlobalizationSettings property of the WorkbookSettings class
// to the class created in first step
book.Settings.GlobalizationSettings = new Cells.CustomSettings();

// Accesses the 1st worksheet from the collection which contains pie chart
Worksheet sheet = book.Worksheets[0];

// Accesses the 1st chart from the collection
Chart chart = sheet.Charts[0];

// Refreshes the chart
chart.Calculate();

// Renders the chart to image
chart.ToImage(dir + "output.png", new ImageOrPrintOptions());

Please check detailed article on Introduction to GlobalizationSettings Class if you want to get more in-depth knowledge of aforementioned usage scenarios.

Create Style Object with CellsFactory Class

Aspose.Cells 16.11.0 has exposed the CellsFactory class which currently has one method, that is; CreateStyle. The CellsFactory.CreateStyle method can be used to create an instance of Style class without adding it to the pool of workbook styles.

Here is a simple usage scenario of newly exposed APIs to create a simple Style object, whereas a detailed article can be viewed on Creating Style Object with CellsFactory Class.

//Initializes the CellsFactory class
CellsFactory factory = new CellsFactory();

//Creates an instance of Style
Style style = factory.CreateStyle();

Access Hyperlink from a GridWeb

Aspose.Cells.GridWeb for .NET 16.11.0 has exposed GetHyperlink method to the GridHyperlinkCollection class that allows to get the instance of GridHyperlink by either passing an instance GridCell or a pair of integers corresponding to the row column indices.

Here is a simple usage scenario of GridHyperlinkCollection.GetHyperlink method, whereas a detailed article can be viewed on Access GridHyperlink from a GridCell Object.

// Gets the active worksheet from the collection
GridWorksheet sheet = GridWeb1.WorkSheets[GridWeb1.ActiveSheetIndex];

// Accesses the GridHyperlinkCollection
GridHyperlinkCollection links = sheet.Hyperlinks;

// Gets hyperlink from cell A1
GridHyperlink link = links.GetHyperlink(sheet.Cells["A1"]);

// Gets hyperlink from cell D1
link = links.GetHyperlink(0, 3);

Other Enhancements and Fixes

Aspose.Cells for .NET 16.11.0 has fixed a few critical bugs as well as enhanced its core for more stability. A few of the worth mentioning enhancements are as follow.

Furthermore, in this release, we have fixed several other issues. For example, issues around reading/writing MS Excel Excel file formats, manipulating and rendering shapes, manipulating PivotTables, copying ranges in the worksheet, rendering to HTML file format, rendering images from Excel worksheets, rendering images files from charts and exporting Excel workbooks to PDF format have been resolved.

Changes to the Public API

This revision of Aspose.Cells for .NET has made some changes to the Public API. A few of the worth mentioning changes are as follow whereas details can be viewed from the Migration Manual.

  • Constructor for the Style class has been marked obsoleted whereas an alternative approach has been exposed that uses the CellsFactory class.
  • The Cell.GetConditionalStyle method has been completely removed from the public APIs because it was obsoleted some time back and an alternative approach has already been exposed by using Cell.GetConditionalFormattingResult method.
  • The Cells.GetMaxDataRowInColumn(int column) method has been removed. It is advised to use the Cells.GetLastDataRow(int) method as an alternative.
  • The PageSetup.Draft property has been removed. It is advised to use the PageSetup.PrintDraft property instead.
  • The AutoFilter.FilterColumnCollection property has been removed while providing the AutoFilter.FilterColumns property to achieve the same goal.

Aspose.Cells for .NET Resources

The resources, you may need to accomplish your tasks:

Join the Conversation

2 Comments

  1. Pingback: My Homepage
Leave a comment

Posted inAspose.Cells Product Family
 

Related Articles