Number Formatting in React Spreadsheet component

3 Jul 202624 minutes to read

Number formatting defines the type of data displayed in the Spreadsheet. You can enable or disable the number formatting using the allowNumberFormatting property. The Spreadsheet supports the following number formatting types:

Types Format Code Format ID
General(default) NA 0
Number 0.00 2
Currency $#,##0.00 NA
Accounting _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_) 44
ShortDate m/d/yyyy 14
LongDate dddd, mmmm dd, yyyy NA
Time h:mm:ss AM/PM NA
Percentage 0.00% 10
Fraction # ?/? 12
Scientific 0.00E+00 11
Text @ 49

Number formatting can be applied in following ways,

  • Using the format property in cell, you can set the desired format to each cell at initial load.
  • Using the numberFormat method, you can set the number format to a cell or range of cells.
  • Selecting the number format option from ribbon toolbar.

Custom Number Formatting

Spreadsheet supports custom number formats to display your data as numbers, dates, times, percentages, and currency values. If the pre-defined number formats do not meet your needs, you can set your own custom formats using custom number formats dialog or numberFormat method.

The different types of custom number format populated in the custom number format dialog are,

Type Format Code Format ID
General(default) NA 0
Number 0 1
Number 0.00 2
Number #,##0 3
Number #,##0.00 4
Number #,##0_);(#,##0) 37
Number #,##0_);[Red](#,##0) 38
Number #,##0.00_);(#,##0.00) 39
Number #,##0.00_);[Red](#,##0.00) 40
Currency $#,##0_);($#,##0) 5
Currency $#,##0_);[Red]($#,##0) 6
Currency $#,##0.00_);($#,##0.00) 7
Currency $#,##0.00_);[Red]($#,##0.00) 8
Percentage 0% 9
Percentage 0.00% 10
Scientific 0.00E+00 11
Scientific ##0.0E+0 48
Fraction # ?/? 12
Fraction # ??/?? 13
ShortDate m/d/yyyy 14
Custom d-mmm-yy 15
Custom d-mmm 16
Custom mmm-yy 17
Custom h:mm AM/PM 18
Custom h:mm:ss AM/PM 19
Custom h:mm 20
Custom h:mm:ss 21
Custom m/d/yyyy h:mm 22
Custom mm:ss 45
Custom mm:ss.0 47
Text @ 49
Custom [h]:mm:ss 46
Accounting _($* #,##0_);_($* (#,##0);_($* "-"_);_(@_) 42
Accounting _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_) 41
Accounting _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_) 44
Accounting _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_) 43

Custom Number formatting can be applied in following ways,

  • Using the numberFormat method, you can set your own custom number format to a cell or range of cells.
  • Selecting the custom number format option from custom number formats dialog or type your own format in dialog input and then click apply button. It will apply the custom format for selected cells.

The following code example shows the number formatting in cell data.

import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective, RowsDirective, RowDirective, CellDirective, CellsDirective, getFormatFromType } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { data } from './datasource';

function App() {
    const spreadsheetRef = React.useRef(null);
    const styles = {
        verticalAlign: 'middle', textAlign: 'center', fontSize: '16pt', fontWeight: 'bold',
        border: '1px solid #e0e0e0', backgroundColor: '#EEEEEE', color: '#279377'
    };
    const cellStyle = { fontWeight: 'bold', fontStyle: 'italic' };
    React.useEffect(() => {
        let spreadsheet = spreadsheetRef.current;
        if (spreadsheet) {
            spreadsheet.cellFormat({ fontWeight: 'bold', fontSize: '12pt', backgroundColor: '#279377', textAlign: 'center', color: '#ffffff', borderBottom: '1px solid #e0e0e0' }, 'A2:F2');
            spreadsheet.cellFormat({ borderTop: '1px solid #e0e0e0', backgroundColor: '#EEEEEE' }, 'A11:F11');
            spreadsheet.setBorder({ border: '1px solid #e0e0e0' }, 'A2:F11', 'Outer');
            // Applied Accounting format to the cells from C3 to E10 range.
            spreadsheet.numberFormat('_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)', 'C3:E10');
            // Applied Percentage format to the cells from C3 to E11 range.
            spreadsheet.numberFormat('0%', 'F3:F10');
            // applied the custom number format for cell form D3 to D10 range
            spreadsheet.numberFormat('[Red][<=2000]$#,##0.00;[Blue][>2000]$#,##0.00', 'D3:D10');
            // applied the custom number format for cell from F3 to F10 range
            spreadsheet.numberFormat('#,##0.00_);[Red](#,##0.00)', 'F3:F10');
        }
    }, []);

    return (
        <div>
            <SpreadsheetComponent ref={spreadsheetRef} showSheetTabs={false} showFormulaBar={false} allowInsert={false} allowDelete={false}>
                <SheetsDirective>
                    <SheetDirective selectedRange={"U15"} showGridLines={false}>
                        <RowsDirective>
                            <RowDirective height={35} customHeight={true}>
                                <CellsDirective>
                                    <CellDirective value={'Sales Team Summary'} style={styles} colSpan={6}></CellDirective>
                                </CellsDirective>
                            </RowDirective>
                            <RowDirective index={10}>
                                <CellsDirective>
                                    <CellDirective index={1} value={'Total:'} style={cellStyle}></CellDirective>
                                    <CellDirective formula={'=SUM(C3:C10)'} format={getFormatFromType('Accounting')}></CellDirective>
                                    <CellDirective formula={'=SUM(D3:D10)'} format={'_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)'}></CellDirective>
                                    <CellDirective formula={'=SUM(E3:E10)'} format={'_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)'}></CellDirective>
                                </CellsDirective>
                            </RowDirective>
                        </RowsDirective>
                        <RangesDirective>
                            <RangeDirective dataSource={data} startCell={"A2"}></RangeDirective>
                        </RangesDirective>
                        <ColumnsDirective>
                            <ColumnDirective width={140}></ColumnDirective>
                            <ColumnDirective width={140}></ColumnDirective>
                            <ColumnDirective width={160}></ColumnDirective>
                            <ColumnDirective width={160}></ColumnDirective>
                            <ColumnDirective width={160}></ColumnDirective>
                            <ColumnDirective width={120}></ColumnDirective>
                        </ColumnsDirective>
                    </SheetDirective>
                </SheetsDirective>
            </SpreadsheetComponent>
        </div>
    );
};
export default App;

