React

Search results

Rows and Columns in React Spreadsheet component

24 May 2022 / 8 minutes to read

Spreadsheet is a tabular format consisting of rows and columns. The intersection point of rows and columns are called as cells. The list of operations that you can perform in rows and columns are,

Insert
Delete
Show and Hide

Insert

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 insertRow method, 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.

Source

Preview

app.jsx

datasource.jsx

index.html

app.tsx

datasource.tsx

import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { data } from './datasource';
export default class App extends React.Component {
    constructor() {
        super(...arguments);
        this.rowsModel = [{
                index: 9,
                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' }]
            }];
    }
    created() {
        // Applies style formatting before inserting the rows
        this.spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'B1:H1');
        // inserting a empty row at 0th index
        this.spreadsheet.insertRow();
        // inserting 2 rows at the 9th index with data
        this.spreadsheet.insertRow(this.rowsModel);
        // Applies style formatting after the rows are inserted
        this.spreadsheet.cellFormat({ textAlign: 'center' }, 'B3:B12');
        this.spreadsheet.cellFormat({ textAlign: 'center' }, 'D3:D12');
        this.spreadsheet.cellFormat({ textAlign: 'center' }, 'F3:H12');
    }
    ;
    render() {
        return (<div> <SpreadsheetComponent ref={(ssObj) => { this.spreadsheet = ssObj; }} created={this.created.bind(this)} 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>);
    }
}
ReactDOM.render(<App />, document.getElementById('root'));

