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

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 or a named range via the SpreadSheet API. Your steps are:

• first, use the getSheetData method to address a desired sheet. Pass the id of the sheet as a parameter of the method
• then call the necessary method depending on your needs:
  • getValue() to return the content of a cell. It takes the row and column ids as parameters and returns the value of a cell.
  • getRangeValue() to return a range of cells. The method takes a string with references to the first and the last cells of the range as a parameter. It returns an array of cell values.
  • getRangeValue() to return a named range of cells. It takes the name of a range as a parameter and returns an array of cell values.

// getting a cell value
$$("ssheet").getSheetData(sheet_name).getValue(1,1) => "Report"
 
// getting a range of cells
$$("ssheet").getSheetData(sheet_name).getRangeValue("C6:D7") => [140,50,48,200]
 
// getting a named range
$$("ssheet").getSheetData(sheet_name).getRangeValue("AFTERDATA") => [140,50,48,200]

Back to top