Programmatically changing cell background color in table

Archived Topics
1

Hi All:

I would like to vary the background color of individual cells of a table (not entire rows or columns), based on values in other cells. Is this possible?

Here are some sample data:

Type Vessel Value
A 10 2.1
B 12 2.5
C 23 1.9
A 13 2.3
B 15 2.0
C 20 1.5

And the logic might be something like:
If type = A
Value<1.5 Then background color = White
Value between 1.5 and 2 Then background color = Yellow
Value >2 Then background color = REd
And so on for each Type.

TIA

D. Lewis

2

David,

The built-in coloring feature only works for coloring entire rows, however by making clever use of the fact that table cells can be HTML formatted, you can achieve the effect that you describe. The logic is done in the SQL query itself.

We have a tutorial about this very subject located here: http://www.inductiveautomation.com/forum/viewtopic.php?t=2334

The tutorial covers coloring the foreground (font) color, but it could just as easilly color the background color, using something like this:

<HTML><span style='background-color:red;'>21.819</span>

Your SQL query will be quite big in order to contain the logic. Let us know if you are having trouble getting this to work, and we can help you out.

3

Hi Carl:

OK, I can see how that would work. However, will this prevent me from doing any calculations using the contents of the cell? For example, I need to allow users to select one or more non-contiguous rows, and display the weighted averages of certain cells (the weighting is determined by the contents of a given column). Will I need to in code search out the number portion of the cell contents (the right hand most 3 characters, for example), convert them to numbers, then calculate averages?

Regards, D. Lewis

4

David,

Ah, good point. If you use HTML to color the numbers, the cell contents becomes Strings instead of Numbers, and it becomes awkward to do programmatic calculations of these numbers. You have 2 options here:

One is to do as you suggested - have your numeric calculation code try to parse out the digits in the HTML strings and use those. I don’t recommend this - very fragile.

Option two is better. The Table component has the ability to hide certain columns from view. It is important to know that these hidden columns can still be accessed programmatically, however. The idea here is to select the HTML formatted data, and then the raw data (use a different column name using the SQL “AS” clause), and hide the raw data.

Lets say your data looked like this:
(Columns)Type, Vessel, Value, RawValue
(Ex. Data) “A”, 10, “…2.1”, 2.1

Now, tell the table to hide the “RawValue” column.
The following code will calculate the average of the selected RawValues:

[code]#This line will vary depending on your window
table = event.source.parent.getComponent(“Table”)

selectedRows = table.getSelectedRows()
if len(selectedRows)>0:
total = 0
for rowNum in selectedRows:
value = table.data.getValueAt(rowNum, 3) # 3 is the column index of “RawValue”
total += value
average = total / len(selectedRows)
print "Average is: ", average
[/code]

5

Yep, raw data is the way to go. Thx.