/**
 * 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
    }
];

<!DOCTYPE html>
<html lang="en">

<head>
    <title>Syncfusion React Spreadsheet</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Essential JS 2 for React Components" />
    <meta name="author" content="Syncfusion" />
    <link href="https://cdn.syncfusion.com/ej2/20.1.57/material.css" rel="stylesheet" type="text/css"/>
    <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <script src="systemjs.config.js"></script>
    <style>
        #loader {
            color: #008cff;
            height: 40px;
            left: 45%;
            position: absolute;
            top: 45%;
            width: 30%;
        }
        body {
            overflow: hidden;
        }
        .customClass.e-cell {
                        background-color: red;
                }
    </style>
</head>

<body>
    <div id='root'>
        <div id='loader'>Loading....</div>
    </div>
</body>

</html>

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

export default class App extends React.Component<{}, {}> {
   public spreadsheet: SpreadsheetComponent;
   public 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' }]
  }];
   public created(): void{
    // Applies style formatting before inserting the rows
    this.spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'B1:H1');
    // inserting a empty row at 0th index
    this.spreadsheet.insertRow();
    // inserting 2 rows at the 9th index with data
    this.spreadsheet.insertRow(this.rowsModel);
    // Applies style formatting after the rows are inserted
    this.spreadsheet.cellFormat({ textAlign: 'center' }, 'B3:B12');
    this.spreadsheet.cellFormat({ textAlign: 'center' }, 'D3:D12');
    this.spreadsheet.cellFormat({ textAlign: 'center' }, 'F3:H12');
};
  
 render() {
    return  ( <div> <SpreadsheetComponent
                    ref={(ssObj) => { this.spreadsheet = ssObj }} created={this.created.bind(this)} 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>);
}
}
ReactDOM.render(<App />, document.getElementById('root'));

/**
 * 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 insertColumn method, 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.

Source

Preview

app.jsx

datasource.jsx

index.html

app.tsx

datasource.tsx

import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective, getCellAddress } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { data } from './datasource';
export default class App extends React.Component {
    constructor() {
        super(...arguments);
        this.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' }];
    }
    created() {
        // Applies style formatting before inserting the column
        this.spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A2:G2');
        // inserting a empty column at 0th index
        this.spreadsheet.insertColumn();
        // inserting 1 column at the 5th index with column model
        this.spreadsheet.insertColumn([{ index: 5, width: 90 }]);
        let rowIndex = 1;
        // Updating the 5th column data
        this.cellsModel.forEach((cell) => {
            this.spreadsheet.updateCell(cell, getCellAddress(rowIndex, 5));
            rowIndex++;
        });
        // Applies style formatting after the columns are inserted
        this.spreadsheet.cellFormat({ textAlign: 'center' }, 'B3:B12');
        this.spreadsheet.cellFormat({ textAlign: 'center' }, 'D3:D12');
        this.spreadsheet.cellFormat({ textAlign: 'center' }, 'F3:H12');
    }
    ;
    render() {
        return (<div> <SpreadsheetComponent ref={(ssObj) => { this.spreadsheet = ssObj; }} created={this.created.bind(this)} 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>);
    }
}
ReactDOM.render(<App />, document.getElementById('root'));

/**
 * 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
    }
];

<!DOCTYPE html>
<html lang="en">

<head>
    <title>Syncfusion React Spreadsheet</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Essential JS 2 for React Components" />
    <meta name="author" content="Syncfusion" />
    <link href="https://cdn.syncfusion.com/ej2/20.1.57/material.css" rel="stylesheet" type="text/css"/>
    <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <script src="systemjs.config.js"></script>
    <style>
        #loader {
            color: #008cff;
            height: 40px;
            left: 45%;
            position: absolute;
            top: 45%;
            width: 30%;
        }
        body {
            overflow: hidden;
        }
        .customClass.e-cell {
                        background-color: red;
                }
    </style>
</head>

<body>
    <div id='root'>
        <div id='loader'>Loading....</div>
    </div>
</body>

</html>

import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective, RowsDirective, RowDirective, CellDirective, CellsDirective, protectSheet, ProtectSettings, Row, RowModel, CellModel, getCellAddress } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective} from '@syncfusion/ej2-react-spreadsheet';
import { data } from './datasource';

export default class App extends React.Component<{}, {}> {
   public spreadsheet: SpreadsheetComponent;
   public 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' }];
   public created(): void{
     // Applies style formatting before inserting the column
     this.spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A2:G2');
     // inserting a empty column at 0th index
     this.spreadsheet.insertColumn();
     // inserting 1 column at the 5th index with column model
     this.spreadsheet.insertColumn([{ index: 5, width: 90 }]);
     let rowIndex: number = 1;
     // Updating the 5th column data
     this.cellsModel.forEach((cell: CellModel): void => {
         this.spreadsheet.updateCell(cell, getCellAddress(rowIndex, 5)); rowIndex++;
     });
     // Applies style formatting after the columns are inserted
     this.spreadsheet.cellFormat({ textAlign: 'center' }, 'B3:B12');
     this.spreadsheet.cellFormat({ textAlign: 'center' }, 'D3:D12');
     this.spreadsheet.cellFormat({ textAlign: 'center' }, 'F3:H12');
};
  
 render() {
    return  ( <div> <SpreadsheetComponent
                    ref={(ssObj) => { this.spreadsheet = ssObj }} created={this.created.bind(this)} 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>);
}
}
ReactDOM.render(<App />, document.getElementById('root'));

/**
 * 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
    }];

Delete

Delete support provides an option for deleting the rows and columns in the spreadsheet. Use allowDelete property to enable or disable the delete option in Spreadsheet.

The rows and columns can be deleted dynamically in the following ways,

Using delete method, you can delete the loaded rows and columns.
Using context menu, you can delete the selected rows and columns.

The following code example shows the delete operation of rows and columns in the spreadsheet.

Source

Preview

app.jsx

datasource.jsx

index.html

app.tsx

datasource.tsx

import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { data } from './datasource';
export default class App extends React.Component {
    created() {
        this.spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:H1');
        // deleting the rows from 8th to 10th index. To delete row, the third argument of enum type is passed as 'Row', the last argument specifies the sheet name or index in which the delete operation will perform. By default,active sheet will be considered. It is applicable only for model type Row and Column.
        this.spreadsheet.delete(8, 10, 'Row', 0); // startIndex, endIndex, Row, sheet index
        // deleting the 2nd and 5th indexed columns
        this.spreadsheet.delete(2, 2, 'Column', 'Sheet2');
        this.spreadsheet.delete(5, 5, 'Column');
        this.spreadsheet.delete(0, 0, "Sheet"); // delete the first sheet. sheet index starts from 0
        // Applies style formatting after deleted the rows and columns
        this.spreadsheet.cellFormat({ textAlign: 'center' }, 'A2:A8');
        this.spreadsheet.cellFormat({ textAlign: 'center' }, 'D2:G8');
    }
    ;
    render() {
        return (<div> <SpreadsheetComponent ref={(ssObj) => { this.spreadsheet = ssObj; }} created={this.created.bind(this)} showRibbon={false} showFormulaBar={false} allowDelete={true}>
                    <SheetsDirective>
                        <SheetDirective name={"Sheet1"}>
                            <RangesDirective>
                                <RangeDirective dataSource={data}></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>
                        <SheetDirective name={"Sheet2"}>
                            <RangesDirective>
                                <RangeDirective dataSource={data}></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>);
    }
}
ReactDOM.render(<App />, document.getElementById('root'));

/**
 * Delete row column 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': true
    },
    {
        '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': true
    },
    {
        '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': false
    },
    {
        'Product Id': 8, 'Product Name': 'Queso Cabrales', 'Supplier Id': 5, 'Quantity Per Unit': '1 kg pkg.', 'Unit Price': 21.00, 'Units In Stock': 22, 'Discontinued': false
    },
    {
        'Product Id': 9, 'Product Name': 'Northwoods Cranberry Sauce', 'Supplier Id': 3, 'Quantity Per Unit': '12 - 12 oz jars', 'Unit Price': 40.00, 'Units In Stock': 6, 'Discontinued': false
    },
    {
        'Product Id': 10, 'Product Name': 'Mishi Kobe Niku', 'Supplier Id': 4, 'Quantity Per Unit': '18 - 500 g pkgs.', 'Unit Price': 97.00, 'Units In Stock': 29, 'Discontinued': true
    }
];

<!DOCTYPE html>
<html lang="en">

<head>
    <title>Syncfusion React Spreadsheet</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Essential JS 2 for React Components" />
    <meta name="author" content="Syncfusion" />
    <link href="https://cdn.syncfusion.com/ej2/20.1.57/material.css" rel="stylesheet" type="text/css"/>
    <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <script src="systemjs.config.js"></script>
    <style>
        #loader {
            color: #008cff;
            height: 40px;
            left: 45%;
            position: absolute;
            top: 45%;
            width: 30%;
        }
        body {
            overflow: hidden;
        }
        .customClass.e-cell {
                        background-color: red;
                }
    </style>
</head>

<body>
    <div id='root'>
        <div id='loader'>Loading....</div>
    </div>
</body>

</html>

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

export default class App extends React.Component<{}, {}> {
   public spreadsheet: SpreadsheetComponent;
   public created(): void{
    this.spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:H1');
    // deleting the rows from 8th to 10th index. To delete row, the third argument of enum type is passed as 'Row', the last argument specifies the sheet name or index in which the delete operation will perform. By default,active sheet will be considered. It is applicable only for model type Row and Column.
    this.spreadsheet.delete(8, 10, 'Row', 0); // startIndex, endIndex, Row, sheet index
    // deleting the 2nd and 5th indexed columns
    this.spreadsheet.delete(2, 2, 'Column', 'Sheet2');
    this.spreadsheet.delete(5, 5, 'Column');
    this.spreadsheet.delete(0, 0, "Sheet"); // delete the first sheet. sheet index starts from 0
    // Applies style formatting after deleted the rows and columns
    this.spreadsheet.cellFormat({ textAlign: 'center' }, 'A2:A8');
    this.spreadsheet.cellFormat({ textAlign: 'center' }, 'D2:G8');
};
  
 render() {
    return  ( <div> <SpreadsheetComponent
                    ref={(ssObj) => { this.spreadsheet = ssObj }} created={this.created.bind(this)} showRibbon={false} showFormulaBar={false} allowDelete={true} >
                    <SheetsDirective>
                        <SheetDirective name={"Sheet1"}>
                            <RangesDirective>
                                <RangeDirective dataSource={data}></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>
                        <SheetDirective name={"Sheet2"}>
                            <RangesDirective>
                                <RangeDirective dataSource={data}></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>);
}
}
ReactDOM.render(<App />, document.getElementById('root'));

/**
 * Delete row column 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': true
    },
    {
        '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': true
    },
    {
        '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': false
    },
    {
        'Product Id': 8, 'Product Name': 'Queso Cabrales', 'Supplier Id': 5, 'Quantity Per Unit': '1 kg pkg.', 'Unit Price': 21.00, 'Units In Stock': 22, 'Discontinued': false
    },
    {
        'Product Id': 9, 'Product Name': 'Northwoods Cranberry Sauce', 'Supplier Id': 3, 'Quantity Per Unit': '12 - 12 oz jars', 'Unit Price': 40.00, 'Units In Stock': 6, 'Discontinued': false
    },
    {
        'Product Id': 10, 'Product Name': 'Mishi Kobe Niku', 'Supplier Id': 4, 'Quantity Per Unit': '18 - 500 g pkgs.', 'Unit Price': 97.00, 'Units In Stock': 29, 'Discontinued': true
    }];

Limitations

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/delete row/column between the filter applied cells.

Hide and show

You can show or hide the rows and columns in the spreadsheet through property binding, method, and context menu.

Row

The rows can be hidden or shown through the following ways,

Using hidden property in row, you can hide/show the rows at initial load.
Using hideRow method, you can hide the rows by specifying the start and end row index, set the last argument hide as false to unhide the hidden rows.
Right-click on the row header and select the desired option from context menu

Column

The columns can be hidden or shown through following ways,

Using hidden property in columns, you can hide/show the columns at initial load.
Using hideColumn method, you can hide the columns by specifying the start and end column index, set the last argument hide as false to unhide the hidden columns.
Right-click on the column header and select the desired option from context menu

The following code example shows the hide/show rows and columns operation in the spreadsheet.

Source

Preview

app.jsx

datasource.jsx

index.html

app.tsx

datasource.tsx

import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective, RowsDirective, RowDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { data } from './datasource';
export default class App extends React.Component {
    created() {
        this.spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:H1');
        // Unhide the 2nd index hidden column
        this.spreadsheet.hideColumn(1, 1, false);
        // Unhide the 3rd index hidden row
        this.spreadsheet.hideRow(3, 3, false);
        // Hiding the 6th index column
        this.spreadsheet.hideColumn(6);
        // Hiding the 8th and 9th index row
        this.spreadsheet.hideRow(8, 9);
        this.spreadsheet.cellFormat({ textAlign: 'center' }, 'D2:H11');
    }
    ;
    render() {
        return (<div> <SpreadsheetComponent ref={(ssObj) => { this.spreadsheet = ssObj; }} created={this.created.bind(this)} showSheetTabs={false} showRibbon={false} showFormulaBar={false}>
                    <SheetsDirective>
                        <SheetDirective>
                            <RangesDirective>
                                <RangeDirective dataSource={data}></RangeDirective>
                            </RangesDirective>
                            <RowsDirective>
                                <RowDirective index={2} hidden={true}></RowDirective>
                                <RowDirective hidden={true}></RowDirective>
                            </RowsDirective>
                            <ColumnsDirective>
                                <ColumnDirective width={150}></ColumnDirective>
                                <ColumnDirective width={100} hidden={true}></ColumnDirective>
                                <ColumnDirective width={100} hidden={true}></ColumnDirective>
                                <ColumnDirective width={80}></ColumnDirective>
                                <ColumnDirective width={80}></ColumnDirective>
                                <ColumnDirective width={80}></ColumnDirective>
                                <ColumnDirective width={80}></ColumnDirective>
                                <ColumnDirective width={80}></ColumnDirective>
                            </ColumnsDirective>
                        </SheetDirective>
                    </SheetsDirective>
                </SpreadsheetComponent> </div>);
    }
}
ReactDOM.render(<App />, document.getElementById('root'));

/**
 * Hide/show data source
 */
