Work with sheets

Starting from v4.1, you can work with multiple sheets in the spreadsheet.

This article describes how to add a new sheet, remove an unnecessary sheet, get all sheets, and get the currently active sheet using API methods. It also explains how to load multiple sheets into the spreadsheet at once.

note

To learn how to interact with multiple sheets via the user interface, check our User Guide.

Starting from v6.0, the Sheet Manager module handles sheet management through the spreadsheet.sheets property. The dedicated Sheet Manager API replaces the sheet-related methods that were previously available directly on the Spreadsheet instance.

Loading multiple sheets

To load several sheets into the spreadsheet, prepare data with the desired number of sheets and their configuration, and pass it to the parse() method as a parameter. The data should be an object. Check the list of attributes the object can include.

note

If the multiSheets configuration option is set to false, only one sheet is created.

Adding a new sheet

To add a new sheet into the spreadsheet, use the sheets.add() method and pass the name of the new sheet as a parameter:

const spreadsheet = new dhx.Spreadsheet("spreadsheet_container", {
    multiSheets: true
});
spreadsheet.parse(data);

// Add a sheet with a custom name
const newSheetId = spreadsheet.sheets.add("Q4 Report");
console.log(newSheetId); // e.g. "sheet_2"

// Add a sheet with an auto-generated name
const anotherId = spreadsheet.sheets.add();

The method returns the id of the created sheet.

Removing a sheet

You can remove a sheet from the spreadsheet by its id via the sheets.remove() method:

const spreadsheet = new dhx.Spreadsheet("spreadsheet_container", {
    multiSheets: true
});
spreadsheet.parse(data);

// Remove a sheet by its id
spreadsheet.sheets.remove("sheet_2");

Note that a sheet is not removed if the spreadsheet has fewer than 2 sheets.

Setting active sheet

To change the active sheet dynamically after initialization of the spreadsheet, use the sheets.setActive() method. It takes the id of a sheet as a parameter:

const spreadsheet = new dhx.Spreadsheet("spreadsheet_container", {
    multiSheets: true
});
spreadsheet.parse(data);

// Switch to the second sheet
spreadsheet.sheets.setActive("sheet_2");

// Verify the switch
const active = spreadsheet.sheets.getActive();
console.log(active.name); // "Sheet 2"

Related sample: Spreadsheet. Set active sheet

Getting active sheet

You can get the currently active sheet by applying the sheets.getActive() method:

const spreadsheet = new dhx.Spreadsheet("spreadsheet_container", {
    multiSheets: true
});
spreadsheet.parse(data);

const active = spreadsheet.sheets.getActive();
console.log(active.name); // "Sheet 1"
console.log(active.id);   // "sheet_1"

The method returns an object with the name and id attributes of the currently active sheet.

Getting all sheets

The sheets.getAll() method returns all sheets of the spreadsheet as an array of sheet objects:

const spreadsheet = new dhx.Spreadsheet("spreadsheet_container", {
    multiSheets: true
});
spreadsheet.parse(data);

const allSheets = spreadsheet.sheets.getAll();
console.log(allSheets);
// [
//   { id: "sheet_1", name: "Sheet 1" },
//   { id: "sheet_2", name: "Sheet 2" }
// ]

Getting a sheet by id

To retrieve a single sheet object by its id, use the sheets.get() method:

const spreadsheet = new dhx.Spreadsheet("spreadsheet_container", {
    multiSheets: true
});
spreadsheet.parse(data);

const sheet = spreadsheet.sheets.get("sheet_1");
console.log(sheet.name); // "Sheet 1"

Clearing sheets

You can clear the data of the specified sheet by its id with the sheets.clear() method:

const spreadsheet = new dhx.Spreadsheet("spreadsheet_container", {
    multiSheets: true
});
spreadsheet.parse(data);

// Clear a specific sheet by id
spreadsheet.sheets.clear("sheet_1");

// Clear the currently active sheet
spreadsheet.sheets.clear();

Related sample: Spreadsheet. Clear

If you need to clear the whole spreadsheet at once, use the clear() method.