const root = createRoot(document.getElementById('root'));
root.render(<App />);
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective, RowsDirective, RowDirective, CellDirective, CellsDirective, getFormatFromType } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { CellStyleModel } from '@syncfusion/ej2-react-spreadsheet';
import { data } from './datasource';

function App() {
    const spreadsheetRef = React.useRef<SpreadsheetComponent>(null);
    const styles: CellStyleModel = {
        verticalAlign: 'middle', textAlign: 'center', fontSize: '16pt', fontWeight: 'bold',
        border: '1px solid #e0e0e0', backgroundColor: '#EEEEEE', color: '#279377'
    };
    const cellStyle: CellStyleModel = { fontWeight: 'bold', fontStyle: 'italic' };
    React.useEffect(() => {
        let spreadsheet = spreadsheetRef.current;
        if (spreadsheet) {
            spreadsheet.cellFormat({ fontWeight: 'bold', fontSize: '12pt', backgroundColor: '#279377', textAlign: 'center', color: '#ffffff', borderBottom: '1px solid #e0e0e0' }, 'A2:F2');
            spreadsheet.cellFormat({ borderTop: '1px solid #e0e0e0', backgroundColor: '#EEEEEE' }, 'A11:F11');
            spreadsheet.setBorder({ border: '1px solid #e0e0e0' }, 'A2:F11', 'Outer');
            // Applied Accounting format to the cells from C3 to E10 range.
            spreadsheet.numberFormat('_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)', 'C3:E10');
            // Applied Percentage format to the cells from C3 to E11 range.
            spreadsheet.numberFormat('0%', 'F3:F10');
            // applied the custom number format for cell form D3 to D10 range
            spreadsheet.numberFormat('[Red][<=2000]$#,##0.00;[Blue][>2000]$#,##0.00', 'D3:D10');
            // applied the custom number format for cell from F3 to F10 range
            spreadsheet.numberFormat('#,##0.00_);[Red](#,##0.00)', 'F3:F10');
        }
    }, []);

    return (
        <div>
            <SpreadsheetComponent ref={spreadsheetRef} showSheetTabs={false} showFormulaBar={false} allowInsert={false} allowDelete={false}>
                <SheetsDirective>
                    <SheetDirective selectedRange={"U15"} showGridLines={false}>
                        <RowsDirective>
                            <RowDirective height={35} customHeight={true}>
                                <CellsDirective>
                                    <CellDirective value={'Sales Team Summary'} style={styles} colSpan={6}></CellDirective>
                                </CellsDirective>
                            </RowDirective>
                            <RowDirective index={10}>
                                <CellsDirective>
                                    <CellDirective index={1} value={'Total:'} style={cellStyle}></CellDirective>
                                    <CellDirective formula={'=SUM(C3:C10)'} format={getFormatFromType('Accounting')}></CellDirective>
                                    <CellDirective formula={'=SUM(D3:D10)'} format={'_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)'}></CellDirective>
                                    <CellDirective formula={'=SUM(E3:E10)'} format={'_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)'}></CellDirective>
                                </CellsDirective>
                            </RowDirective>
                        </RowsDirective>
                        <RangesDirective>
                            <RangeDirective dataSource={data} startCell={"A2"}></RangeDirective>
                        </RangesDirective>
                        <ColumnsDirective>
                            <ColumnDirective width={140}></ColumnDirective>
                            <ColumnDirective width={140}></ColumnDirective>
                            <ColumnDirective width={160}></ColumnDirective>
                            <ColumnDirective width={160}></ColumnDirective>
                            <ColumnDirective width={160}></ColumnDirective>
                            <ColumnDirective width={120}></ColumnDirective>
                        </ColumnsDirective>
                    </SheetDirective>
                </SheetsDirective>
            </SpreadsheetComponent>
        </div>
    );
};
export default App;

const root = createRoot(document.getElementById('root')!);
root.render(<App />);
/**
 * Number formatting data source
 */
export let data = [
    { 'Salesperson': 'Jeffrey Burke', 'Region Covered': 'Oklahoma', 'February 2019 Sales': '28000', 'Cost of Sales': '2460', 'January 2019 Sales': '21238', 'Percent Change': '.32' },
    { 'Salesperson': 'Amy Fernandez', 'Region Covered': 'North Carolina', 'February 2019 Sales': '23138', 'Cost of Sales': '1521', 'January 2019 Sales': '23212', 'Percent Change': '0' },
    { 'Salesperson': 'Mark Hayes', 'Region Covered': 'Massachusetts', 'February 2019 Sales': '25092', 'Cost of Sales': '1530', 'January 2019 Sales': '20454', 'Percent Change': '.23' },
    { 'Salesperson': 'Judith Ray', 'Region Covered': 'California', 'February 2019 Sales': '21839', 'Cost of Sales': '1923', 'January 2019 Sales': '24619', 'Percent Change': '-.11' },
    { 'Salesperson': 'Rany Graham', 'Region Covered': 'South Carolina', 'February 2019 Sales': '23342', 'Cost of Sales': '2397', 'January 2019 Sales': '20045', 'Percent Change': '.16' },
    { 'Salesperson': 'Christina Foster', 'Region Covered': 'Delaware', 'February 2019 Sales': '23368', 'Cost of Sales': '1500', 'January 2019 Sales': '17537', 'Percent Change': '.33' },
    { 'Salesperson': 'Judy Green', 'Region Covered': 'Texas', 'February 2019 Sales': '21510', 'Cost of Sales': '1657', 'January 2019 Sales': '24951', 'Percent Change': '-.14' },
    { 'Salesperson': 'Paula Hall', 'Region Covered': 'Virginia', 'February 2019 Sales': '21314', 'Cost of Sales': '2418', 'January 2019 Sales': '18082', 'Percent Change': '.18' }
];
/**
 * Number formatting data source
 */
