# 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.

## 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.
// 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);

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.
// 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_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]);
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;
}
}