How to Preserve Conditional Formatting When Adding or Removing Rows

Conditional formatting is applied at the cell level in SpreadJS. Therefore, when the rows or columns of the related cells are deleted, so too is the related conditional formatting. Depending on your use case, you may have many new rows being added or removed, but wish to reuse the conditional formatting on resulting new cells. 

One way we can preserve the formatting is to store the formatting of the existing rows before adding or removing any new rows. This is achieved by using getRules. Let’s break down an example:

        // Insert a Table
        var spreadNS = GC.Spread.Sheets;
        var table = sheet.tables.add("Table1", 0, 0, 9, 5, spreadNS.Tables.TableThemes["medium4"]);
        for (var i = 1; i <= 8; i++) {

            sheet.getCell(i, 0).value(i + 1);
            sheet.getCell(i, 1).value(i + 2);
            sheet.getCell(i, 2).value(i + 3);
            sheet.getCell(i, 3).value(i + 4);
            sheet.getCell(i, 4).value(i + 5);

        }
        sheet.setColumnWidth(0, 100);
        sheet.setColumnWidth(1, 100);
        sheet.setColumnWidth(2, 100);
        sheet.setColumnWidth(3, 100);
        sheet.setColumnWidth(4, 100);

        var cfs = sheet.conditionalFormats;
        var style = new spreadNS.Style();
        style.backColor = '#F4F8EB';
        style.foreColor = '#82bc00';
        //apply style for numbers greater than 8 
        var cvRule = cfs.addCellValueRule(
            spreadNS.ConditionalFormatting.ComparisonOperators.greaterThan,
            8, 0, style, [new GC.Spread.Sheets.Range(1, 0, 8, 5)]);

    }

    removeRows = () => {
        let spread = this.designer.getWorkbook();
        let sheet = spread.getActiveSheet();
        // Store the Conditional Formatting
        this.conditionalFormats = JSON.parse(JSON.stringify(sheet.conditionalFormats.getRules()));
        
        sheet.deleteRows(1, 8);
    }

    addRows = () => {
        let spread = this.designer.getWorkbook();
        let sheet = spread.getActiveSheet();
        sheet.addRows(1, 8);
        this.addConditionalFormatting();
    }

In the above code, we have created a table with a conditional format; when a cell in the table is greater than 8, then the cell has a new cell style is applied. We also have two custom functions – removeRows and addRows. These functions allow us to call getRules when deleting a range of rows, as well as call our addConditionalFormatting function when adding a new range of rows. We can define addConditionalFormatting like so: 

addConditionalFormatting = () => {
        for (let i = 0; i < this.conditionalFormats.length; i++) {
            let rule = this.conditionalFormats[i];

            // For ranges, use the previous range or add new range.
            let ranges = [];
            rule.ranges.forEach((range) => {
                ranges.push(new GC.Spread.Sheets.Range(range.row, range.col, range.rowCount, range.colCount));
            });

            let style = new GC.Spread.Sheets.Style();
            if (rule.style) {
                style.fromJSON(rule.style);
            }

            // Create the new Rule
            let newRule = new GC.Spread.Sheets.ConditionalFormatting.NormalConditionRule(rule.ruleType, ranges,
                style, rule.operator, rule.value1 ? rule.value1 : null, rule.value2 ? rule.value2 : null, rule.text ? rule.text : null,
                rule.formula ? rule.formula : null, rule.type ? rule.type : null, rule.rank ? rule.rank : null);

            // Add the New Rule Back to the Sheet
            sheet.conditionalFormats.addRule(newRule);
        }
    }

While in this example we cannot remove the original rows while preserving the conditional formatting, we can add or remove any other rows beyond the original row range, and they will all have the same conditional formatting. This allows us to have a scalable conditionally-formatted table without having to manually reapply the same conditional formatting to new rows. We are also free to remove rows without losing our conditional format rule, so long as we do not remove the original rows. 

Try out this React sample and see for yourself!