Lazarus

Programming => Packages and Libraries => FPSpreadsheet => Topic started by: MAndreato on May 17, 2021, 01:07:28 pm

Title: How to ignore conditional formatting when not supported by FPSpreadsheet
Post by: MAndreato on May 17, 2021, 01:07:28 pm
Attached there are:

Conditional formatting is not yet supported for Excel OOXML on the stable version 1.12 of FPSpreadsheet, so if you compile and run the project, Test2.xlsx will contain only unformatted “a” – actually that’s fine for me!
However, if I try the app with the latest FPSpreadsheet snapshot; when closing, it raises "Writing conditional font not supported by XLSX writer".
This is because xlsxooxml.pas has the new TsSpreadOOXMLWriter.WriteDifferentialFormats method but it's incomplete.

How can I “bypass” the new incomplete feature?
I tried to comment the call to WriteDifferentialFormats in TsSpreadOOXMLWriter.WriteGlobalFiles but it produces a malformed Test2.xlsx.
Title: Re: How to ignore conditional formatting when not supported by FPSpreadsheet
Post by: wp on May 18, 2021, 01:06:25 pm
Now I looked into conditional formats again, and found that fpspreadsheet writes empty font names to the xlsx files in case of conditional formatting which Excel does not seem to like. After fixing this the conditional font formats can be read be Excel without any issues, and also your test program works correctly.

Get yourself the latest fpspreadsheet from ccr (snapshot or svn).
Title: Re: How to ignore conditional formatting when not supported by FPSpreadsheet
Post by: MAndreato on May 18, 2021, 01:53:53 pm
Thanks wp, but testing with the more complex attached xlsx still produces an invalid Test2.xlsx.
(maybe it's not related to conditional formatting)
Title: Re: How to ignore conditional formatting when not supported by FPSpreadsheet
Post by: wp on May 19, 2021, 07:59:35 pm
That was hard stuff! The xlsx file must have the <conditionalFormattings> node before the <hyperlinks> node - they had been written in reverse order...

Please test the new revision in ccr.
Title: Re: How to ignore conditional formatting when not supported by FPSpreadsheet
Post by: MAndreato on May 19, 2021, 10:20:09 pm
Well done!
Now we're reaching the next level  :D --> see attached
Title: Re: How to ignore conditional formatting when not supported by FPSpreadsheet
Post by: wp on May 19, 2021, 11:02:29 pm
This one was easy: the xlsx (and ods) writers were using the local format settings for writing floating point values to expressions for conditional formats.
Title: Re: How to ignore conditional formatting when not supported by FPSpreadsheet
Post by: MAndreato on May 20, 2021, 07:53:05 am
[SOLVED] I confirm that with last SVN the app produces correct conditional formatted xlsx.
Thank you wp!
TinyPortal © 2005-2018