Using GlobalizationSettings Class for Custom Subtotal Labels and Other Label of Pie Chart with Node.js via C++
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(ConsolidationFunction): Gets the total name of the function.
- GlobalizationSettings.getGrandTotalName(ConsolidationFunction): Gets the grand total name of the function.
Custom Labels for Subtotals
The GlobalizationSettings class can be used to customize the Subtotal labels by overriding the GlobalizationSettings.getTotalName(ConsolidationFunction) & GlobalizationSettings.getGrandTotalName(ConsolidationFunction) methods as demonstrated ahead.
const AsposeCells = require("aspose.cells.node");
const path = require("path");
// Defines a custom class derived from GlobalizationSettings class
class CustomSettings extends AsposeCells.GlobalizationSettings {
// Overrides the GetTotalName method
getTotalName(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 AsposeCells.ConsolidationFunction.Average:
return "AVG";
// Handle other cases as per requirement
default:
return super.getTotalName(functionType);
}
}
// Overrides the GetGrandTotalName method
getGrandTotalName(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 AsposeCells.ConsolidationFunction.Average:
return "GRD AVG";
// Handle other cases as per requirement
default:
return super.getGrandTotalName(functionType);
}
}
}
In order to inject custom labels, it is required to assign the WorkbookSettings.getGlobalizationSettings() property to an instance of the CustomSettings class defined above before adding the Subtotals to the worksheet.
const AsposeCells = require("aspose.cells.node");
const path = require("path");
// Defines a custom class derived from GlobalizationSettings class
class CustomSettings extends AsposeCells.GlobalizationSettings {
// Overrides the GetTotalName method
getTotalName(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 AsposeCells.ConsolidationFunction.Average:
return "AVG";
// Handle other cases as per requirement
default:
return super.getTotalName(functionType);
}
}
// Overrides the GetGrandTotalName method
getGrandTotalName(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 AsposeCells.ConsolidationFunction.Average:
return "GRD AVG";
// Handle other cases as per requirement
default:
return super.getGrandTotalName(functionType);
}
}
}
try {
const path = require("path");
const AsposeCells = require("aspose.cells.node");
// The path to the documents directory.
const dataDir = path.join(__dirname, "data");
// Loads an existing spreadsheet containing some data
const workbook = new AsposeCells.Workbook(path.join(dataDir, "sample.xlsx"));
// Assigns the GlobalizationSettings property of the WorkbookSettings class to a custom class created
workbook.getSettings().setGlobalizationSettings(new CustomSettings());
// Accesses the 1st worksheet from the collection which contains data that resides in the cell range A2:B9
const sheet = workbook.getWorksheets().get(0);
// Adds Subtotal of type Average to the worksheet
sheet.getCells().subtotal(AsposeCells.CellArea.createCellArea("A2", "B9"), 0, AsposeCells.ConsolidationFunction.Average, [1]);
// Calculates Formulas
workbook.calculateFormula();
// Auto fits all columns
sheet.autoFitColumns();
// Saves the workbook on disc
workbook.save(path.join(dataDir, "output_out.xlsx"));
} catch (error) {
console.error(`Test failed: ${error.message}`);
throw error;
}