[Solved] Set Cell Color based on RGB values in a cell

[Myndex]

NEED: To set the color of a cell based on an RGB value that is calculated by that cell, or perhaps another cell.

Conditional formatting isn't useful as I'd have to have a million styles (and I am aware of some of the hacks to get around the CF limit of 3...) The problem isn't the CF limit, the problem is that CF uses styles, and does not allow you to directly set the RGB values of a cell (from what I can tell, or am I missing something???)

STYLE() also requires that you have a style set.

What I need to do ideally is:

Cell A1 contains hex value #5500BB and the background is thus purple. When I change the cell to #00FF00 it becomes green.
------

My assumption at this point is that I have to write something. What I'd like most to create is just a function:

Either an 8bit: RGB( 123 ; 80 ; 255 ) or HEX: RGB( AA ; E0 ; 12 ) or decimal: RGB( 0.76 ; 0.05 ; 0.68 ) or maybe all with a selector: RGB( AA ; E0 ; 12 ; H )

Before I start trying to write this I thought I should ask if there is a method I am missing that already does this, and can I even change the cell color directly with a macro?

[robleyd]

Just out of curiosity, what is the actual problem you are trying to solve? This doesn't sound like the "conventional" use of a spreadsheet

[Myndex]

Just out of curiosity, what is the actual problem you are trying to solve? This doesn't sound like the "conventional" use of a spreadsheet

Define conventional use, LOL...

IMO a spreadsheet is for anything involving math or statistics. So that encompasses a whole lot.

I use spreadsheets for research, crunching numbers, modeling algorithms, prototyping code ideas before building them into an app, and data visualization.

Right now I am doing a lot of research into human visual perception of contrast, essential color theory, and correlation between various color models and colorspaces, among other things. I use the spreadsheet to create an array of values and at the moment I need to copy and paste those values to another app to then see the colors and color combinations.

It would really speed things along if I had a function to do that. It would also help with planning the perception experiments.

But it looks like what I need to do may be fairly trivial, I'll post is when I have something working.

A

[Zizi64]

viewtopic.php?f=20&t=74350

[Myndex]

Hmmm.

Well it appears that running a macro as a function inside a cell will not allow changing the color of that or any cell. Yet it works when run as a macro.

theCell = theSheet.getCellByPosition(CellAddress.Column,CellAddress.Row)
theCell.CellBackColor = RGB(R,G,B)

Is there some additional class/method I need to use when running as a function in a cell formula?

Also, I came across this:

// --- Get cell address. ---
com.sun.star.sheet.XCellAddressable xCellAddr = (com.sun.star.sheet.XCellAddressable)
UnoRuntime.queryInterface(com.sun.star.sheet.XCellAddressable.class, xCell);
com.sun.star.table.CellAddress aAddress = xCellAddr.getCellAddress();

But was unable to get these methods to function in Basic in the macro editor — are they only available in Java and C++ ?

thank you!

[Myndex]

Okay, so I'm a little sad that I couldn't make a function that ran inside a cell, what I did instead was to use a sheet event, "Content Changed" to trigger the macro.

Since the sheet event sends a Cell Object of the changed cell to the macro, I was able to get everything else I needed to change that cell's color to the hex sRGB color entered into that cell.

The thin code is:

Code: Select all   Expand viewCollapse view

Sub setColor(changed As Object)

Dim sheet As Object
Dim cell As Object
Dim fAccess As Object
Dim sRGB As Long

sheet = ThisComponent.Sheets(changed.cellAddress.sheet)
cell = sheet.getCellByPosition(changed.cellAddress.column,changed.cellAddress.row)

fAccess = CreateUnoService("com.sun.star.sheet.FunctionAccess")
 
sRGB = fAccess.CallFunction("HEX2DEC", array(cell.String))

cell.CellBackColor = sRGB

End Sub


Not Sure why I had to array() to make CallFunction work. Obviously no error checking whatsoever, so changing more than one cell at a time throws an error, as does entering a non-valid HEX value. Also, this obviously tries to affect *any* cell that changes.