export let data = [
    { 'Item Name': 'Casual Shoes', 'Date': '02/14/2019', 'Time': '11:34:32 AM', 'Quantity': 10, 'Price': 20, 'Amount': '=D2*E2', 'Discount': 1, 'Profit': 10 },
    { 'Item Name': 'Sports Shoes', 'Date': '06/11/2019', 'Time': '05:56:32 AM', 'Quantity': 20, 'Price': 30, 'Amount': '=D3*E3', 'Discount': 5, 'Profit': 50 },
    { 'Item Name': 'Formal Shoes', 'Date': '07/27/2019', 'Time': '03:32:44 AM', 'Quantity': 20, 'Price': 15, 'Amount': '=D4*E4', 'Discount': 7, 'Profit': 27 },
    { 'Item Name': 'Sandals & Floaters', 'Date': '11/21/2019', 'Time': '06:23:54 AM', 'Quantity': 15, 'Price': 20, 'Amount': '=D5*E5', 'Discount': 11, 'Profit': 67 },
    { 'Item Name': 'Flip- Flops & Slippers', 'Date': '06/23/2019', 'Time': '12:43:59 AM', 'Quantity': 30, 'Price': 10, 'Amount': '=D6*E6', 'Discount': 10, 'Profit': 70 },
    { 'Item Name': 'Sneakers', 'Date': '07/22/2019', 'Time': '10:55:53 AM', 'Quantity': 40, 'Price': 20, 'Amount': '=D7*E7', 'Discount': 13, 'Profit': 66 },
    { 'Item Name': 'Running Shoes', 'Date': '02/04/2019', 'Time': '03:44:34 AM', 'Quantity': 20, 'Price': 10, 'Amount': '=D8*E8', 'Discount': 3, 'Profit': 14 },
    { 'Item Name': 'Loafers', 'Date': '11/30/2019', 'Time': '03:12:52 AM', 'Quantity': 31, 'Price': 10, 'Amount': '=D9*E9', 'Discount': 6, 'Profit': 29 },
    { 'Item Name': 'Cricket Shoes', 'Date': '07/09/2019', 'Time': '11:32:14 AM', 'Quantity': 41, 'Price': 30, 'Amount': '=D10*E10', 'Discount': 12, 'Profit': 166 },
    { 'Item Name': 'T-Shirts', 'Date': '10/31/2019', 'Time': '12:01:44 AM', 'Quantity': 50, 'Price': 10, 'Amount': '=D11*E11', 'Discount': 9, 'Profit': 55 }
];

