Worksheet in EJ2 JavaScript Spreadsheet control

23 Jul 202624 minutes to read

A worksheet is a collection of cells organized in the form of rows and columns that allows you to store, format, and manipulate the data.

Before working with worksheet APIs, you must have a Spreadsheet control initialized in your application. For setup details, refer to the getting started documentation.

Add sheet

You can dynamically add or insert a sheet in one of the following ways:

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

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

ej.base.enableRipple(true);

var sheets = [{
    name: 'Price Details',
    ranges: [{ dataSource: data }],
    columns: [{ width: 150 }, { width: 110 }, { width: 110 }, { width: 85 }, { width: 85 }, { width: 85 }, { width: 85 },
        { width: 85 }]
}];

var spreadsheet = new ej.spreadsheet.Spreadsheet({
    sheets: sheets,
    created: function () {
        // Applies style formatting to active sheet before inserting 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([{
            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 }]
        }]);
        // 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');
    },
    // Removed the unwanted support for this samples
    showRibbon: false, showFormulaBar: false
});

spreadsheet.appendTo('#spreadsheet');
<!DOCTYPE html><html lang="en"><head>
        <title>EJ2 SpreadSheet</title>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <meta name="description" content="Typescript UI Controls">
        <meta name="author" content="Syncfusion">
        <link rel="shortcut icon" href="resources/favicon.ico">
        <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-base/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-inputs/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-buttons/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-splitbuttons/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-lists/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-navigations/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-popups/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-dropdowns/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-grids/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-spreadsheet/styles/tailwind3.css" rel="stylesheet">
        <link href="styles.css" rel="stylesheet">
        
        <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
        <script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/33.1.44/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>

<body>
        <!--Element which is going to render-->
        
        <div id="container">
                <div id="spreadsheet"></div>
        </div>


<script>
var ele = document.getElementById('container');
if(ele) {
  ele.style.visibility = "visible";
}   
      </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

Insert a sheet programmatically and make it the active sheet

Using the insertSheet method, you can insert one or more sheets at the desired index. You can then make the inserted sheet the active sheet by focusing on 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.

// Initialize the Spreadsheet component.

var columns = [
    { width: 180 }, { width: 130 }, { width: 130 },
    { width: 180 }, { width: 130 }, { width: 120 }
];

var sheet = [{
    name: 'Car Sales Report',
    ranges: [{ dataSource: data }],
    columns: columns
}]

var spreadsheet = new ej.spreadsheet.Spreadsheet({
    sheets: sheet
});

// Render initialized Spreadsheet.
spreadsheet.appendTo('#spreadsheet');

document.getElementById("insertSheet").onclick = function () {
    spreadsheet.insertSheet(
        [
            {
                index: 1,
                name: 'new_sheet',
                ranges: [
                    {
                        dataSource: employeeData,
                        startCell: 'A1'
                    },
                ],
                columns: columns,
            },
        ]
    );
    // Use the timeout function to wait until the sheet is inserted.
    setTimeout(function () {
        // Method for switching to a new sheet.
        spreadsheet.goTo('new_sheet!A1');
    })
};
<!DOCTYPE html>
<html lang="en">

<head>
  <title>EJ2 SpreadSheet</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <meta name="description" content="Typescript UI Controls">
  <meta name="author" content="Syncfusion">
  <link rel="shortcut icon" href="resources/favicon.ico">
  <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-base/styles/tailwind3.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-inputs/styles/tailwind3.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-buttons/styles/tailwind3.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-splitbuttons/styles/tailwind3.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-lists/styles/tailwind3.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-navigations/styles/tailwind3.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-popups/styles/tailwind3.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-dropdowns/styles/tailwind3.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-grids/styles/tailwind3.css" rel="stylesheet">
  <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-spreadsheet/styles/tailwind3.css" rel="stylesheet">
  <link href="styles.css" rel="stylesheet">

  <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>

  <script src="https://cdn.syncfusion.com/ej2/33.1.44/dist/ej2.min.js" type="text/javascript"></script>
  <script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>

<body>
  <!--Element which is going to render-->

  <div id="container">
    <button class="e-btn custom-btn" id="insertSheet">Insert Sheet</button>
    <div id="spreadsheet"></div>
  </div>


  <script>
    var ele = document.getElementById('container');
    if (ele) {
      ele.style.visibility = "visible";
    }   
  </script>
  <script src="index.js" type="text/javascript"></script>
</body>

</html>

Delete sheet

The Spreadsheet has support for removing an existing worksheet. You can dynamically delete a sheet in the following ways:

  • Right-click on the sheet tab, and then select the Delete option from the context menu.
  • Use the delete method to delete sheets programmatically.

Rename sheet

You can dynamically rename an existing worksheet in the following ways:

  • Right-click on the sheet tab, and then select the Rename option from the context menu.

Headers

By default, the row and column headers are visible in worksheets. You can dynamically show or hide worksheet headers in one of the following ways:

  • Switch to the View tab, and then select the Hide Headers option to hide both the row and column headers.
  • Set the showHeaders property on a sheet in the sheets collection to true or false to show or hide the headers at initial load. By default, showHeaders is enabled in each worksheet.

To toggle headers at runtime, update the showHeaders property on the target sheet model and call spreadsheet.dataBind() to reflect the change.

Gridlines

Gridlines appear as cell borders and are used to distinguish cells on the worksheet. You can dynamically show or hide gridlines in one of the following ways:

  • Switch to the View tab, and then select the Hide Gridlines option to hide the gridlines in the worksheet.
  • Set the showGridLines property on a sheet in the sheets collection to true or false to show or hide the gridlines at initial load. By default, showGridLines is enabled in each worksheet.