Interestingly, writing CellBackColor does NOT toss out the style that was set, so a style can be set and persist despite other aspects of a cell changing. So to restrict what cells get changed I am going to used a style called "sRGB" and then use IF Cell.CellStyle = "sRGB" BlahBlah. thus only cells with the specifically named style will be changed. Also need to add a provision to change other cells than the one with the new value, and along with that a "page refresh'.

I want to thank @Lupp @Villeroy @Zizi64 and of course @Pitonyak for their many posts, info, and code snippets, etc.

[Zizi64]

Interestingly, writing CellBackColor does NOT toss out the style that was set, so a style can be set and persist despite other aspects of a cell changing

Yes, this method equivalent to the direct (manual) formatting of a cell. The manual formatting overwrites the propertyies of the applied style, but you can reset it (by Ctrl-M), because the property value still unchanged in the property set of the style.

(You can manipulate/adjust the properties of the styles too - by macros)

[hubert lambert]

Hi,

The sheet event "Content changed" is the easiest way to achieve this, IMHO. And you could make your code far simpler :

Code: Select all   Expand viewCollapse view

sub setColor(cell)
    svalue = trim(cell.String)
    if left(svalue, 1) = "#" then svalue = right(svalue, len(svalue)-1)
    cell.CellBackColor = int("&h" + svalue)
end sub

A add-in function could also do the job, as it can access the XCellRange interface of the cell or range passed as argument, but it's no so effortless...
Regards.

Edit: Here is example of add-in function: download and double-click the oxt file, accept the installation of the extension, and reboot the office program. In Calc, you have now a new BACKGROUND function under Spreadsheet category : XBackground.oxt (3.62 KiB) Downloaded 153 times

[Myndex]

Edit: Here is example of add-in function: download and double-click the oxt file, accept the installation of the extension, and reboot the office program. In Calc, you have now a new BACKGROUND function under Spreadsheet category : background.png XBackground.oxt

I was looking EVERYWHERE for such a function. Is this yours? Or is there an add-on repository I don't know about?

[Myndex]

Hi,

The sheet event "Content changed" is the easiest way to achieve this, IMHO. And you could make your code far simpler :

Code: Select all   Expand viewCollapse view

sub setColor(cell)
    svalue = trim(cell.String)
    if left(svalue, 1) = "#" then svalue = right(svalue, len(svalue)-1)
    cell.CellBackColor = int("&h" + svalue)
end sub

Thank you as I posted earlier I am using Content Changed. And yesterday was the first time I wrote any script for Open Office —for you code, I think I see what you're doing, does this: int("&h" + svalue) convert a hex value to an integer?? Where are there complete docs on these things? It seems half the english docs here have been defaced and are literally blank and empty.

[hubert lambert]

Ok, I actually didn't realize it was your first attempt in OpenOffice macro. Nice shot anyway !

About the documentation, you need to distinguish between the UNO language, an abstract language to communicate with the office program, and the script language (basic, python...) that interfaces with that UNO level.
When one writes

Code: Select all   Expand viewCollapse view

value = right(svalue, len(svalue)-1)

it's pure basic, which is all describe in the integrated help pages. And, yes, basic uses the prefix "&h" to identify hex representation.

When one writes

Code: Select all   Expand viewCollapse view

cell.CellBackColor = &hD7115F

he actually uses the UNO service com.sun.star.table.CellProperties. In general beginners get lost here .

Uno methods, properties... are describe on the api site which is absolutly awkward. When facing a UNO object (ie all objects obtained from the office program), you should first use a introspection tool, like MRI or Xray, to get informed about its propreties, methods and so on. Then read the api if needed. Developer's Guide is sometimes usefull, if you need more details. But the best place to start is the Andrew Pitonyak's books : http://www.pitonyak.org/oo.php.

Regarding the little addin BACKGROUND, it's a really quick example I wrote for this post, over an older one, to illustrate that option. If you were ever interested, I should polish it a little bit more... and take more time to explain it . Btw the oxt file is just a renamed zip file, you can easily see the different pieces.

Regards.

[Myndex]

Ok, I actually didn't realize it was your first attempt in OpenOffice macro. Nice shot anyway !

Last time I used basic was nearly 20 years ago, LOL... In recent years I've mostly been using PHP, JavaScript, very little C/C++ ... Currently working/learning more Python.

