fpspreadsheet file writing issues

[marcov]

I think you cannot, except by going to a computer with US formatting and reading all the values and hard-coding them in our code. But only the decimal separator is relevant here, so the rest should be irrelevant.

On Windows you can use sysutils.getlocaleformatsettings. It is a standard Delphi call, but unfortunately it uses a Windows LCID as argument so it is windows only and marked with "platform;"

[José Mejuto]

When writing measurement data to an excel file using fpspreadsheet I can across two bugs:

• When more than a certain number of cells is written to the spreadsheet the file cannot be opened in Excel any more. The limit seems to depend on the file version (sfOpenDocuments does not seem to have this limitation though). See the attached demo for a demonstration. Change the constant N (the number of cells to be written) to a smaller value to get readable files.

[...]

Hello,

The bug has been fixed in revision 2454. The bug happends when the file needs more than 2 FAT sectors as it suddenly jumps to 128 instead 3 (missing division).

I land in this post by chance, I hope the fix arrives in time.

[BigChimp]

Thanks, joshy.
Just tested with wp's initial test program and fpspreadsheet r2434.

Edit: Windows Vista x64

Test5.xls and Test8.xls can be opened by Excel 2000 (XP)
The .xlsx file can't be opened as it is a newer format (don't intend to download compatibility pack...)

LibreOffice 3.5.2.2: test5.xls, test8.xls open.
On opening the .xlsx file, it asks for a filter/file type. Selected Microsoft 2007/2010 XML=> general error.
Test.ods opens on libreoffice.

[José Mejuto]

Thanks, joshy.
Just tested with wp's initial test program and fpspreadsheet r2434.

Test5.xls and Test8.xls can be opened by Excel 2000 (XP)
The .xlsx file can't be opened as it is a newer format (don't intend to download compatibility pack...)

LibreOffice 3.5.2.2: test5.xls, test8.xls open.
On opening the .xlsx file, it asks for a filter/file type. Selected Microsoft 2007/2010 XML=> general error.
Test.ods opens on libreoffice.

Hello,

xlsx is a different beast, the fix only affects the .xls ones under Windows 7.

[BigChimp]

Hi Joshy,

Didn't know that or more correctly, I did know it was different, just didn't care: just blindly tested everything

Thanks for the fix!

[José Mejuto]

Hi Joshy,
Didn't know that or more correctly, I did know it was different, just didn't care: just blindly tested everything
Thanks for the fix!

Hi, basically the xls, doc, ppt, etc from Microsoft family are something like a big disk (up to 2 GB ?) which can have files inside (streams), folder, etc, while xlsx is a zip file with several .xml inside. So xls is a quite complex structured storage which works more or less like a disk, where you can write files, folders, delete, move, alter, etc without rewritting the whole file.

[wp]

Thank you, JoshyFun. All test files open fine in Excel 2007 and OpenOffice now. I wasn't aware that the xls file format is so close to hardware.

One small issue left, although not very important in practice: to my knowledge, xlsx and ods allow for more than 65536 rows. But when I use in my demo N=70000 rows the data vanish after 65536 rows.

The reason seems to be that fpspreadsheet is not very consistent with data types for row and cell addresses. TsWorksheet uses cardinal for ARow and ACol indexes while the TSpreadWriter uses word only.

[José Mejuto]

Thank you, JoshyFun. All test files open fine in Excel 2007 and OpenOffice now. I wasn't aware that the xls file format is so close to hardware.

One small issue left, although not very important in practice: to my knowledge, xlsx and ods allow for more than 65536 rows. But when I use in my demo N=70000 rows the data vanish after 65536 rows.

The reason seems to be that fpspreadsheet is not very consistent with data types for row and cell addresses. TsWorksheet uses cardinal for ARow and ACol indexes while the TSpreadWriter uses word only.

Yes, maybe the "word" is inherited by the very first format used to write. Maybe Felipe can say if the "words" can be changed to cardinal without side effects.

[felipemdc]

Yes, maybe the "word" is inherited by the very first format used to write.

Yes, fpspreadsheet started in 2007 and at that time Excel was limited to a maximum of Word rows, so I saw no need to make more space. I also don't use more then that in my files. It can be changed to cardinal safely.

[wp]

The attached patch replaces the WORD row/cell indices with CARDINALs. It would be fine if somebody with write-access to the svn could apply it.

[EDIT]
Added a check for the maximum row and column index in case of the old xls formats. See patch WordToCardinal1.

[2nd EDIT]
No! Not my best day today... The changes introduced in the new patch WordToCardinal1 don't compile.