export let data: Object[] = [
    { 'Salesperson': 'Jeffrey Burke', 'Region Covered': 'Oklahoma', 'February 2019 Sales': '28000', 'Cost of Sales': '2460', 'January 2019 Sales': '21238', 'Percent Change': '.32' },
    { 'Salesperson': 'Amy Fernandez', 'Region Covered': 'North Carolina', 'February 2019 Sales': '23138', 'Cost of Sales': '1521', 'January 2019 Sales': '23212', 'Percent Change': '0' },
    { 'Salesperson': 'Mark Hayes', 'Region Covered': 'Massachusetts', 'February 2019 Sales': '25092', 'Cost of Sales': '1530', 'January 2019 Sales': '20454', 'Percent Change': '.23' },
    { 'Salesperson': 'Judith Ray', 'Region Covered': 'California', 'February 2019 Sales': '21839', 'Cost of Sales': '1923', 'January 2019 Sales': '24619', 'Percent Change': '-.11' },
    { 'Salesperson': 'Rany Graham', 'Region Covered': 'South Carolina', 'February 2019 Sales': '23342', 'Cost of Sales': '2397', 'January 2019 Sales': '20045', 'Percent Change': '.16' },
    { 'Salesperson': 'Christina Foster', 'Region Covered': 'Delaware', 'February 2019 Sales': '23368', 'Cost of Sales': '1500', 'January 2019 Sales': '17537', 'Percent Change': '.33' },
    { 'Salesperson': 'Judy Green', 'Region Covered': 'Texas', 'February 2019 Sales': '21510', 'Cost of Sales': '1657', 'January 2019 Sales': '24951', 'Percent Change': '-.14' },
    { 'Salesperson': 'Paula Hall', 'Region Covered': 'Virginia', 'February 2019 Sales': '21314', 'Cost of Sales': '2418', 'January 2019 Sales': '18082', 'Percent Change': '.18' }
];

Configure Culture-specific Custom format

Earlier, the custom format dialog always showed formats using English settings (group separator, decimal separator, and currency symbol were not updated based on the applied culture). Starting from version 27.1.*, the dialog now displays formats according to the applied culture. You can select a culture-based number format or enter your own format using the culture-specific decimal separator, group separator, and currency symbol. After that, click Apply to apply the culture-specific custom format to the selected cells.

The Spreadsheet supports customizing formats in the dialog using the configureLocalizedFormat method. In this method, you pass a collection of default number format IDs along with their corresponding format codes. The dialog will then display the customized formats. You can refer to the default number format IDs from the Excel built-in number format reference.

Compared to Excel, the date, time, currency, and accounting formats vary across different cultures. For example, when an Excel file with the date format 'm/d/yyyy' is imported in the en-US culture, the spreadsheet displays the date in that format. However, when the same file is imported in the German culture, the date format changes to 'dd.MM.yyyy', which is the default for that region. The default number format ID for the date is 14. To customize the date format based on the culture, you should map the default number format ID to the appropriate culture-specific format code, like this: { id: 14, code: 'dd.MM.yyyy' } in the configureLocalizedFormat method.

The format code should use the default decimal separator (.) and group separator (,).

The code below illustrates how culture-based format codes are mapped to their corresponding number format ID for the German culture.

import { configureLocalizedFormat } from '@syncfusion/ej2-react-spreadsheet';

const deLocaleFormats = [
    { id: 14, code: 'dd.MM.yyyy' },
    { id: 15, code: 'dd. MMM yy' },
    { id: 16, code: 'dd. MMM' },
    { id: 17, code: 'MMM yy' },
    { id: 20, code: 'hh:mm' },
    { id: 21, code: 'hh:mm:ss' },
    { id: 22, code: 'dd.MM.yyyy hh:mm' },
    { id: 37, code: '#,##0;-#,##0' },
    { id: 38, code: '#,##0;[Red]-#,##0' },
    { id: 39, code: '#,##0.00;-#,##0.00' },
    { id: 40, code: '#,##0.00;[Red]-#,##0.00' },
    { id: 5, code: '#,##0 "€";-#,##0 "€"' },
    { id: 6, code: '#,##0 "€";[Red]-#,##0 "€"' },
    { id: 7, code: '#,##0.00 "€";-#,##0.00 "€"' },
    { id: 8, code: '#,##0.00 "€";[Red]-#,##0.00 "€"' },
    { id: 41, code: '_-* #,##0_-;-* #,##0_-;_-* "-"_-;_-@_-' },
    { id: 42, code: '_-* #,##0 "€"_-;-* #,##0 "€"_-;_-* "-" "€"_-;_-@_-' },
    { id: 43, code: '_-* #,##0.00_-;-* #,##0.00_-;_-* "-"??_-;_-@_-' },
    { id: 44, code: '_-* #,##0.00 "€"_-;-* #,##0.00 "€"_-;_-* "-"?? "€"_-;_-@_-' }
];

