Blog

All Blog Posts | Next Post | Previous Post

True Colors

Wednesday, October 7, 2009

Excel and its true colors

Excel 2007 was the most innovative version of Excel since Excel 97. It has new features that are actually useful, and this is no little to say of an application as mature and complete as Excel. What else could you add to something that already does everything, and in fact probably does too much?
But the developers of Excel 2007 managed to come with things like the ribbon, the million rows, the new xml file format, the improved headers and footers or the improved look. My hat is off to them.

Now, everybody uses applications in a different way, and what is a "must have" feature for you might be an annoyance for me. Or viceversa. If you ask me about the ribbon, I am not impressed. In fact, many times I long for the old menus when working in 2007, and wish they had given us a choice to keep them. Million rows? Well, if you really need to use Excel as if it was a database... Xml file format? It is no easier than the old binary format, and in fact, in many parts it is actually more complex. Better headers and footers? Yep, I love that one, but is isn't that earth shaking either. Improved look? Well... now we are talking! For me, that is the feature in Excel 2007.

So I think I now have to explain why I am so exited about the improved look, and not that much about the other features. And I think I will let two images speak for me:
Excel 2003

Excel 2007

Those are two files I created in for this post, one in Excel 2003 and the other in 2007,using the defaults, and that somehow resemble all those other files that we constantly create. To me, Excel 2007 just creates much nicer looking files by default, and also allows you to make nice-looking files easier. And that is much more important than any other new thing, since it is something that will affect every spreadsheet you make, and in a good and visible way.

Attentive readers might have noticed that I included two things under "Improved look". On one side, we have the much better default looking, with nicer default colors, and live templates that allow you to chose between designs created by professionals. And on the other side, we have a much better color management. The improved color management (and how it translates to FlexCel) is what I am going to write about in the rest of this post.

Color modes

In Excel 2003 we had a palette with only 56 colors, and you could only use colors included in it. Even when you could change the palette, it was hard, nobody did it, and you were left with millions of spreadsheets with the same tiring bright yellow and light blue combinations. No shades, no subtle colors, no anything. Bright yellow should be good enough for everybody.

In Excel 2007, now we have three different color modes:

RGB
Themed colors
Indexed colors

RGB is the most basic, but also the most comprehensive color mode, and you might wonder why would you need anything else. I mean, with RGB you can represent any color that you can enter in Excel. Technically, that is all that is needed. But in reality, you might find yourself using RGB very little. As it happens with all "too powerful" methods, RGB colors are also inflexible and difficult to change.

Indexed colors are kept for backwards compatibility with Excel 2003, whatever that is, and even when we fully support them in FlexCel, you shouldn't be using them. Once you end reading this paragraph, just forget I ever mentioned them. But while limiting, indexed colors had a nice advantage over RGB, and that is that you could for example change the palette color "7" from red to blue, and all color "7" in the sheets will change. You can't do that with RGB, once you have set a color as red, it will keep being red.

And so we arrive to themed colors. Themed colors are what you see by default when you choose a color in Excel:

They look similar to the indexed palette in 2003, but that is just in the surface, they are a different beast. The first thing to notice is that there are only 12 themed colors, even if you see 60 of them in the image. In the color selection box, you can see 10 unique themed colors in the first row. Vertically, you have different variations of the same themed color. And the other 2 colors to complete the 12 are related to hyperlinks, so they are not visible in the combo box.

So how does it work? To really understand them, you need to stop thinking in RGB color spaces, and start thinking in HSL. Each color in the theme is represented by a hue and a saturation, and you get different combinations of it chaging the lightness (also called brightness or tint) of the color. In the image above, we are hovering over the "Text 2" themed color, which is "Dark blue", and this particular one is 25% darker than the original theme color (in the top row).

Why did the people who designed all of this go with HSL and not RGB for the theme color definition? Just because HSL is much better for getting colors that play along well.
I would have not enough space in this already long post to convince you in the benefits of HSL, but here there is a link you can read:

http://www.davidarno.org/2008/09/23/hsl-hsv-blowing-the-lid-on-a-designer-secret-slides-and-notes/

FlexCel fully supports HSL colors through the THslColor struct, that can be automatically converted to and from normal System.Drawing.Color. We also support L*a*b* colorspace trough the TLabColor struct in FlexCel.Core namespace, but to keep this simple let's not speak about L*a*b. Just say that L*a*b* colors are better to match the "nearest color", and that we use them to calculate the nearest color index when we need to save color indexes for Excel 2003.

So, to use themed colors you need to specify both which one of the 12 themed colors you are using, and also the tint (or brightness) of the color. Once you have set all colors in a file, you can change the theme, and the same as with indexed colors, all colors in the file will change. But different from indexed colors, the new theme is probably going to look nice same as did the old. Because you change the hue and saturation, but the tint remains the same, so dark colors in theme 1 will be dark colors in theme 2, and so will light colors. You change the colors, but in a sensible way.

