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.

  1. GlobalizationSettings.getTotalName: Gets the total name of the function.
  2. GlobalizationSettings.getGrandTotalName: Gets the grand total name of the function.
  3. 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-Java
public String getTotalName(int functionType) {
switch (functionType) {
case ConsolidationFunction.AVERAGE:
return "AVG";
// Handle other cases
default:
return super.getTotalName(functionType);
}
}
public String getGrandTotalName(int functionType) {
switch (functionType) {
case ConsolidationFunction.AVERAGE:
return "GRAND AVG";
// Handle other cases
default:
return super.getGrandTotalName(functionType);
}
}
public String getOtherName()
{
String language = Locale.getDefault().getLanguage();
System.out.println(language);
switch (language)
{
case "en":
return "Other";
case "fr":
return "Autre";
case "de":
return "Andere";
//Handle other cases as per requirement
default:
return super.getOtherName();
}
}

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-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(CustomLabelsforSubtotals.class) + "articles/";
// 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.getSettings().setGlobalizationSettings(new CustomSettings());
// Accesses the 1st worksheet from the collection which contains data
// Data resides in the cell range A2:B9
Worksheet sheet = book.getWorksheets().get(0);
// Adds SubTotal of type Average to the worksheet
sheet.getCells().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 + "CustomLabelsforSubtotals_out.xlsx");

Custom Text for Other Label of Pie Chart

The GlobalizationSettings class 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 default language set for JVM.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
public String getTotalName(int functionType) {
switch (functionType) {
case ConsolidationFunction.AVERAGE:
return "AVG";
// Handle other cases
default:
return super.getTotalName(functionType);
}
}
public String getGrandTotalName(int functionType) {
switch (functionType) {
case ConsolidationFunction.AVERAGE:
return "GRAND AVG";
// Handle other cases
default:
return super.getGrandTotalName(functionType);
}
}
public String getOtherName()
{
String language = Locale.getDefault().getLanguage();
System.out.println(language);
switch (language)
{
case "en":
return "Other";
case "fr":
return "Autre";
case "de":
return "Andere";
//Handle other cases as per requirement
default:
return super.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-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(CustomTextforOtherLabelofPieChart.class) + "articles/";
//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.getSettings().setGlobalizationSettings(new CustomSettings());
//Accesses the 1st worksheet from the collection which contains pie chart
Worksheet sheet = book.getWorksheets().get(0);
//Accesses the 1st chart from the collection
Chart chart = sheet.getCharts().get(0);
//Refreshes the chart
chart.calculate();
//Renders the chart to image
chart.toImage(dataDir + "CustomTextforOtherLabelofPieChart_out.png", new ImageOrPrintOptions());

Following is the resultant image when locale of the machine is set to France. As you can see that the label “Other” has been translated to “Autre” as defined in CustomSettings class.

todo:image_alt_text