Blog

All Blog Posts | Next Post | Previous Post

FlexCel .NET 5.0 is out!

Friday, September 4, 2009

Finally, after so many months of intense development, FlexCel 5.0 for .NET is out. This means, among other things, that I get some time for blogging and updating you guys on how things are going.
I plan to make a small series of posts with fun and weird stuff that happened during its development, and that we just couldn't comment at the moment because we were too busy coding, testing, re coding and retesting. And attentive readers should notice that I said "I plan". Will those posts actually be written? Well, of course it depends on how soon we are struck in the deadline for FlexCel 5.1 (and FlexCel for VCL too, it has not been forgotten). But the intention is there, and I will try to stick to it.
Now, for this post I would like to address some random stuff, in no particular order:

Is FlexCel 5 a complete implementation of XLSX?

Sadly not. It covers all the basics, cell values, formatting, formulas, print setup, merged cells, etc. But it won't load images, charts, objects or conditional formats. Why? The short answer is that there is a lot of people that can use this right now, and it didn't made sense to wait until we have complete support of every feature under the sun to make a release.

A longer answer has to be with the way we develop. Instead of "superficial" coding style, where you write a "beta" that has thousands of features and thousands of bugs, we use an "in depth" development approach. That is, we start with a small feature, test it, re test it, find border cases, make sure it works as good as possible, create "torturing" tests to make sure it won't break when we develop something else, and only then we move to the next. We grow one feature at a time.
Probably from a commercial point of view it doesn't make much sense, but this is just how we work. Did you know for example that the "=Dollar" function behaves differently in Excel 2007 than it does in Excel 2003? And that FlexCel will output different results when calculating depending if it is in Excel 2003 to 2007 mode?

I do understand that probably for most customers it might have been preferable that we had spent time in a more "shallow" programming where everything "kind of" works but there are lots of glitchs in everything. But as said, that is just not how we work. When we code a feature, we make sure it works as expected. So "formulas" is a feature in 5.0, and that includes the fact that "=dollar" behaves differently, and also includes implementing array formulas or "what if" tables. (even when they are not really that used, and we could have used that time implementing more popular features)

Of course no software is 100% bug free, and we do get bug reports from time to time as everybody else, but we try to make sure you won't run into unexpected issues with the supported parts. And here I will tell you a little story: When we were evaluating whether to do our first FlexCel for .NET version, there was a big customer constantly asking us when we were going to do it. I had a good relationship with that customer (in fact I later traveled to Australia to do some consulting work for them), so I asked them, sincerely, why they were so interested in a FlexCel .NET version from us, when there were already other solutions in the market with more features that FlexCel for VCL had. Their answer? "Well, the reason we want FlexCel is that it actually works". And having things actually work has always been our main objective, now and then.

Should I migrate all my xls reports to xlsx right away?

This is of course a personal decision, but my recommendation would be: If you don't need more than 65536 rows or 256 columns, don't migrate yet. As said above xlsx support is not yet complete, but also it is not as mature as xls support. No matter how much we tested (and we did test a lot), xls file format support has many years of stability, and of being tested in real world situations by thousands of customers.

Also most other features in xlsx except the grid size; like true color, or a different page header/footer for odd/even pages are supported also in xls. So you can create those nice reports in Excel 2007, using Excel 2007 features, and then save them as xls. FlexCel will use this new features, even if the file format is not xlsx.

And if you *do* need more than 65536, rows, you can probably migrate those reports to xlsx. They probably won't be so complex, because such a huge file is not normally created for human consumption and they don't need things like images or much formatting.

How long until 5.1?

FlexCel development is slow. As said before, this has a lot to do with the way we develop, writing some lines of code, writing some test, retesting, refactoring some other code... quality just takes time, and there is no way around it.
But this doesn't mean that it will take another year to get to 5.1. Actually because the way we develop, constantly cleaning rewriting and and refactoring old code, adding new features is easier than if we had a an old code base that never was always patched instead of rewritten. The rewriting takes more time, but it also makes it easier to keep updating the code. Version 5.0 was a big change in the core parts of FlexCel, because we had to change the way it works, and so it took a lot of time. But this should be an exception rather than the norm .

With respect of why we had to rewrite the code; FlexCel 4.x worked like this: Open a binary xls file, modify its contents, and write it back. This was fine for supporting a single file format, but the approach turns out impossible when you have to support two completely different ones. Now FlexCel has to actually understand every record it is reading and writing, so if can write it to xls or xlsx depending on the file format you choose to save. And luckily, Microsoft released new documentation of the xls file format, so we could actually implement a component that understands every record, this wouldn't have been possible two years ago.

Now that the core has been rewritten new features should follow faster, and we expect to go back to the "1-2" months release cycle we had before in the 4.x series.

Why .NET 3.5 for xlsx?

You should really ask Microsoft this one. For some reason only known to the guys creating .NET (and maybe not even to them) Microsoft didn't add zip file support until .NET 3.0, and xlsx files are zip files.

So, we were faced with the decision to implement some new dirty hacks to support zip in .NET 2.0, or just require 3.5. At the time I am writing this, I think requiring 3.5 was the right choice. .NET 3.5 is just .NET 2.0 + added classes, the core is exactly the same, so you can upgrade your applications, not matter how complex, without fear of breaking anything. Just because you will be using the same core dlls as in 2.0. This is not a jump as it could have been moving form .NET 1.1 to 2.0 or what will be moving from .NET 2/3 to 4.

We might revise this decision in the future (if many of you write to me asking for it...), but right now I think we better spend this time in adding new features. If you need to support the newest file formats like xlsx, I think it is reasonable to ask for newer versions of the framework. Supporting old versions adds a lot of burden to the code base, that probably will be obsolete soon anyway as sooner or later we will be migrating from 2.0.

What breaking changes?

For all the changes in the core engine, we managed to keep the interface almost unchanged, and this was no small work. The actual changes needed are described in the "API Guide" ("Considerations about Excel 2007 support" section) and you should give that a read. But I want to discuss here the only likely issue you are going to find:
ColorIndexes have been converted to Colors. I will be doing other post with an in depth explanation of the changes, what they mean, the HSL/RGB colorspaces used, how the conversion is done from indexed colors to true color, why this all was done and much more. But by the moment I will just answer the burning question: "How do I get my code to compile again?"

The short answer is, you need to change:

x.ColorIndex = xls.NearestColorIndex(Color.Blue);

x.Color = Color.Blue;

You can also of course do much more than this simple conversion, like using color themes or adding a gradient background to a cell. And you can use APIMate to find out what the exact code is to add a gradient or a themed color with tint.

But for the "simple" task of getting the code back compiling, you can use the following regular expression search and replace:

Search expression: ColorIndex = xls.NearestColorIndex\({.*}\)
Replace expression: Color = \1

Just change the "xls" part in the expression to whatever your XlsFile object is named.

What about FlexCel for VCL?

FlexCel for VCL codebase is converted from .NET codebase, with an internally modified Mono compiler. This modified compiler compiles C# code to Delphi code instead of MSIL, and it works amazingly well. We have been using it for some time in all the new things added to FlexCel for VCL. So we needed to finish FlexCel for .NET first in order to backport the code. It is early right now to say how long until it is finished, but porting around 150,000 lines of code is no small task, even if automated. I can't make promises, but the work is being done.

Adrian Gallero

This blog post has not received any comments yet.