Blog
All Blog Posts | Next Post | Previous Post
FlexCel 7 has landed!
Wednesday, September 4, 2019
After a long time coming, I am extremely happy to say that FlexCel 7 arrived. And when I say extremely happy I mean it: It has been a long road to travel, but we finally got there. So without losing more time, what is it about?
In a shallow level: It is all about rendering charts in xlsx files. Now you can export xlsx files with charts to PDF or HTML, and they will include the charts.
Going more in deep: It is all about the details. If you look at the what's new for FlexCel 7, you'll see that rendering charts is just the first entry, and after that there is a huge list of changes. Making the charts look perfect required everything else to be perfect too, and so we spent more than a year in a parallel FlexCel 7 branch polishing every little detail. No stone was left unturned. We also gave a lot of needed care to other platforms besides Windows: A lot of the FlexCel .NET code was developed using JetBrains' Rider on a mac using .NET core and Skia, instead of Visual Studio in Windows. On the Delphi side, we developed a lot in Linux and Windows 64 instead of doing everything in Win32.
And going even deeper: It is really about finishing the translation from xls to xlsx. When we released FlexCel 5, we had basic support for xlsx files, but a lot of stuff just worked better if you just kept using xls. Xlsx files were a completely new thing starting from zero, while we already had added great support developed over the years for xls files.
During the lifetime of 5.x and 6.x we kept adding features to our xlsx engine, so by 6.26 there was no reason to use xls files anymore if your customers didn't need them... and if you didn't need to export charts to PDF or HTML. Exporting charts to PDF/HTML was the only thing that required you to fall back to using xls files until now. But the xlsx chart support closes that chapter. From now on, we plan on keeping excellent support for xls (because it is not going away), but our focus will be in xlsx.
Tales from the trenches
Looking back, the number of tweaks and changes was huge, and even if you are not interested in charts at all, I am sure you will find some detail that matters to you and that is just better in FlexCel 7. But what kind of details are we speaking about here? FlexCel 6 was already very good at rendering xls/x files, so what did we make better? Writing about every change would take a book, so that's not something we will do here. But anyway, I'd love to give you an idea of the kind of details we have been working on this full year, besides the chart rendering itself. Below are just some random examples of stuff we did on a day-to-day basis.The disappearing Arial Black
We caught this one because we were doing this particular development using .NET Core and Google's Skia. When rendering a chart, everything was looking nice, but the font on the title was bolder than it should be. In all platforms except Skia the font was ok, but for Skia a different font was used.Investigating, we discovered that if you try to use the font "Arial Black", it will not find it and use Segoe UI instead. To make it work, you need to set it to Arial, and the weight to Black. So now FlexCel 7 workarounds this and also other similar cases like "Arial Narrow" for all platforms using Skia. This is Linux and Android in Delphi, and .NET Core and Android in .NET.
You can read more about it here: https://github.com/mono/SkiaSharp/issues/682
The operation that could destabilize the runtime.
One thing we did in our endless refactoring of the code was to make thousands of read-write fields read-only. We did at the very beginning of the FlexCel 7 development. Now, the first time we tested it in Visual Studio 2019 RC, we started getting a strange error: "Operation could destabilize the runtime".We opened a bug with the Visual Studio team, which you can read here: https://developercommunity.visualstudio.com/content/problem/503750/operation-could-destabilize-the-runtime-and-peveri.html
Sadly, we couldn't get them to fix it: They found it ok that their own compiled assemblies didn't pass their own PEVerify tool, they found it ok that if you were using partial trust just the fact of updating to VS2019 would break your app, and they found it ok to introduce a breaking change like this in a dot release like 7.3.
But we didn't find it ok, so we unmarked a bunch of fields that were causing the issue back to read-write, and added a PEVerify step to our build process to ensure that we never ship an assembly that doesn't pass PEVerify, even if it is not our fault.
The case of the rotated gradients
I remember this one because it happened on Easter and we spend the weekend with some friends. It all started when I noticed some gradients in the charts were not looking right.Here we have a test Excel file with some rotated gradients:
They looked fine when using GDI+ or exporting to PDF in FlexCel:
But when rendering in other platforms, like to macOS' CoreGraphics or Android's Skia, the gradients weren't quite right. Here you can see how they looked in macOS:
Let's ignore that the direction of the line was from bottom to top instead of top to bottom; that is a different bug and tale. As you can see in the red circles, the line was not going from corner to corner as it did in the other cases. What happened was just a different interpretation of the word "rotation". Excel and GDI+ here were understanding a "45-degree rotation" in a rectangle as a line that went from the bottom-left corner of the rectangle to the top-right corner. On the other side, CoreGraphics and Skia were understanding a 45 degree rotation as a line rotated 45 degrees from the horizontal, no matter the shape where it was placed (as you can see in the last screenshot. The line rotated the real 45 degrees, instead of scaling the angle so it went from corner to corner).
It got worse: While Excel normally would draw "scaled gradients", in some test files we had it would draw "actual angle gradients", and that meant that I needed to support both types of gradients (and also reverse-engineer the xls files to find out which bit selected the gradient used, but that was the easy part). So I spent my free time on Eastern calculating the formulas to convert one type of gradient into the other. As I didn't bring my computer with me, this was all done on paper (and really, that kind of stuff is just simpler to do in paper). Here you can see one of the almost 20 pages it took to get to the solution:
I expected that I was over-complicating the problem and that at the end all the equations would cancel themselves, but they didn't. The solution has a bunch of sin and cos that you can't simplify. But the formulas worked just great once I was back home and now FlexCel 7 will render the rotated gradients ok. And yes, the direction in macOS is now top to bottom too.
Side note: As the solution was more complex than I expected, I wondered how the other spreadsheets would render those files. Did they also spend the time to get it right? Here is LibreOffice:
And here is Google Sheets:
And finally, Apple's Numbers:
Ok, nobody seems to care about it, but we do. It is a pattern we see too often, so we've kind of got used to it. But we believe those details matter.
Dark gray is the new black
This one happened while writing the algorithm to resize the plot area the axis captions. For this particular Excel test file:
The axis title at the left was rendering wrong:
That was what the code we were writing, so it was expected. But if you look twice, you might realize that the color or the axis titles is also "pure black" while the other labels are on a dark gray. This made us rewrite the code which read the axis colors so it could handle this specific case.
This is the final file as FlexCel renders it now. Notice how besides correctly resizing the plot area, now the axis are dark gray as they should:
The FlexCel 7 logo
The logo at the start of this post is a scatter chart made in Excel, which I rendered to SVG using FlexCel 7. So the logo is scalable and will zoom if you zoom the page.This is how the original Excel file looks like:
If you look closely, there are a couple of details here:
1. The gridlines in Excel are not crossing the axis at the axis labels (1, 2, 3...) but they rather cross between them (at 1.5, 2.5...). This is because for some reason while I was doing the chart I ended up with a "category" axis for the y axis, instead of a "value" axis as it should be for a scatter chart. And this highlights one of the biggest issues when coding the charts: When you start clicking and dragging in Excel, you might end up doing stuff you don't even know how you did it, and we have to render it correctly. But in this particular case, this is one "feature" we think is not worth fixing. I don't think there is any reason to have the gridlines cross between categories, and it looks just wrong to me. And you can't do it directly from Excel UI either.
2. The line endings are round in Excel, and flat in FlexCel. This is something we knew when we released FlexCel 7, but we couldn't delay the release until it was fixed. However, the world didn't end with FlexCel 7, and we have already fixed it (a couple of days after FlexCel 7 was released). This is how it looks with our development version (once again, as SVG, not png):
And for completeness, this is how it looks in the latest LibreOffice:
In Google Sheets:
And in Apple's Numbers:
As it is the usual case, LibreOffice and Apple's Numbers don't get all the details right, but at least they make an effort. Google Sheets doesn't seem to even try. And notice that no one tried to copy the Excel behavior of printing the gridlines between the axis labels. This is one thing we all can agree on.
Extra: Just for fun, this is how the chart looks in Excel itself, if you save it as xls and reopen it:
Note how when converting the chart it "fixes" the category axis and converts it to a value axis. It now shows the gridline for 9 at 9, and the gridlines at 0 and 10 show exactly like in FlexCel. This reassures us that the "gridlines between labels" thing isn't a bug worth fixing.
The axis that went below and under the data.
This is another case that we discovered a little after releasing FlexCel 7, and which we have already fixed internally here. We have this chart in Excel:
Which you can see like this in:
Libre office:
Google Sheets:
Apple's Numbers:
FlexCel was drawing it almost like Excel, but with the error bars below the axis instead of over them (the axis are the one in yellow and the one in red). Ok, simple fix: Just switch the order in which we draw in the code. This is how it looks now with the yet unreleased dev version:
Easy, isn't it?
Well, not quite. One nice thing about FlexCel (but also one of the things that slowed us down more doing the development) is that we have a huge set of thousands of files that we created or our customers sent to us for including in the test suite during the almost 20 years of FlexCel history. Those are a lot of files. And whenever we make a change like the one here, we need to make sure we didn't break anything on any of those files.
Here, the most obvious error immediately jumped when we run the test suite: Yes, the axis should be drawn below the data, but the axis labels (the 0, 1, 2... text on the axis)... well, those go over the data. We wouldn't notice it in this particular file, but there were hundreds of files where it was easily visible. So we had to split the DrawAxis method in two: The method that originally drew the axis and text now is one method that draws the lines (and that runs earlier) and another that draws the labels (and that runs after the data has been plotted).
Once we fixed that, we run it again... and we found a different problem. Now with the axis switched, the line charts were ok, but bar charts were wrong. (and area charts too, but we found that later). It looks like Excel draws the axis over the data in bar charts, but under the data in line charts.
Here is an example of how Excel draws a chart with a bar and a line:
Look at the horizontal axis line (the one in the 0 value). You'll see it goes over the bar lines, but below the line chart. So once again, we had to go back and change the code until everything was fine. This is how the chart renders in FlexCel right now:
You might notice that we also made the line inside the legend a little wider than in the released FlexCel version, so it matches Excel more closely. And now, once again for the sake of completeness:
Libre office:
As you can see, LibreOffice just draws all over the axis.
Google Sheets:
As is usually the case with google sheets, I am not even sure on what they were trying to do here (And this is one of the charts they got better). But what I am sure is that if we had the quality standards google sheets has for charts, we would have shipped xlsx chart support a year ago.
Apple's Numbers:
Apple's Numbers also draws everything over the axis.
Wrapping it up
I literally could keep writing about this stuff for days. After all, we developed the xlsx chart engine for more than a year until it became something that would not embarrass us, and every day we had one a different story like the ones in this post. Without going further, the last two tales happened in the couple of days after we released FlexCel 7. I wrote about them because they are fresher in my mind, but a thousand others happened while we were making the chart engine.I could show you hundreds of screenshots of test files, show you how they look in Excel, FlexCel, and the others, and some can be really fun, but this is long enough already. It will be more interesting if you try it with your own charts, anyway.
What I wanted to explain in this post, in any case, is why it took us so long to deliver this chart support. And when you add all the day-to-day stuff and support questions answered, I believe it starts to make more sense. You also have to remember that we were developing both FlexCel 7 and 6 in parallel, backporting the fixes from one to the other. So when you look at what was done the last year, you don't have to stop at FlexCel 6.26. To see all that was done since August last year, you have to keep scrolling up to 6.21.5. It was for sure a busy year, as have been all the others, and as will probably be in the future too.
Now, a bigger question might be forming on your mind: Does it matter? After all, it is just a bunch of lines over a canvas, isn't it? Couldn't we have released something that looked remotely like an Excel chart long ago and call it a day? And does it really matter if we draw the axis over or under the data?
I think it does matter. A single detail might not matter much, but when you have 100 small details wrong, the charts start looking nothing like the real thing. And when you look at the "creative" ways people in which people use Excel, it gets worse. I've seen an "odometer" implemented as a transparent pie chart over a png. Move the pie just a little to the left or the right -not even do anything like the atrocities that google sheets and the others do- just move the pie a little to the left, and the odometer will look like trash. I've seen a way to "break" an axis by using a marker with a bitmap, like this:
If you write the axis over the marker and not the other way around, the effect is lost. A single detail, but it breaks everything.
We don't want our customers to beta test us. I find it disrespectful to just send them some code that vaguely draws some lines on the screen and call that "chart rendering support". If we offer you chart rendering support, we should give you something that resembles chart rendering support. Period. But won't there be bugs, anyway? Yes, of course. However, we will at least make our best effort to minimize them.
What's next?
Ok, this was all fun to write, but FlexCel 7 was already released, and so question becomes "what is coming next?" And the answer is "a lot". In the short term, we are still polishing little details in the chart engine, which will ship in a near update. We will also be providing a full API to write charts, with APIMate support, which was originally planned for FlexCel 7 too, but we had to delay it to ship the rendering engine. Most of that work is done, but we have to polish it up before we can make it public.In the medium term, we'll keep obsessing over every small detail as always, but there is also bigger stuff brewing which I can't wait to show you. With the FlexCel 7 release we arrived at a place where there isn't that much left to add, and that gives us time to focus on newer things. There are interesting times ahead.
Adrian Gallero
This blog post has received 2 comments.
2. Thursday, September 12, 2019 at 11:50:16 AM
Hi,Sorry, not at the moment. To be honest it has been like a year since I last checked out fpc, maybe it is time to look at it again.
Last time we checked it, they were missing some needed functionality like anonymous methods, there was some issues with the strings being utf8 or 16, and there were many internal compiler errors trying to compile the code. We actually made it compile with a lot of effort, conditional defines, and commenting out big parts of the code, but it was just not working right. I''ll have to check if now the situation is different.
But the other thing that happens is that we are working in FlexCel for C/C++ (also delayed because FlexCel 7 took too much time). If FlexCel C++ is ready before we are able to compile with fpc, we are considering the possibility to provide the C libs and pascal wrappers so you would be able to use FlexCel C++ from lazarus exactly the same way you can use FlexCel for VCL in Delphi now, even if the engine itself is in C++11 instead of pascal.
Adrian Gallero
All Blog Posts | Next Post | Previous Post
Rikalo Zeljan