I've used the following for a script on monthly metrics:
my $percent_format = $workbook->addformat();
$percent_format->set_num_format(
'[Red][<=.96]0.0%;[Blue][<=.99]0.0%;[Color 10]0.0%');
And then you use that format wherever a format is called for. Basically, it says if the value is less than or equal to .96, color it red, if it's less than or equal to .99, color it blue, otherwise, color it with 'Color 10' (which is green, IIRC). Moreover, it formats it as a percent with one place after the decimal point. When I wrote this, I remember there being some limit to how many conditions you could have. I could be wrong, though. HTH.
thor
Feel the white light, the light within
Be your own disciple, fan the sparks of will
For all of us waiting, your kingdom will come
| [reply]
[d/l] |
<workbook>
<worksheet name="Foo">
<if name="my_val" op=">=" value="4">
<format bgcolor="red">
<cell><var name="my_val" /></cell>
</format>
</if>
<if name="my_val" op="<" value="4">
<cell><var name="my_val" /></cell>
</if>
</worksheet>
</workbook>
See the documentation for more info.
| [reply]
[d/l] |
As far as I can see, this can only generate what WriteExcel can already do.
The point is that the conditional format(s) I'd like to put in are referencing formulae and could be tweaked by the end-user, i.e. they adjust a value of a cell somewhere, Excel recalculates various formulae and another cell's background color changes to show the effect.
If it was simply a case of testing a value and setting the background while writing the worksheet I could do that directly with WriteExcel.
| [reply] |
As far as I can see, this can only generate what WriteExcel can already do.
Seeing as it's a wrapper around S::WE, I'd be very surprised if it could do something that S::WE couldn't do. However, it can help redefine the problemspace in a different way. But, that's not your issue here.
Now, if you wanted to do a conditional within the formula, there isn't a Perl module that will help ... right now. Maybe this is a space for you to write one? Have a way to help generate the string that can be passed to S::WE based on some templating?
| [reply] |
my $funky = $workbook->add_format( color => 42 );
# ...
$worksheet->write( $i, $j, $val, $val < 0 ? $funky : () );
| [reply]
[d/l] |