About the documentation, you need to distinguish between the UNO language, an abstract language to communicate with the office program, and the script language (basic, python...) that interfaces with that UNO level....snip...When one writes

Code: Select all   Expand viewCollapse view

cell.CellBackColor = &hD7115F

he actually uses the UNO service com.sun.star.table.CellProperties. In general beginners get lost here .....snip.....OpenOffice.org_Developers_Guide]Developer's Guide[/url] is sometimes usefull, if you need more details. But the best place to start is the Andrew Pitonyak's books : http://www.pitonyak.org/oo.php.

I'm not unfamiliar with object concepts, but sadly the documentation here is literally all over the place, with links to empty pages, and english links with english summaries that link to pages in various asian languages. Something is quite wrong there. I could not find a BASIC code reference for instance. I read a lot of Pitonyak's books yesterday, quite helpful. Mainly I found difficulty determining when certain UNO methods are accessible in a particular context or scope.

Regarding the little addin BACKGROUND, it's a really quick example I wrote for this post, over an older one, to illustrate that option. If you were ever interested, I should polish it a little bit more... and take more time to explain it . Btw the oxt file is just a renamed zip file, you can easily see the different pieces.

To be honest, it's ideal for what I need it for, and it works well. There is an instability where doing too many UNDOs at a time involving cells with the BACKGROUND function will cause the entire app to crash and terminate unexpectedly, but not really a problem for me. I really do appreciate you providing that as it is saving me a ton of time related to some color and vision theory research I am doing, Thank you again!

Andy

[MrProgrammer]

Currently working/learning more Python.

Several people on the Macros and UNO API forum and here have suggested it is better to do macro programming in a modern language like Python instead of writing in Basic. For example, it is simple to sort data in Python but in Basic one must carefully program that. A problem with Python, I think, is that many of the examples/documentation one can find are for Basic. However, as hubert lambert explained it's the UNO interface which is difficult to understand, and that is the same for both languages.

I could not find a BASIC code reference for instance.

OpenOffice → Help → Contents → Macros and Programming
OpenOffice.org 3.2 BASIC Guide (I think there are no significant changes between 3.2 and the current version)

the documentation here is literally all over the place, with links to empty pages, and english links with english summaries that link to pages in various asian languages

Please provide specific links to the problems you have noted.

[Myndex]

Please provide specific links to the problems you have noted.

Missing images: https://wiki.openoffice.org/wiki/Documentation/BASIC_Guide/Events

BLANK:
These are just a few examples:

https://wiki.openoffice.org/wiki/Docume ... _Documents
https://wiki.openoffice.org/wiki/Docume ... _Documents
https://wiki.openoffice.org/wiki/Docume ... _Documents
https://wiki.openoffice.org/wiki/Docume ... et_Outline


EN is the normal language code for English, except here, example:
https://wiki.openoffice.org/wiki/EN/Doc ... _of_Charts

And pages like this are peppered through english search results:
https://wiki.openoffice.org/wiki/Zh/Doc ... rol_Events

[MrProgrammer]

As you may have noted in our Survival guide this is a user-to-user forum. I'm not a developer and can't fix anything in the documentation. I think Contributor's 101 would have information about contacting the project to make updates. Meanwhile, I can offer some suggestions.

Missing images: https://wiki.openoffice.org/wiki/Docume ... ide/Events

Yes, I see that too. The Firefox browser reports: The image “https://wiki.openoffice.org/w/images/thumb/d/df/Documentation_basicguide_dlg_03.gif/500px-Documentation_basicguide_dlg_03.gif” cannot be displayed because it contains errors. However, I can right-click the image and choose Open Link in New Tab. If I do that, the image appears! Open Link in New Tab also works in Safari.

BLANK: These are just a few examples:

Parts of the Wiki seem to be broken. Perhaps you can find the missing information in the Developer's Guide PDF, say starting on page 901.

Since your signature shows you use MacOS I'll give you a link to [Tutorial] Mac FAQ though it has nothing to do with this topic.

[hubert lambert]

To be honest, it's ideal for what I need it for, and it works well. There is an instability where doing too many UNDOs at a time involving cells with the BACKGROUND function will cause the entire app to crash and terminate unexpectedly, but not really a problem for me. I really do appreciate you providing that as it is saving me a ton of time related to some color and vision theory research I am doing, Thank you again!

