Using GlobalizationSettings Class for Custom Subtotal Labels and Other Label of Pie Chart
Possible Usage Scenarios
Aspose.Cells APIs have exposed the GlobalizationSettings class in order to deal with the scenarios where the user wishes to use custom labels for Subtotals in a spreadsheet. Moreover, the GlobalizationSettings class can also be used to modify the Other label for the Pie chart while rendering worksheet or chart.
Introduction to GlobalizationSettings Class
The GlobalizationSettings class currently offers the following 3 methods which can be overridden in a custom class to get desired labels for the Subtotals or to render custom text for the Other label of a Pie chart.
- GlobalizationSettings.GetTotalName: Gets the total name of the function.
- GlobalizationSettings.GetGrandTotalName: Gets the grand total name of the function.
- GlobalizationSettings.GetOtherName: Gets the name of “Other” labels for Pie charts.
Custom Labels for Subtotals
The GlobalizationSettings class can be used to customize the Subtotal labels by overriding the GlobalizationSettings.GetTotalName & GlobalizationSettings.GetGrandTotalName methods as demonstrated ahead.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// 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.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Loads an existing spreadsheet containing some data | |
Workbook book = new Workbook(dataDir + "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 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(dataDir + "output_out.xlsx"); |
Custom Text for Other Label of Pie Chart
The GlobalizationSettings class offers 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 culture identifier.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// Defines a custom class inherited by GlobalizationSettings class | |
class GlobalCustomSettings : ChartGlobalizationSettings | |
{ | |
// 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 an image while utilizing the CustomSettings class created above.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Loads an existing spreadsheet containing a pie chart | |
Workbook book = new Workbook(dataDir + "sample.xlsx"); | |
// Assigns the GlobalizationSettings property of the WorkbookSettings class to the class created in first step | |
book.Settings.GlobalizationSettings.ChartSettings = new GlobalCustomSettings(); | |
// 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(dataDir + "output_out.png", new ImageOrPrintOptions()); |