How to add color scales to ODF?

Markus Mohrhard markus.mohrhard at
Sat May 12 21:55:33 PDT 2012


2012/5/12 Stefan Knorr (Astron) <heinzlesspam at>:
> Only sent this to Kohei... sending to list, too, now.
> On 12 May 2012 12:09, Stefan Knorr (Astron) <heinzlesspam at> wrote:
>> Hi all,
>> On 11 May 2012 17:50, Kohei Yoshida <kohei.yoshida at> wrote:
>>> On Fri, May 11, 2012 at 8:53 AM, Michael Stahl <mstahl at> wrote:
>>>> i wonder if that restriction is really necessary.
>>> IMO it is.  Imagine a case where the same color scale definition is
>>> applied to non-contiguous regions, and you having to decide whether to
>>> scale those regions as if they are unified, or treat them as
>>> independent ranges (therefore independent scaling).  Having that

I'm not sure that I understand the problem here so I will just explain
what is implemented ( we can of course force limitations). I've used a
ScRangeList that keeps the range(s) where the color scale is applied.
So in theory it is possible to have a range like A1:B4;C8:F12 for one
color scale. But this is only intended for the reason that you might
want to select an non continuous range. It does not mean that we can
use the same color scale entry in the file for different ranges.
That means if you define a color scale for one range/range list and
later for another one with the same information we can't use the same
color scale entry. That is the main reason why I think that we need an
independent entry where we can also add the range( list). The range is
an integral part of a color scale and normally with "Duplicate value"
settings also for conditional formatting. Honestly I think that styles
are totally misused in the context of conditional formatting.

>> Interestingly, MSO 2010 has a feature whereby it automatically adds
>> neighbouring cells to conditionally formatted ranges. For instance,
>> you can define A5:B7 as the range for the format. If you then click
>> into an empty cell at the bottom or right of your defined range (ex:
>> cell A8) and add a value to it, the range will automatically become
>> A5:B7;A8 [1]. So, while the two-or-more cells restriction still
>> applies[2], the continuous range requirement seems like it is reducing
>> Excel compatibility, to me (for better or worse).

We would be able to do the same thing with my current implementation.
The only question is if we can implement it easily in the calc core
without loosing to much performance in big sheets. I  have some xlsx
test documents exactly for this case.

>> [2] I believe what happens when when you set a colour scale for only
>> one cell in Excel and use extrema/percentages/percentiles, is that it
>> just isn't coloured at all.

No it colors the cell as do we. We just choose different colors for
that. Excel uses the color for the maximum number and we use the color
for the minimum number. I will need to think about solutions for this


P.S. If anyone actually understands the Excel percentile behavior it
would be great if he'd ping me.This is the only missing point in xlsx
import yet.

More information about the LibreOffice mailing list