Insert Worksheet in React Spreadsheet

27 May 202624 minutes to read

In the React Spreadsheet component, you can insert new worksheets to organize and manage your data more effectively. Sheets can be added either through the user interface or programmatically, allowing you to expand your workbook as needed. You can choose to add a sheet next to the current sheet, at a specific position, or make the newly inserted sheet active.

Ways to Insert a Worksheet

You can dynamically add or insert a sheet by one of the following ways,

• Click the Add Sheet button in the sheet tab. This will add a new empty sheet next to current active sheet.
• Right-click on the sheet tab, and then select Insert option from the context menu to insert a new empty sheet before the current active sheet.
• Using insertSheet method, you can insert one or more sheets at your desired index.

The following code example shows the insert sheet operation in spreadsheet.

app.jsx

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);
    React.useEffect(() => {
        const insSheetModel = [{
            index: 1,
            name: 'Inserted Sheet',
            ranges: [{ dataSource: data }],
            columns: [
                { width: 150 },
                { width: 110 },
                { width: 110 },
                { width: 85 },
                { width: 85 },
                { width: 85 },
                { width: 85 },
                { width: 85 },
            ]
        }];
        let spreadsheet = spreadsheetRef.current;
        if (spreadsheet) {
            // Applies style formatting to the active sheet before inserting a new sheet
            spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:H1');
            spreadsheet.cellFormat({ textAlign: 'center' }, 'D2:H11');
            // inserting a new sheet with data at 1st index
            // You can also insert empty sheets by specifying the start and end sheet index instead of sheet model
            spreadsheet.insertSheet(insSheetModel);
            // Applies style formatting for the inserted sheet
            spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'Inserted Sheet!A1:H1');
            spreadsheet.cellFormat({ textAlign: 'center' }, 'Inserted Sheet!D2:H11');
        }
    }, []);

    return (
        <SpreadsheetComponent showRibbon={false} ref={spreadsheetRef} showFormulaBar={true}>
            <SheetsDirective>
                <SheetDirective name="Price Details">
                    <RangesDirective>
                        <RangeDirective dataSource={data}></RangeDirective>
                    </RangesDirective>
                    <ColumnsDirective>
                        <ColumnDirective width={150}></ColumnDirective>
                        <ColumnDirective width={110}></ColumnDirective>
                        <ColumnDirective width={110}></ColumnDirective>
                        <ColumnDirective width={85}></ColumnDirective>
                        <ColumnDirective width={85}></ColumnDirective>
                        <ColumnDirective width={85}></ColumnDirective>
                        <ColumnDirective width={85}></ColumnDirective>
                        <ColumnDirective width={85}></ColumnDirective>
                    </ColumnsDirective>
                </SheetDirective>
            </SheetsDirective>
        </SpreadsheetComponent>
    );
};
export default App;

const root = createRoot(document.getElementById('root'));
root.render(<App />);

app.tsx

import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective, SheetModel } 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(() => {
        const insSheetModel: SheetModel[] = [{
            index: 1,
            name: 'Inserted Sheet',
            ranges: [{ dataSource: data }],
            columns: [{ width: 150 }, { width: 110 }, { width: 110 }, { width: 85 }, { width: 85 }, { width: 85 }, { width: 85 }, { width: 85 }]
        }];
        let spreadsheet = spreadsheetRef.current;
        if (spreadsheet) {
            // Applies style formatting to the active sheet before inserting a new sheet
            spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:H1');
            spreadsheet.cellFormat({ textAlign: 'center' }, 'D2:H11');
            // inserting a new sheet with data at 1st index
            // You can also insert empty sheets by specifying the start and end sheet index instead of sheet model
            spreadsheet.insertSheet(insSheetModel);
            // Applies style formatting for the inserted sheet
            spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'Inserted Sheet!A1:H1');
            spreadsheet.cellFormat({ textAlign: 'center' }, 'Inserted Sheet!D2:H11');
        }
    }, []);

    return (
        <SpreadsheetComponent showRibbon={false} ref={spreadsheetRef} showformulaBar={true}>
            <SheetsDirective>
                <SheetDirective name="Price Details">
                    <RangesDirective>
                        <RangeDirective dataSource={data}></RangeDirective>
                    </RangesDirective>
                    <ColumnsDirective>
                        <ColumnDirective width={150}></ColumnDirective>
                        <ColumnDirective width={110}></ColumnDirective>
                        <ColumnDirective width={110}></ColumnDirective>
                        <ColumnDirective width={85}></ColumnDirective>
                        <ColumnDirective width={85}></ColumnDirective>
                        <ColumnDirective width={85}></ColumnDirective>
                        <ColumnDirective width={85}></ColumnDirective>
                        <ColumnDirective width={85}></ColumnDirective>
                    </ColumnsDirective>
                </SheetDirective>
            </SheetsDirective>
        </SpreadsheetComponent>
    );
};
export default App;

