Bug in ChartAPI demo

FLX TMS FlexCel for VCL & FMX
1

Using the latest version (7.9 downloaded 2 days ago. I tried running the ChartAPI demo which worked fine in 7.8. Now when I create the Excel file with the chart and let the demo open the file in Excel I get a warning that the file is broken.

image
image917×116 3.37 KB

If I let Excel "repair" the file it deletes the entire graph.

If I comment out the 3 lines (uChartAPI.pas lines 97...)

TextAttributes := TDrawingTextAttributes.Create(nil, 'en-US', '', 16, TRUE, nil,
NullableTDrawingUnderlineStyle.Null, NullableTDrawingTextStrike.Null, nil,
NullableTDrawingTextCapitalization.Null, NullableTDrawingCoordinate.Null,
nil, nil, nil, false, false, false, 0, '');
TextFill := TSolidFill_Create(TDrawingColor.FromRgb($80, $80, $80));
RunProperties := TDrawingTextProperties.Create(TextFill,
nil,
nil,
nil,
TDrawingUnderline.Null,
TThemeTextFont.Create('Calibri Light'),
nil,
nil,
nil,
TDrawingHyperlink.Null,
TDrawingHyperlink.Null,
false,
TextAttributes);

...then the Excel file loads fine, so I guess the defaults for the RunProperties are "safe", but something about the settings in the demo are the problem.

Any ideas?

2

Hi,
Sorry about that, the demo is wrong. The problem is the line:
TextAttributes := TDrawingTextAttributes.Create(nil, 'en-US', '', 16, TRUE, nil,

It should be:
TextAttributes := TDrawingTextAttributes.Create(nil, 'en-US', '', 1600, TRUE, nil,

Because the size (see TDrawingTextAttributes.Size Property | FlexCel Studio for VCL and FireMonkey documentation ) is in 1/100 of point. So a size of 16 means 0.016 points, which seems to crash Excel. And as it happens with this things, when Excel finds a number it can't handle, it will "recover" the file by deleting the full thing (in this case the chart), not just ignoring the number it couldn't parse.

The problem appeared because in 7.8 we used a normal "TRichString" which is the one used in xls files and we hadn't had time to update it. But in 7.9 we updated it to a TDrawingRichString, which is what xlsx uses, and ensures no information is lost in xlsx files (like the original problem you had where text links wouldn't be preserved, because they aren't in the TRichString, but they exist in TDrawingRichString).

For what it is worth, in a TRichString the size is divided by 20 (Excel is just like that), and the line was:

fnt.Name := 'Calibri Light';
 fnt.Size20 := 320;
 fnt.Color := TExcelColor.FromArgb($80, $80, $80);

Somehow, when we updated the demo we updated it to a size of 16, not 1600. We did update it correctly in the FlexCel.NET demo, so it was just an oversight.

By the way, we could check if the number is "too small" that it will cause Excel to crash (like 16 in this case), but as this isn't a documented limitation, we prefer not to. In the past we have added many restrictions like that because they crashed the Excel of the day, but then later Excel fixes the limitation, and we keep it when we shouldn't.

So in short, replace the 16 by 1600 and it should work fine. And make sure to not use too small values in the font size. Too small values crash Excel, but that's really a bug in Excel. LibreOffice handles the file just fine, as does of course FlexCel. You can see the title in all its diminute size in the previewer here:

image
image1442×906 95.7 KB

3

Adrian,

Thank you. That does indeed resolve it.

It also made me think of a slightly cleaner solution for the method I use to set up axes. It seems a bit wrong to be passing font size for the label in *100 and for the tick label font in *20. So now I pass a double of the actual point size (e.g. 12.0 pt) and do make the scaling essentially transparent.

4

Yes, for your wrappers it is best to use the same font size everywhere, by multiplying and dividing. We don't do it since we want to give you low-level access to the raw data that is written in the file, and units inside xls/x files are a mix from stuff from 30 years ago with stuff added last week.