To toggle gridlines at runtime, update the showGridLines property on the target sheet model and call spreadsheet.dataBind() to reflect the change.

The following code example demonstrates hiding both headers and gridlines on a single sheet at initial load.

ej.base.enableRipple(true);

var columns = [{ width: 150 }, { width: 110 }, { width: 110 }, { width: 85 }, { width: 85 }, { width: 85 }, { width: 85 },
        { width: 85 }];

var sheets = [{
    name: 'Price Details',
    ranges: [{ dataSource: data }],
    columns: columns,
    // Hiding the gridlines in `Price Details` sheet
    showGridLines: false,
    // Hiding the headers in `Price Details` sheet
    showHeaders: false
}];

var spreadsheet = new ej.spreadsheet.Spreadsheet({
    sheets: sheets,
    created: function () {
        spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:H1');
        spreadsheet.cellFormat({ textAlign: 'center' }, 'D2:H11');
        // The gridlines has be removed, so setting border to range of cells.
        spreadsheet.setBorder({ border: '1px solid #e0e0e0' }, 'A1:H11');
    },
    // Removed the unwanted support for this samples
    showFormulaBar: false, showSheetTabs: false
});

spreadsheet.appendTo('#spreadsheet');
<!DOCTYPE html><html lang="en"><head>
        <title>EJ2 SpreadSheet</title>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <meta name="description" content="Typescript UI Controls">
        <meta name="author" content="Syncfusion">
        <link rel="shortcut icon" href="resources/favicon.ico">
        <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-base/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-inputs/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-buttons/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-splitbuttons/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-lists/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-navigations/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-popups/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-dropdowns/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-grids/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-spreadsheet/styles/tailwind3.css" rel="stylesheet">
        <link href="styles.css" rel="stylesheet">
        
        <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
        <script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/33.1.44/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>

<body>
        <!--Element which is going to render-->
        
        <div id="container">
                <div id="spreadsheet"></div>
        </div>


<script>
var ele = document.getElementById('container');
if(ele) {
  ele.style.visibility = "visible";
}   
      </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

Sheet visibility

Hiding a worksheet can help prevent unauthorized or accidental changes to your file.

There are three visibility states, similar to Microsoft Excel:

State Description
Visible You can see the worksheet once the component is loaded.
Hidden This worksheet is not visible, but you can unhide it by selecting the sheet from the List All Sheets dropdown menu in the sheet tab area.
VeryHidden This worksheet is not visible and cannot be unhidden. Setting the state property to Visible is the only way to view this sheet.

The following code example shows the three types of sheet visibility state.

ej.base.enableRipple(true);

var sheets =  [{
    name: 'Visible Sheet',
    ranges: [{ dataSource: data }],
    columns: [{ width: 150 }, { width: 110 }, { width: 110 }, { width: 85 }, { width: 85 }, { width: 85 }, { width: 85 },
        { width: 85 }],
    // State sets as `Visible` by default. No need to said in sample
    state: 'Visible'
},
{
    name: 'Very Hidden Sheet',
    ranges: [{ dataSource: data }],
    columns: [{ width: 150 }, { width: 110 }, { width: 110 }, { width: 85 }, { width: 85 }, { width: 85 }, { width: 85 },
        { width: 85 }],
    // Sets sheet state as `VeryHidden`. It can't be unhidden.
    state: 'VeryHidden'
},
{
    name: 'Hidden Sheet',
    ranges: [{ dataSource: data }],
    columns: [{ width: 150 }, { width: 110 }, { width: 110 }, { width: 85 }, { width: 85 }, { width: 85 }, { width: 85 },
        { width: 85 }],
    // Sets sheet state as `Hidden`. It can be unhidden dynamically.
    state: 'Hidden'
}];

var spreadsheet = new ej.spreadsheet.Spreadsheet({
    sheets: sheets,
    created: function () {
        // Applies style formatting to active visible sheet
        spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:H1');
        spreadsheet.cellFormat({ textAlign: 'center' }, 'D2:H11');
        // Applies style formatting to active hidden sheet
        spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'Hidden Sheet!A1:H1');
        spreadsheet.cellFormat({ textAlign: 'center' }, 'Hidden Sheet!D2:H11');
    },
    openUrl: 'https://document.syncfusion.com/web-services/spreadsheet-editor/api/spreadsheet/open',
    saveUrl: 'https://document.syncfusion.com/web-services/spreadsheet-editor/api/spreadsheet/save',
    // Removed the unwanted support for this samples
    showFormulaBar: false, showRibbon: false
});

spreadsheet.appendTo('#spreadsheet');
<!DOCTYPE html><html lang="en"><head>
        <title>EJ2 SpreadSheet</title>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <meta name="description" content="Typescript UI Controls">
        <meta name="author" content="Syncfusion">
        <link rel="shortcut icon" href="resources/favicon.ico">
        <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-base/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-inputs/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-buttons/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-splitbuttons/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-lists/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-navigations/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-popups/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-dropdowns/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-grids/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-spreadsheet/styles/tailwind3.css" rel="stylesheet">
        <link href="styles.css" rel="stylesheet">
        
        <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
        <script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/33.1.44/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>

<body>
        <!--Element which is going to render-->
        
        <div id="container">
                <div id="spreadsheet"></div>
        </div>


<script>
var ele = document.getElementById('container');
if(ele) {
  ele.style.visibility = "visible";
}   
      </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

See Also