// Mapping culture-based number formats for the "de" culture: The "spreadsheetRef.current" parameter is an instance of the spreadsheet component, and the "deLocaleFormats" parameter is an array containing format codes and their corresponding format IDs for the "de" culture.
configureLocalizedFormat(spreadsheetRef.current, deLocaleFormats);

The following code example demonstrates how to configure culture-based formats for different cultures in the spreadsheet.

import * as React from 'react';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective, getFormatFromType, configureLocalizedFormat } from '@syncfusion/ej2-react-spreadsheet';
import { DropDownListComponent } from '@syncfusion/ej2-react-dropdowns';
import { loadCldr, setCurrencyCode, setCulture, Ajax } from '@syncfusion/ej2-base';
import { data } from './datasource';

function App() {

  const spreadsheetRef = React.useRef(null);
  // Loading the culture-related files.
  const loadCultureFiles = (locales) => {
    const files = ['ca-gregorian', 'numbers', 'timeZoneNames', 'currencies', 'numberingSystems'];
    locales.forEach((locale) => {
      for (const fileName of files) {
        const url = `./cldr-data/${fileName === 'numberingSystems' ? '' : `${locale}/`}${fileName}.json`;
        const ajax = new Ajax(url, 'GET', false);
        ajax.onSuccess = (value) => loadCldr(JSON.parse(value));
        ajax.send();
      }
    });
  }

  loadCultureFiles(['de', 'fr-CH', 'zh']);
  // Setting German culture.
  setCulture('de');
  // Setting currency code for the German culture.
  setCurrencyCode('EUR');

  const localeFormats = {
    'de': [{ id: 37, code: '#,##0;-#,##0' }, { id: 38, code: '#,##0;[Red]-#,##0' },
    { id: 39, code: '#,##0.00;-#,##0.00' }, { id: 40, code: '#,##0.00;[Red]-#,##0.00' }, { id: 5, code: '#,##0 "€";-#,##0 "€"' },
    { id: 6, code: '#,##0 "€";[Red]-#,##0 "€"' }, { id: 7, code: '#,##0.00 "€";-#,##0.00 "€"' },
    { id: 8, code: '#,##0.00 "€";[Red]-#,##0.00 "€"' }, { id: 41, code: '_-* #,##0_-;-* #,##0_-;_-* "-"_-;_-@_-' },
    { id: 42, code: '_-* #,##0 "€"_-;-* #,##0 "€"_-;_-* "-" "€"_-;_-@_-' },
    { id: 43, code: '_-* #,##0.00_-;-* #,##0.00_-;_-* "-"??_-;_-@_-' },
    { id: 44, code: '_-* #,##0.00 "€"_-;-* #,##0.00 "€"_-;_-* "-"?? "€"_-;_-@_-' },
    { id: 14, code: 'dd.MM.yyyy' }, { id: 15, code: 'dd. MMM yy' }, { id: 16, code: 'dd. MMM' }, { id: 17, code: 'MMM yy' },
    { id: 20, code: 'hh:mm' }, { id: 21, code: 'hh:mm:ss' }, { id: 22, code: 'dd.MM.yyyy hh:mm' }],
    'zh': [{ id: 37, code: '#,##0;-#,##0' }, { id: 38, code: '#,##0;[Red]-#,##0' }, { id: 39, code: '#,##0.00;-#,##0.00' },
    { id: 40, code: '#,##0.00;[Red]-#,##0.00' }, { id: 5, code: '"¥"#,##0;"¥"-#,##0' }, { id: 6, code: '"¥"#,##0;[Red]"¥"-#,##0' },
    { id: 7, code: '"¥"#,##0.00;"¥"-#,##0.00' }, { id: 8, code: '"¥"#,##0.00;[Red]"¥"-#,##0.00' },
    { id: 41, code: '_ * #,##0_ ;_ * -#,##0_ ;_ * "-"_ ;_ @_' }, { id: 42, code: '_ "¥"* #,##0_ ;_ "¥"* -#,##0_ ;_ "¥"* "-"_ ;_ @_' },
    { id: 43, code: '_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * "-"??_ ;_ @_' },
    { id: 44, code: '_ "¥"* #,##0.00_ ;_ "¥"* -#,##0.00_ ;_ "¥"* "-"??_ ;_ @_' },
    { id: 14, code: 'yyyy/m/d' }, { id: 22, code: 'yyyy/m/d h:mm' }],
    'fr-CH': [{ id: 37, code: '#,##0;-#,##0' }, { id: 38, code: '#,##0;[Red]-#,##0' }, { id: 39, code: '#,##0.00;-#,##0.00' },
    { id: 40, code: '#,##0.00;[Red]-#,##0.00' }, { id: 5, code: '#,##0 "CHF";-#,##0 "CHF"' },
    { id: 6, code: '#,##0 "CHF";[Red]-#,##0 "CHF"' }, { id: 7, code: '#,##0.00 "CHF";-#,##0.00 "CHF"' },
    { id: 8, code: '#,##0.00 "CHF";[Red]-#,##0.00 "CHF"' }, { id: 14, code: 'dd.MM.yyyy' }, { id: 15, code: 'dd.MMM.yy' },
    { id: 16, code: 'dd.MMM' }, { id: 17, code: 'MMM.yy' }, { id: 20, code: 'HH:mm' }, { id: 21, code: 'HH:mm:ss' },
    { id: 22, code: 'dd.MM.yyyy HH:mm' }, { id: 42, code: '_-* #,##0 "CHF"_-;-* #,##0 "CHF"_-;_-* "-" "CHF"_-;_-@_-' },
    { id: 44, code: '_-* #,##0.00 "CHF"_-;-* #,##0.00 "CHF"_-;_-* "-"?? "CHF"_-;_-@_-' },
    { id: 41, code: '_-* #,##0_-;-* #,##0_-;_-* "-"_-;_-@_-' }, { id: 43, code: '_-* #,##0.00_-;-* #,##0.00_-;_-* "-"??_-;_-@_-' }
    ],
    'en-US': []
  };

  // Mapping default number formats for the German ('de') locale before the spreadsheet is created.
  // We can also map the formats after the spreadsheet is created in the created event.
  configureLocalizedFormat(spreadsheetRef.current, localeFormats['de']);

  const cultureList = [
    { Culture: 'German - Germany', localeOption: 'de EUR ;' },
    { Culture: 'French - Switzerland', localeOption: 'fr-CH CHF ;' },
    { Culture: 'Chinese - China', localeOption: 'zh CNY ,' },
    { Culture: 'English', localeOption: 'en-US USD ,' }
  ];

  const placeholder = 'Select a locale';
  // maps the appropriate column to fields property
  const fields = { text: 'Culture', value: 'localeOption' };

  const change = (args) => {
    const spreadsheet = spreadsheetRef.current;
    const localeOption = (args.value).split(' ');
    // Setting the culture name like 'de', 'fr-CH', 'zh', and 'en-US'.
    const cultureName = localeOption[0];
    setCulture(cultureName);
    // Setting the currency code for the selected locale like 'EUR', 'CNY', 'CHF', and 'USD'.
    setCurrencyCode(localeOption[1]);
    // Mapping the default number format codes for the selected locale.
    configureLocalizedFormat(spreadsheet, localeFormats[cultureName]);
    // Setting the culture for the spreadsheet.
    spreadsheet.locale = cultureName;
    // Setting the list separator for the selected locale.
    spreadsheet.listSeparator = localeOption[2];
    // Refreshing the changes immediately in the spreadsheet.
    spreadsheet.dataBind();
    applyFormats();
  }

  const applyFormats = () => {
    const spreadsheet = spreadsheetRef.current;
    // Apply format to the specified range in the active sheet.
    // The getFormatFromType method returns the culture-based format code based on the mapped formats.
    // If a format ID is not mapped or is not applicable, it will return the format code based on the loaded culture.
    // For 'en-US' (English) culture, the format code will be 'm/d/yyyy'.
    // For 'de' (German) culture, the format code will be 'dd.MM.yyyy'.
    // For 'fr-CH' (French-Switzerland) culture, the format code will be 'dd.MM.yyyy'.
    // For 'zh' (Chinese) culture, the format code will be 'yyyy/m/d'.
    spreadsheet.numberFormat(getFormatFromType('ShortDate'), 'B2:B11');
    // For 'en-US' (English) culture, the format code will be 'h:mm:ss AM/PM'.
    // For 'de' (German) culture, the format code will be 'HH:mm:ss'.
    // For 'fr-CH' (French-Switzerland) culture, the format code will be 'HH:mm:ss'.
    // For 'zh' (Chinese) culture, the format code will be 'h:mm:ss AM/PM'.
    spreadsheet.numberFormat(getFormatFromType('Time'), 'C2:C11');
    // For 'en-US' (English) culture, the format code will be '$#,##0.00'.
    // For 'de' (German) culture, the format code will be '#,##0.00 "€"'.
    // For 'fr-CH' (French-Switzerland) culture, the format code will be '#,##0.00 "CHF"'.
    // For 'zh' (Chinese) culture, the format code will be '"¥"#,##0.00'.
    spreadsheet.numberFormat(getFormatFromType('Currency'), 'E2:F11');
    // For 'en-US' (English) culture, the format code will be '_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)'.
    // For 'de' (German) culture, the format code will be '_-* #,##0.00 "€"_-;-* #,##0.00 "€"_-;_-* "-"?? "€"_-;_-@_-'.
    // For 'fr-CH' (French-Switzerland) culture, the format code will be '_-* #,##0.00 "CHF"_-;-* #,##0.00 "CHF"_-;_-* "-"?? "CHF"_-;_-@_-'
    // For 'zh' (Chinese) culture, the format code will be '_ "¥"* #,##0.00_ ;_ "¥"* -#,##0.00_ ;_ "¥"* "-"??_ ;_ @_'
    spreadsheet.numberFormat(getFormatFromType('Accounting'), 'H2:H11');
    // The percentage format code will be '0.00%' for all the cultures.
    spreadsheet.numberFormat('0.00%', 'G2:G11');
  }

  const created = () => {
    let spreadsheet = spreadsheetRef.current;
    if (spreadsheet) {
      spreadsheet.cellFormat({ textAlign: 'center', fontWeight: 'bold' }, 'A1:H1');
      applyFormats();
    }
  };

  return (
    <div>
      <DropDownListComponent id="ddlelement" dataSource={cultureList} fields={fields} placeholder={placeholder} change={change} />
      <SpreadsheetComponent ref={spreadsheetRef} locale='de' listSeparator=';' created={created}>
        <SheetsDirective>
          <SheetDirective>
            <RangesDirective>
              <RangeDirective dataSource={data}></RangeDirective>
            </RangesDirective>
            <ColumnsDirective>
              <ColumnDirective width={120}></ColumnDirective>
              <ColumnDirective width={180}></ColumnDirective>
              <ColumnDirective width={100}></ColumnDirective>
              <ColumnDirective width={120}></ColumnDirective>
              <ColumnDirective width={120}></ColumnDirective>
            </ColumnsDirective>
          </SheetDirective>
        </SheetsDirective>
      </SpreadsheetComponent>
    </div>
  )
};
export default App;
import * as React from 'react';
import { DropDownListComponent, ChangeEventArgs } from '@syncfusion/ej2-react-dropdowns';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective, getFormatFromType, configureLocalizedFormat, FormatOption } from '@syncfusion/ej2-react-spreadsheet';
import { loadCldr, setCurrencyCode, setCulture, Ajax } from '@syncfusion/ej2-base';
import { data } from './datasource';