Here's a new version of this little extension. The function definition is slightly different, so the first extension need to be removed before installing the new one. If I'm right, the undo problem does no longer occure.
The second parameter could be a hexadecimal value (as string) or a decimal value (as numeric).
Regards.

[Zizi64]

Missing images: https://wiki.openoffice.org/wiki/Docume ... ide/Events

Which images are missing? It appeared two pictures for me on that webpage.

[Myndex]

Missing images: https://wiki.openoffice.org/wiki/Docume ... ide/Events

Which images are missing? It appeared two pictures for me on that webpage.

The two

https://wiki.openoffice.org/wiki/File:D ... dlg_03.gif
https://wiki.openoffice.org/wiki/File:D ... dlg_02.gif

are not loading inline to the page on either Safari or Chrome on OS X 10.13.6 — interestingly I can open them directly, so the images are okay but an error is thrown for the inline versions (??!!)

[Myndex]

To be honest, it's ideal for what I need it for, and it works well. There is an instability where doing too many UNDOs at a time involving cells with the BACKGROUND function will cause the entire app to crash and terminate unexpectedly, but not really a problem for me. I really do appreciate you providing that as it is saving me a ton of time related to some color and vision theory research I am doing, Thank you again!

Here's a new version of this little extension. The function definition is slightly different, so the first extension need to be removed before installing the new one. If I'm right, the undo problem does no longer occure.
The second parameter could be a hexadecimal value (as string) or a decimal value (as numeric).
Regards.

Again I want to thank you so much for this, I am currently researching visual contrast for accessibility for the W3C's WCAG accessibility guidelines, as well as researching color-theory for computer display environments. While my other macros were working, they were a bit cumbersome - your extension makes everything fast and easy, which is a key as I am evaluating literally thousands of colors on the way to developing a perceptual contrast test.

Thank you!!
Andy


WHY A SPREADSHEET

For those that were wondering why I am using a spreadsheet: There are a dozen different math methods to calculate/predict color and human perception of contrast. The spreadsheet is being used to evaluate every variety of math & proposed math for color and contrast prediction in a computer display environment so I can collect early empirical data before developing a stand-alone app or algorithm.

The early experiments are live and public at: https://www.myndex.com/WEB/Perception wherein I was generating color values and then using regex to parse them into a set of DIVs for a webpage. But I'm at the point where I need to do a lot of environmental testing, taking a laptop to various ambient light environments to evaluate real-world contrast examples and limitations. Being able to use the spreadsheet means I don't need to create webpages or build an app for the purpose, and can focus on collecting data.

Thanks again for all the help!!!

[keme]

Another subject concerning visual perception (I think):
posting.php?mode=reply&f=9&t=98083#preview

Not directly related, but you may benefit from sharing experiences. Just a thought...

[MrProgrammer]

Which images are missing?

The images are available but the web page does not display them, perhaps because it is coded incorrectly. Since the page is part of the Wiki, the incorrect coding may be in the posting to the Wiki, or it may be the Wiki-to-webpage converter is faulty.

The OpenOffice Basic Development Environment

The Assign Action Dialog

[Zizi64]

Win 7 prof x64, Firefox 66.0.5 x64:

[Myndex]

Win 7 prof x64, Firefox 66.0.5 x64:

Which shows it works on Firefox under windows. But it does not work on certain other systems.

Firefox, Chrome, Safari will not download any more than 11 bytes, which are:

Code: Select all   Expand viewCollapse view

0000: 47 49 46 38 39 61 C3 B4 01 09 02                   GIF89a.....


So there must be "something" that causes the download to abort? Looking into this, I can see the same image on the page that the image links to, and that image URL is:

The broken file:

The page HTML. The missing file is in folder "thumb" ??

These two are the only two gifs I could find that were in a folder called thumb.

So, perhaps the WINDOWS browser is loading the image listed later in the HTML srcset, but the MACOS browsers are not seeing the other URLs??

[DiGro]

Regarding the missing pages I've entered a ticket in JIRA to look into it.
https://issues.apache.org/jira/browse/OPENOFFICE-182

Nothing happened yet. Sorry