How FlexCel implements true colors

The first thing you need to do when moving to 5.0 is to forget about indexed colors. Yes, you still can use them, but you shouldn't.

Now you should start thinking in TExcelColor structs. TExcelColor is a struct that encapsulates all the 3 color modes supported, and can have an indexed color, a themed color or an RGB color inside. It can be converted implicitly from a System.Drawing.Color, so wherever a parameter is a TExcelColor you can write a System.Drawing.Color instead.

So for example, you could write:

Property.Color = TExcelColor.FromRgb(Color.Blue.ToArgb())

but, given the explicit conversion between Color and Excel color, you can just write

Property.Color = Color.Blue;

even when Property.Color expects a TExcelColor, not a Color.

As mentioned above, we also provide TLabColor and THSLColor structures that can be converted implicitly to and from System.Drawing.Color too, and will help you if you want to work in other color spaces.

So now we have set a property with a TExcelColor. What happens when you open this file in an old Excel version like Excel 2003? It doesn't know about this RGB/Theme stuff, it only knows about indexed colors!

What FlexCel does is to convert the TExcelColors to indexed when saving as xls, but also preserving the true color for Excel 2007 in the same xls file. So when Excel 2007 opens the file it will read the true color, and when Excel 2003 opens it, it will read the indexed colors. This is done all transparently for you. While in FlexCel 4.0 you were explicitly setting the color indexes, now you don't have to think about them anymore. You specify the colors, not the colorindexes and let FlexCel calculate colorindexes when saving.

And while we are at it, let me mention that in 5.0 we have improved a lot the color matching algorithm used to convert colors to colorindexes from the old "xls.NearestColorIndex" in 4.0. Also it is worth noting that color matching is not an exact science, and everybody sees colors differently, so some people might think a shade of blue is "nearer" from blue1 than from blue2, while other people might think the opposite. The color matching algorithm in FlexCel 5 is quite complex, but we believe it gives great results.

Well, I think this should have covered the basics. As a last thing, let me remind you that this is just theory so you know what is really going on. In practice things are quite simple: If you are using the API, just fire APIMate and find out the exact commands you need to type. ApiMate will not only tell you the code for setting colors, but also for new features like setting a gradient in a cell. If you are using reports, everything will keep working as it always did, you don't need to change anything.

What about backward compatibility?

Here at FlexCel we are very concerned about keeping 100% backwards compatibility with older versions. To us this is a must, we can't expect you rewrite your reports/code every time you update the FlexCel version. Making sure your code won't break is one of our biggest worries, and if we have to chose between a "not so elegant solution that will keep backward compatibility" and "a really elegant solution that will break old things" we will go with the first option. And even so, for the first time in history in FlexCel.NET, we introduced a big breaking change with the color management for API users (FlexCelReport users won't have to change anything).

Now all "ColorIndex" properties are just "Color", and instead of holding an integer, they hold a TExcelColor object. This was something we thought a lot about, but at the end having a breaking change was the only thing that made sense. The reasons?

Even when it is a breaking change, it will break at compile time. If this wans't true, there was no way we could have done the change. A change that manifests at runtime (either by throwing an exception in code that worked fine in 4.0, or by creating files that are different than what 4.0 created with the same code) is a bug, not a breaking change. We might do this because an error on our side, but never in purpose.

You can have the code compile (and work!) again with a simple search and replace, as mentioned in my last post.

Keeping the old ColorIndex properties, even if deprecated, would be really confusing for new users, and also make our own code much harder and difficult to maintain. There are a lot of ColorIndex properties and we don't want them all hanging around just because we once needed them.

5.0 was the right place to make a breaking change if there is ever a right place. 5.1 wouldn't be a good place, if we discovered later that we really needed to do it.

We wanted your code to break! As explained at the beginning of this article, color management is the feature in Excel 2007, and it would be sad if you don't take advantage of it. Old code like:
```
c.ColorIndex = xls.NearestColorIndex(Color.Magenta);
```
will convert the magenta to the nearest entry in the color palette (let's say it is colorindex 6), and then assign 6 to the ColorIndex property. Now the color in both Excel 2003 and 2007 will be 6, and never magenta. You have destroyed the extra information (color is really magenta, not the color 6) before assigning the property. The replaced code:
```
c.Color = Color.Magenta;
```
even when it looks similar, behaves differently. It will now assign a struct to the property, that has all the information (color is magenta). When saving, but only at save time FlexCel will convert this magenta to colorindex 6 (because Excel 2003 can't show any other thing), but it will also save the real magenta for when you load the file in Excel 2007, or in FlexCel 5 itself.

Closing up

Bringing true colors to your spreadsheets is probably the most important thing about Excel 2007. Taking advantage of that is simple, and the results are really nice. And even when Excel 2003 or older won't be able to show them, Excel 2007 and FlexCel 5 will. The future looks colorful!

Adrian Gallero

This blog post has not received any comments yet.