Since FlexCel 7.27.1, every xlsx/xlsm file saved with Protection.OpenPassword is rejected by Excel with "The file is corrupt and cannot be opened." The same code with FlexCel 7.26.0 produces files that Excel opens normally.
My Environment:
TMS FlexCel Studio for VCL and FMX 7.27.1 (TMS Smart Setup), RAD Studio 13 (Delphi 37.0), Win64 and Win32
Microsoft Excel 16.0.20430.20092 (Microsoft 365), Windows 11
Every xlsx encryption algorithm is affected: AES_128 (default), AES_192, AES_256
Code to reproduce:
program FlexCelAgileRepro; {$APPTYPE CONSOLE} uses System.SysUtils, VCL.FlexCel.Core, FlexCel.XlsAdapter; var xls: TXlsFile; begin xls := TXlsFile.Create(True); try xls.NewFile(1); xls.SetCellValue(1, 1, 'test'); xls.Protection.OpenPassword := 'test'; xls.Save('encrypted.xlsx'); finally xls.Free; end; end.
Hi,
Thanks for informing us. Indeed, it was a group of stuff that could go wrong, all going wrong at the same time.
We use more modern encryption algorithms in 7.27, which have a bigger key size. Those keys are saved as base64, and being bigger, they are broken with a CRLF in Delphi. This causes Excel to crash.
In FlexCel .NET, where we manually opened the reference files to test them, it didn't happen.
In FlexCel VCL, the comparer that compares encrypted files with the references generated by FlexCel.NET, reported that they were the same, because they were. The comparer for encrypted files doesn't do a byte-to-byte comparison as the others, because it would be meaningless (as a single byte changes every byte in the encrypted file). So the comparer unencrypts it first, and when it does, it gets the same data as in the reference files. Because this is a bug in Excel's xml parser.
We've fixed the bug, and will be releasing a new version as soon as the build is ready, all tests are passing, and we manually verify that Excel can some hundreds of files in our tests.