<!DOCTYPE html>
<html lang="en">

<head>
    <title>Syncfusion React Spreadsheet</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Essential JS 2 for React Components" />
    <meta name="author" content="Syncfusion" />
    <link href="https://cdn.syncfusion.com/ej2/20.1.57/material.css" rel="stylesheet" type="text/css"/>
    <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <script src="systemjs.config.js"></script>
    <style>
        #loader {
            color: #008cff;
            height: 40px;
            left: 45%;
            position: absolute;
            top: 45%;
            width: 30%;
        }
        body {
            overflow: hidden;
        }
        .customClass.e-cell {
                        background-color: red;
                }
    </style>
</head>

<body>
    <div id='root'>
        <div id='loader'>Loading....</div>
    </div>
</body>

</html>

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

export default class App extends React.Component<{}, {}> {
   public spreadsheet: SpreadsheetComponent;
   public created(): void{
    this.spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:H1');
    // Unhide the 2nd index hidden column
    this.spreadsheet.hideColumn(1, 1, false);
    // Unhide the 3rd index hidden row
    this.spreadsheet.hideRow(3, 3, false);
    // Hiding the 6th index column
    this.spreadsheet.hideColumn(6);
    // Hiding the 8th and 9th index row
    this.spreadsheet.hideRow(8, 9);
    this.spreadsheet.cellFormat({ textAlign: 'center' }, 'D2:H11');
};
  
