TAdvGridExcelIO Excel: File Error - Some number formats may have been lost

VCL TMS VCL UI Pack
1

Hi,
TMS VCl UI Pack 13.0.4.0

When using TAdvGridExcelIO to export a grid with datetime values the following message is shown when opening the file and the datetime ends up as a double number in the generated file:
Excel: File Error - Some number formats may have been lost

The problem happens with Delphi 11.
When using Delphi 10.4 update 2 there is no problem.

I have the issue on Windows 10 and 11.
I am using Excel 2016 to open the files.
Application is built in x32.

Issue can be reproduced with this code:

procedure TForm1.Button1Click(Sender: TObject);
var
  excelIO: TAdvGridExcelIO;
begin
  AdvStringGrid1.Cells[1,1] := DateTimeToStr(Now);

  AdvStringGrid1.BeginUpdate;
  excelIO := TAdvGridExcelIO.Create(nil);
  try
    excelIO.Options.ExportShowInExcel := True;
    excelIO.Options.ExportOverwrite := omAlways;
    excelIO.AdvStringGrid := AdvStringGrid1;
    excelIO.XLSExport('...some location...');
  finally
    excelIO.Free;
    AdvStringGrid1.EndUpdate;
  end;
end;

What I get when working properly: 2023-11-22 17:54
What I get instead: 45252,7106

Not sure if this is related but maybe this RSP might be of interest to help in your investigation:
https://quality.embarcadero.com/browse/RSP-35691

Hopefully this can be fixed.

Thanks

2

You might need to check date separator value and / or control the formatting for XLS output via the OnCellFormat event as explained at:

https://www.tmssoftware.com/site/asg56.asp

3

Hi,
we have debugged your code and there is really an issue that must be fixed on your end. Yes we can specify a format but that only hides the real problem. We are talking about code that was working fine before Delphi 11. Let me explain.

The problem arises when dealing with time format. Delphi supports minutes as nn or mm (mm works only if it is found after hh, else is is the month so technically the correct way is nn).
However it seems your excel utility was coded taking for granted the time part would always be mm.

In Delphi 10.4 the default time format is hh:mm
Embarcadero decided to change this in Delphi 11 and the default time format is now hh:nn . They changed and "fixed" a few "bugs" in Delphi 11 regarding datetime parsing and this is having side effects on existing code. This would be part of the changes.

Excel only understands mm for minutes. It will give an error if you try to put nn in a cell format.

I will skip right at the conclusion or else my post would be too long but if you look at both of those tms units:
tmsUXlsXF and tmsUXlsStrings

In Delphi 10.4 TFormatRecord.CreateFromData receives the Fmt param as 'yyyy/MM/dd hh:mm'

In Delphi 11 TFormatRecord.CreateFromData receives the Fmt param as 'yyyy/mm/dd hh:nn' (nn instead of mm for minutes)

So when TExcelString.Create is called from TFormatRecord.CreateFromData
In Delphi 11 it tries to specify the invalid Excel format of 'yyyy/mm/dd hh:nn'.
The proof that is is invalid is that if you try to create a cell format with nn from Excel it will give an error. Also, if you modify the ShortData variable at the end of TExcelString.Create to change the nn part to mm it works.

So it seems you need to find a way to translate the nn to mm somewhere in your code to compensate for the fact that the minute part or the Delphi format can now be nn. Otherwise it means all your customers have to start specifying a format for no other reason than to sidestep this newly introduced breaking change.

The problem exists for both datetime and time values without a date part.

Hopefully you are willing to fix this.

Thanks

5

We'll check this. The main issue is that Excel doesn't have a "nn" modifier, so it is impossible to map. I mean, you could have a format that is "nn": this will be the minutes. But if you save this as "mm" to Excel, it will be the month, as Excel's mm behaves the same as Delphi's mm (it is only the minutes if it is preceded by hh)

Given that this was a change in the default settings we will try to see what can be done, but in general, if you used "nn" it is likely because "mm" didn't work, and it won't work in Excel anyway.

6

Hello,
Good, we both agree that Excel does not support nn for minutes. This is why I suggested you need to implement a translation that ultimately switches nn to mm before injecting the format to the Excel cell.

But I must point out that we do not use either nn or mm. We do not specify anything and let the TMS component do it's work. We did not start using nn because mm did not work.

I am not writing this to me annoying but just so that it is clear that the issue is between your component and the Delphi rtl. It has nothing to do with the way we are using the component because it's default settings are used.

Thank you for taking the time to look into this.

7

Yes, I was pointing that converting nn to mm is wrong. It will work only if nn is before hh or after ss. That's why it wasn't originally converted. Better fail when something isn't fully supported, than convert minutes (in delphi) to months (in Excel) without telling you.

Yes, I understood that too, and in your case, converting nn to mm makes complete sense. What I was saying is that for other users who started using nn because mm didn't work, this solution just makes it worse (it will silently show the wrong number). But I honestly don't see much other alternatives than to convert nn to mm, so I think we'll do that. As said, we are looking into it.

I am not writing this to me annoying but just so that it is clear that the issue is between your component and the Delphi rtl. It has nothing to do with the way we are using the component because it's default settings are used.

I understood and I thank you for taking the time to investigate and write this report. We are going to fix it somehow. (likely converting nn to mm, but we'll investigate if there isn'a better way, because that conversion is risky. The reason nn exists is so you can use it where mm doesn't work, and in Excel it will still not work)

8

Just to let you know that this has been fixed internally, it will be available in the next release of vcl ui pack

1 Like