Insert in React Spreadsheet
10 Jul 202624 minutes to read
You can insert rows or columns anywhere in a spreadsheet. Use the allowInsert property to enable or disable the insert option in Spreadsheet.
Row
The rows can be inserted in the following ways,
- Using
insertRowmethod, you can insert the rows once the component is loaded. - Using context menu, insert the empty rows in the desired position.
The following code example shows the options for inserting rows in the spreadsheet.
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 rowsModel = [{
index: 9, // Need to specify the index for the first row collection, the specified rows will be inserted in this index.
cells: [{ value: '' }, { value: '8' }, { value: 'Northwoods Cranberry Sauce' }, { value: '3' }, { value: '12 - 12 oz jars' },
{ value: '40.00' }, { value: '6' }, { value: 'false' }]
},
{
cells: [{ value: '' }, { value: '9' }, { value: 'Mishi Kobe Niku' }, { value: '4' }, { value: '18 - 500 g pkgs.' }, { value: '97.00' }, { value: '29' }, { value: 'true' }]
}];
const onCreated = () => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
// Applies style formatting before inserting the rows
spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'B1:H1');
// inserting a empty row at 0th index
spreadsheet.insertRow();
// inserting 2 rows at the 9th index with data
spreadsheet.insertRow(rowsModel);
// Applies style formatting after the rows are inserted
spreadsheet.cellFormat({ textAlign: 'center' }, 'B3:B12');
spreadsheet.cellFormat({ textAlign: 'center' }, 'D3:D12');
spreadsheet.cellFormat({ textAlign: 'center' }, 'F3:H12');
}
};
return (
<div>
<SpreadsheetComponent ref={spreadsheetRef} created={onCreated} showSheetTabs={false} showRibbon={false} showFormulaBar={false} >
<SheetsDirective>
<SheetDirective>
<RangesDirective>
<RangeDirective dataSource={data} startCell={"B1"}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={20}></ColumnDirective>
<ColumnDirective width={90}></ColumnDirective>
<ColumnDirective width={220}></ColumnDirective>
<ColumnDirective width={90}></ColumnDirective>
<ColumnDirective width={140}></ColumnDirective>
<ColumnDirective width={90}></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, RowModel } 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 rowsModel: RowModel[] = [{
index: 9, // Need to specify the index for the first row collection, the specified rows will be inserted in this index.
cells: [{ value: '' }, { value: '8' }, { value: 'Northwoods Cranberry Sauce' }, { value: '3' }, { value: '12 - 12 oz jars' },
{ value: '40.00' }, { value: '6' }, { value: 'false' }]
},
{
cells: [{ value: '' }, { value: '9' }, { value: 'Mishi Kobe Niku' }, { value: '4' }, { value: '18 - 500 g pkgs.' }, { value: '97.00' }, { value: '29' }, { value: 'true' }]
}];
const onCreated = (): void => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
// Applies style formatting before inserting the rows
spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'B1:H1');
// inserting a empty row at 0th index
spreadsheet.insertRow();
// inserting 2 rows at the 9th index with data
spreadsheet.insertRow(rowsModel);
// Applies style formatting after the rows are inserted
spreadsheet.cellFormat({ textAlign: 'center' }, 'B3:B12');
spreadsheet.cellFormat({ textAlign: 'center' }, 'D3:D12');
spreadsheet.cellFormat({ textAlign: 'center' }, 'F3:H12');
}
};
return (
<div>
<SpreadsheetComponent ref={spreadsheetRef} created={onCreated} showSheetTabs={false} showRibbon={false} showFormulaBar={false} >
<SheetsDirective>
<SheetDirective>
<RangesDirective>
<RangeDirective dataSource={data} startCell={"B1"}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={20}></ColumnDirective>
<ColumnDirective width={90}></ColumnDirective>
<ColumnDirective width={220}></ColumnDirective>
<ColumnDirective width={90}></ColumnDirective>
<ColumnDirective width={140}></ColumnDirective>
<ColumnDirective width={90}></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 />);/**
* Insert row data source
*/
export let data = [
{
'Product Id': 1, 'Product Name': 'Chai', 'Supplier Id': 1, 'Quantity Per Unit': '10 boxes x 20 bags', 'Unit Price': 18.00, 'Units In Stock': 39, 'Discontinued': false
},
{
'Product Id': 2, 'Product Name': 'Chang', 'Supplier Id': 1, 'Quantity Per Unit': '24 - 12 oz bottles', 'Unit Price': 19.00, 'Units In Stock': 17, 'Discontinued': true
},
{
'Product Id': 3, 'Product Name': 'Aniseed Syrup', 'Supplier Id': 1, 'Quantity Per Unit': '12 - 550 ml bottles', 'Unit Price': 10.00, 'Units In Stock': 13, 'Discontinued': false
},
{
'Product Id': 4, 'Product Name': 'Chef Anton\'s Cajun Seasoning', 'Supplier Id': 2, 'Quantity Per Unit': '48 - 6 oz jars', 'Unit Price': 22.00, 'Units In Stock': 53, 'Discontinued': true
},
{
'Product Id': 5, 'Product Name': 'Chef Anton\'s Gumbo Mix', 'Supplier Id': 2, 'Quantity Per Unit': '36 boxes', 'Unit Price': 21.35, 'Units In Stock': 0, 'Discontinued': true
},
{
'Product Id': 6, 'Product Name': 'Grandma\'s Boysenberry Spread', 'Supplier Id': 3, 'Quantity Per Unit': '12 - 8 oz jars', 'Unit Price': 25.00, 'Units In Stock': 120, 'Discontinued': false
},
{
'Product Id': 7, 'Product Name': 'Uncle Bob\'s Organic Dried Pears', 'Supplier Id': 3, 'Quantity Per Unit': '12 - 1 lb pkgs.', 'Unit Price': 30.00, 'Units In Stock': 15, 'Discontinued': true
},
{
'Product Id': 10, 'Product Name': 'Queso Cabrales', 'Supplier Id': 5, 'Quantity Per Unit': '1 kg pkg.', 'Unit Price': 21.00, 'Units In Stock': 22, 'Discontinued': false
}
];/**
* Insert row data source
*/
export let data: Object[] = [
{
'Product Id': 1, 'Product Name': 'Chai', 'Supplier Id': 1, 'Quantity Per Unit': '10 boxes x 20 bags', 'Unit Price': 18.00, 'Units In Stock': 39, 'Discontinued': false
},
{
'Product Id': 2, 'Product Name': 'Chang', 'Supplier Id': 1, 'Quantity Per Unit': '24 - 12 oz bottles', 'Unit Price': 19.00, 'Units In Stock': 17, 'Discontinued': true
},
{
'Product Id': 3, 'Product Name': 'Aniseed Syrup', 'Supplier Id': 1, 'Quantity Per Unit': '12 - 550 ml bottles', 'Unit Price': 10.00, 'Units In Stock': 13, 'Discontinued': false
},
{
'Product Id': 4, 'Product Name': 'Chef Anton\'s Cajun Seasoning', 'Supplier Id': 2, 'Quantity Per Unit': '48 - 6 oz jars', 'Unit Price': 22.00, 'Units In Stock': 53, 'Discontinued': true
},
{
'Product Id': 5, 'Product Name': 'Chef Anton\'s Gumbo Mix', 'Supplier Id': 2, 'Quantity Per Unit': '36 boxes', 'Unit Price': 21.35, 'Units In Stock': 0, 'Discontinued': true
},
{
'Product Id': 6, 'Product Name': 'Grandma\'s Boysenberry Spread', 'Supplier Id': 3, 'Quantity Per Unit': '12 - 8 oz jars', 'Unit Price': 25.00, 'Units In Stock': 120, 'Discontinued': false
},
{
'Product Id': 7, 'Product Name': 'Uncle Bob\'s Organic Dried Pears', 'Supplier Id': 3, 'Quantity Per Unit': '12 - 1 lb pkgs.', 'Unit Price': 30.00, 'Units In Stock': 15, 'Discontinued': true
},
{
'Product Id': 10, 'Product Name': 'Queso Cabrales', 'Supplier Id': 5, 'Quantity Per Unit': '1 kg pkg.', 'Unit Price': 21.00, 'Units In Stock': 22, 'Discontinued': false
}];Column
The columns can be inserted in the following ways,
- Using
insertColumnmethod, you can insert the columns once the component is loaded. - Using context menu, insert the empty columns in the desired position.
The following code example shows the options for inserting columns in the spreadsheet.
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective, getCellAddress } 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 cellsModel = [{ value: 'Unit Price', style: { fontWeight: 'bold', textAlign: 'center' } }, { value: '18.00' },
{ value: '19.00' }, { value: '10.00' }, { value: '22.00' }, { value: '21.35' }, { value: '25.00' }, { value: '30.00' },
{ value: '21.00' }, { value: '40.00' }, { value: '97.00' }];
const onCreated = () => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
// Applies style formatting before inserting the column
spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A2:G2');
// inserting a empty column at 0th index
spreadsheet.insertColumn();
// inserting 1 column at the 5th index with column model
spreadsheet.insertColumn([{ index: 5, width: 90 }]);
let rowIndex = 1;
// Updating the 5th column data
cellsModel.forEach((cell) => {
if (spreadsheet) {
spreadsheet.updateCell(cell, getCellAddress(rowIndex, 5)); rowIndex++;
}
});
// Applies style formatting after the columns are inserted
spreadsheet.cellFormat({ textAlign: 'center' }, 'B3:B12');
spreadsheet.cellFormat({ textAlign: 'center' }, 'D3:D12');
spreadsheet.cellFormat({ textAlign: 'center' }, 'F3:H12');
}
};
return (
<div>
<SpreadsheetComponent ref={spreadsheetRef} created={onCreated} showSheetTabs={false} showRibbon={false} showFormulaBar={false} >
<SheetsDirective>
<SheetDirective>
<RangesDirective>
<RangeDirective dataSource={data} startCell={"A2"}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={90}></ColumnDirective>
<ColumnDirective width={220}></ColumnDirective>
<ColumnDirective width={90}></ColumnDirective>
<ColumnDirective width={140}></ColumnDirective>
<ColumnDirective width={90}></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, CellModel, getCellAddress } 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 cellsModel: CellModel[] = [{ value: 'Unit Price', style: { fontWeight: 'bold', textAlign: 'center' } }, { value: '18.00' },
{ value: '19.00' }, { value: '10.00' }, { value: '22.00' }, { value: '21.35' }, { value: '25.00' }, { value: '30.00' },
{ value: '21.00' }, { value: '40.00' }, { value: '97.00' }];
const onCreated = (): void => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
// Applies style formatting before inserting the column
spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A2:G2');
// inserting a empty column at 0th index
spreadsheet.insertColumn();
// inserting 1 column at the 5th index with column model
spreadsheet.insertColumn([{ index: 5, width: 90 }]);
let rowIndex: number = 1;
// Updating the 5th column data
cellsModel.forEach((cell: CellModel) => {
if (spreadsheet) {
spreadsheet.updateCell(cell, getCellAddress(rowIndex, 5)); rowIndex++;
}
});
// Applies style formatting after the columns are inserted
spreadsheet.cellFormat({ textAlign: 'center' }, 'B3:B12');
spreadsheet.cellFormat({ textAlign: 'center' }, 'D3:D12');
spreadsheet.cellFormat({ textAlign: 'center' }, 'F3:H12');
}
};
return (
<div>
<SpreadsheetComponent ref={spreadsheetRef} created={onCreated} showSheetTabs={false} showRibbon={false} showFormulaBar={false} >
<SheetsDirective>
<SheetDirective>
<RangesDirective>
<RangeDirective dataSource={data} startCell={"A2"}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={90}></ColumnDirective>
<ColumnDirective width={220}></ColumnDirective>
<ColumnDirective width={90}></ColumnDirective>
<ColumnDirective width={140}></ColumnDirective>
<ColumnDirective width={90}></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 />);/**
* Insert column data source
*/
export let data = [
{
'Product Id': 1, 'Product Name': 'Chai', 'Supplier Id': 1, 'Quantity Per Unit': '10 boxes x 20 bags', 'Units In Stock': 39, 'Discontinued': true
},
{
'Product Id': 2, 'Product Name': 'Chang', 'Supplier Id': 1, 'Quantity Per Unit': '24 - 12 oz bottles', 'Units In Stock': 17, 'Discontinued': true
},
{
'Product Id': 3, 'Product Name': 'Aniseed Syrup', 'Supplier Id': 1, 'Quantity Per Unit': '12 - 550 ml bottles', 'Units In Stock': 13, 'Discontinued': true
},
{
'Product Id': 4, 'Product Name': 'Chef Anton\'s Cajun Seasoning', 'Supplier Id': 2, 'Quantity Per Unit': '48 - 6 oz jars', 'Units In Stock': 53, 'Discontinued': true
},
{
'Product Id': 5, 'Product Name': 'Chef Anton\'s Gumbo Mix', 'Supplier Id': 2, 'Quantity Per Unit': '36 boxes', 'Units In Stock': 0, 'Discontinued': true
},
{
'Product Id': 6, 'Product Name': 'Grandma\'s Boysenberry Spread', 'Supplier Id': 3, 'Quantity Per Unit': '12 - 8 oz jars', 'Units In Stock': 120, 'Discontinued': false
},
{
'Product Id': 7, 'Product Name': 'Uncle Bob\'s Organic Dried Pears', 'Supplier Id': 3, 'Quantity Per Unit': '12 - 1 lb pkgs.', 'Units In Stock': 15, 'Discontinued': false
},
{
'Product Id': 8, 'Product Name': 'Queso Cabrales', 'Supplier Id': 5, 'Quantity Per Unit': '1 kg pkg.', 'Units In Stock': 22, 'Discontinued': false
},
{
'Product Id': 9, 'Product Name': 'Northwoods Cranberry Sauce', 'Supplier Id': 3, 'Quantity Per Unit': '12 - 12 oz jars', 'Units In Stock': 6, 'Discontinued': false
},
{
'Product Id': 10, 'Product Name': 'Mishi Kobe Niku', 'Supplier Id': 4, 'Quantity Per Unit': '18 - 500 g pkgs.', 'Units In Stock': 29, 'Discontinued': true
}
];/**
* Insert column data source
*/
export let data: Object[] = [
{
'Product Id': 1, 'Product Name': 'Chai', 'Supplier Id': 1, 'Quantity Per Unit': '10 boxes x 20 bags', 'Units In Stock': 39, 'Discontinued': true
},
{
'Product Id': 2, 'Product Name': 'Chang', 'Supplier Id': 1, 'Quantity Per Unit': '24 - 12 oz bottles', 'Units In Stock': 17, 'Discontinued': true
},
{
'Product Id': 3, 'Product Name': 'Aniseed Syrup', 'Supplier Id': 1, 'Quantity Per Unit': '12 - 550 ml bottles', 'Units In Stock': 13, 'Discontinued': true
},
{
'Product Id': 4, 'Product Name': 'Chef Anton\'s Cajun Seasoning', 'Supplier Id': 2, 'Quantity Per Unit': '48 - 6 oz jars', 'Units In Stock': 53, 'Discontinued': true
},
{
'Product Id': 5, 'Product Name': 'Chef Anton\'s Gumbo Mix', 'Supplier Id': 2, 'Quantity Per Unit': '36 boxes', 'Units In Stock': 0, 'Discontinued': true
},
{
'Product Id': 6, 'Product Name': 'Grandma\'s Boysenberry Spread', 'Supplier Id': 3, 'Quantity Per Unit': '12 - 8 oz jars', 'Units In Stock': 120, 'Discontinued': false
},
{
'Product Id': 7, 'Product Name': 'Uncle Bob\'s Organic Dried Pears', 'Supplier Id': 3, 'Quantity Per Unit': '12 - 1 lb pkgs.', 'Units In Stock': 15, 'Discontinued': false
},
{
'Product Id': 8, 'Product Name': 'Queso Cabrales', 'Supplier Id': 5, 'Quantity Per Unit': '1 kg pkg.', 'Units In Stock': 22, 'Discontinued': false
},
{
'Product Id': 9, 'Product Name': 'Northwoods Cranberry Sauce', 'Supplier Id': 3, 'Quantity Per Unit': '12 - 12 oz jars', 'Units In Stock': 6, 'Discontinued': false
},
{
'Product Id': 10, 'Product Name': 'Mishi Kobe Niku', 'Supplier Id': 4, 'Quantity Per Unit': '18 - 500 g pkgs.', 'Units In Stock': 29, 'Discontinued': true
}];Limitations of insert
The following features have some limitations in Insert/Delete:
- Insert row/column between the formatting applied cells.
- Insert row/column between the data validation.
- Insert row/column between the conditional formatting applied cells.
- Insert row/column between the filter applied cells.