How to globalize the currency, date and time formats applied data in JavaScript Spreadsheet
This article explains how to globalize the currency, date, and time formats applied to 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 use a different culture other than English, follow the steps below.
In the below sample, we have set the French culture(fr-CH) and Euro currency code.
Installing CLDR Data
CLDR data is available as an npm package. So, you can install it using the command below for our package.
npm install cldr-dataLoading 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 the 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
Note:
If global culture is not set, then en-US is set as default locale and USD is set as the 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
Note:
All components’ localized texts are provided in the ej2-locale GitHub repository. Also find the example sample from this link.
Conclusion
We hope you enjoyed learning about how to globalize the currency, date and time formats applied data in JavaScript Spreadsheet.
You can refer to our JavaScript Spreadsheet page to learn about its other groundbreaking feature representations. You can also explore our JavaScript Spreadsheet Documentation to understand how to manipulate data.
For current customers, you can check out our components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our other controls.
If you have any queries or require clarifications, please let us know in the comments section below. You can also contact us through our support forums, BoldDesk Support, or feedback portal. We are always happy to assist you!
