Modify Cell Content - Shade Alternate Spans

This rule shades the cells for each row or column span in the table. Two colors are specified, and the spans are shaded alternatively with these two colors. By selecting Rules below your table and clicking Edit Rule you can choose different colors for your table, and whether or not the row spans or column spans are shaded.

Example

Technical details

The shading applies only to the lowest level of spans in the table and assumes that all of the spans are adjacent to one another (ie there are no rows without spans between a pair of spans). This is the usual case when creating a banner.

How to apply this rule

For the first time in a project

Select the table(s)/chart(s) that you wish to apply the rule to.
Start typing the name of the Rule into the Search features and data box in the top right of the Q window.
Click on the Rule when it appears in the QScripts and Rules section of the search results.

OR

Select Automate > Browse Online Library.
Choose this rule from the list.

Additional applications of the rule

Select a table or chart that has the rule and any table(s)/chart(s) that you wish to apply the rule to.
Click on the Rules tab (bottom-left of the table/chart).
Select the rule that you wish to apply.
Click on the Apply drop-down and choose your desired option.
Check New items to have it automatically applied to new items that you create. Use Edit > Project Options > Save as Template to create a new project template that automatically uses this rule.

Removing the rule

Select the table(s)/chart(s) that you wish to remove the rule from.
Press the Rules tab (bottom-right corner).
Press Apply next to the rule you wish to remove and choose the appropriate option.

How to modify the rule

Click on the Rules tab (bottom-left of the table/chart).
Select the rule that you wish to modify.
Click Edit Rule and make the desired changes. Alternatively, you can use the JavaScript below to make your own rule (see Customizing Rules).

JavaScript

includeWeb('Table JavaScript Utility Functions'); 

excludeRTables();

shadeSpans('AliceBlue', 'White', true);

