Recent

Author Topic: How to ignore conditional formatting when not supported by FPSpreadsheet  (Read 2988 times)

MAndreato

  • New member
  • *
  • Posts: 8
Attached there are:
  • a simple Test.xlsx: on A1 there is just “a” plus a conditional formatting rule to evidence that A1 is equal to "a" (the default format is red filling with dark red text)
  • a little project: on starting it opens the Test.xlsx and when closing it saves the same data into Test2.xlsx

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.

wp

  • Hero Member
  • *****
  • Posts: 8398
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).
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

MAndreato

  • New member
  • *
  • Posts: 8
Thanks wp, but testing with the more complex attached xlsx still produces an invalid Test2.xlsx.
(maybe it's not related to conditional formatting)

wp

  • Hero Member
  • *****
  • Posts: 8398
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.
« Last Edit: May 19, 2021, 10:03:36 pm by wp »
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

MAndreato

  • New member
  • *
  • Posts: 8
Well done!
Now we're reaching the next level  :D --> see attached

wp

  • Hero Member
  • *****
  • Posts: 8398
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.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

MAndreato

  • New member
  • *
  • Posts: 8
[SOLVED] I confirm that with last SVN the app produces correct conditional formatted xlsx.
Thank you wp!

 

TinyPortal © 2005-2018