function App() {

  const spreadsheetRef = React.useRef<SpreadsheetComponent>(null);
  // Loading the culture-related files.
  const loadCultureFiles: (locales: string[]) => void = (locales: string[]): void => {
    const files: string[] = ['ca-gregorian', 'numbers', 'timeZoneNames', 'currencies', 'numberingSystems'];
    locales.forEach((locale: string) => {
      for (const fileName of files) {
        const url: string = `./cldr-data/${fileName === 'numberingSystems' ? '' : `${locale}/`}${fileName}.json`;
        const ajax: Ajax = new Ajax(url, 'GET', false);
        ajax.onSuccess = (value: string) => loadCldr(JSON.parse(value));
        ajax.send();
      }
    });
  }

  loadCultureFiles(['de', 'fr-CH', 'zh']);
  // Setting German culture.
  setCulture('de');
  // Setting currency code for the German culture.
  setCurrencyCode('EUR');

  const localeFormats: { [key: string]: FormatOption[] } = {
    'de': [{ id: 37, code: '#,##0;-#,##0' }, { id: 38, code: '#,##0;[Red]-#,##0' },
    { id: 39, code: '#,##0.00;-#,##0.00' }, { id: 40, code: '#,##0.00;[Red]-#,##0.00' }, { id: 5, code: '#,##0 "€";-#,##0 "€"' },
    { id: 6, code: '#,##0 "€";[Red]-#,##0 "€"' }, { id: 7, code: '#,##0.00 "€";-#,##0.00 "€"' },
    { id: 8, code: '#,##0.00 "€";[Red]-#,##0.00 "€"' }, { id: 41, code: '_-* #,##0_-;-* #,##0_-;_-* "-"_-;_-@_-' },
    { id: 42, code: '_-* #,##0 "€"_-;-* #,##0 "€"_-;_-* "-" "€"_-;_-@_-' },
    { id: 43, code: '_-* #,##0.00_-;-* #,##0.00_-;_-* "-"??_-;_-@_-' },
    { id: 44, code: '_-* #,##0.00 "€"_-;-* #,##0.00 "€"_-;_-* "-"?? "€"_-;_-@_-' },
    { id: 14, code: 'dd.MM.yyyy' }, { id: 15, code: 'dd. MMM yy' }, { id: 16, code: 'dd. MMM' }, { id: 17, code: 'MMM yy' },
    { id: 20, code: 'hh:mm' }, { id: 21, code: 'hh:mm:ss' }, { id: 22, code: 'dd.MM.yyyy hh:mm' }],
    'zh': [{ id: 37, code: '#,##0;-#,##0' }, { id: 38, code: '#,##0;[Red]-#,##0' }, { id: 39, code: '#,##0.00;-#,##0.00' },
    { id: 40, code: '#,##0.00;[Red]-#,##0.00' }, { id: 5, code: '"¥"#,##0;"¥"-#,##0' }, { id: 6, code: '"¥"#,##0;[Red]"¥"-#,##0' },
    { id: 7, code: '"¥"#,##0.00;"¥"-#,##0.00' }, { id: 8, code: '"¥"#,##0.00;[Red]"¥"-#,##0.00' },
    { id: 41, code: '_ * #,##0_ ;_ * -#,##0_ ;_ * "-"_ ;_ @_' }, { id: 42, code: '_ "¥"* #,##0_ ;_ "¥"* -#,##0_ ;_ "¥"* "-"_ ;_ @_' },
    { id: 43, code: '_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * "-"??_ ;_ @_' },
    { id: 44, code: '_ "¥"* #,##0.00_ ;_ "¥"* -#,##0.00_ ;_ "¥"* "-"??_ ;_ @_' },
    { id: 14, code: 'yyyy/m/d' }, { id: 22, code: 'yyyy/m/d h:mm' }],
    'fr-CH': [{ id: 37, code: '#,##0;-#,##0' }, { id: 38, code: '#,##0;[Red]-#,##0' }, { id: 39, code: '#,##0.00;-#,##0.00' },
    { id: 40, code: '#,##0.00;[Red]-#,##0.00' }, { id: 5, code: '#,##0 "CHF";-#,##0 "CHF"' },
    { id: 6, code: '#,##0 "CHF";[Red]-#,##0 "CHF"' }, { id: 7, code: '#,##0.00 "CHF";-#,##0.00 "CHF"' },
    { id: 8, code: '#,##0.00 "CHF";[Red]-#,##0.00 "CHF"' }, { id: 14, code: 'dd.MM.yyyy' }, { id: 15, code: 'dd.MMM.yy' },
    { id: 16, code: 'dd.MMM' }, { id: 17, code: 'MMM.yy' }, { id: 20, code: 'HH:mm' }, { id: 21, code: 'HH:mm:ss' },
    { id: 22, code: 'dd.MM.yyyy HH:mm' }, { id: 42, code: '_-* #,##0 "CHF"_-;-* #,##0 "CHF"_-;_-* "-" "CHF"_-;_-@_-' },
    { id: 44, code: '_-* #,##0.00 "CHF"_-;-* #,##0.00 "CHF"_-;_-* "-"?? "CHF"_-;_-@_-' },
    { id: 41, code: '_-* #,##0_-;-* #,##0_-;_-* "-"_-;_-@_-' }, { id: 43, code: '_-* #,##0.00_-;-* #,##0.00_-;_-* "-"??_-;_-@_-' }
    ],
    'en-US': []
  };

  // Mapping default number formats for the German ('de') locale before the spreadsheet is created.
  // We can also map the formats after the spreadsheet is created in the created event.
  configureLocalizedFormat(spreadsheetRef.current, localeFormats['de']);

  const cultureList: { [key: string]: Object }[] = [
    { Culture: 'German - Germany', localeOption: 'de EUR ;' },
    { Culture: 'French - Switzerland', localeOption: 'fr-CH CHF ;' },
    { Culture: 'Chinese - China', localeOption: 'zh CNY ,' },
    { Culture: 'English', localeOption: 'en-US USD ,' }
  ];

  // maps the appropriate column to fields property
  const fields: Object = { text: 'Culture', value: 'localeOption' };
  const placeholder: string = 'Select a locale';

  const change = (args: ChangeEventArgs): void => {
    const spreadsheet: SpreadsheetComponent = spreadsheetRef.current;
    const localeOption: string[] = (args.value as string).split(' ');
    // Setting the culture name like 'de', 'fr-CH', 'zh', and 'en-US'.
    const cultureName: string = localeOption[0];
    setCulture(cultureName);
    // Setting the currency code for the selected locale like 'EUR', 'CNY', 'CHF', and 'USD'.
    setCurrencyCode(localeOption[1]);
    // Mapping the default number format codes for the selected locale.
    configureLocalizedFormat(spreadsheet, localeFormats[cultureName]);
    // Setting the culture for the spreadsheet.
    spreadsheet.locale = cultureName;
    // Setting the list separator for the selected locale.
    spreadsheet.listSeparator = localeOption[2];
    // Refreshing the changes immediately in the spreadsheet.
    spreadsheet.dataBind();
    applyFormats();
  }

  const applyFormats = (): void => {
    const spreadsheet: SpreadsheetComponent = spreadsheetRef.current;
    // Apply format to the specified range in the active sheet.
    // The getFormatFromType method returns the culture-based format code based on the mapped formats.
    // If a format ID is not mapped or is not applicable, it will return the format code based on the loaded culture.
    // For 'en-US' (English) culture, the format code will be 'm/d/yyyy'.
    // For 'de' (German) culture, the format code will be 'dd.MM.yyyy'.
    // For 'fr-CH' (French-Switzerland) culture, the format code will be 'dd.MM.yyyy'.
    // For 'zh' (Chinese) culture, the format code will be 'yyyy/m/d'.
    spreadsheet.numberFormat(getFormatFromType('ShortDate'), 'B2:B11');
    // For 'en-US' (English) culture, the format code will be 'h:mm:ss AM/PM'.
    // For 'de' (German) culture, the format code will be 'HH:mm:ss'.
    // For 'fr-CH' (French-Switzerland) culture, the format code will be 'HH:mm:ss'.
    // For 'zh' (Chinese) culture, the format code will be 'h:mm:ss AM/PM'.
    spreadsheet.numberFormat(getFormatFromType('Time'), 'C2:C11');
    // For 'en-US' (English) culture, the format code will be '$#,##0.00'.
    // For 'de' (German) culture, the format code will be '#,##0.00 "€"'.
    // For 'fr-CH' (French-Switzerland) culture, the format code will be '#,##0.00 "CHF"'.
    // For 'zh' (Chinese) culture, the format code will be '"¥"#,##0.00'.
    spreadsheet.numberFormat(getFormatFromType('Currency'), 'E2:F11');
    // For 'en-US' (English) culture, the format code will be '_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)'.
    // For 'de' (German) culture, the format code will be '_-* #,##0.00 "€"_-;-* #,##0.00 "€"_-;_-* "-"?? "€"_-;_-@_-'.
    // For 'fr-CH' (French-Switzerland) culture, the format code will be '_-* #,##0.00 "CHF"_-;-* #,##0.00 "CHF"_-;_-* "-"?? "CHF"_-;_-@_-'
    // For 'zh' (Chinese) culture, the format code will be '_ "¥"* #,##0.00_ ;_ "¥"* -#,##0.00_ ;_ "¥"* "-"??_ ;_ @_'
    spreadsheet.numberFormat(getFormatFromType('Accounting'), 'H2:H11');
    // The percentage format code will be '0.00%' for all the cultures.
    spreadsheet.numberFormat('0.00%', 'G2:G11');
  }

  const created = () => {
    let spreadsheet = spreadsheetRef.current;
    if (spreadsheet) {
      spreadsheet.cellFormat({ textAlign: 'center', fontWeight: 'bold' }, 'A1:H1');
      applyFormats();
    }
  };

  return (
    <div>
      <DropDownListComponent id="ddlelement" width='150px' popupHeight='200px' dataSource={cultureList} fields={fields} placeholder={placeholder} change={change} />
      <SpreadsheetComponent ref={spreadsheetRef} locale='de' listSeparator=';' created={created}>
        <SheetsDirective>
          <SheetDirective>
            <RangesDirective>
              <RangeDirective dataSource={data}></RangeDirective>
            </RangesDirective>
            <ColumnsDirective>
              <ColumnDirective width={120}></ColumnDirective>
              <ColumnDirective width={180}></ColumnDirective>
              <ColumnDirective width={100}></ColumnDirective>
              <ColumnDirective width={120}></ColumnDirective>
              <ColumnDirective width={120}></ColumnDirective>
            </ColumnsDirective>
          </SheetDirective>
        </SheetsDirective>
      </SpreadsheetComponent>
    </div>
  )
};
export default App;
export let data =[
    { 'Item Name': 'Casual Shoes', Date: '14.02.2014', Time: '11:34:32 AM', Quantity: 10, Price: 20, Amount: '=PRODUCT(D2;E2)', Discount: '2%', Profit: '=PRODUCT(G2;F2)' },
    { 'Item Name': 'Sports Shoes', Date: '11.06.2014', Time: '05:56:32 AM', Quantity: 20, Price: 30, Amount: '=PRODUCT(D3;E3)', Discount: '5%', Profit: '=PRODUCT(G3;F3)' },
    { 'Item Name': 'Formal Shoes', Date: '27.07.2014', Time: '03:32:44 AM', Quantity: 20, Price: 15, Amount: '=PRODUCT(D4;E4)', Discount: '7,5%', Profit: '=PRODUCT(G4;F4)' },
    { 'Item Name': 'Sandals & Floaters', Date: '21.11.2014', Time: '06:23:54 AM', Quantity: 15, Price: '20,45', Amount: '=PRODUCT(D5;E5)', Discount: '11%', Profit: '=PRODUCT(G5;F5)' },
    { 'Item Name': 'Flip- Flops & Slippers', Date: '23.06.2014', Time: '12:43:59 AM', Quantity: 30, Price: '10,67', Amount: '=PRODUCT(D6;E6)', Discount: '10%', Profit: '=PRODUCT(G6;F6)' },
    { 'Item Name': 'Sneakers', Date: '22.07.2014', Time: '10:55:53 AM', Quantity: 40, Price: 20, Amount: '=PRODUCT(D7;E7)', Discount: '13,2%', Profit: '=PRODUCT(G7;F7)' },
    { 'Item Name': 'Running Shoes', Date: '04.02.2014', Time: '03:44:34 AM', Quantity: 20, Price: '10,5', Amount: '=PRODUCT(D8;E8)', Discount: '3%', Profit: '=PRODUCT(G8;F8)' },
    { 'Item Name': 'Loafers', Date: '30.11.2014', Time: '03:12:52 AM', Quantity: 31, Price: 10, Amount: '=PRODUCT(D9;E9)', Discount: '6,67', Profit: '=PRODUCT(G9;F9)' },
    { 'Item Name': 'Cricket Shoes', Date: '09.07.2014', Time: '11:32:14 AM', Quantity: 41, Price: 30, Amount: '=PRODUCT(D10;E10)', Discount: '12,5%', Profit: '=PRODUCT(G10;F10)' },
    { 'Item Name': 'T-Shirts', Date: '31.10.2014', Time: '12:01:44 AM', Quantity: 50, Price: '10,75', Amount: '=PRODUCT(D11;E11)', Discount: '9%', Profit: '=PRODUCT(G11;F11)' }
];
/**
 * Globalization data source in German (de) culture.
 */
