Work with sheets

Starting from v4.1, the library allows working with multiple sheets in the spreadsheet.

In this article we'll discuss the details on how to implement such operations as adding a new sheet into the spreadsheet, removing the unnecessary sheet, getting all sheets, or getting the currently active sheet via using API methods. Besides, we'll explain 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, sheet management is handled by the Sheet Manager module, accessible via 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, you should prepare data with the desired number of sheets and their configuration and pass them to the parse() method as a parameter. The data should be an object. Check the list of attributes the object can include.

note

In case the multiSheets configuration option is set to false, only one sheet will be 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 won't be removed if the number of sheets in the spreadsheet is less than 2.

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

It is possible to get the sheet that is currently active 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 allows you to get all sheets of the spreadsheet. The method returns an array with a set 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

There is the ability to clear the data of the specified sheet by its id via 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.