[Solved] Creating a color-gradient graphic from spreadsheet

[Sushewakka]

Good day. I would like to take a normalized data spreadsheet (values ranking from 0 to 1) and represent it as a color gradient graphic. I have included an example of the data tables I'm working with and an example of the graphic style I'm looking for. Any help would be appreciated. I tried different searches and couldn't find a solution, so I apologize if this has been asked earlier. Thank you for your time.

[Hagar Delest]

Hi and welcome to the forum!

I don't think you can do that with AOO.

But perhaps you can fake it with conditional formatting and using a dedicated sheet to color the cells. See: Creating a 'heatmap' from data.

NB: it may have been asked already, see: [Tutorial] Reporting bugs or suggestions.

Please add '[Solved]' at the beginning of your first post title (edit button) if your issue has been fixed.

[Sushewakka]

Thank you for your reply. I attempted to use the conditional format option, but it is limited to three conditions; and I'd need to establish at least ten conditions. I tried to nest "if" conditions to simulate the gradient selection, but apparently this is not an option. Is there a way to make recursive "if" conditions to assign the styles to the different values (after being reduced to a single decimal point)?

[Hagar Delest]

See that one: [Solved] Conditional formatting highlighting cells.

Please add '[Solved]' at the beginning of your first post title (edit button) if your issue has been fixed.

[acknak]

LibreOffice supports more conditional formats--no limit as far as I know.

Even so, Calc's design is particularly bad at this sort of representation. You have to have separate color names and separate styles defined for each color, and if you ever want to change the colors, you have to go through and re-define each color and edit each style.

It's possible to make that easier with a simple macro program, but it's still a big hassle for such a simple problem.

You can use a table to assign a style, but Calc doesn't handle this well--it gets very slow, basically unusable.

[Charlie Young]

The attached spreadsheet forgoes the conditional formatting and just uses macros.

The input and output ranges are hardcoded in the ColorRange sub. I used the Gimp colorpicker tool to get the color values from the above table.

Code: Select all

REM  *****  BASIC  *****
Option Explicit

Sub ColorRange()
	Dim oDoc As Object
	Dim InRange As Object, OutRange As Object
	Dim i As Long, j As Long
	Dim v As Double
	
	oDoc = ThisComponent
	
	InRange = oDoc.sheets(0).getCellRangeByName("C2:R17")
	OutRange = oDoc.sheets(0).getCellRangeByName("C22:R37")
	
	for i = 0 to InRange.Rows.Count - 1
		for j = 0 to InRange.Columns.Count - 1
			v = InRange.getCellByPosition(j,i).Value
			OutRange.getCellByPosition(j,i).CellBackColor = getColor(v)
		next j
	next i

End Sub


Function getColor(x As Double) As Long
	Dim ColorArray
	
	ColorArray = Array(16777215,16773592,16769970,16766604,16763237,16759615,16756249,16751616,16745216,16739072,16732672,16726272,16719872,16713728,15925248,14811136,13631488,12517376,11337728,10223616,9109504)

	getColor = ColorArray(getColorIndex(x))
End Function


Function getColorIndex(x As Double) As Long
	getIndex = Val(Format(20*Val(Format(x,"0.00")),"0"))
End Function

[MrProgrammer]

Hi, and welcome to the forum.

I don't think you can do that with AOO.

I always love it when people say that because then it make finding a solution even more rewarding.

This can be done without macros in OOo. The hardest part is building the desired colors and eleven new styles.

• Use OpenOffice.org > Preferences > OpenOffice.org > Colors on a Mac or Tools > Options > OpenOffice.org > Colors on other platforms and create (or find) the colors that you want. I called them 0.0, 0.1, 0.2, … but they can have any names. You can use existing colors if they are suitable.
• Use Format > Style Catalog and build styles with names 0, 0.1, 0.2, 0.3, …, 1 and the background colors (from first step) that you want. The style names must match the VLOOKUP table in the next step. Note that styles 0 and 1 do not have a decimal fraction.

  

• On a second sheet create a VLOOKUP table with the groupings that you want to use. In this case it's values from 0 up to (but not including) 0.1 in the first group, values from 0.1 up to (but not including) 0.2 in the second group, etc. See [Tutorial] VLOOKUP questions and answers if you don't understand the VLOOKUP function.

  

• On the first sheet modify your cells to add style information. Select the cells, Edit > Find & Replace, More options, select Current selection only and Regular expressions, Search for .+, Replace with =IF(STYLE(VLOOKUP(&;Sheet2.$A$2:$A$12;1;1));&) =IF(STYLE(VLOOKUP(&;Sheet2.\$A\$2:\$A\$12;1;1));;&), Replace All. I recommend unchecking Current selection only and Regular expressions, before clicking Close.

Note how the formula in cell H2 has been modified by the replace dialog. VLOOKUP on 0.19826 will return 0.1, so that becomes the style name for the cell. If you don't want to see the numbers displayed in the heat chart, use Format > Cells > Numbers > Format code > :::. You could adjust the VLOOKUP table (and the style names) if you would prefer to see values like 0.19826 rounded to 0.2 intead of truncated to 0.1:

• 0, 0.05, 0.15, 0.25, 0.35, 0.45, 0.55, 0.65, 0.75, 0.85, 0.95

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Hagar Delest]

As usual, MrProgrammer's explanation, is very clear!
Note that it's the same than the attachment of the [quite old] topic I've linked after my first post. But the style definition is even more elegant!

[Villeroy]

Another one using relative references instead of regular expressions: http://user.services.openoffice.org/en/ ... p?id=13365
Hit F9 to recalculate all.

[MrProgrammer]

Another one using relative references instead of regular expressions: download/file.php?id=13365

Note, however, that topic Is it possible to count by color? warns that using the STYLE() function in a conditional formatting condition may cause Calc to loop. I've verified that behavior in OOo 3.2 where the example in the download causes OpenOffice to use 66% of the processor as long as the spreadsheet is open. The looping does not occur when STYLE() is used in a cell formula. It's possible that this difficulty has been corrected in later releases of OOo or in AOO, LibreOffice, NeoOffice, etc.

[acknak]

... It's possible that this difficulty has been corrected in later releases of OOo or in AOO, LibreOffice, NeoOffice, etc.

Nope. If anything, it seems to be worse. When I tried a simple style lookup in AOO 3.4, it was nearly unusable--very unresponsive, constantly updating/flashing, etc.

I haven't checked LibO or the others.

[Villeroy]

I see. The problem occurs with acknak's 10x10 game board but not with my 20,000 random values. The difference between the 2 use cases is that acknak's criteria range is calculated whereas mine is constant.

1) You can turn off automatic calculation to avoid this problem.
2) You may write a specific macro as Charly did.
3) You may use a generic macro such as my [Calc] More than 3 conditional formats which basically replaces dialog input with unlimited cell input.
4) There used to be some extension for OOo providing a dialog with more than 3 conditions.
5) LibreOffice has an extensible c.f. dialog.

[Sushewakka]

Thank you all very much for your help. MrProgrammer's method solved the issue in my case, but again, thanks to everyone for your answers and help.