Cell range in React Spreadsheet component
14 Dec 202424 minutes to read
A group of cells in a sheet is known as cell range.
To get start quickly with Cell Range, you can check on this video:
Wrap text
Wrap text allows you to display large content as multiple lines in a single cell. By default, the wrap text support is enabled. Use the allowWrap property to enable or disable the wrap text support in spreadsheet.
Wrap text can be applied or removed to a cell or range of cells in the following ways,
- Using the
wrapproperty incell, you can enable or disable wrap text to a cell at initial load. - Select or deselect wrap button from ribbon toolbar to apply or remove the wrap text to the selected range.
- Using the
wrapmethod, you can apply or remove the wrap text once the component is loaded.
The following code example shows the wrap text functionality in 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);
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:H1');
spreadsheet.cellFormat({ verticalAlign: 'middle' }, 'A1:H5');
spreadsheet.cellFormat({ textAlign: 'center' }, 'A2:B5');
spreadsheet.cellFormat({ textAlign: 'center' }, 'D2:D5');
// To wrap the cells from E2 to E5 range
spreadsheet.wrap('E2:E5');
// To unwrap the H3 cell
spreadsheet.wrap('H3', false);
}
}, []);
return (
<div>
<SpreadsheetComponent ref={spreadsheetRef} showFormulaBar={false} >
<SheetsDirective>
<SheetDirective name={"Movie List"}>
<RowsDirective>
<RowDirective height={30}>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={7} wrap={true}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={7} wrap={true}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={7} wrap={true}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={7} wrap={true}></CellDirective>
</CellsDirective>
</RowDirective>
</RowsDirective>
<RangesDirective>
<RangeDirective dataSource={data}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={100} index={1}></ColumnDirective>
<ColumnDirective width={140}></ColumnDirective>
<ColumnDirective width={90}></ColumnDirective>
<ColumnDirective width={150}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={90}></ColumnDirective>
<ColumnDirective width={180}></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 { data } 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:H1');
spreadsheet.cellFormat({ verticalAlign: 'middle' }, 'A1:H5');
spreadsheet.cellFormat({ textAlign: 'center' }, 'A2:B5');
spreadsheet.cellFormat({ textAlign: 'center' }, 'D2:D5');
// To wrap the cells from E2 to E5 range
spreadsheet.wrap('E2:E5');
// To unwrap the H3 cell
spreadsheet.wrap('H3', false);
}
}, []);
return (
<div>
<SpreadsheetComponent ref={spreadsheetRef} showFormulaBar={false} >
<SheetsDirective>
<SheetDirective name={"Movie List"}>
<RowsDirective>
<RowDirective height={30}>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={7} wrap={true}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={7} wrap={true}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={7} wrap={true}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={7} wrap={true}></CellDirective>
</CellsDirective>
</RowDirective>
</RowsDirective>
<RangesDirective>
<RangeDirective dataSource={data}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={100} index={1}></ColumnDirective>
<ColumnDirective width={140}></ColumnDirective>
<ColumnDirective width={90}></ColumnDirective>
<ColumnDirective width={150}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={90}></ColumnDirective>
<ColumnDirective width={180}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
</SpreadsheetComponent>
</div>
);
};
export default App;
const root = createRoot(document.getElementById('root')!);
root.render(<App />);/**
* Wrap text data source
*/
export let data = [
{
'No': '1',
'Released on': 1994,
'Title': 'Forrest Gump',
'Rating': '5 Stars',
'Casts': 'Tom Hanks, Robin Wright, Gary Sinise',
'Directed By': 'Robert Zemeckis',
'Genre': 'Drama',
'Comments': 'Based on the 1986 novel of the same name by Winston Groom'
},
{
'No': '2',
'Released on': 1946,
'Title': 'It’s a Wonderful Life',
'Rating': '2 Stars',
'Casts': 'James Stewart, Donna Reed, Lionel Barrymore',
'Directed By': 'Frank Capra',
'Genre': 'Drama',
'Comments': 'Colorized version'
},
{
'No': '3',
'Released on': 1988,
'Title': 'Big',
'Rating': '4 Stars',
'Casts': 'Tom Hanks, Elizabeth Perkins, Robert Loggia',
'Directed By': 'Penny Marshall',
'Genre': 'Comedy',
'Comments': 'A thirteen-year-old boy wishes to be big, and his wish comes true.'
},
{
'No': '4',
'Released on': 1954,
'Title': 'Rear Window',
'Rating': '4 Stars',
'Casts': 'James Stewart, Grace Kelly, Wendell Corey',
'Directed By': 'Alfred Hitchcock',
'Genre': 'Suspense',
'Comments': 'Truly suspenseful and masterfully crafted'
}
];/**
* Wrap text data source
*/
export let data: Object[] = [
{
'No': '1',
'Released on': 1994,
'Title': 'Forrest Gump',
'Rating': '5 Stars',
'Casts': 'Tom Hanks, Robin Wright, Gary Sinise',
'Directed By': 'Robert Zemeckis',
'Genre': 'Drama',
'Comments': 'Based on the 1986 novel of the same name by Winston Groom'
},
{
'No': '2',
'Released on': 1946,
'Title': 'It’s a Wonderful Life',
'Rating': '2 Stars',
'Casts': 'James Stewart, Donna Reed, Lionel Barrymore',
'Directed By': 'Frank Capra',
'Genre': 'Drama',
'Comments': 'Colorized version'
},
{
'No': '3',
'Released on': 1988,
'Title': 'Big',
'Rating': '4 Stars',
'Casts': 'Tom Hanks, Elizabeth Perkins, Robert Loggia',
'Directed By': 'Penny Marshall',
'Genre': 'Comedy',
'Comments': 'A thirteen-year-old boy wishes to be big, and his wish comes true.'
},
{
'No': '4',
'Released on': 1954,
'Title': 'Rear Window',
'Rating': '4 Stars',
'Casts': 'James Stewart, Grace Kelly, Wendell Corey',
'Directed By': 'Alfred Hitchcock',
'Genre': 'Suspense',
'Comments': 'Truly suspenseful and masterfully crafted'
}
];Limitations of Wrap text
The following features have some limitations in wrap text:
- Sorting with wrap text applied data.
- Merge with wrap text
Merge cells
Merge cells allows users to span two or more cells in the same row or column into a single cell. When cells with multiple values are merged, top-left most cell data will be the data for the merged cell. By default, the merge cells option is enabled. Use allowMerge property to enable or disable the merge cells option in spreadsheet.
You can merge the range of cells in the following ways,
- Set the
rowSpanandcolSpanproperty incellto merge the number of cells at initial load. - Select the range of cells and apply merge by selecting the desired option from ribbon toolbar.
- Use
mergemethod to merge the range of cells, once the component is loaded.
The available merge options in spreadsheet are,
| Type | Action |
|---|---|
| Merge All | Combines all the cells in a range in to a single cell (default). |
| Merge Horizontally | Combines cells in a range as row-wise. |
| Merge Vertically | Combines cells in a range as column-wise. |
| UnMerge | Splits the merged cells into multiple cells. |
The following code example shows the merge cells operation in spreadsheet.
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective, RowsDirective, RowDirective, CellsDirective, CellDirective } 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);
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:S1');
spreadsheet.numberFormat('h:mm AM/PM', 'C1:S1');
spreadsheet.cellFormat({ verticalAlign: 'middle' }, 'A1:S11');
// Merging the `K4:M4` cells using method
spreadsheet.merge('K4:M4');
// Merging the 5th and 6th row cells across 11th, 12th and 13th column
spreadsheet.merge('K5:M6', 'Vertically');
// Merging the 18th and 19th column cells across 2nd, 3rd and 4th row
spreadsheet.merge('N4:O6', 'Horizontally');
}
}, []);
return (
<div>
<SpreadsheetComponent ref={spreadsheetRef} showFormulaBar={false}>
<SheetsDirective>
<SheetDirective name={"Merge Cells"}>
<RowsDirective>
<RowDirective height={35}></RowDirective>
<RowDirective height={35}>
<CellsDirective>
<CellDirective index={1} rowSpan={2}></CellDirective>
<CellDirective colSpan={2}></CellDirective>
<CellDirective index={6} colSpan={3}></CellDirective>
<CellDirective index={10} rowSpan={2} colSpan={3}></CellDirective>
<CellDirective index={13} colSpan={2}></CellDirective>
<CellDirective index={17} colSpan={2}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective height={35}>
<CellsDirective>
<CellDirective index={3} colSpan={3}></CellDirective>
<CellDirective index={6} colSpan={4}></CellDirective>
<CellDirective index={13} colSpan={3}></CellDirective>
<CellDirective index={17} colSpan={2}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective height={35}>
<CellsDirective>
<CellDirective index={2} colSpan={3}></CellDirective>
<CellDirective index={5} colSpan={2}></CellDirective>
<CellDirective index={7} colSpan={3}></CellDirective>
<CellDirective index={15} colSpan={2}></CellDirective>
<CellDirective index={17} colSpan={2}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective height={35}>
<CellsDirective>
<CellDirective index={2} colSpan={3}></CellDirective>
<CellDirective index={6} colSpan={4}></CellDirective>
<CellDirective index={16} colSpan={2}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective height={35}>
<CellsDirective>
<CellDirective index={2} colSpan={4}></CellDirective>
<CellDirective index={7} colSpan={3}></CellDirective>
<CellDirective index={15} colSpan={2}></CellDirective>
<CellDirective index={17} colSpan={2}></CellDirective>
</CellsDirective>
</RowDirective>
</RowsDirective>
<RangesDirective>
<RangeDirective dataSource={data}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={90}></ColumnDirective>
<ColumnDirective width={150}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></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, CellsDirective, CellDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { data } 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:S1');
spreadsheet.numberFormat('h:mm AM/PM', 'C1:S1');
spreadsheet.cellFormat({ verticalAlign: 'middle' }, 'A1:S11');
// Merging the `K4:M4` cells using method
spreadsheet.merge('K4:M4');
// Merging the 5th and 6th row cells across 11th, 12th and 13th column
spreadsheet.merge('K5:M6', 'Vertically');
// Merging the 18th and 19th column cells across 2nd, 3rd and 4th row
spreadsheet.merge('N4:O6', 'Horizontally');
}
}, []);
return (
<div>
<SpreadsheetComponent ref={spreadsheetRef} showFormulaBar={false}>
<SheetsDirective>
<SheetDirective name={"Merge Cells"}>
<RowsDirective>
<RowDirective height={35}></RowDirective>
<RowDirective height={35}>
<CellsDirective>
<CellDirective index={1} rowSpan={2}></CellDirective>
<CellDirective colSpan={2}></CellDirective>
<CellDirective index={6} colSpan={3}></CellDirective>
<CellDirective index={10} rowSpan={2} colSpan={3}></CellDirective>
<CellDirective index={13} colSpan={2}></CellDirective>
<CellDirective index={17} colSpan={2}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective height={35}>
<CellsDirective>
<CellDirective index={3} colSpan={3}></CellDirective>
<CellDirective index={6} colSpan={4}></CellDirective>
<CellDirective index={13} colSpan={3}></CellDirective>
<CellDirective index={17} colSpan={2}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective height={35}>
<CellsDirective>
<CellDirective index={2} colSpan={3}></CellDirective>
<CellDirective index={5} colSpan={2}></CellDirective>
<CellDirective index={7} colSpan={3}></CellDirective>
<CellDirective index={15} colSpan={2}></CellDirective>
<CellDirective index={17} colSpan={2}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective height={35}>
<CellsDirective>
<CellDirective index={2} colSpan={3}></CellDirective>
<CellDirective index={6} colSpan={4}></CellDirective>
<CellDirective index={16} colSpan={2}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective height={35}>
<CellsDirective>
<CellDirective index={2} colSpan={4}></CellDirective>
<CellDirective index={7} colSpan={3}></CellDirective>
<CellDirective index={15} colSpan={2}></CellDirective>
<CellDirective index={17} colSpan={2}></CellDirective>
</CellsDirective>
</RowDirective>
</RowsDirective>
<RangesDirective>
<RangeDirective dataSource={data}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={90}></ColumnDirective>
<ColumnDirective width={150}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
</SpreadsheetComponent>
</div>
);
};
export default App;
const root = createRoot(document.getElementById('root')!);
root.render(<App />);/**
* Merge cells data source
*/
export let data = [
{
'Employee ID': 10001,
'Employee Name': 'Davolio',
'9:00 AM': 'Analysis Tasks',
'9:30 AM': 'Analysis Tasks',
'10:00 AM': 'Team Meeting',
'10:30 AM': 'Testing',
'11:00 AM': 'Development',
'11:30 AM': 'Development',
'12:00 PM': 'Development',
'12:30 PM': 'Support',
'1:00 PM': 'Lunch Break',
'1:30 PM': 'Lunch Break',
'2:00 PM': 'Lunch Break',
'2:30 PM': 'Testing',
'3:00 PM': 'Testing',
'3:30 PM': 'Development',
'4:00 PM': 'Conference',
'4:30 PM': 'Team Meeting',
'5:00 PM': 'Team Meeting'
},
{
'Employee ID': 10002,
'Employee Name': 'Buchanan',
'9:00 AM': 'Task Assign',
'9:30 AM': 'Support',
'10:00 AM': 'Support',
'10:30 AM': 'Support',
'11:00 AM': 'Testing',
'11:30 AM': 'Testing',
'12:00 PM': 'Testing',
'12:30 PM': 'Testing',
'1:00 PM': 'Lunch Break',
'1:30 PM': 'Lunch Break',
'2:00 PM': 'Lunch Break',
'2:30 PM': 'Development',
'3:00 PM': 'Development',
'3:30 PM': 'Check Mail',
'4:00 PM': 'Check Mail',
'4:30 PM': 'Team Meeting',
'5:00 PM': 'Team Meeting'
},
{
'Employee ID': 10003,
'Employee Name': 'Fuller',
'9:00 AM': 'Check Mail',
'9:30 AM': 'Check Mail',
'10:00 AM': 'Check Mail',
'10:30 AM': 'Analysis Tasks',
'11:00 AM': 'Analysis Tasks',
'11:30 AM': 'Support',
'12:00 PM': 'Support',
'12:30 PM': 'Support',
'1:00 PM': 'Lunch Break',
'1:30 PM': 'Lunch Break',
'2:00 PM': 'Lunch Break',
'2:30 PM': 'Development',
'3:00 PM': 'Development',
'3:30 PM': 'Team Meeting',
'4:00 PM': 'Team Meeting',
'4:30 PM': 'Development',
'5:00 PM': 'Development'
},
{
'Employee ID': 10004,
'Employee Name': 'Leverling',
'9:00 AM': 'Testing',
'9:30 AM': 'Check Mail',
'10:00 AM': 'Check Mail',
'10:30 AM': 'Support',
'11:00 AM': 'Testing',
'11:30 AM': 'Testing',
'12:00 PM': 'Testing',
'12:30 PM': 'Testing',
'1:00 PM': 'Lunch Break',
'1:30 PM': 'Lunch Break',
'2:00 PM': 'Lunch Break',
'2:30 PM': 'Development',
'3:00 PM': 'Development',
'3:30 PM': 'Check Mail',
'4:00 PM': 'Conference',
'4:30 PM': 'Conference',
'5:00 PM': 'Team Meeting'
},
{
'Employee ID': 10005,
'Employee Name': 'Peacock',
'9:00 AM': 'Task Assign',
'9:30 AM': 'Task Assign',
'10:00 AM': 'Task Assign',
'10:30 AM': 'Task Assign',
'11:00 AM': 'Check Mail',
'11:30 AM': 'Support',
'12:00 PM': 'Support',
'12:30 PM': 'Support',
'1:00 PM': 'Lunch Break',
'1:30 PM': 'Lunch Break',
'2:00 PM': 'Lunch Break',
'2:30 PM': 'Development',
'3:00 PM': 'Development',
'3:30 PM': 'Team Meeting',
'4:00 PM': 'Team Meeting',
'4:30 PM': 'Testing',
'5:00 PM': 'Testing'
}
];/**
* Merge cells data source
*/
export let data: Object[] = [
{
'Employee ID': 10001,
'Employee Name': 'Davolio',
'9:00 AM': 'Analysis Tasks',
'9:30 AM': 'Analysis Tasks',
'10:00 AM': 'Team Meeting',
'10:30 AM': 'Testing',
'11:00 AM': 'Development',
'11:30 AM': 'Development',
'12:00 PM': 'Development',
'12:30 PM': 'Support',
'1:00 PM': 'Lunch Break',
'1:30 PM': 'Lunch Break',
'2:00 PM': 'Lunch Break',
'2:30 PM': 'Testing',
'3:00 PM': 'Testing',
'3:30 PM': 'Development',
'4:00 PM': 'Conference',
'4:30 PM': 'Team Meeting',
'5:00 PM': 'Team Meeting'
},
{
'Employee ID': 10002,
'Employee Name': 'Buchanan',
'9:00 AM': 'Task Assign',
'9:30 AM': 'Support',
'10:00 AM': 'Support',
'10:30 AM': 'Support',
'11:00 AM': 'Testing',
'11:30 AM': 'Testing',
'12:00 PM': 'Testing',
'12:30 PM': 'Testing',
'1:00 PM': 'Lunch Break',
'1:30 PM': 'Lunch Break',
'2:00 PM': 'Lunch Break',
'2:30 PM': 'Development',
'3:00 PM': 'Development',
'3:30 PM': 'Check Mail',
'4:00 PM': 'Check Mail',
'4:30 PM': 'Team Meeting',
'5:00 PM': 'Team Meeting'
},
{
'Employee ID': 10003,
'Employee Name': 'Fuller',
'9:00 AM': 'Check Mail',
'9:30 AM': 'Check Mail',
'10:00 AM': 'Check Mail',
'10:30 AM': 'Analysis Tasks',
'11:00 AM': 'Analysis Tasks',
'11:30 AM': 'Support',
'12:00 PM': 'Support',
'12:30 PM': 'Support',
'1:00 PM': 'Lunch Break',
'1:30 PM': 'Lunch Break',
'2:00 PM': 'Lunch Break',
'2:30 PM': 'Development',
'3:00 PM': 'Development',
'3:30 PM': 'Team Meeting',
'4:00 PM': 'Team Meeting',
'4:30 PM': 'Development',
'5:00 PM': 'Development'
},
{
'Employee ID': 10004,
'Employee Name': 'Leverling',
'9:00 AM': 'Testing',
'9:30 AM': 'Check Mail',
'10:00 AM': 'Check Mail',
'10:30 AM': 'Support',
'11:00 AM': 'Testing',
'11:30 AM': 'Testing',
'12:00 PM': 'Testing',
'12:30 PM': 'Testing',
'1:00 PM': 'Lunch Break',
'1:30 PM': 'Lunch Break',
'2:00 PM': 'Lunch Break',
'2:30 PM': 'Development',
'3:00 PM': 'Development',
'3:30 PM': 'Check Mail',
'4:00 PM': 'Conference',
'4:30 PM': 'Conference',
'5:00 PM': 'Team Meeting'
},
{
'Employee ID': 10005,
'Employee Name': 'Peacock',
'9:00 AM': 'Task Assign',
'9:30 AM': 'Task Assign',
'10:00 AM': 'Task Assign',
'10:30 AM': 'Task Assign',
'11:00 AM': 'Check Mail',
'11:30 AM': 'Support',
'12:00 PM': 'Support',
'12:30 PM': 'Support',
'1:00 PM': 'Lunch Break',
'1:30 PM': 'Lunch Break',
'2:00 PM': 'Lunch Break',
'2:30 PM': 'Development',
'3:00 PM': 'Development',
'3:30 PM': 'Team Meeting',
'4:00 PM': 'Team Meeting',
'4:30 PM': 'Testing',
'5:00 PM': 'Testing'
}
];Limitations of Merge
The following features have some limitations in Merge:
- Merge with filter.
- Merge with wrap text.
Data Validation
Data Validation is used to restrict the user from entering the invalid data. You can use the allowDataValidation property to enable or disable data validation.
- The default value for
allowDataValidationproperty istrue.
Apply Validation
You can apply data validation to restrict the type of data or the values that users enter into a cell.
You can apply data validation by using one of the following ways,
- Select the Data tab in the Ribbon toolbar, and then choose the Data Validation item.
- Use the
addDataValidation()method programmatically.
Clear Validation
Clear validation feature is used to remove data validations from the specified ranges or the whole worksheet.
You can clear data validation rule by one of the following ways,
- Select the Data tab in the Ribbon toolbar, and then choose the Clear Validation item.
- Use the
removeDataValidation()method programmatically.
Highlight Invalid Data
Highlight invalid data feature is used to highlight the previously entered invalid values.
You can highlight an invalid data by using one of the following ways,
- Select the Data tab in the Ribbon toolbar, and then choose the Highlight Invalid Data item.
- Use the
addInvalidHighlight()method programmatically.
Clear Highlighted Invalid Data
Clear highlight feature is used to remove the highlight from invalid cells.
You can clear the highlighted invalid data by using the following ways,
- Select the Data tab in the Ribbon toolbar, and then choose the Clear Highlight item.
- Use the
removeInvalidHighlight()method programmatically.
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RowsDirective, RowDirective, CellsDirective, CellDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
function App() {
const spreadsheetRef = React.useRef(null);
const boldCenter = { fontWeight: 'bold', textAlign: 'center' };
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
//Add Data validation to range.
spreadsheet.addDataValidation({ type: 'TextLength', operator: 'LessThanOrEqualTo', value1: '4' }, 'A2:A5');
spreadsheet.addDataValidation({ type: 'WholeNumber', operator: 'NotEqualTo', value1: '1' }, 'B2:B5');
spreadsheet.addDataValidation({ type: 'Date', operator: 'NotEqualTo', value1: '04/11/2019' }, 'F2:F5');
spreadsheet.addDataValidation({ type: 'Time', operator: 'Between', value1: '10:00:00 AM', value2: '11:00:00 AM' }, 'G2:G5');
spreadsheet.addDataValidation({ type: 'Decimal', operator: 'LessThan', value1: '100000.00' }, 'H2:H5');
//Highlight Invalid Data.
spreadsheet.addInvalidHighlight('A1:H5');
}
}, []);
return (
<div> <SpreadsheetComponent ref={spreadsheetRef} showFormulaBar={false}>
<SheetsDirective>
<SheetDirective name={'PriceDetails'}>
<RowsDirective>
<RowDirective index={0}>
<CellsDirective>
<CellDirective index={0} value={'Seller Name'} style={boldCenter}></CellDirective>
<CellDirective index={1} value={'Customer Id'} style={boldCenter}></CellDirective>
<CellDirective index={2} value={'Customer Name'} style={boldCenter}></CellDirective>
<CellDirective index={3} value={'Product Name'} style={boldCenter}></CellDirective>
<CellDirective index={4} value={'Product Price'} style={boldCenter}></CellDirective>
<CellDirective index={5} value={'Sales Date'} style={boldCenter}></CellDirective>
<CellDirective index={6} value={'Billing Time'} style={boldCenter}></CellDirective>
<CellDirective index={7} value={'Total Price'} style={boldCenter}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={1}>
<CellsDirective>
<CellDirective index={0} value={'John'}></CellDirective>
<CellDirective index={1} value={'1'} validation={{ type: 'WholeNumber', operator: 'NotEqualTo', value1: '1' }}></CellDirective>
<CellDirective index={2} value={'Nash'}></CellDirective>
<CellDirective index={3} value={'Digger'} validation={{ type: 'List', value1: 'Digger, Digger, Cherrypicker' }}></CellDirective>
<CellDirective index={4} value={'50000'} validation={{ type: 'List', value1: '50000,50000,45000' }}></CellDirective>
<CellDirective index={5} value={'04/11/2019'}></CellDirective>
<CellDirective index={6} value={'11:34:32 AM'}></CellDirective>
<CellDirective index={7} value={'1,45,000.00'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={2}>
<CellsDirective>
<CellDirective index={0} value={'Mike'}></CellDirective>
<CellDirective index={1} value={'2'} validation={{ type: 'WholeNumber', operator: 'NotEqualTo', value1: '1' }}></CellDirective>
<CellDirective index={2} value={'Jim'}></CellDirective>
<CellDirective index={3} value={'Cherrypicker'} validation={{ type: 'List', value1: 'Cherrypicker, JCB, Wheelbarrow' }}></CellDirective>
<CellDirective index={4} value={'45000'} validation={{ type: 'List', value1: '45000,90000,40' }}></CellDirective>
<CellDirective index={5} value={'04/11/2019'}></CellDirective>
<CellDirective index={6} value={'11:34:32 AM'}></CellDirective>
<CellDirective index={7} value={'1,45,000.00'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={3}>
<CellsDirective>
<CellDirective index={0} value={'shane'}></CellDirective>
<CellDirective index={1} value={'3'} validation={{ type: 'WholeNumber', operator: 'NotEqualTo', value1: '1' }}></CellDirective>
<CellDirective index={2} value={'Sean'}></CellDirective>
<CellDirective index={3} value={'Kango'} validation={{ type: 'List', value1: 'Kango, Ropes' }}></CellDirective>
<CellDirective index={4} value={'450'} validation={{ type: 'List', value1: '450, 95' }}></CellDirective>
<CellDirective index={5} value={'06/25/2019'}></CellDirective>
<CellDirective index={6} value={'01:30:11 PM'}></CellDirective>
<CellDirective index={7} value={'545.00'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={4}>
<CellsDirective>
<CellDirective index={0} value={'John'}></CellDirective>
<CellDirective index={1} value={'1'} validation={{ type: 'WholeNumber', operator: 'NotEqualTo', value1: '1' }}></CellDirective>
<CellDirective index={2} value={'Nash'}></CellDirective>
<CellDirective index={3} value={'JCB'} validation={{ type: 'List', value1: 'JCB, Ropes, scaffolding' }}></CellDirective>
<CellDirective index={4} value={'90000'} validation={{ type: 'List', value1: '90000, 95, 10000' }}></CellDirective>
<CellDirective index={5} value={'09/22/2019'}></CellDirective>
<CellDirective index={6} value={'12:30:02 PM'}></CellDirective>
<CellDirective index={7} value={'1,00,095.00'}></CellDirective>
</CellsDirective>
</RowDirective>
</RowsDirective>
<ColumnsDirective>
<ColumnDirective width={88}></ColumnDirective>
<ColumnDirective width={88}></ColumnDirective>
<ColumnDirective width={106}></ColumnDirective>
<ColumnDirective width={98}></ColumnDirective>
<ColumnDirective width={88}></ColumnDirective>
<ColumnDirective width={86}></ColumnDirective>
<ColumnDirective width={107}></ColumnDirective>
<ColumnDirective width={81}></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, RowsDirective, RowDirective, CellsDirective, CellDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { CellStyleModel } from '@syncfusion/ej2-react-spreadsheet';
function App() {
const spreadsheetRef = React.useRef<SpreadsheetComponent>(null);
const boldCenter: CellStyleModel = { fontWeight: 'bold', textAlign: 'center' };
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
//Add Data validation to range.
spreadsheet.addDataValidation({ type: 'TextLength', operator: 'LessThanOrEqualTo', value1: '4' }, 'A2:A5');
spreadsheet.addDataValidation({ type: 'WholeNumber', operator: 'NotEqualTo', value1: '1' }, 'B2:B5');
spreadsheet.addDataValidation({ type: 'Date', operator: 'NotEqualTo', value1: '04/11/2019' }, 'F2:F5');
spreadsheet.addDataValidation({ type: 'Time', operator: 'Between', value1: '10:00:00 AM', value2: '11:00:00 AM' }, 'G2:G5');
spreadsheet.addDataValidation({ type: 'Decimal', operator: 'LessThan', value1: '100000.00' }, 'H2:H5');
//Highlight Invalid Data.
spreadsheet.addInvalidHighlight('A1:H5');
}
}, []);
return (
<div> <SpreadsheetComponent ref={spreadsheetRef} showFormulaBar={false}>
<SheetsDirective>
<SheetDirective name={'PriceDetails'}>
<RowsDirective>
<RowDirective index={0}>
<CellsDirective>
<CellDirective index={0} value={'Seller Name'} style={boldCenter}></CellDirective>
<CellDirective index={1} value={'Customer Id'} style={boldCenter}></CellDirective>
<CellDirective index={2} value={'Customer Name'} style={boldCenter}></CellDirective>
<CellDirective index={3} value={'Product Name'} style={boldCenter}></CellDirective>
<CellDirective index={4} value={'Product Price'} style={boldCenter}></CellDirective>
<CellDirective index={5} value={'Sales Date'} style={boldCenter}></CellDirective>
<CellDirective index={6} value={'Billing Time'} style={boldCenter}></CellDirective>
<CellDirective index={7} value={'Total Price'} style={boldCenter}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={1}>
<CellsDirective>
<CellDirective index={0} value={'John'}></CellDirective>
<CellDirective index={1} value={'1'} validation={{ type: 'WholeNumber', operator: 'NotEqualTo', value1: '1' }}></CellDirective>
<CellDirective index={2} value={'Nash'}></CellDirective>
<CellDirective index={3} value={'Digger'} validation={{ type: 'List', value1: 'Digger, Digger, Cherrypicker' }}></CellDirective>
<CellDirective index={4} value={'50000'} validation={{ type: 'List', value1: '50000,50000,45000' }}></CellDirective>
<CellDirective index={5} value={'04/11/2019'}></CellDirective>
<CellDirective index={6} value={'11:34:32 AM'}></CellDirective>
<CellDirective index={7} value={'1,45,000.00'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={2}>
<CellsDirective>
<CellDirective index={0} value={'Mike'}></CellDirective>
<CellDirective index={1} value={'2'} validation={{ type: 'WholeNumber', operator: 'NotEqualTo', value1: '1' }}></CellDirective>
<CellDirective index={2} value={'Jim'}></CellDirective>
<CellDirective index={3} value={'Cherrypicker'} validation={{ type: 'List', value1: 'Cherrypicker, JCB, Wheelbarrow' }}></CellDirective>
<CellDirective index={4} value={'45000'} validation={{ type: 'List', value1: '45000,90000,40' }}></CellDirective>
<CellDirective index={5} value={'04/11/2019'}></CellDirective>
<CellDirective index={6} value={'11:34:32 AM'}></CellDirective>
<CellDirective index={7} value={'1,45,000.00'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={3}>
<CellsDirective>
<CellDirective index={0} value={'shane'}></CellDirective>
<CellDirective index={1} value={'3'} validation={{ type: 'WholeNumber', operator: 'NotEqualTo', value1: '1' }}></CellDirective>
<CellDirective index={2} value={'Sean'}></CellDirective>
<CellDirective index={3} value={'Kango'} validation={{ type: 'List', value1: 'Kango, Ropes' }}></CellDirective>
<CellDirective index={4} value={'450'} validation={{ type: 'List', value1: '450, 95' }}></CellDirective>
<CellDirective index={5} value={'06/25/2019'}></CellDirective>
<CellDirective index={6} value={'01:30:11 PM'}></CellDirective>
<CellDirective index={7} value={'545.00'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={4}>
<CellsDirective>
<CellDirective index={0} value={'John'}></CellDirective>
<CellDirective index={1} value={'1'} validation={{ type: 'WholeNumber', operator: 'NotEqualTo', value1: '1' }}></CellDirective>
<CellDirective index={2} value={'Nash'}></CellDirective>
<CellDirective index={3} value={'JCB'} validation={{ type: 'List', value1: 'JCB, Ropes, scaffolding' }}></CellDirective>
<CellDirective index={4} value={'90000'} validation={{ type: 'List', value1: '90000, 95, 10000' }}></CellDirective>
<CellDirective index={5} value={'09/22/2019'}></CellDirective>
<CellDirective index={6} value={'12:30:02 PM'}></CellDirective>
<CellDirective index={7} value={'1,00,095.00'}></CellDirective>
</CellsDirective>
</RowDirective>
</RowsDirective>
<ColumnsDirective>
<ColumnDirective width={88}></ColumnDirective>
<ColumnDirective width={88}></ColumnDirective>
<ColumnDirective width={106}></ColumnDirective>
<ColumnDirective width={98}></ColumnDirective>
<ColumnDirective width={88}></ColumnDirective>
<ColumnDirective width={86}></ColumnDirective>
<ColumnDirective width={107}></ColumnDirective>
<ColumnDirective width={81}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
</SpreadsheetComponent>
</div>
);
};
export default App;
const root = createRoot(document.getElementById('root')!);
root.render(<App />);Custom Data validation
The Spreadsheet supports custom data validation, allowing users to define their own validation rules for specific cells or ranges. This feature enables you to set conditions that the entered data must meet, making it particularly useful when predefined validation options, such as numbers, dates, or lists, are insufficient.
With custom validation, you can enforce rules using logical expressions or formulas, ensuring that only valid data is entered into the Spreadsheet.
For example, consider a scenario where you want to ensure that a cell contains a number between 10 and 100. To achieve this, define a validation rule using a formula that checks if the entered value is greater than 10 and less than 100. The formula for this validation is =AND(A1>10, A1<100), where A1 refers to the cell being validated.
When this rule is applied, the Spreadsheet evaluates the entered value against the formula. If a user enters a value outside the specified range, an alert notifies them of the invalid input. This helps users correct errors efficiently and ensures that only desired values are accepted.
You can apply custom data validation using two methods.
- The first is through the Data Validation dialog in the Ribbon toolbar. Navigate to the Data tab, select the Data Validation option, and choose the Custom type from the Allow dropdown menu.
- The second method is programmatically, using the
addDataValidation()method, which allows developers to set custom rules dynamically via code.
The following code example demonstrates how to add custom data validation with a formula in a Spreadsheet.
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RowsDirective, RowDirective, CellsDirective, CellDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
function App() {
const spreadsheetRef = React.useRef(null);
const boldCenter = { fontWeight: 'bold', textAlign: 'center' };
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
//Add Custom Data validation to range.
spreadsheet.addDataValidation({ type: 'Custom', value1: '=AND(B2>10, B2<100)' }, 'E2:E5');
//Highlight Invalid Data.
spreadsheet.addInvalidHighlight('E2:E5');
}
}, []);
return (
<div> <SpreadsheetComponent ref={spreadsheetRef} showFormulaBar={false}>
<SheetsDirective>
<SheetDirective name={'PriceDetails'}>
<RowsDirective>
<RowDirective index={0}>
<CellsDirective>
<CellDirective index={0} value={'Seller Name'} style={boldCenter}></CellDirective>
<CellDirective index={1} value={'Customer Id'} style={boldCenter}></CellDirective>
<CellDirective index={2} value={'Customer Name'} style={boldCenter}></CellDirective>
<CellDirective index={3} value={'Product Name'} style={boldCenter}></CellDirective>
<CellDirective index={4} value={'Product Price'} style={boldCenter}></CellDirective>
<CellDirective index={5} value={'Total Price'} style={boldCenter}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={1}>
<CellsDirective>
<CellDirective index={0} value={'John'}></CellDirective>
<CellDirective index={1} value={'101'}></CellDirective>
<CellDirective index={2} value={'Nash'}></CellDirective>
<CellDirective index={3} value={'Digger'}></CellDirective>
<CellDirective index={4} value={'50000'}></CellDirective>
<CellDirective index={5} value={'1,45,000.00'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={2}>
<CellsDirective>
<CellDirective index={0} value={'Mike'}></CellDirective>
<CellDirective index={1} value={'25'}></CellDirective>
<CellDirective index={2} value={'Jim'}></CellDirective>
<CellDirective index={3} value={'Cherrypicker'}></CellDirective>
<CellDirective index={4} value={'45000'}></CellDirective>
<CellDirective index={5} value={'1,45,000.00'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={3}>
<CellsDirective>
<CellDirective index={0} value={'shane'}></CellDirective>
<CellDirective index={1} value={'35'}></CellDirective>
<CellDirective index={2} value={'Sean'}></CellDirective>
<CellDirective index={3} value={'Kango'}></CellDirective>
<CellDirective index={4} value={'35000'}></CellDirective>
<CellDirective index={5} value={'1,54,500.00'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={4}>
<CellsDirective>
<CellDirective index={0} value={'John'}></CellDirective>
<CellDirective index={1} value={'101'}></CellDirective>
<CellDirective index={2} value={'Nash'}></CellDirective>
<CellDirective index={3} value={'JCB'}></CellDirective>
<CellDirective index={4} value={'90000'}></CellDirective>
<CellDirective index={5} value={'1,00,095.00'}></CellDirective>
</CellsDirective>
</RowDirective>
</RowsDirective>
<ColumnsDirective>
<ColumnDirective width={88}></ColumnDirective>
<ColumnDirective width={88}></ColumnDirective>
<ColumnDirective width={106}></ColumnDirective>
<ColumnDirective width={98}></ColumnDirective>
<ColumnDirective width={88}></ColumnDirective>
<ColumnDirective width={81}></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, RowsDirective, RowDirective, CellsDirective, CellDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { CellStyleModel } from '@syncfusion/ej2-react-spreadsheet';
function App() {
const spreadsheetRef = React.useRef<SpreadsheetComponent>(null);
const boldCenter: CellStyleModel = { fontWeight: 'bold', textAlign: 'center' };
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
//Add Custom Data validation to range.
spreadsheet.addDataValidation({ type: 'Custom', value1: '=AND(B2>10, B2<100)' }, 'E2:E5');
//Highlight Invalid Data.
spreadsheet.addInvalidHighlight('E2:E5');
}
}, []);
return (
<div> <SpreadsheetComponent ref={spreadsheetRef} showFormulaBar={false}>
<SheetsDirective>
<SheetDirective name={'PriceDetails'}>
<RowsDirective>
<RowDirective index={0}>
<CellsDirective>
<CellDirective index={0} value={'Seller Name'} style={boldCenter}></CellDirective>
<CellDirective index={1} value={'Customer Id'} style={boldCenter}></CellDirective>
<CellDirective index={2} value={'Customer Name'} style={boldCenter}></CellDirective>
<CellDirective index={3} value={'Product Name'} style={boldCenter}></CellDirective>
<CellDirective index={4} value={'Product Price'} style={boldCenter}></CellDirective>
<CellDirective index={5} value={'Total Price'} style={boldCenter}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={1}>
<CellsDirective>
<CellDirective index={0} value={'John'}></CellDirective>
<CellDirective index={1} value={'101'}></CellDirective>
<CellDirective index={2} value={'Nash'}></CellDirective>
<CellDirective index={3} value={'Digger'}></CellDirective>
<CellDirective index={4} value={'50000'}></CellDirective>
<CellDirective index={5} value={'1,45,000.00'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={2}>
<CellsDirective>
<CellDirective index={0} value={'Mike'}></CellDirective>
<CellDirective index={1} value={'25'}></CellDirective>
<CellDirective index={2} value={'Jim'}></CellDirective>
<CellDirective index={3} value={'Cherrypicker'}></CellDirective>
<CellDirective index={4} value={'45000'}></CellDirective>
<CellDirective index={5} value={'1,45,000.00'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={3}>
<CellsDirective>
<CellDirective index={0} value={'shane'}></CellDirective>
<CellDirective index={1} value={'35'}></CellDirective>
<CellDirective index={2} value={'Sean'}></CellDirective>
<CellDirective index={3} value={'Kango'}></CellDirective>
<CellDirective index={4} value={'35000'}></CellDirective>
<CellDirective index={5} value={'1,54,500.00'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={4}>
<CellsDirective>
<CellDirective index={0} value={'John'}></CellDirective>
<CellDirective index={1} value={'101'}></CellDirective>
<CellDirective index={2} value={'Nash'}></CellDirective>
<CellDirective index={3} value={'JCB'}></CellDirective>
<CellDirective index={4} value={'90000'}></CellDirective>
<CellDirective index={5} value={'1,00,095.00'}></CellDirective>
</CellsDirective>
</RowDirective>
</RowsDirective>
<ColumnsDirective>
<ColumnDirective width={88}></ColumnDirective>
<ColumnDirective width={88}></ColumnDirective>
<ColumnDirective width={106}></ColumnDirective>
<ColumnDirective width={98}></ColumnDirective>
<ColumnDirective width={88}></ColumnDirective>
<ColumnDirective width={81}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
</SpreadsheetComponent>
</div>
);
};
export default App;
const root = createRoot(document.getElementById('root')!);
root.render(<App />);Limitations of Data validation
The following features have some limitations in Data Validation:
- Entire row data validation.
- Insert row between the data validation.
- Copy/paste with data validation.
- Delete cells between data validation applied range.
Auto Fill
Auto Fill is used to fill the cells with data based on adjacent cells. It also follows a pattern from adjacent cells if available. There is no need to enter the repeated data manually. You can use allowAutoFill property to enable/disable the auto fill support. You can also use showFillOptions property to enable/disable the fill option and fillType property to change the default auto fill option which is available in autoFillSettings.
You can do this by one of the following ways,
- Using “AutoFillOptions” menu which is open, while drag and drop the cell using fill handle element.
- Use the autoFill() method programmatically.
The available parameters in autoFill() method are,
| Parameter | Type | Description |
|---|---|---|
| fillRange | string |
Specifies the fill range. |
| dataRange | string |
Specifies the data range. |
| direction | AutoFillDirection |
Specifies the direction(“Up”,”Right”,”Down”,”Left”)to be filled. |
| fillType | AutoFillType |
Specifies the fill type(“CopyCells”,”FillSeries”,”FillFormattingOnly”,”FillWithoutFormatting”) for autofill action. |
In Auto Fill we have following options,
- Copy Cells
- Fill Series
- Fill Formatting Only
- Fill Without Formatting
- The default auto fill option is “FillSeries” which can be referred from
fillTypeproperty.
Copy Cells
To copy the selected cell content to the adjacent cells. You can do this by one of the following ways,
- Using fill handle to select the adjacent cell range and “Copy Cells” option in “AutoFillOptions” menu to fill the adjacent cells.
- Using “CopyCells” as fill type in
autoFillmethod to fill the adjacent cells.
Fill Series
To fill the series of numbers, characters, or dates based on selected cell content to the adjacent cells with their formats.
You can do this by one of the following ways,
- Using fill handle to select the adjacent cell range and “Fill Series” option in “AutoFillOptions” menu to fill the adjacent cells.
- Using “FillSeries” as fill type in
autoFillmethod to fill the adjacent cells.
Fill Formatting Only
To fill the cell style and number formatting based on the selected cell content to the adjacent cells without their content.
You can do this by one of the following ways,
- Using fill handle to select the adjacent cell range and “Fill Formatting Only” option in “AutoFillOptions” menu to fill the adjacent cells.
- Using “FillFormattingOnly” as fill type in
autoFillmethod to fill the adjacent cells.
Fill Without Formatting
To fill series of numbers, characters, or dates based on the selected cells to the adjacent cells without their formats.
You can do this by one of the following ways,
- Using fill handle to select the adjacent cell range and “Fill Without Formatting” option in “AutoFillOptions” menu to fill the adjacent cells.
- Using “FillWithoutFormatting” as fill type in
autoFillmethod to fill the adjacent cells.
In the following sample, you can enable/disable the fill option on the button click event by using the showFillOptions property in autoFillSettings.
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 } from '@syncfusion/ej2-react-spreadsheet';
import { data } from './datasource';
function App() {
const spreadsheetRef = React.useRef(null);
const btnClick = () => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
spreadsheet.autoFillSettings.showFillOptions = !spreadsheet.autoFillSettings.showFillOptions; //To change whether fill options need to be shown or not.
}
};
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
spreadsheet.cellFormat({ backgroundColor: '#357cd2', color: '#fff', fontWeight: 'bold', textAlign: 'center' }, 'A1:H1');
spreadsheet.autoFill('D4:D11', 'D2:D3', 'Down', 'CopyCells');
spreadsheet.autoFill('E4:E11', 'E2:E3', 'Down', 'FillSeries');
spreadsheet.autoFill('B4:B11', 'B2:B3', 'Down', 'FillFormattingOnly');
spreadsheet.autoFill('C4:C11', 'C2:C3', 'Down', 'FillWithoutFormatting');
}
}, []);
return (
<div>
<button className='e-btn' onClick={btnClick}>Change ShowFillOptions</button>
<SpreadsheetComponent ref={spreadsheetRef}>
<SheetsDirective>
<SheetDirective>
<RangesDirective>
<RangeDirective dataSource={data}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={130}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></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 } from '@syncfusion/ej2-react-spreadsheet';
import { data } from './datasource';
function App() {
const spreadsheetRef = React.useRef<SpreadsheetComponent>(null);
const btnClick = (): void => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
spreadsheet.autoFillSettings.showFillOptions = !spreadsheet.autoFillSettings.showFillOptions; //To change whether fill options need to be shown or not.
}
};
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
spreadsheet.cellFormat({ backgroundColor: '#357cd2', color: '#fff', fontWeight: 'bold', textAlign: 'center' }, 'A1:H1');
spreadsheet.autoFill('D4:D11', 'D2:D3', 'Down', 'CopyCells');
spreadsheet.autoFill('E4:E11', 'E2:E3', 'Down', 'FillSeries');
spreadsheet.autoFill('B4:B11', 'B2:B3', 'Down', 'FillFormattingOnly');
spreadsheet.autoFill('C4:C11', 'C2:C3', 'Down', 'FillWithoutFormatting');
}
}, []);
return (
<div>
<button className='e-btn' onClick={btnClick}>Change ShowFillOptions</button>
<SpreadsheetComponent ref={spreadsheetRef}>
<SheetsDirective>
<SheetDirective>
<RangesDirective>
<RangeDirective dataSource={data}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={130}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
</SpreadsheetComponent>
</div>
);
};
export default App;
const root = createRoot(document.getElementById('root')!);
root.render(<App />);export let data = [
{ 'Item Name': 'Casual Shoes', Date: '02/14/2014', Time: '11:34:32 AM', Quantity: 10, Price: 20, Amount: 200, Discount: 1, Profit: 10 },
{ 'Item Name': 'Sports Shoes', Date: '06/11/2014', Time: '05:56:32 AM', Quantity: 20, Price: 30, Amount: 600, Discount: 5, Profit: 50 },
{ 'Item Name': 'Formal Shoes', Date: '07/27/2014', Time: '03:32:44 AM', Quantity: 20, Price: 15, Amount: 300, Discount: 7, Profit: 27 },
{ 'Item Name': 'Sandals & Floaters', Date: '11/21/2014', Time: '06:23:54 AM', Quantity: 15, Price: 20, Amount: 300, Discount: 11, Profit: 67 },
{ 'Item Name': 'Flip- Flops & Slippers', Date: '06/23/2014', Time: '12:43:59 AM', Quantity: 30, Price: 10, Amount: 300, Discount: 10, Profit: 70 },
{ 'Item Name': 'Sneakers', Date: '07/22/2014', Time: '10:55:53 AM', Quantity: 40, Price: 20, Amount: 800, Discount: 13, Profit: 66 },
{ 'Item Name': 'Running Shoes', Date: '02/04/2014', Time: '03:44:34 AM', Quantity: 20, Price: 10, Amount: 200, Discount: 3, Profit: 14 },
{ 'Item Name': 'Loafers', Date: '11/30/2014', Time: '03:12:52 AM', Quantity: 31, Price: 10, Amount: 310, Discount: 6, Profit: 29 },
{ 'Item Name': 'Cricket Shoes', Date: '07/09/2014', Time: '11:32:14 AM', Quantity: 41, Price: 30, Amount: 1210, Discount: 12, Profit: 166 },
{ 'Item Name': 'T-Shirts', Date: '10/31/2014', Time: '12:01:44 AM', Quantity: 50, Price: 10, Amount: 500, Discount: 9, Profit: 55 },
];export let data: Object[] = [
{ 'Item Name': 'Casual Shoes', Date: '02/14/2014', Time: '11:34:32 AM', Quantity: 10, Price: 20, Amount: 200, Discount: 1, Profit: 10 },
{ 'Item Name': 'Sports Shoes', Date: '06/11/2014', Time: '05:56:32 AM', Quantity: 20, Price: 30, Amount: 600, Discount: 5, Profit: 50 },
{ 'Item Name': 'Formal Shoes', Date: '07/27/2014', Time: '03:32:44 AM', Quantity: 20, Price: 15, Amount: 300, Discount: 7, Profit: 27 },
{ 'Item Name': 'Sandals & Floaters', Date: '11/21/2014', Time: '06:23:54 AM', Quantity: 15, Price: 20, Amount: 300, Discount: 11, Profit: 67 },
{ 'Item Name': 'Flip- Flops & Slippers', Date: '06/23/2014', Time: '12:43:59 AM', Quantity: 30, Price: 10, Amount: 300, Discount: 10, Profit: 70 },
{ 'Item Name': 'Sneakers', Date: '07/22/2014', Time: '10:55:53 AM', Quantity: 40, Price: 20, Amount: 800, Discount: 13, Profit: 66 },
{ 'Item Name': 'Running Shoes', Date: '02/04/2014', Time: '03:44:34 AM', Quantity: 20, Price: 10, Amount: 200, Discount: 3, Profit: 14 },
{ 'Item Name': 'Loafers', Date: '11/30/2014', Time: '03:12:52 AM', Quantity: 31, Price: 10, Amount: 310, Discount: 6, Profit: 29 },
{ 'Item Name': 'Cricket Shoes', Date: '07/09/2014', Time: '11:32:14 AM', Quantity: 41, Price: 30, Amount: 1210, Discount: 12, Profit: 166 },
{ 'Item Name': 'T-Shirts', Date: '10/31/2014', Time: '12:01:44 AM', Quantity: 50, Price: 10, Amount: 500, Discount: 9, Profit: 55 },
];Limitations of Autofill
The following features have some limitations in Autofill:
- Flash Fill option in Autofill feature.
- Fill with Conditional Formatting applied cells.
Clear
Clear feature helps you to clear the cell contents (formulas and data), formats (including number formats, conditional formats, and borders) in a spreadsheet. When you apply clear all, both the contents and the formats will be cleared simultaneously.
Apply Clear Feature
You can apply clear feature by using one of the following ways,
- Select the clear icon in the Ribbon toolbar under the Home Tab.
- Using the
clear()method to clear the values.
Clear has the following types in the spreadsheet,
| Options | Uses |
|---|---|
Clear All |
Used to clear all contents, formats, and hyperlinks. |
Clear Formats |
Used to clear the formats (including number formats, conditional formats, and borders) in a cell. |
Clear Contents |
Used to clear the contents (formulas and data) in a cell. |
Clear Hyperlinks |
Used to clear the hyperlink in a cell. |
Methods
Clear the cell contents and formats in the Spreadsheet document by using the clear method. The clear method has type and range as parameters. The following code example shows how to clear the cell contents and formats in the button click event.
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { DropDownButtonComponent } from '@syncfusion/ej2-react-splitbuttons';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective } 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 items = [
{ text: "Clear All" },
{ text: "Clear Formats" },
{ text: "Clear Contents" },
{ text: "Clear Hyperlinks" }
];
const handleItemSelect = (args) => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
if (args.item.text === 'Clear All')
spreadsheet.clear({ type: 'Clear All', range: 'D1:D10' }); // Clear the content, formats and hyperlinks applied in the provided range.
if (args.item.text === 'Clear Formats')
spreadsheet.clear({ type: 'Clear Formats', range: 'B1:B10' }); // Clear the formats applied in the provided range
if (args.item.text === 'Clear Contents')
spreadsheet.clear({ type: 'Clear Contents', range: 'A1:A10' }); // Clear the content in the provided range
if (args.item.text === 'Clear Hyperlinks')
spreadsheet.clear({ type: 'Clear Hyperlinks', range: 'F2:F6' }); // Clear the hyperlinks applied in the provided range
}
};
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
spreadsheet.cellFormat({ fontWeight: 'bold', fontSize: '12pt' }, 'A1:F1');
spreadsheet.cellFormat({ color: '#10c469' }, 'B1:B10');
}
}, []);
return (
<div>
<DropDownButtonComponent items={items} select={handleItemSelect}> Clear </DropDownButtonComponent>
<SpreadsheetComponent ref={spreadsheetRef}>
<SheetsDirective>
<SheetDirective>
<RangesDirective>
<RangeDirective dataSource={data} />
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={100} />
<ColumnDirective width={110} />
<ColumnDirective width={100} />
<ColumnDirective width={180} />
<ColumnDirective width={130} />
<ColumnDirective width={130} />
</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 { DropDownButtonComponent, ItemModel } from '@syncfusion/ej2-react-splitbuttons';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective, MenuSelectEventArgs } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { data } from './datasource';
function App() {
const spreadsheetRef = React.useRef<SpreadsheetComponent>(null);
const items: ItemModel[] = [
{ text: "Clear All" },
{ text: "Clear Formats" },
{ text: "Clear Contents" },
{ text: "Clear Hyperlinks" }
];
const handleItemSelect = (args: MenuSelectEventArgs): void => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
if (args.item.text === 'Clear All')
spreadsheet.clear({ type: 'Clear All', range: 'D1:D10' }); // Clear the content, formats and hyperlinks applied in the provided range.
if (args.item.text === 'Clear Formats')
spreadsheet.clear({ type: 'Clear Formats', range: 'B1:B10' }); // Clear the formats applied in the provided range
if (args.item.text === 'Clear Contents')
spreadsheet.clear({ type: 'Clear Contents', range: 'A1:A10' }); // Clear the content in the provided range
if (args.item.text === 'Clear Hyperlinks')
spreadsheet.clear({ type: 'Clear Hyperlinks', range: 'F2:F6' }); // Clear the hyperlinks applied in the provided range
}
};
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
spreadsheet.cellFormat({ fontWeight: 'bold', fontSize: '12pt' }, 'A1:F1');
spreadsheet.cellFormat({ color: '#10c469' }, 'B1:B10');
}
}, []);
return (
<div>
<DropDownButtonComponent items={items} select={handleItemSelect}> Clear </DropDownButtonComponent>
<SpreadsheetComponent ref={spreadsheetRef}>
<SheetsDirective>
<SheetDirective>
<RangesDirective>
<RangeDirective dataSource={data} />
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={100} />
<ColumnDirective width={110} />
<ColumnDirective width={100} />
<ColumnDirective width={180} />
<ColumnDirective width={130} />
<ColumnDirective width={130} />
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
</SpreadsheetComponent>
</div>
);
};
export default App;
const root = createRoot(document.getElementById('root')!);
root.render(<App />);/**
* Default data source
*/
export let data = [{
OrderID: 10248,
CustomerID: 'VINET',
EmployeeID: 5,
ShipName: 'Vins et alcools Chevalier',
ShipCity: 'Reims',
Website: 'https://www.amazon.com/'
},
{
OrderID: 10249,
CustomerID: 'TOMSP',
EmployeeID: 6,
ShipName: 'Toms Spezialitäten',
ShipCity: 'Münster',
Website: 'https://www.overstock.com/'
},
{
OrderID: 10250,
CustomerID: 'HANAR',
EmployeeID: 4,
ShipName: 'Hanari Carnes',
ShipCity: 'Rio de Janeiro',
Website: 'https://www.aliexpress.com/'
},
{
OrderID: 10251,
CustomerID: 'VICTE',
EmployeeID: 3,
ShipName: 'Victuailles en stock',
ShipCity: 'Lyon',
Website: 'http://www.alibaba.com/'
},
{
OrderID: 10252,
CustomerID: 'SUPRD',
EmployeeID: 4,
ShipName: 'Suprêmes délices',
ShipCity: 'Charleroi',
Website: 'https://taobao.com/'
}];/**
* Default data source
*/
export let data: Object[] = [{
OrderID: 10248,
CustomerID: 'VINET',
EmployeeID: 5,
ShipName: 'Vins et alcools Chevalier',
ShipCity: 'Reims',
Website: 'https://www.amazon.com/'
},
{
OrderID: 10249,
CustomerID: 'TOMSP',
EmployeeID: 6,
ShipName: 'Toms Spezialitäten',
ShipCity: 'Münster',
Website: 'https://www.overstock.com/'
},
{
OrderID: 10250,
CustomerID: 'HANAR',
EmployeeID: 4,
ShipName: 'Hanari Carnes',
ShipCity: 'Rio de Janeiro',
Website: 'https://www.aliexpress.com/'
},
{
OrderID: 10251,
CustomerID: 'VICTE',
EmployeeID: 3,
ShipName: 'Victuailles en stock',
ShipCity: 'Lyon',
Website: 'http://www.alibaba.com/'
},
{
OrderID: 10252,
CustomerID: 'SUPRD',
EmployeeID: 4,
ShipName: 'Suprêmes délices',
ShipCity: 'Charleroi',
Website: 'https://taobao.com/'
}];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.