Produced xlsx file is rejected by EU service unless opened and resaved in Excel

[Midiar]

I'm producing an xlsx file that will be imported into an EU service.
The problem is that the receiving service outputs a number of error messages about missing values.
The values are not missing, and after I open the xlsx in Excel, and then save it again from there, it is imported without problems.

I tried the following, because I had done something similar in XLSReadWrite v4:

Code: Select all

    xls.CalcDimensions;
    for i := 0 to xls.Count - 1 do
    begin
      xls.Sheets[i].CalcDimensionsEx;
      xls.Sheets[i].Protection.Clear;
    end;

Any tips on how to tweak the object before saving?
(XLSReadWrite v6.01.10 on Delphi 2010, but the same happens in Delphi 6)

::tor

[larsa]

Hello

What are the error messages?

[Midiar]

These are the error message from the EU service, all very application specifi:

'Title of the project' - Compulsory field. Maximum length is 500 characters.
'Objectives of the project' - Compulsory field. Maximum length is 2500 characters.
'Potential benefits likely to derive from this project' - Compulsory field. Maximum length is 2500 characters.
'In what procedures will the animals typically be used' - Compulsory field. Maximum length is 2500 characters.
'Expected impacts/adverse effects on the animals' - Compulsory field. Maximum length is 2500 characters.
'Reasons for the planned fate of the animals after the procedure' - Compulsory field. Maximum length is 2500 characters.
'1. Replacement' - Compulsory field. Maximum length is 2500 characters.
'2. Reduction' - Compulsory field. Maximum length is 2500 characters.
'3. Refinement' - Compulsory field. Maximum length is 2500 characters.
'Explain the choice of species and the related life stages' - Compulsory field. Maximum length is 2500 characters.
'Duration of the project' - Compulsory field. Must be a whole number between 1 and 60.
'Keywords' - At least one Keyword must be provided (non-empty text).

I then open the xlsx file in Excel, and save it. Then the import succeeds with no validation errors.
I have sent both files to components@axolot.com, the failing one, and the succeeding one. They only have dummy data.

::tor

[Midiar]

Thanks, Lars, for replying to my email.

It all turned out to be about formula calculation.
The following 2 lines before saving the file, fixed it:

Code: Select all

    xls.CompileFormulas;
    xls.Formulas.CalculateAndVerify;

::tor

[Midiar]

I'd like to revisit this post of mine, for clarity:

As mentioned, the last change (CompileFormulas+CalculateAndVerify) makes sure that the EU service accepts the calculated cells.
But as it turns out that change also makes Excel complain that the file is corrupt.

The problem was probably something related to extensive use of formulae and defined labels to implement translation of the whole xlsx file,
combined with strict lockdown of cells and sheets.
It was solved by opening the template xlsx file in Google, and then saving it there, unlocking the protection.
After that Excel stopped complaining about the resulting, filled-in file -- and the EU service also accepts the file.

::tor