 render() {
    return  ( <div> <SpreadsheetComponent
                    ref={(ssObj) => { this.spreadsheet = ssObj }} created={this.created.bind(this)} showSheetTabs={false} showRibbon={false} showFormulaBar={false} >
                    <SheetsDirective>
                        <SheetDirective>
                            <RangesDirective>
                                <RangeDirective dataSource={data}></RangeDirective>
                            </RangesDirective>
                            <RowsDirective>
                                <RowDirective index={2} hidden={true}></RowDirective>
                                <RowDirective hidden={true}></RowDirective>
                            </RowsDirective>
                            <ColumnsDirective>
                                <ColumnDirective width={150}></ColumnDirective>
                                <ColumnDirective width={100} hidden={true}></ColumnDirective>
                                <ColumnDirective width={100} hidden={true}></ColumnDirective>
                                <ColumnDirective width={80}></ColumnDirective>
                                <ColumnDirective width={80}></ColumnDirective>
                                <ColumnDirective width={80}></ColumnDirective>
                                <ColumnDirective width={80}></ColumnDirective>
                                <ColumnDirective width={80}></ColumnDirective>
                            </ColumnsDirective>
                        </SheetDirective>
                    </SheetsDirective>
                </SpreadsheetComponent> </div>);
}
}
ReactDOM.render(<App />, document.getElementById('root'));

