Formatting in React Spreadsheet component
9 Oct 202424 minutes to read
Formatting options make your data easier to view and understand. The different types of formatting options in the Spreadsheet are,
- Number Formatting
- Text Formatting
- Cell Formatting
To get start quickly with Formatting, you can check on this video:
Number Formatting
Number formatting provides a type for your data in the Spreadsheet. Use the allowNumberFormatting property to enable or disable the number formatting option in the Spreadsheet. The different types of number formatting supported in Spreadsheet are,
| 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
formatproperty incell, you can set the desired format to each cell at initial load. - Using the
numberFormatmethod, 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
numberFormatmethod, 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-based custom format
Previously, the custom format dialog always displayed formats using the English settings (group separator, decimal separator, and currency symbol were not updated based on the applied culture). Starting from version 27.1.*, the custom format dialog will now display formats according to the applied culture. You can select a culture-based number format from the dialog or enter your own format using the culture-specific decimal separator, group separator, and currency symbol. Then, click “Apply” to apply the culture-specific custom format to the selected cells.
The spreadsheet allows customization of formats in the custom format dialog using the configureLocalizedFormat method. In this method, you need to pass a collection containing the default number format IDs and their corresponding format codes as arguments. Based on this collection, the custom format dialog will 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)' }
];Text and cell formatting
Text and cell formatting enhances the look and feel of your cell. It helps to highlight a particular cell or range of cells from a whole workbook. You can apply formats like font size, font family, font color, text alignment, border etc. to a cell or range of cells. Use the allowCellFormatting property to enable or disable the text and cell formatting option in Spreadsheet. You can set the formats in following ways,
- Using the
styleproperty, you can set formats to each cell at initial load. - Using the
cellFormatmethod, you can set formats to a cell or range of cells. - You can also apply by clicking the desired format option from the ribbon toolbar.
Fonts
Various font formats supported in the spreadsheet are font-family, font-size, bold, italic, strike-through, underline and font color.
Text Alignment
You can align text in a cell either vertically or horizontally using the textAlign and verticalAlign property.
Indents
To enhance the appearance of text in a cell, you can change the indentation of a cell content using textIndent property.
Fill color
To highlight cell or range of cells from whole workbook you can apply background color for a cell using backgroundColor property.
Borders
You can add borders around a cell or range of cells to define a section of worksheet or a table. The different types of border options available in the spreadsheet are,
| Types | Actions |
|---|---|
| Top Border | Specifies the top border of a cell or range of cells. |
| Left Border | Specifies the left border of a cell or range of cells. |
| Right Border | Specifies the right border of a cell or range of cells. |
| Bottom Border | Specifies the bottom border of a cell or range of cells. |
| No Border | Used to clear the border from a cell or range of cells. |
| All Border | Specifies all border of a cell or range of cells. |
| Horizontal Border | Specifies the top and bottom border of a cell or range of cells. |
| Vertical Border | Specifies the left and right border of a cell or range of cells. |
| Outside Border | Specifies the outside border of a range of cells. |
| Inside Border | Specifies the inside border of a range of cells. |
You can also change the color, size, and style of the border. The size and style supported in the spreadsheet are,
| Types | Actions |
|---|---|
| Thin | Specifies the 1px border size (default). |
| Medium | Specifies the 2px border size. |
| Thick | Specifies the 3px border size. |
| Solid | Used to create the solid border (default). |
| Dashed | Used to create the dashed border. |
| Dotted | Used to create the dotted border. |
| Double | Used to create the double border. |
Borders can be applied in the following ways,
- Using the
border,borderLeft,borderRight,borderBottomproperties, you can set the desired border to each cell at initial load. - Using the
setBordermethod, you can set various border options to a cell or range of cells. - Selecting the border options from ribbon toolbar.
The following code example shows the style formatting in text and cells of the spreadsheet.
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective, RowsDirective, RowDirective, CellDirective, CellsDirective } 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 = { fontFamily: 'Axettac Demo', verticalAlign: 'middle', textAlign: 'center', fontSize: '18pt', fontWeight: 'bold', color: '#279377', border: '1px solid #e0e0e0' };
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
// Setting common styles to table header cells
spreadsheet.cellFormat({ fontWeight: 'bold', fontSize: '12pt', backgroundColor: '#279377', color: '#ffffff' }, 'A2:E2');
// Setting common styles to whole table cells
spreadsheet.cellFormat({ verticalAlign: 'middle', fontFamily: 'Axettac Demo' }, 'A2:E12');
// Column wise styles setting
spreadsheet.cellFormat({ textAlign: 'center' }, 'A2:A12');
// Setting text-indent to 2 and 4 column
let style = { textAlign: 'left', textIndent: '8pt' };
spreadsheet.cellFormat(style, 'B2:B12');
spreadsheet.cellFormat(style, 'D2:D12');
spreadsheet.cellFormat({ fontStyle: 'italic', textAlign: 'right' }, 'C3:C12');
spreadsheet.cellFormat({ textAlign: 'center' }, 'E2:E12');
// Applied border to range of cells using 'setBorder' method
spreadsheet.setBorder({ borderLeft: '1px solid #e0e0e0', borderRight: '1px solid #e0e0e0' }, 'A2:E2');
spreadsheet.setBorder({ border: '1px solid #e0e0e0' }, 'A4:E11', 'Horizontal');
spreadsheet.setBorder({ border: '1px solid #e0e0e0' }, 'A3:E12', 'Outer');
spreadsheet.cellFormat({ color: '#10c469', textDecoration: 'line-through' }, 'E3:E4');
spreadsheet.cellFormat({ color: '#10c469', textDecoration: 'line-through' }, 'E9');
spreadsheet.cellFormat({ color: '#10c469', textDecoration: 'line-through' }, 'E12');
spreadsheet.cellFormat({ color: '#FFC107', textDecoration: 'underline' }, 'E5');
spreadsheet.cellFormat({ color: '#FFC107', textDecoration: 'underline' }, 'E8');
spreadsheet.cellFormat({ color: '#FFC107', textDecoration: 'underline' }, 'E11');
spreadsheet.cellFormat({ color: '#62c9e8' }, 'E6');
spreadsheet.cellFormat({ color: '#62c9e8' }, 'E10');
spreadsheet.cellFormat({ color: '#ff5b5b' }, 'E7');
}
}, []);
return (
<div>
<SpreadsheetComponent ref={spreadsheetRef} showRibbon={false} showFormulaBar={false} showSheetTabs={false} allowEditing={false} allowDelete={false} allowInsert={false} >
<SheetsDirective>
<SheetDirective selectedRange={"U15"} showGridLines={false}>
<RowsDirective>
<RowDirective height={40} customHeight={true}>
<CellsDirective>
<CellDirective colSpan={5} value={'Order Summary'} style={styles}></CellDirective>
</CellsDirective>
</RowDirective>
</RowsDirective>
<RangesDirective>
<RangeDirective dataSource={data} startCell={'A2'}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={200}></ColumnDirective>
<ColumnDirective width={110}></ColumnDirective>
<ColumnDirective width={140}></ColumnDirective>
<ColumnDirective width={90}></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 } 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 = { fontFamily: 'Axettac Demo', verticalAlign: 'middle', textAlign: 'center', fontSize: '18pt', fontWeight: 'bold', color: '#279377', border: '1px solid #e0e0e0' };
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
// Setting common styles to table header cells
spreadsheet.cellFormat({ fontWeight: 'bold', fontSize: '12pt', backgroundColor: '#279377', color: '#ffffff' }, 'A2:E2');
// Setting common styles to whole table cells
spreadsheet.cellFormat({ verticalAlign: 'middle', fontFamily: 'Axettac Demo' }, 'A2:E12');
// Column wise styles setting
spreadsheet.cellFormat({ textAlign: 'center' }, 'A2:A12');
// Setting text-indent to 2 and 4 column
let style: CellStyleModel = { textAlign: 'left', textIndent: '8pt' };
spreadsheet.cellFormat(style, 'B2:B12');
spreadsheet.cellFormat(style, 'D2:D12');
spreadsheet.cellFormat({ fontStyle: 'italic', textAlign: 'right' }, 'C3:C12');
spreadsheet.cellFormat({ textAlign: 'center' }, 'E2:E12');
// Applied border to range of cells using 'setBorder' method
spreadsheet.setBorder({ borderLeft: '1px solid #e0e0e0', borderRight: '1px solid #e0e0e0' }, 'A2:E2');
spreadsheet.setBorder({ border: '1px solid #e0e0e0' }, 'A4:E11', 'Horizontal');
spreadsheet.setBorder({ border: '1px solid #e0e0e0' }, 'A3:E12', 'Outer');
spreadsheet.cellFormat({ color: '#10c469', textDecoration: 'line-through' }, 'E3:E4');
spreadsheet.cellFormat({ color: '#10c469', textDecoration: 'line-through' }, 'E9');
spreadsheet.cellFormat({ color: '#10c469', textDecoration: 'line-through' }, 'E12');
spreadsheet.cellFormat({ color: '#FFC107', textDecoration: 'underline' }, 'E5');
spreadsheet.cellFormat({ color: '#FFC107', textDecoration: 'underline' }, 'E8');
spreadsheet.cellFormat({ color: '#FFC107', textDecoration: 'underline' }, 'E11');
spreadsheet.cellFormat({ color: '#62c9e8' }, 'E6');
spreadsheet.cellFormat({ color: '#62c9e8' }, 'E10');
spreadsheet.cellFormat({ color: '#ff5b5b' }, 'E7');
}
}, []);
return (
<div>
<SpreadsheetComponent ref={spreadsheetRef} showRibbon={false} showFormulaBar={false} showSheetTabs={false} allowEditing={false} allowDelete={false} allowInsert={false} >
<SheetsDirective>
<SheetDirective selectedRange={"U15"} showGridLines={false}>
<RowsDirective>
<RowDirective height={40} customHeight={true}>
<CellsDirective>
<CellDirective colSpan={5} value={'Order Summary'} style={styles}></CellDirective>
</CellsDirective>
</RowDirective>
</RowsDirective>
<RangesDirective>
<RangeDirective dataSource={data} startCell={'A2'}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={200}></ColumnDirective>
<ColumnDirective width={110}></ColumnDirective>
<ColumnDirective width={140}></ColumnDirective>
<ColumnDirective width={90}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
</SpreadsheetComponent>
</div>
);
};
export default App;
const root = createRoot(document.getElementById('root')!);
root.render(<App />);/**
* Cell formatting data source
*/
export let data = [
{ 'Order Id': 'SF1001', 'Product': 'Laptop Backpack (Blue)', 'Ordered Date': '02/14/2014', 'Ordered By': 'Rahul Sharma', 'Shipment': 'Delivered' },
{ 'Order Id': 'SF1002', 'Product': 'Oppo F1 S mobile back cover', 'Ordered Date': '06/11/2014', 'Ordered By': 'Adi Pathak', 'Shipment': 'Delivered' },
{ 'Order Id': 'SF1003', 'Product': 'Tupperware 4 bottle set', 'Ordered Date': '07/27/2014', 'Ordered By': 'Himani Arora', 'Shipment': 'Pending' },
{ 'Order Id': 'SF1004', 'Product': 'Tupperware Lunch box', 'Ordered Date': '11/21/2014', 'Ordered By': 'Samuel Samson', 'Shipment': 'Shipped' },
{ 'Order Id': 'SF1005', 'Product': 'Panosonic Hair Dryer', 'Ordered Date': '06/23/2014', 'Ordered By': 'Neha', 'Shipment': 'Cancelled' },
{ 'Order Id': 'SF1006', 'Product': 'Philips LED 2 bulb set', 'Ordered Date': '07/22/2014', 'Ordered By': 'Christine J', 'Shipment': 'Pending' },
{ 'Order Id': 'SF1007', 'Product': 'Moto G4 plus headphone', 'Ordered Date': '02/04/2014', 'Ordered By': 'Shiv Nagar', 'Shipment': 'Delivered' },
{ 'Order Id': 'SF1008', 'Product': 'Lakme Eyeliner Pencil', 'Ordered Date': '11/30/2014', 'Ordered By': 'Cherry', 'Shipment': 'Shipped' },
{ 'Order Id': 'SF1009', 'Product': 'Listerine mouthwash', 'Ordered Date': '07/09/2014', 'Ordered By': 'Siddartha Mishra', 'Shipment': 'Pending' },
{ 'Order Id': 'SF1010', 'Product': 'Protinex original', 'Ordered Date': '10/31/2014', 'Ordered By': 'Ravi Chugh', 'Shipment': 'Delivered' },
];/**
* Cell formatting data source
*/
export let data: Object[] = [
{ 'Order Id': 'SF1001', 'Product': 'Laptop Backpack (Blue)', 'Ordered Date': '02/14/2014', 'Ordered By': 'Rahul Sharma', 'Shipment': 'Delivered' },
{ 'Order Id': 'SF1002', 'Product': 'Oppo F1 S mobile back cover', 'Ordered Date': '06/11/2014', 'Ordered By': 'Adi Pathak', 'Shipment': 'Delivered' },
{ 'Order Id': 'SF1003', 'Product': 'Tupperware 4 bottle set', 'Ordered Date': '07/27/2014', 'Ordered By': 'Himani Arora', 'Shipment': 'Pending' },
{ 'Order Id': 'SF1004', 'Product': 'Tupperware Lunch box', 'Ordered Date': '11/21/2014', 'Ordered By': 'Samuel Samson', 'Shipment': 'Shipped' },
{ 'Order Id': 'SF1005', 'Product': 'Panosonic Hair Dryer', 'Ordered Date': '06/23/2014', 'Ordered By': 'Neha', 'Shipment': 'Cancelled' },
{ 'Order Id': 'SF1006', 'Product': 'Philips LED 2 bulb set', 'Ordered Date': '07/22/2014', 'Ordered By': 'Christine J', 'Shipment': 'Pending' },
{ 'Order Id': 'SF1007', 'Product': 'Moto G4 plus headphone', 'Ordered Date': '02/04/2014', 'Ordered By': 'Shiv Nagar', 'Shipment': 'Delivered' },
{ 'Order Id': 'SF1008', 'Product': 'Lakme Eyeliner Pencil', 'Ordered Date': '11/30/2014', 'Ordered By': 'Cherry', 'Shipment': 'Shipped' },
{ 'Order Id': 'SF1009', 'Product': 'Listerine mouthwash', 'Ordered Date': '07/09/2014', 'Ordered By': 'Siddartha Mishra', 'Shipment': 'Pending' },
{ 'Order Id': 'SF1010', 'Product': 'Protinex original', 'Ordered Date': '10/31/2014', 'Ordered By': 'Ravi Chugh', 'Shipment': 'Delivered' },
];Limitations of Formatting
The following features are not supported in Formatting:
- Insert row/column between the formatting applied cells.
- Formatting support for row/column.
Conditional Formatting
Conditional formatting helps you to format a cell or range of cells based on the conditions applied. You can enable or disable conditional formats by using the allowConditionalFormat property.
- The default value for the
allowConditionalFormatproperty istrue.
Apply Conditional Formatting
You can apply conditional formatting by using one of the following ways,
- Select the conditional formatting icon in the Ribbon toolbar under the Home Tab.
- Using the
conditionalFormat()method to define the condition. - Using the
conditionalFormatsin sheets model.
Conditional formatting has the following types in the spreadsheet,
Highlight cells rules
Highlight cells rules option in the conditional formatting enables you to highlight cells with a preset color depending on the cell’s value.
The following options can be given for the highlight cells rules as type,
- ‘GreaterThan’, ‘LessThan’, ‘Between’, ‘EqualTo’, ‘ContainsText’, ‘DateOccur’, ‘Duplicate’, ‘Unique’.
The following preset colors can be used for formatting styles,
"RedFT"- Light Red Fill with Dark Red Text,"YellowFT"- Yellow Fill with Dark Yellow Text,"GreenFT"- Green Fill with Dark Green Text,"RedF"- Red Fill,"RedT"- Red Text.
Top bottom rules
Top bottom rules option in the conditional formatting allows you to apply formatting to the cells that satisfy a statistical condition with other cells in the range.
The following options can be given for the top bottom rules as type,
- ‘Top10Items’, ‘Bottom10Items’, ‘Top10Percentage’, ‘Bottom10Percentage’, ‘BelowAverage’, ‘AboveAverage’.
Data Bars
You can apply data bars to represent the data graphically inside a cell. The longest bar represents the highest value and the shorter bars represent the smaller values.
The following options can be given for the data bars as type,
- ‘BlueDataBar’, ‘GreenDataBar’, ‘RedDataBar’, ‘OrangeDataBar’, ‘LightBlueDataBar’, ‘PurpleDataBar’.
Color Scales
Using color scales, you can format your cells with two or three colors, where different color shades represent the different cell values. In the Green-Yellow-Red(GYR) Color Scale, the cell that holds the minimum value is colored as red. The cell that holds the median is colored as yellow, and the cell that holds the maximum value is colored as green. All other cells are colored proportionally.
The following options can be given for the color scales as type,
- ‘GYRColorScale’, ‘RYGColorScale’, ‘GWRColorScale’, ‘RWGColorScale’, ‘BWRColorScale’, ‘RWBColorScale’, ‘WRColorScale’, ‘RWColorScale’, ‘GWColorScale’, ‘WGColorScale’, ‘GYColorScale’, ‘YGColorScale’.
Icon Sets
Icon sets will help you to visually represent your data with icons. Every icon represents a range of values. In the Three Arrows(colored) icon, the green arrow icon represents the values greater than 67%, the yellow arrow icon represents the values between 33% to 67%, and the red arrow icon represents the values less than 33%.
The following options can be given for the icon sets as type,
- ‘ThreeArrows’, ‘ThreeArrowsGray’, ‘FourArrowsGray’, ‘FourArrows’, ‘FiveArrowsGray’, ‘FiveArrows’, ‘ThreeTrafficLights1’, ‘ThreeTrafficLights2’, ‘ThreeSigns’, ‘FourTrafficLights’, ‘FourRedToBlack’, ‘ThreeSymbols’, ‘ThreeSymbols2’, ‘ThreeFlags’, ‘FourRating’, ‘FiveQuarters’, ‘FiveRating’, ‘ThreeTriangles’, ‘ThreeStars’, ‘FiveBoxes’.
Custom Format
Using the custom format for conditional formatting you can set cell styles like color, background color, font style, font weight, and underline.
In the MAY and JUN columns, we have applied conditional formatting custom format.
- In the Conditional format, custom format supported for Highlight cell rules and Top bottom rules.
Clear Rules
You can clear the defined rules by using one of the following ways,
- Using the “Clear Rules” option in the Conditional Formatting button of HOME Tab in the ribbon to clear the rule from selected cells.
- Using the
clearConditionalFormat()method to clear the defined rules.
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective, ConditionalFormatsDirective, ConditionalFormatDirective } from '@syncfusion/ej2-react-spreadsheet';
import { conditionalFormatData } from './datasource';
function App() {
const spreadsheetRef = React.useRef(null);
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:N1');
spreadsheet.conditionalFormat({ type: 'RYGColorScale', range: 'E2:E9' });
spreadsheet.conditionalFormat({ type: 'ThreeArrows', range: 'H2:H9' });
spreadsheet.conditionalFormat({
type: 'Top10Items', value: '1',
format: { style: { color: '#ffffff', backgroundColor: '#009999', fontWeight: 'bold' } }, range: 'F2:F9'
});
spreadsheet.conditionalFormat({
type: 'Bottom10Items', value: '1',
format: { style: { color: '#ffffff', backgroundColor: '#c68d53', fontWeight: 'bold' } }, range: 'G2:G9'
});
}
}, []);
return (
<div>
<SpreadsheetComponent ref={spreadsheetRef} showFormulaBar={false}>
<SheetsDirective>
<SheetDirective name='Car Sales Record'>
<RangesDirective>
<RangeDirective dataSource={conditionalFormatData}></RangeDirective>
</RangesDirective>
<ConditionalFormatsDirective>
<ConditionalFormatDirective type='GreaterThan' cFColor='RedFT' value='700' range='B2:B9'></ConditionalFormatDirective>
<ConditionalFormatDirective type='Bottom10Items' cFColor='YellowFT' value='4' range='C2:C9'></ConditionalFormatDirective>
<ConditionalFormatDirective type='BlueDataBar' range='D2:D9'></ConditionalFormatDirective>
</ConditionalFormatsDirective>
<ColumnsDirective>
<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 } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective, ConditionalFormatsDirective, ConditionalFormatDirective } from '@syncfusion/ej2-react-spreadsheet';
import { conditionalFormatData } from './datasource';
function App() {
const spreadsheetRef = React.useRef<SpreadsheetComponent>(null);
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:N1');
spreadsheet.conditionalFormat({ type: 'RYGColorScale', range: 'E2:E9' });
spreadsheet.conditionalFormat({ type: 'ThreeArrows', range: 'H2:H9' });
spreadsheet.conditionalFormat({
type: 'Top10Items', value: '1',
format: { style: { color: '#ffffff', backgroundColor: '#009999', fontWeight: 'bold' } }, range: 'F2:F9'
});
spreadsheet.conditionalFormat({
type: 'Bottom10Items', value: '1',
format: { style: { color: '#ffffff', backgroundColor: '#c68d53', fontWeight: 'bold' } }, range: 'G2:G9'
});
}
}, []);
return (
<div>
<SpreadsheetComponent ref={spreadsheetRef} showFormulaBar={false}>
<SheetsDirective>
<SheetDirective name='Car Sales Record'>
<RangesDirective>
<RangeDirective dataSource={conditionalFormatData}></RangeDirective>
</RangesDirective>
<ConditionalFormatsDirective>
<ConditionalFormatDirective type='GreaterThan' cFColor='RedFT' value='700' range='B2:B9'></ConditionalFormatDirective>
<ConditionalFormatDirective type='Bottom10Items' cFColor='YellowFT' value='4' range='C2:C9'></ConditionalFormatDirective>
<ConditionalFormatDirective type='BlueDataBar' range='D2:D9'></ConditionalFormatDirective>
</ConditionalFormatsDirective>
<ColumnsDirective>
<ColumnDirective width={120}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
</SpreadsheetComponent>
</div>
);
};
export default App;
const root = createRoot(document.getElementById('root')!);
root.render(<App />);/**
* Conditional formatting data source
*/
export let conditionalFormatData = [
{ 'EV Model': 'BMW I3', 'JAN': 1224, 'FEB': 423, 'MAR': 585, 'APR': 367, 'MAY': 729, 'JUN': 733, 'TOTAL': '=SUM(B2:G2)' },
{ 'EV Model': 'Tesla Model S', 'JAN': 975, 'FEB': 763, 'MAR': 723, 'APR': 483, 'MAY': 983, 'JUN': 589, 'TOTAL': '=SUM(B3:G3)' },
{ 'EV Model': 'Chevrolet Volt', 'JAN': 113, 'FEB': 289, 'MAR': 675, 'APR': 458, 'MAY': 391, 'JUN': 198, 'TOTAL': '=SUM(B4:G4)' },
{ 'EV Model': 'Jaguar I-PACE', 'JAN': 78, 'FEB': 177, 'MAR': 244, 'APR': 99, 'MAY': 312, 'JUN': 129, 'TOTAL': '=SUM(B5:G5)' },
{ 'EV Model': 'Tesla Model X', 'JAN': 978, 'FEB': 1108, 'MAR': 1604, 'APR': 879, 'MAY': 1070, 'JUN': 1001, 'TOTAL': '=SUM(B6:G6)' },
{ 'EV Model': 'Nissan LEAF', 'JAN': 229, 'FEB': 978, 'MAR': 1202, 'APR': 822, 'MAY': 135, 'JUN': 878, 'TOTAL': '=SUM(B7:G7)' },
{ 'EV Model': 'Honda Clarity EV', 'JAN': 671, 'FEB': 1302, 'MAR': 466, 'APR': 989, 'MAY': 679, 'JUN': 891, 'TOTAL': '=SUM(B8:G8)' },
{ 'EV Model': 'Toyota Prius Prime', 'JAN': 978, 'FEB': 1362, 'MAR': 1872, 'APR': 678, 'MAY': 900, 'JUN': 867, 'TOTAL': '=SUM(B9:G9)' }
];/**
* Conditional formatting data source
*/
export let conditionalFormatData: Object[] = [
{ 'EV Model': 'BMW I3', 'JAN': 1224, 'FEB': 423, 'MAR': 585, 'APR': 367, 'MAY': 729, 'JUN': 733, 'TOTAL': '=SUM(B2:G2)' },
{ 'EV Model': 'Tesla Model S', 'JAN': 975, 'FEB': 763, 'MAR': 723, 'APR': 483, 'MAY': 983, 'JUN': 589, 'TOTAL': '=SUM(B3:G3)' },
{ 'EV Model': 'Chevrolet Volt', 'JAN': 113, 'FEB': 289, 'MAR': 675, 'APR': 458, 'MAY': 391, 'JUN': 198, 'TOTAL': '=SUM(B4:G4)' },
{ 'EV Model': 'Jaguar I-PACE', 'JAN': 78, 'FEB': 177, 'MAR': 244, 'APR': 99, 'MAY': 312, 'JUN': 129, 'TOTAL': '=SUM(B5:G5)' },
{ 'EV Model': 'Tesla Model X', 'JAN': 978, 'FEB': 1108, 'MAR': 1604, 'APR': 879, 'MAY': 1070, 'JUN': 1001, 'TOTAL': '=SUM(B6:G6)' },
{ 'EV Model': 'Nissan LEAF', 'JAN': 229, 'FEB': 978, 'MAR': 1202, 'APR': 822, 'MAY': 135, 'JUN': 878, 'TOTAL': '=SUM(B7:G7)' },
{ 'EV Model': 'Honda Clarity EV', 'JAN': 671, 'FEB': 1302, 'MAR': 466, 'APR': 989, 'MAY':679, 'JUN': 891, 'TOTAL': '=SUM(B8:G8)' },
{ 'EV Model': 'Toyota Prius Prime', 'JAN': 978, 'FEB': 1362, 'MAR': 1872, 'APR': 678, 'MAY': 900, 'JUN': 867, 'TOTAL': '=SUM(B9:G9)' }
];Limitations of Conditional formatting
The following features have some limitations in Conditional Formatting:
- Insert row/column between the conditional formatting.
- Conditional formatting with formula support.
- Copy and paste the conditional formatting applied cells.
- Custom rule support.
Note
You can refer to our React Spreadsheet feature tour page for its groundbreaking feature representations. You can also explore our React Spreadsheet example to knows how to present and manipulate data.