I. Overview
1. Problem description
By displaying different row background by conditions, you can change the background colors of rows/columns when certain conditions are met. How to display different colors between rows?
2. Solution
The formula row() is used to get the row number (the formula col() is used to get the column number) in conditional formatting > background, and then it is implemented by calculation.
II. Example 1: setting background color by interlacing
1. Preparation
Dataset query: SELECT * FROM ORDERS
2. Setting background color by interlacing
Select cell A2, click conditional formattingon the right to add a condition, select background as attributes, click Edit to select a color, and set the range to current row. Select formula for formula condition type, click define, enter formula: row()% 2 = 0, and then click Add.Condition Description: row() is to get the current line number, which is divided by 2, and the remaining 0 is even number line. That is, even rows have backgrounds, and odd rows have no backgrounds.
The steps are as follows:
3. Effect preview
Save the report and click page preview. The effect is as follows:
Both app and H5 support it.
III. Example 2: set the background color every N lines
Taking the template described in example 1 as an example, you only need to modify the formula in the condition attribute of cell A2.
1. set the background color in N rows
The modified formula style is: ROUNDUP((ROW()-M)/N,0)%2=0. The formula is explained as follows:
| Formula | Explain |
|---|---|
| ROW() | gets the current row number |
| M | Number of Title lines |
| N | number of rows in gap |
| ROUNDUP() | round up |
| ROUNDUP((ROW()-M)/N,0)%2=0 | the quotient obtained by dividing the current line number from 1 by the number of interval lines n is rounded up. If the integer value can be divided by 2, there is background in the line, and there is no background in the line that cannot be divided by 2 |
Select cell A2, then M = 1, set n = 5, then change the formula to round up ((row () - 1) / 5,0)% 2 = 0, that is, starting from the line of A2, the background color set is displayed every 5 lines.
2. Effect preview
Save the report and click page preview. The effect is as follows:
Both app and H5 support it.
IV. Example 3: setting the background color in groups
In the group report, if you want to set the background color by grouping, you can't set the conditional formatting in the grouping cell, but ser in the right cell of the grouping cell, and judge whether the background color is displayed according to the grouping sequence number.
1. Data preparation
Create a new normal report, create a new database query DS1, and the SQL query statement is: select * from Products
2. Design form
Enter the title in cells A1-C1, drag cells A2-C2 into the corresponding data column, set the expansion direction of cells A2-C2 to vertical expansion, and set the data in cells A2 to grouping. The table style is as follows:
3. Setting background color
Select cell B2, click conditional formatting on the right as example1 and 2 to add a condition, select background as the attributes , click Edit to select a color, and set the range to current row. Select formula for formula condition type, click define to enter formula: &A2% 2 = 0, and then click Add.
Condition Description: &A2 is the serial number to obtain the extension value of A2 cell, and the remaining 0 is even if it is divided by 2. That is to say, even number group has background, odd number group has no background.
The steps are as follows:
4. Effect preview
Save the report and click page preview. The effect is as follows:
Both app and H5 support it
V. Example 4: setting the background color by column
The background color setting for separated columns can only take effect when the column is derived from horizontal expansion, and the expanded header row will also change color.
When the background color can be set every other column, the principle of every N columns is the same as that of every N rows.
1. Data preparation
Create a new normal report, create a new database query DS1, and the SQL query statement is: select * from ORDERSDETAIL
2. Design form
Drag the data column into the cell and set the properties of each cell as shown in the following table:
Cell | Column | Attribute |
|---|---|---|
| A2 | order ID | vertical expansion, the rest default |
| B1 | Product ID | horizontal expansion, the rest default |
| B2 | quantity | no expansion, and the data is set to "summary sum" |
The table style is as follows:
3. Setting the background color by column
Select cell B1, click conditional formatting on the right, add condition, and select background from the attribute drop-down list. Click Edit to customize the color and set the range to current column. Select formula for formula condition type, click definition, enter formula: col()% 2 = 0, and then click Add.
Condition Description: col() is to get the current line number, which is divided by 2, and the remaining 0 is even column. That is, even sequence has background, odd sequence has no background.
The steps are as follows:
4. Effect preview
Save the report and click page preview. The effect is as follows:
Both app and H5 support it
VI. Template download
For completed templates：
Eample1:
%fr_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\ReportApplication\FeaturesApplication\LineBg.cpt
Example2:
%fr_HOME%\webapps\webroot\WEB-INF\reportlets\doc EN\ReportApplication\FeaturesApplication\LineBg_N.cpt
Example3: %fr_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\ReportApplication\FeaturesApplication\LineBG_Group.cpt
Example4: %fr_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\ReportApplication\FeaturesApplication\LineBg_Col.cpt
