Implement Cell.FormulaLocal similar to Excel VBA Range.FormulaLocal with JavaScript via C++
Possible Usage Scenarios
Microsoft Excel Formulas may have different names in different locales or regions or languages. For example, SUM function is called SUMME in German. Aspose.Cells cannot work with non-English function names. In Microsoft Excel VBA, there is Range.FormulaLocal property that returns the name of the function as per its language or region. Aspose.Cells for JavaScript via C++ also provides Cell.formulaLocal property for this purpose. However, this property will only work when you implement GlobalizationSettings.localFunctionName(standardName) method.
Implement Cell.FormulaLocal similar to Excel VBA Range.FormulaLocal
The following sample code explains how to implement GlobalizationSettings.localFunctionName(standardName) method. The method returns the local name of the standard function. If the standard function name is SUM, it returns UserFormulaLocal_SUM. You can change the code as per your needs and return the correct local function names e.g. SUM is SUMME in German and TEXT is ТЕКСТ in Russian. Please also see the console output of the sample code given below for reference.
Sample Code
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells Globalization 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");
});
// Define GS class implementing localization override
class GS extends AsposeCells.GlobalizationSettings {
localFunctionName(standardName) {
// Change the SUM function name as per your needs.
if (standardName === "SUM") {
return "UserFormulaLocal_SUM";
}
// Change the AVERAGE function name as per your needs.
if (standardName === "AVERAGE") {
return "UserFormulaLocal_AVERAGE";
}
return "";
}
}
document.getElementById('runExample').addEventListener('click', () => {
const resultDiv = document.getElementById('result');
// Create workbook
const wb = new Workbook();
// Assign GlobalizationSettings implementation class
wb.settings.globalizationSettings = new GS();
// Access first worksheet
const ws = wb.worksheets.get(0);
// Access some cell
const cell = ws.cells.get("C4");
// Assign SUM formula and get its FormulaLocal
cell.formula = "SUM(A1:A2)";
const formulaLocal1 = cell.formulaLocal;
// Assign AVERAGE formula and get its FormulaLocal
cell.formula = "=AVERAGE(B1:B2, B5)";
const formulaLocal2 = cell.formulaLocal;
resultDiv.innerHTML = `<p>Formula Local 1: ${formulaLocal1}</p><p>Formula Local 2: ${formulaLocal2}</p>`;
// Save the workbook and provide download link
const outputData = wb.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 Excel File';
});
</script>
</html>
Console Output
Formula Local: =UserFormulaLocal_SUM(A1:A2)
Formula Local: =UserFormulaLocal_AVERAGE(B1:B2,B5)