/**
 * Hide/show data source
 */
export let data: Object[] = [
    { 'Item Name': 'Casual Shoes', 'Date': '02/14/2019', 'Time': '11:34:32 AM', 'Quantity': 10, 'Price': 20, 'Amount': '=D2*E2', 'Discount': 1, 'Profit': 10 },
    { 'Item Name': 'Sports Shoes', 'Date': '06/11/2019', 'Time': '05:56:32 AM', 'Quantity': 20, 'Price': 30, 'Amount': '=D3*E3', 'Discount': 5, 'Profit': 50 },
    { 'Item Name': 'Formal Shoes', 'Date': '07/27/2019', 'Time': '03:32:44 AM', 'Quantity': 20, 'Price': 15, 'Amount': '=D4*E4', 'Discount': 7, 'Profit': 27 },
    { 'Item Name': 'Sandals & Floaters', 'Date': '11/21/2019', 'Time': '06:23:54 AM', 'Quantity': 15, 'Price': 20, 'Amount': '=D5*E5', 'Discount': 11, 'Profit': 67 },
    { 'Item Name': 'Flip- Flops & Slippers', 'Date': '06/23/2019', 'Time': '12:43:59 AM', 'Quantity': 30, 'Price': 10, 'Amount': '=D6*E6', 'Discount': 10, 'Profit': 70 },
    { 'Item Name': 'Sneakers', 'Date': '07/22/2019', 'Time': '10:55:53 AM', 'Quantity': 40, 'Price': 20, 'Amount': '=D7*E7', 'Discount': 13, 'Profit': 66 },
    { 'Item Name': 'Running Shoes', 'Date': '02/04/2019', 'Time': '03:44:34 AM', 'Quantity': 20, 'Price': 10, 'Amount': '=D8*E8', 'Discount': 3, 'Profit': 14 },
    { 'Item Name': 'Loafers', 'Date': '11/30/2019', 'Time': '03:12:52 AM', 'Quantity': 31, 'Price': 10, 'Amount': '=D9*E9', 'Discount': 6, 'Profit': 29 },
    { 'Item Name': 'Cricket Shoes', 'Date': '07/09/2019', 'Time': '11:32:14 AM', 'Quantity': 41, 'Price': 30, 'Amount': '=D10*E10', 'Discount': 12, 'Profit': 166 },
    { 'Item Name': 'T-Shirts', 'Date': '10/31/2019', 'Time': '12:01:44 AM', 'Quantity': 50, 'Price': 10, 'Amount': '=D11*E11', 'Discount': 9, 'Profit': 55 }
];

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.

Rows and Columns in React Spreadsheet component

Insert

Row

Column

Delete

Limitations

Hide and show

Row

Column

Note

See Also