Skip to main content

Work with Spreadsheet

While users interact with Spreadsheet via its intuitive interface, you can work with the component using simple API.

Undo/redo actions​

There is the undo() API method that allows reverting the latest action:

spreadsheet.undo();

To reapply a reverted action once again use the redo() method:

spreadsheet.redo();

Adding/removing rows and columns​

Columns​

To add/delete a column, use the related API methods:

Pass to the methods the id of the cell that contains the id of a column that should be added.

// adds an empty "C" column 
spreadsheet.addColumn("C1");
// removes the "C" column
spreadsheet.deleteColumn("C1");

When a new column is added, neighboring columns are moved to the right.

Rows​

To add/delete a row, use the API methods below:

Pass to the methods the id of the cell that contains the id of a row that should be added.

// adds an empty second row
spreadsheet.addRow("A2");
// removes the second row
spreadsheet.deleteRow("A2");

When a new row is added, neighboring rows are moved one cell down.

Autofit column width​

To change the column width so that it would automatically adjust to the longest content in the column, apply the fitColumn() method. The method takes one parameter - the id of the cell that contains the name of the necessary column.

// adjusts the width of the "G" column
spreadsheet.fitColumn("G2");

Filtering data​

Set filter​

You can filter data in the spreadsheet and render only the records that meet the specified criteria. For that, you need to use the setFilter() method and specify the rules of filtering for the necessary column(s) there.

For example, you can specify criteria of filtering for a separate column:

// filter data by criteria specified for column A
spreadsheet.setFilter("A2", [{condition: { factor: "tc", value: "e" }, exclude: ["Peru"]}]);

// filter data by criteria specified for column C
spreadsheet.setFilter("C1", [{}, {}, {condition: {factor: "inb", value: [5,8]}, exclude: [3.75]}]);

In this case, a filter icon will be added for each column from the range of data.

But you may also specify the filtering criteria for a range of cells as in:

// filter data by criteria specified for column C
spreadsheet.setFilter("C1:C9", [{condition: {factor: "inb", value: [5,8]}, exclude: [3.75]}]);

// filter data by criteria specified for column A and C
spreadsheet.setFilter("A1:C10", [{condition: {factor: "tc", value: "e"}}, {}, {condition: {factor: "ib", value: [5,8]}}]);

and a filter icon will be added only for columns withing the specified range.

Related sample: Spreadsheet. Filtering via API

Reset filter​

If you want to reset filtering, apply the setFilter() method without parameters or pass only the first parameter to the method:

spreadsheet.setFilter("A2");

Get filter​

To get the criteria by which data are currently filtered in a sheet, apply the getFilter() method. Pass the ID of the necessary sheet as a parameter to the method.

const filter = spreadsheet.getFilter("Income");

You don't need to pass the sheet's ID if you want to get the filter criteria applied to the currently active sheet:

const filter = spreadsheet.getFilter();

Searching for data​

You may get cells which contain specific records by passing the value which you need to search to the search() method.

spreadsheet.search("min"); // -> ['D1']

At the same time, you may pass true as a value of the second parameter. This will open the search bar and highlight the found cells in the spreadsheet:

spreadsheet.search("min", true);

By default, the spreadsheet will search the cells on the currently active sheet. To search for records on the other sheet, pass its ID as the third parameter of the method:

spreadsheet.search("min", false, "Income");

To hide the search bar, use the hideSearch() method:

spreadsheet.hideSearch();

Sorting data​

From v4.3, you can sort data in the spreadsheet via the sortCells() method. Pass to the method two parameters:

  • cell - the id(s) of a cell(s) or a range of cells by which you want the data in the spreadsheet to be sorted
  • dir - the sorting direction: 1 - ascending sort order, -1 - descending sort order
const spreadsheet = new dhx.Spreadsheet("spreadsheet", {
    // config parameters
});

spreadsheet.sortCells("B2:B11", -1);

Clearing spreadsheet​

You can clear the whole spreadsheet at once via the clear() method:

spreadsheet.clear();

Related sample: Spreadsheet. Clear

If you need to clear a specific sheet, use the clearSheet() method.