Excel not accepting xlsx file with styles written by fpspreadsheet

[wp]

Trying to add font support to the xlsx file format in fpspreadsheet I am stuck with the problem that Excel2007 complains about unreadable content in the file written by fpspreadsheet (styles section), repairs it and then shows the correct file. When I close Excel an instance remains running and has be be killed in task manager. LibreOffice, on the other hand, shows the file correctly without any further complaints.

I checked the xml files again and again, and cannot find the issue, but I am sure that it must be a trivial cause. Maybe there's someone out there who has more experience with xlsx than me.

The attached zip contains the "test.xlsx" file written by spreadsheet, as well as the simple project which wrote the file along with my development version of xlsxooxml.pas which has to be copied into current sources of fpspreadsheet (recent trunk). The demo writes dummy text in a few cells using various fonts and text colors.

[Mike.Cornflake]

Found the broken xml in style.xml, no idea where in code to look...

Code: [Select]

<cellXfs count="9">
      <xf numFmtId="0" fontId="0" xfId="0" fillId="0" borderId="0" />
      <xf numFmtId="0" fontId="1" applyFont="1" xfId="0" fillId="0" borderId="0" />
      <xf numFmtId="0" fontId="1" applyFont="1" xfId="0" fillId="0" borderId="0" /> 
      <xf numFmtId="0" fontId="6" applyFont="1" xfId="0" fillId="0" borderId="0" />
      <xf numFmtId="0" fontId="7" applyFont="1" xfId="0" fillId="0" borderId="0" />
      <xf numFmtId="0" fontId="8" applyFont="1" xfId="0" fillId="0" borderId="0" />
      <xf numFmtId="0" fontId="9" applyFont="1" xfId="0" fillId="0" borderId="0" />
      <xf numFmtId="0" fontId="10" applyFont="1" xfId="0" fillId="0" borderId="0" />
      <xf numFmtId="0" fontId="11" applyFont="1" xfId="0" fillId="0" borderId="0" />  <---  fontId is 0 based, and you've only 11 Fonts defined. 
   </cellXfs>
I've confirmed that manually changing fontId="11" to fontId="1", then recompressing results in a valid xlsx.   

I'll see if I can find the relevant code...

UPDATE:  I'm no longer sure the relevant code is in xlsxooxml.pas.  Looks like .FontIndex is built up elsewhere.    I've been meaning to use fpspreadsheet, I'll download tonight...

UPDATE2: I was curious as to why LibreOffice could open this with no problems.  Looking at sheet1.xml, I see you're not referencing the xf with the broken fontId, so LibreOffice probably doesn't notice the issue.

And I've just realised this is development code, there's probably no use in me downloading the latest...

[wp]

Ah - good catch! I'll dig into that. This problem goes back to BIFF where Microsoft once had decided to switch from 0- to 1-based font numbers, therefore the font #4 does not exist. In order to have consistent font ids throughout the various file formats I added a nil value at this position to the font list. In ooxml I did skip this font, but probably screwed up the numbers.

Thank you - it would have taken days until I would have found the issue...

[wp]

Font support is working now for XLSX in the current trunk version (rev. 3312).

[Mike.Cornflake]

Nice work.  You certainly don't rest on your laurels do you?

[morknot]

My! How time flies.

I have a similar problem with unreadable content and Excel 2007(!!!) which seems to relate to merged cells. The error log is:

<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>error116200_01.xml</logFileName>
<summary>Errors were detected in file 'C:\AQ\fpsheet4.xlsx'</summary>
<removedRecords summary="Following is a list of removed records:">
<removedRecord>Removed Records: Merge cells from /xl/worksheets/sheet1.xml part</removedRecord>
</removedRecords>
</recoveryLog>

Excel's recovery process does result in the spreadsheet being perfectly formatted. There are no problems (obviously?) when the fpsheet4 file is saved as xls.

Any help gratefully received.

Windows 11
Lazarus 2.2
fpspreadsheet 1.16.0.0

[Mike.Cornflake]

Windows 11
Lazarus 2.2
fpspreadsheet 1.16.0.0

Lazarus 2.2 is quite old.  Any chance you can update to something newer (3.4?) and try again?

Cheers

Mike

[morknot]

Thank you for your reply, Mike.

I have updated Lazarus to 3.4 but unfortunately the same issue still occurs.

Regards

Andy

[rvk]

Any help gratefully received.

You would need to provide the code how you generate that test-file (and possible the corrupt file itself).

[morknot]

My apologies to both of you.

I have gone through my formatting code for merged cells and discovered a merging overlap. Having corrected the code there are no more error notifications from Excel.

Thank you both for replying.