export let data: Object[] = [
    { 'Item Name': 'Casual Shoes', Date: '14.02.2014', Time: '11:34:32 AM', Quantity: 10, Price: 20, Amount: '=PRODUCT(D2;E2)', Discount: '2%', Profit: '=PRODUCT(G2;F2)' },
    { 'Item Name': 'Sports Shoes', Date: '11.06.2014', Time: '05:56:32 AM', Quantity: 20, Price: 30, Amount: '=PRODUCT(D3;E3)', Discount: '5%', Profit: '=PRODUCT(G3;F3)' },
    { 'Item Name': 'Formal Shoes', Date: '27.07.2014', Time: '03:32:44 AM', Quantity: 20, Price: 15, Amount: '=PRODUCT(D4;E4)', Discount: '7,5%', Profit: '=PRODUCT(G4;F4)' },
    { 'Item Name': 'Sandals & Floaters', Date: '21.11.2014', Time: '06:23:54 AM', Quantity: 15, Price: '20,45', Amount: '=PRODUCT(D5;E5)', Discount: '11%', Profit: '=PRODUCT(G5;F5)' },
    { 'Item Name': 'Flip- Flops & Slippers', Date: '23.06.2014', Time: '12:43:59 AM', Quantity: 30, Price: '10,67', Amount: '=PRODUCT(D6;E6)', Discount: '10%', Profit: '=PRODUCT(G6;F6)' },
    { 'Item Name': 'Sneakers', Date: '22.07.2014', Time: '10:55:53 AM', Quantity: 40, Price: 20, Amount: '=PRODUCT(D7;E7)', Discount: '13,2%', Profit: '=PRODUCT(G7;F7)' },
    { 'Item Name': 'Running Shoes', Date: '04.02.2014', Time: '03:44:34 AM', Quantity: 20, Price: '10,5', Amount: '=PRODUCT(D8;E8)', Discount: '3%', Profit: '=PRODUCT(G8;F8)' },
    { 'Item Name': 'Loafers', Date: '30.11.2014', Time: '03:12:52 AM', Quantity: 31, Price: 10, Amount: '=PRODUCT(D9;E9)', Discount: '6,67', Profit: '=PRODUCT(G9;F9)' },
    { 'Item Name': 'Cricket Shoes', Date: '09.07.2014', Time: '11:32:14 AM', Quantity: 41, Price: 30, Amount: '=PRODUCT(D10;E10)', Discount: '12,5%', Profit: '=PRODUCT(G10;F10)' },
    { 'Item Name': 'T-Shirts', Date: '31.10.2014', Time: '12:01:44 AM', Quantity: 50, Price: '10,75', Amount: '=PRODUCT(D11;E11)', Discount: '9%', Profit: '=PRODUCT(G11;F11)' }
];

Note

You can refer to our React Spreadsheet Editor feature tour page for feature highlights. You can also explore our React Spreadsheet example to learn how to present and manipulate data.

See Also