Spreadsheet::WriteExcel -> Conditional Formatting Colors

sectokia has asked for the wisdom of the Perl Monks concerning the following question:

In the doc for 'Spreadsheet::WriteExcel' there this example for conditional color based on positive or negative percent value:

$f_change->set_num_format('[Green]0.0%;[Red]-0.0%;0.0%');

I would like to do something slightly different: Green if over 80%, orange if over 40%, otherwise red. However I cannot understand the syntax of the format string

Everything I have tried results in excel complaining about the format and asking to recover the document :-(

Any ideas? Thanks!

Comment on Spreadsheet::WriteExcel -> Conditional Formatting Colors Download Code

Replies are listed 'Best First'.
Re: Spreadsheet::WriteExcel -> Conditional Formatting Colors by swl (Prior) on Jul 05, 2021 at 06:41 UTC
XLSX files are just zipped XML files (plus some other components). You could try setting up a simple worksheet with your desired formatting, and then extract the file contents (I use 7zip on Windows) and view them using a text editor to see what is expected. Look under the `xl/worksheets` subdir to find the actual data. Edit - although that is for setting the rules directly in the file. Maybe there is a more effective approach using Spreadsheet::Excel.	[reply] [d/l]

Replies are listed 'Best First'.

Re: Spreadsheet::WriteExcel -> Conditional Formatting Colors
by swl (Prior) on Jul 05, 2021 at 06:41 UTC

XLSX files are just zipped XML files (plus some other components). You could try setting up a simple worksheet with your desired formatting, and then extract the file contents (I use 7zip on Windows) and view them using a text editor to see what is expected.

Look under the xl/worksheets subdir to find the actual data.

Edit - although that is for setting the rules directly in the file. Maybe there is a more effective approach using Spreadsheet::Excel.

[reply]
[d/l]