Themes and Style

SpreadJS allows you to apply themes and styles over the pivot table to change its appearance.

Themes

SpreadJS provides 29 light, 28 medium, and 28 dark predefined themes which can be applied to a pivot table. The following image shows the light10 theme:

You can change the theme of a pivot table in one of the following ways:

• Set the theme while adding a pivot table to the spreadsheet. Refer to the following sample code.

  var myPivotTable = sheet.pivotTables.add("myPivotTable", "tableSales", 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.light10);

• Set the theme by using theme option shown below.

  myPivotTable.theme(GC.Spread.Pivot.PivotTableThemes.light10);

Style

You can apply a pivot table style to highlight any specific data. The style method can be used to apply styles to row and column fields. The following image shows the style for column fields of a pivot table.

The following code sample shows how to apply style for column fields.

function setMarkSubtotalPivotTableStyle(pivotTable) {
    // create style to mark Qtr Total's in specific color
    let style = new GC.Spread.Sheets.Style();
    style.backColor = "#03A685";
    let subtotalLabelPivotArea = {
        labelOnly: true,
        references: [{
            fieldName: "Qt",
            subtotal: true,
            items: ["Qtr1", "Qtr2", "Qtr3", "Qtr4"]
        }]
    };
    // set style to mark Qtr Total's in specific color
    pivotTable.setStyle(subtotalLabelPivotArea, style);
    let subtotalDataPivotArea = {
        dataOnly: true,
        references: [{
            fieldName: "Qtr",
            subtotal: true,
            items: ["Qtr1", "Qtr2", "Qtr3", "Qtr4"]
        }]
    };
    pivotTable.setStyle(subtotalDataPivotArea, style);
}

The following image shows the style for row fields of a pivot table.

The following code sample shows how to apply style for row fields.

function setMarkSeattlePivotTableStyle(pivotTable) {
    // create and set style to mark row with "Seattle" City
    let seattlePivotArea = {
        references: [{
            fieldName: "City",
            items: ["Seattle"],
            applyLabel: true,
            applyData: true,
            applyGrandTotal: true,
            applySubtotal: true
        }]
    };
    let style = new GC.Spread.Sheets.Style();
    style.backColor = "yellow";
    pivotTable.setStyle(seattlePivotArea, style);
}        

Set Pivot Area Offset

SpreadJS provides an offset property to style the specific pivot area where default offset refers to the pivot area’s left cell (0,0) as the base cell to define the range.

You can implement the IPivotAreaOffset interface which includes row, col, rowCount, and colCount to set the offset property where:

• row and col depict the row and column offset for the base cell.

• rowCount and colCount depict the number of rows and columns to be styled.

However, the offset can only affect specified areas such as corner area, top right area, grand total area, and row header area.

Rules to set row, col, rowCount, and colCount are as follows:

• If the rowCount and colCount are not a number or less than 2, it is treated as 1.

• If the row and col are infinity, it represents the last row of the pivot area.

• If the rowCount and colCount are infinity, it represents count till the last row or last column of this pivot table.

Example

The following image illustrates two different cases. In the first case, the offset property is set using label reference. In the second case, a specific pivot area is defined using the PivotAreaType enum as corner and set the offset property to specify the pivot table's area.

The following code sample depicts how to set offset property for a specific pivot area.

let style = new GC.Spread.Sheets.Style();
style.backColor = "rgb(247, 167, 17)";
let labelPivotAreaWithOffset = {
    labelOnly: true,
    references: [{
        fieldName: "Region",
        items: ["East"]
    }],
    offset: {
        row: 0,
        col: 0,
        rowCount: 2,
        colCount: 1
    }
};
pivotTable.setStyle(labelPivotAreaWithOffset, style);
let cornerPivotAreaWithOffset = {
    type: GC.Spread.Pivot.PivotAreaType.corner,
    offset: {
        row: 0,
        col: 1,
        rowCount: 1,
        colCount: 1
    }
};
pivotTable.setStyle(labelPivotAreaWithOffset, style);
let cornerPivotAreaWithOffset = {
    type: GC.Spread.Pivot.PivotAreaType.corner,
    offset: {
        row: 0,
        col: 1,
        rowCount: 1,
        colCount: 1
    }
};
pivotTable.setStyle(cornerPivotAreaWithOffset, style);