const root = createRoot(document.getElementById('root')!);
root.render(<App />);

datasource.jsx

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

datasource.tsx

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

Insert a sheet programmatically and make it active sheet

A sheet is a collection of cells organized in the form of rows and columns that allows you to store, format, and manipulate the data. Using insertSheet method, you can insert one or more sheets at the desired index. Then, you can make the inserted sheet as active sheet by focusing the start cell of that sheet using the goTo method.

The following code example shows how to insert a sheet programmatically and make it the active sheet.

app.jsx

import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent } from '@syncfusion/ej2-react-spreadsheet';
import { SheetsDirective, SheetDirective, RangesDirective, RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { data, employeeData } from './datasource';
function App() {
  const spreadsheetRef = React.useRef(null);
  const btnClick = () => {
    let spreadsheet = spreadsheetRef.current;
    if (spreadsheet) {
      spreadsheet.insertSheet(
        [
          {
            index: 1,
            name: 'new_sheet',
            ranges: [
              {
                dataSource: employeeData,
                startCell: 'A1'
              },
            ]
          },
        ]
      );
      // Use the timeout function to wait until the sheet is inserted.
      setTimeout(() => {
        // Method for switching to a new sheet.
        spreadsheet.goTo('new_sheet!A1');
      })
    }
  };
  return (
    <div>
      <button className='e-btn custom-btn' onClick={btnClick}>Insert Sheet</button>
      <SpreadsheetComponent ref={spreadsheetRef}>
        <SheetsDirective>
          <SheetDirective name="Car Sales Report">
            <RangesDirective>
              <RangeDirective dataSource={data}></RangeDirective>
            </RangesDirective>
            <ColumnsDirective>
              <ColumnDirective width={180}></ColumnDirective>
              <ColumnDirective width={130}></ColumnDirective>
              <ColumnDirective width={130}></ColumnDirective>
              <ColumnDirective width={180}></ColumnDirective>
              <ColumnDirective width={130}></ColumnDirective>
              <ColumnDirective width={120}></ColumnDirective>
            </ColumnsDirective>
          </SheetDirective>
        </SheetsDirective>
      </SpreadsheetComponent>
    </div>
  );
}

export default App;

const root = createRoot(document.getElementById('root'));
root.render(<App />);

app.tsx

import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent } from '@syncfusion/ej2-react-spreadsheet';
import { SheetsDirective, SheetDirective, RangesDirective, RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { data, employeeData } from './datasource';
function App() {
  const spreadsheetRef = React.useRef<SpreadsheetComponent>(null);
  const btnClick = (): void => {
    let spreadsheet: SpreadsheetComponent = spreadsheetRef.current;
    if (spreadsheet) {
      spreadsheet.insertSheet(
        [
          {
            index: 1,
            name: 'new_sheet',
            ranges: [
              {
                dataSource: employeeData,
                startCell: 'A1'
              },
            ]
          },
        ]
      );
      // Use the timeout function to wait until the sheet is inserted.
      setTimeout(() => {
        // Method for switching to a new sheet.
        spreadsheet.goTo('new_sheet!A1');
      })
    }
  };
  return (
    <div>
      <button className='e-btn custom-btn' onClick={btnClick}>Insert Sheet</button>
      <SpreadsheetComponent ref={spreadsheetRef}>
        <SheetsDirective>
          <SheetDirective name="Car Sales Report">
            <RangesDirective>
              <RangeDirective dataSource={data}></RangeDirective>
            </RangesDirective>
            <ColumnsDirective>
              <ColumnDirective width={180}></ColumnDirective>
              <ColumnDirective width={130}></ColumnDirective>
              <ColumnDirective width={130}></ColumnDirective>
              <ColumnDirective width={180}></ColumnDirective>
              <ColumnDirective width={130}></ColumnDirective>
              <ColumnDirective width={120}></ColumnDirective>
            </ColumnsDirective>
          </SheetDirective>
        </SheetsDirective>
      </SpreadsheetComponent>
    </div>
  );
}

export default App;

const root = createRoot(document.getElementById('root')!);
root.render(<App />);