Working with Sheets

Hiding Headers

The headers of rows and columns of a sheet can be hidden with the help of the hideHeaders method.

You should pass the necessary state of headers as a parameter:

// hide headers
$$("ssheet").hideHeaders(true);
// show headers
$$("ssheet").hideHeaders(false);

Related sample:  Borders and Gridlines

Hiding Gridlines

You can also hide the lines of the SpreadSheet grid.

Use the hideGridlines() method for this purpose. The method takes the boolean parameter state:

// hide grid lines
$$("ssheet").hideBorders(true);
// show grid lines
$$("ssheet").hideBorders(false);

Related sample:  Borders and Gridlines

You can hide sheets or make them visible with the help of the setSheetState method. Pass the necessary state of headers as a parameter:

• "visible" - to make a sheet visible;
• "hidden" - to hide a sheet. It can be made visible on UI or by calling setSheetState() with the "visible" parameter.
• "veryHidden" - to hide a sheet and disable the opportunity to make it visible on UI. The only way to make it visible again is to call setSheetState() with the "visible" parameter.

To check the visibility state, use the getSheetState method.

You can add several sheets into a SpreadSheet. Follow these steps:

1) enable a bottom bar

A bottom bar is necessary to switch between the sheets. For this, use the bottombar property with the true value.

2) add the desired number of sheets with their configuration

The sheets config is the solution you need. As its value you need to specify an array of sheet objects. Each object has the following properties:

• name - (string) the sheet name
• content - (object) an object with the sheet's configuration/data
  • data - (array) an array with data of the sheet. Each element of the data array is set as an array that consists of three elements:
    • rowId
    • columnId
    • value

webix.ui({
    view:"spreadsheet",
    data:{
        sheets: [
            {
                name: "Tab 1",
                content:{
                    data:[
                        [1,1,"Page 1"]
                    ]
                }
            },
            {
                name: "Tab 2",
                content:{
                    data:[
                        [1,1,"Page 2"]
                    ]
                }
            },
            {
                name: "Tab 3",
                content:{
                    data:[
                        [1,1,"Page 3"]
                    ]
                }
            }
        ]
    },
    bottombar:true
});

Related sample:  Multiple sheets

Alternatively, you can load sheets as an array of objects with sheet names and content:

webix.ui({
    view:"spreadsheet",
    data: [
        {
            name: "Tab 1",
            content:{ .. }
        },
        {
            name: "Tab 2",
            content:{ .. }
        },
        {
            name: "Tab 3",
            content:{ .. }
        }
    ],
    bottombar:true
});

You can add a new sheet by using the addSheet method. If you don't provide a name, SpreadSheet will automatically generate one.

$$("ssheet").addSheet();

You can pass the sheet's content, name, and visibility as optional parameters:

$$("ssheet").addSheet({data:[[1,1,"Page 2"]]}, "My new Tab", false);

It is possible to copy the content of a sheet into a new sheet. You need to complete two steps:

1) get content of the sheet you want to copy with the help of the serialize() method:

// getting content of the active sheet
var content = $$("ssheet").serialize();

2) create a new sheet using the addSheet method and pass the received content as a parameter:

// copy to a new sheet
$$("ssheet").addSheet(content);

When a user enters an incorrect or repeated name for a sheet, a warning popup of the following kind appears:

You can localize the text in the warning by redefining the corresponding labels in the webix.i18n.spreadsheet object and use a custom popup. For example:

const labels = webix.i18n.spreadsheet.labels;
labels["sheet-rename-invalid-name"] = "Invalid name";
// a locale label for a repeated name of a sheet
labels["sheet-rename-name-exists"] = "Another sheet with same name";
labels["sheet-rename-no-blank"] = "Can't be empty";
labels["sheet-rename-name-length"] = "No more than 31 characters";
labels["sheet-rename-invalid-characters"] = "prohibited symbols: \\ / ? * [ ]";
 
webix.ui({
    view:"spreadsheet",
    toolbar:"full",
    data:spreadsheet_data
});

The resulting popup with warning on the incorrect renaming of a sheet will look like this:

You can easily access the data of any sheet cell as well as get the necessary range of cells via the SpreadSheet API. To do this, call the necessary method depending on your needs:

• getCellValue to return the value of a cell.
• setCellValue to set the value for a cell.
• getRangeValue to return values from the range (or multiple ranges).
• setRangeValue to set a value for the range (or multiple ranges).

// getting a cell value
var value = $$("ssheet").getCellValue(3, 1, true, "Sheet2"); //-> "=5"
 
// setting a cell value
$$("ssheet").setCellValue(3, 1, "=5", "Sheet2");
 
// getting values from a single range
var value = $$("ssheet").getRangeValue("A1:B2","Sheet2"); //-> [3456, 6543]
 
// getting values from multiple ranges
var value = $$("ssheet").getRangeValue("A1:B2;C1:D2","Sheet2"); 
//-> [[3456, 6543],[1234, 5678]]
 
// setting values for a single range
$$("ssheet").setRangeValue("A1:B2", [1,2,3,4], "Sheet2");
 
// setting values for multiple ranges
$$("ssheet").setRangeValue("A1:B2;C1:D2", [[1,2,3,4],[5,6,7,8]], "Sheet2");