// Shade the spans in the table.
//
// Alternate span colors are specified using primary_color and secondary_color.
// If use_row is true then rows will be shaded, otherwise columns will be shaded.
function shadeSpans(primary_color, secondary_color, use_row) {
    // Determine colors
    let user_choice = shadingUserForm(primary_color, secondary_color, use_row);
    primary_color = user_choice.primary;
    secondary_color = user_choice.secondary;
    use_row = user_choice.useRow;

    // Determine the first span that overlaps
    let max_span = findFirstOverlappingSpan(use_row);

    // Apply shading
    interlaceSpanColors(max_span, primary_color, secondary_color, use_row);

    // In newer versions we can allow the user to change the colors dynamically with color pickers.
    function shadingUserForm(primary_color, secondary_color, use_row) {
        // One dimensional tables do not have real columns, just statistics,
        // so only shading rows makes sense.
        let is_one_dimensional = table.columnLabels == null;
        if (is_one_dimensional)
            use_row = true;
        if (fileFormatVersion() > 8.12) {
            // Two color pickers
            let primary_color_picker = form.newColorPicker('primaryColor');
            let secondary_color_picker = form.newColorPicker('secondaryColor');
            primary_color_picker.setDefault(primary_color);
            secondary_color_picker.setDefault(secondary_color);

            // Option for rows and columns
            let row_column;
            if (!is_one_dimensional) {
                row_column = form.newComboBox('rowcol', ['Rows', 'Columns']);
                row_column.setDefault('Columns');
            } else {
                row_column = form.newLabel('Rows');
            }
            
            // Labels
            let label_1 = form.newLabel("Shade");
            let label_2 = form.newLabel("by");
            let label_3 = form.newLabel("and");

            // Set appearance of form and rule
            let description = form.newLabel('Apply an alternating color scheme to the row or column spans in the table');
            description.lineBreakAfter = true;
            form.setInputControls([description, label_1, row_column, label_2, primary_color_picker, label_3, secondary_color_picker]);
            form.setHeading('Shade Alternate Spans');
            let sumlabel = is_one_dimensional ? "row" : row_column.getValue().toLowerCase();
            form.setSummary('Shade ' + sumlabel + ' spans');
            form.setOutputColors([primary_color_picker.getValue(), secondary_color_picker.getValue()]);

            primary_color = primary_color_picker.getValue();
            secondary_color = secondary_color_picker.getValue();
            if (!is_one_dimensional)
                use_row = row_column.getValue() == 'Rows';
        }
        return {primary: primary_color, secondary: secondary_color, useRow: use_row};
    }

    // Place the primary color in the first span, the secondary color in the second span
    // and then alternate.
    // Any cells without a span (e.g. at the bottom of the table) will be given either the
    // primary or secondary depending on the color of the last span.
    function interlaceSpanColors(max_span, primary_color, secondary_color, use_row) {
        let spans;
        if (use_row)
            spans = table.rowSpans;
        else
            spans = table.columnSpans;
        
        let colors = table.cellColors;
        let num_rows = table.numberRows;
        let num_cols = table.numberColumns;

        // Loop over each span, setting the color.
        let all_spanned_indices = [];
        if (use_row) {
            for (let j = 0; j < max_span; j++) {
                let current_indices = spans[j].indices;
                let num_indices_in_span = current_indices.length;
                // Color the cells in this span appropriately
                for (let t = 0; t < num_indices_in_span; t++) {
                    let current_index = current_indices[t];
                    for (let k = 0; k < num_cols; k++) {
                        if (j % 2 == 0)
                            colors[current_index][k] = primary_color;
                        else
                            colors[current_index][k] = secondary_color;
                    }
                    all_spanned_indices.push(current_index);
                }
            }
        } else {
            for (let j = 0; j < max_span; j++) {
                let current_indices = spans[j].indices;
                let num_indices_in_span = current_indices.length;
                // Color the cells in this span appropriately
                for (let t = 0; t < num_indices_in_span; t++) {
                    let current_index = current_indices[t];
                    for (let k = 0; k < num_rows; k++) {
                        if (j % 2 == 0)
                            colors[k][current_index] = primary_color;
                        else
                            colors[k][current_index] = secondary_color;
                    }
                    all_spanned_indices.push(current_index);
                }
            }

        }

        // Color any rows/columns (e.g. NET) at the bottom/right of the table that are not in a span.
        let indices_without_span = [];
        let total_indices;
        if (use_row)
            total_indices = num_rows;
        else
            total_indices = num_cols
        for (let j = 0; j < total_indices; j++) {
            if (all_spanned_indices.indexOf(j) == -1)
                indices_without_span.push(j);
        }
        let end_color;
        if (max_span % 2 == 0)
            end_color = primary_color;
        else
            end_color = secondary_color;
        
        let num_remaining_indices = indices_without_span.length;

        for (let j = 0; j < num_remaining_indices; j++) {
            let current_index = indices_without_span[j];
            if (use_row)   
                for (let k = 0; k < num_cols; k++)
                    colors[current_index][k] = end_color;
            else
                for (let k = 0; k < num_rows; k++)
                    colors[k][current_index] = end_color;
        }
        table.cellColors = colors;
    }

    // Finds the first span that contains a row/column
    // that has already been found in an earlier span.
    //
    // If use_row is true then this function will look at row spans,
    // otherwise it will look at column spans.
    function findFirstOverlappingSpan(use_row) {
        // Use row or column spans?
        let spans;
        if (use_row)
            spans = table.rowSpans;
        else
            spans = table.columnSpans;

        let spanned_indices = [];
        let num_spans = spans.length;
        let max_span = num_spans;
        let no_overlapping_spans = true;

        for (let j = 0; j < num_spans; j++) {
            let current_span = spans[j];
            let current_indices = current_span.indices;
            let num_indices_in_span = current_indices.length;
            for (let k = 0; k < num_indices_in_span; k++)
                if (spanned_indices.indexOf(current_indices[k]) != -1) {
                    no_overlapping_spans = false;
                    max_span = j;
                    break;
                } else
                    spanned_indices.push(current_indices[k]);
            if (!no_overlapping_spans)
                break;
        }
        if (no_overlapping_spans)
            return num_spans;
        else    
            return max_span;
    }
}

Prior to the 15th of December, 2015, this page was known as Adding Color - Shade Spans