Recent

Author Topic: Excel not accepting xlsx file with styles written by fpspreadsheet  (Read 6153 times)

wp

  • Hero Member
  • *****
  • Posts: 12122
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

  • Hero Member
  • *****
  • Posts: 1262
Re: Excel not accepting xlsx file with styles written by fpspreadsheet
« Reply #1 on: July 12, 2014, 09:02:19 pm »
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...
« Last Edit: July 12, 2014, 09:11:55 pm by Mike.Cornflake »
Lazarus Trunk/FPC Trunk on Windows [7, 10]
  Have you tried searching this forum or the wiki?:   http://wiki.lazarus.freepascal.org/Alternative_Main_Page
  BOOKS! (Free and otherwise): http://wiki.lazarus.freepascal.org/Pascal_and_Lazarus_Books_and_Magazines

wp

  • Hero Member
  • *****
  • Posts: 12122
Re: Excel not accepting xlsx file with styles written by fpspreadsheet
« Reply #2 on: July 12, 2014, 10:16:12 pm »
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

  • Hero Member
  • *****
  • Posts: 12122
Re: Excel not accepting xlsx file with styles written by fpspreadsheet
« Reply #3 on: July 13, 2014, 12:13:40 am »
Font support is working now for XLSX in the current trunk version (rev. 3312).

Mike.Cornflake

  • Hero Member
  • *****
  • Posts: 1262
Re: Excel not accepting xlsx file with styles written by fpspreadsheet
« Reply #4 on: July 13, 2014, 01:57:19 pm »
Nice work.  You certainly don't rest on your laurels do you? :)
Lazarus Trunk/FPC Trunk on Windows [7, 10]
  Have you tried searching this forum or the wiki?:   http://wiki.lazarus.freepascal.org/Alternative_Main_Page
  BOOKS! (Free and otherwise): http://wiki.lazarus.freepascal.org/Pascal_and_Lazarus_Books_and_Magazines

morknot

  • New Member
  • *
  • Posts: 49
  • still learning
Re: Excel not accepting xlsx file with styles written by fpspreadsheet
« Reply #5 on: June 10, 2024, 01:35:45 pm »
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

  • Hero Member
  • *****
  • Posts: 1262
Re: Excel not accepting xlsx file with styles written by fpspreadsheet
« Reply #6 on: June 14, 2024, 05:05:02 am »
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
Lazarus Trunk/FPC Trunk on Windows [7, 10]
  Have you tried searching this forum or the wiki?:   http://wiki.lazarus.freepascal.org/Alternative_Main_Page
  BOOKS! (Free and otherwise): http://wiki.lazarus.freepascal.org/Pascal_and_Lazarus_Books_and_Magazines

morknot

  • New Member
  • *
  • Posts: 49
  • still learning
Re: Excel not accepting xlsx file with styles written by fpspreadsheet
« Reply #7 on: June 14, 2024, 12:50:20 pm »
Thank you for your reply, Mike.

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

Regards

Andy

rvk

  • Hero Member
  • *****
  • Posts: 6276
Re: Excel not accepting xlsx file with styles written by fpspreadsheet
« Reply #8 on: June 14, 2024, 01:57:48 pm »
Any help gratefully received.
You would need to provide the code how you generate that test-file (and possible the corrupt file itself).

morknot

  • New Member
  • *
  • Posts: 49
  • still learning
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.

 

TinyPortal © 2005-2018