Using GlobalizationSettings Class for Custom Subtotal Labels and Other Label of Pie Chart with JavaScript via C++
Contents
[
Hide
]
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.totalName(ConsolidationFunction): Gets the total name of the function.
- GlobalizationSettings.grandTotalName(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.totalName(ConsolidationFunction) & GlobalizationSettings.grandTotalName(ConsolidationFunction) methods as demonstrated ahead.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Custom GlobalizationSettings Example</title>
</head>
<body>
<h1>Custom GlobalizationSettings Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
// Defines a custom class derived from GlobalizationSettings class
class CustomSettings extends AsposeCells.GlobalizationSettings {
// Overrides the totalName method (converted from getTotalName)
totalName(functionType) {
switch (functionType) {
case AsposeCells.ConsolidationFunction.Average:
return "AVG";
default:
return super.totalName(functionType);
}
}
// Overrides the grandTotalName method (converted from getGrandTotalName)
grandTotalName(functionType) {
switch (functionType) {
case AsposeCells.ConsolidationFunction.Average:
return "GRD AVG";
default:
return super.grandTotalName(functionType);
}
}
}
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
const resultDiv = document.getElementById('result');
if (!fileInput.files.length) {
resultDiv.innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
// Read the selected file and process with Aspose.Cells
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiate a Workbook object from the file bytes
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Create and assign the custom globalization settings to the workbook
const customSettings = new CustomSettings();
workbook.globalizationSettings = customSettings;
// Save the workbook and provide a download link
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
resultDiv.innerHTML = '<p style="color: green;">Custom globalization settings applied. Click the download link to get the modified file.</p>';
});
</script>
</html>
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.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells Subtotal with Custom Globalization Settings</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
// Defines a custom class derived from GlobalizationSettings class
class CustomSettings extends AsposeCells.GlobalizationSettings {
// Overrides the TotalName method (converted from getTotalName)
totalName(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.totalName(functionType);
}
}
// Overrides the GrandTotalName method (converted from getGrandTotalName)
grandTotalName(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.grandTotalName(functionType);
}
}
}
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
if (!fileInput.files.length) {
document.getElementById('result').innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiating a Workbook object from uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Assigns the GlobalizationSettings property of the WorkbookSettings class to a custom class created
workbook.settings.globalizationSettings = new CustomSettings();
// Accesses the 1st worksheet from the collection which contains data that resides in the cell range A2:B9
const sheet = workbook.worksheets.get(0);
// Adds Subtotal of type Average to the worksheet
sheet.cells.subtotal(AsposeCells.CellArea.createCellArea("A2", "B9"), 0, AsposeCells.ConsolidationFunction.Average, [1]);
// Calculates Formulas
workbook.calculateFormula();
// Auto fits all columns
sheet.autoFitColumns();
// Saving the workbook and preparing download link
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output_out.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Operation completed successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
The GlobalizationSettings class only works for adding new Subtotals. If a spreadsheet already contains Subtotals, their labels cannot be modified.