How to globalize the currency, date and time formats applied data in JavaScript Spreadsheet
This knowledge base explains to globalize the currency, date and time formats applied data in the Spreadsheet. You can adapt the Spreadsheet to various languages by parsing and formatting the date or number (Internationalization), adding culture specific customization and translation to the text (Localization).
Globalization
The Internationalization library provides support for formatting and parsing date and number objects using the official Unicode CLDR JSON data.
By default, Spreadsheet is set to follow the English culture (‘en-US’). If you want to go with different culture other than English, follow the below steps.
In the below sample, we have set the French culture(fr-CH) and Euro currency code.
Installing CLDR Data
CLDR data is available as npm package. So, you can install it through the below command to our package.
npm install cldr-data
Loading Culture Data
It requires the following CLDR data to be loaded using loadCldr function for cultures other than en-US.
File Name | Path |
ca-gregorian | cldr/main/en/ca-gregorian.json |
timeZoneNames | cldr/main/en/timeZoneNames.json |
numbers | cldr/main/en/numbers.json |
numberingSystems | cldr/supplemental/numberingSystems.json |
currencies | cldr/main/en/currencies.json |
Binding to i18n library
Now import the required culture files
import { loadCldr } from '@syncfusion/ej2-base';
import * as currencies from './currencies.json';
import * as cagregorian from './ca-gregorian.json';
import * as numbers from './numbers.json';
import * as timeZoneNames from './timeZoneNames.json';
import * as numberingSystems from './numberingSystems.json';
loadCldr(currencies, cagregorian, numbers, timeZoneNames, numberingSystems);
Changing Global Culture and Currency Code
To set the default culture and the currency code for Spreadsheet control, you can use the methods setCulture for setting default locale and setCurrencyCode for setting the currency code.
Setting Global Culture
import {setCulture} from '@syncfusion/ej2-base';
setCulture('fr-CH'); //To set the global culture (French culture)
Setting Currency Code
import { setCurrencyCode} from '@syncfusion/ej2-base';
setCurrencyCode(EUR); //To set the Euro currency code
If global culture is not set, then en-US is set as default locale and USD is set as default currency code.
[HTML]
<div id="spreadsheet"></div>
[TS]
import { Spreadsheet } from '@syncfusion/ej2-spreadsheet';
import { loadCldr, L10n, setCulture, setCurrencyCode } from '@syncfusion/ej2-base';
import * as dataSource from './default-data.json';
import * as currencies from './currencies.json';
import * as cagregorian from './ca-gregorian.json';
import * as numbers from './numbers.json';
import * as timeZoneNames from './timeZoneNames.json';
import * as numberingSystems from './numberingSystems.json';
loadCldr(currencies, cagregorian, numbers, timeZoneNames, numberingSystems);
//To set the global culture (French culture)
setCulture('fr-CH');
//To set the Euro currency code
setCurrencyCode('EUR');
// To localize the text content of the Syncfusion Spreadsheet control.
L10n.load({
'fr-CH': {
'spreadsheet': {
'File': 'Fichier',
'Home': 'Accueil',
'Insert': 'Insérer',
'Formulas': 'Formules',
'Data': 'Les données',
'View': 'Vue',
'Cut': 'Coupe',
'Copy': 'Copie',
'Paste': 'Pâte',
'PasteSpecial': 'Pâte spéciale',
'All': 'Tous les',
'Values': 'Valeurs',
'Formats': 'Les formats',
'Font': 'fonte',
'FontSize': 'Taille de police',
'Bold': 'Audacieux',
'Italic': 'Italique',
'Underline': 'Souligner',
'Strikethrough': 'Barré',
'TextColor': 'Couleur du texte',
'FillColor': 'La couleur de remplissage',
'HorizontalAlignment': 'Alignement horizontal',
'AlignLeft': 'Alignez à gauche',
'AlignCenter': 'centre',
'AlignRight': 'Aligner à droite',
'VerticalAlignment': 'Alignement vertical',
'AlignTop': 'Aligner en haut',
'AlignMiddle': 'Aligner le milieu',
'AlignBottom': 'Aligner le bas',
'InsertFunction': 'Insérer une fonction',
'Delete': 'Effacer',
'Rename': 'Rebaptiser',
'Hide': 'Cacher',
'Unhide': 'Démasquer',
'NumberFormat': 'Nombre Format',
}
}
});
/**
* Spreadsheet locale sample
*/
//Initialize Spreadsheet component
let spreadsheet: Spreadsheet = new Spreadsheet({
// Specifies the locale
locale: 'fr-CH',
sheets: [
{
name: 'Car Sales Report',
ranges: [{ dataSource: (dataSource as any).defaultData }],
rows: [
{
index: 30,
cells: [
{ index: 4, value: 'Total Amount:', style: { fontWeight: 'bold', textAlign: 'right' } },
{ formula: '=SUM(F2:F30)', style: { fontWeight: 'bold' } },
]
}],
columns: [
{ width: 180 }, { width: 130 }, { width: 130 }, { width: 180 },
{ width: 130 }, { width: 120 }
]
}],
created: (): void => {
//Applies cell and number formatting to specified range of the active sheet
spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }, 'A1:G1');
spreadsheet.numberFormat(getFormatFromType("Currency"), "F2:F31");
}
});
//Render initialized Spreadsheet component
spreadsheet.appendTo('#spreadsheet');
Demo Sample: https://stackblitz.com/edit/e5eytz-aktaxr?file=index.ts
All components’ localized texts are provided in the ej2-locale GitHub repository. Also find the example sample from this link.
