Page 1 of 1
Produced xlsx file is rejected by EU service unless opened and resaved in Excel
Posted: Tue Jan 10, 2023 2:07 pm
by 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
Re: Produced xlsx file is rejected by EU service unless opened and resaved in Excel
Posted: Wed Jan 11, 2023 10:11 am
by larsa
Hello
What are the error messages?
Re: Produced xlsx file is rejected by EU service unless opened and resaved in Excel
Posted: Wed Jan 11, 2023 2:16 pm
by 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
Re: Produced xlsx file is rejected by EU service unless opened and resaved in Excel
Posted: Mon Jan 23, 2023 12:45 pm
by 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
Re: Produced xlsx file is rejected by EU service unless opened and resaved in Excel
Posted: Thu May 04, 2023 11:27 am
by 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