Working with Sheets

Hiding/Showing Headers and Gridlines

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

Hiding/Showing Sheets

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.

Adding Multiple Sheets

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
});

Adding a new Sheet

You can add a new sheet by using the addSheet method. You should pass the sheet's content as a parameter:

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

Copying Sheet

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);

Getting Sheet Data

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.
• setRangeValue to set a value for the range.

// 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 the range
var value = $$("ssheet").getRangeValue("A1:B2","Sheet2"); //-> [3456, 6543]
 
// setting values for the range
$$("ssheet").setRangeValue("A1:B2", [1,2,3,4], "Sheet2");

Back to top