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

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
    var 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
    var 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.
        var is_one_dimensional = table.columnLabels == null;
        if (is_one_dimensional)
            use_row = true;
        if (fileFormatVersion() > 8.12) {
            // Two color pickers
            var primary_color_picker = form.newColorPicker('primaryColor');
            var secondary_color_picker = form.newColorPicker('secondaryColor');
            primary_color_picker.setDefault(primary_color);
            secondary_color_picker.setDefault(secondary_color);

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

            // Set appearance of form and rule
            form.setInputControls([label_1, row_column, label_2, primary_color_picker, label_3, secondary_color_picker]);
            form.setHeading('Adding Color - Shade Spans');
            form.setSummary('Shade 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) {
        var spans;
        if (use_row)
            spans = table.rowSpans;
        else
            spans = table.columnSpans;
        
        var colors = table.cellColors;
        var num_rows = table.numberRows;
        var num_cols = table.numberColumns;

        // Loop over each span, setting the color.
        var all_spanned_indices = [];
        if (use_row) {
            for (var j = 0; j < max_span; j++) {
                var current_indices = spans[j].indices;
                var num_indices_in_span = current_indices.length;
                // Color the cells in this span appropriately
                for (var t = 0; t < num_indices_in_span; t++) {
                    var current_index = current_indices[t];
                    for (var 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 (var j = 0; j < max_span; j++) {
                var current_indices = spans[j].indices;
                var num_indices_in_span = current_indices.length;
                // Color the cells in this span appropriately
                for (var t = 0; t < num_indices_in_span; t++) {
                    var current_index = current_indices[t];
                    for (var 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.
        var indices_without_span = [];
        var total_indices;
        if (use_row)
            total_indices = num_rows;
        else
            total_indices = num_cols
        for (var j = 0; j < total_indices; j++) {
            if (all_spanned_indices.indexOf(j) == -1)
                indices_without_span.push(j);
        }
        var end_color;
        if (max_span % 2 == 0)
            end_color = primary_color;
        else
            end_color = secondary_color;
        
        var num_remaining_indices = indices_without_span.length;

        for (var j = 0; j < num_remaining_indices; j++) {
            var current_index = indices_without_span[j];
            if (use_row)   
                for (var k = 0; k < num_cols; k++)
                    colors[current_index][k] = end_color;
            else
                for (var 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?
        var spans;
        if (use_row)
            spans = table.rowSpans;
        else
            spans = table.columnSpans;

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

        for (var j = 0; j < num_spans; j++) {
            var current_span = spans[j];
            var current_indices = current_span.indices;
            var num_indices_in_span = current_indices.length;
            for (var 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

Modify Cell Content - Shade Alternate Spans

Example

Technical details

How to apply this rule

For the first time in a project

Additional applications of the rule

Removing the rule

How to modify the rule

JavaScript

See also

Navigation menu

Modify Cell Content - Shade Alternate Spans

Example

Technical details

How to apply this rule

For the first time in a project

Additional applications of the rule

Removing the rule

How to modify the rule

JavaScript

See also

Navigation menu

Search