Excel 2010 cannot read large(ish, 8MB) files created by fpspreadsheet

[grandehombre]

Hi,

I am using Lazarus v1.0.10  i386-win32-win32/win64,  FPC v2.6.2 on Win7-64.
I use fpspreadsheet (can't find version numbers in source, however, it was extracted from fpspreadsheet-1.1.zip)

I use the excel8write and uncomment the looper in line 45, I generate a 9000 lines file with 8 columns (instead of just 4 shown in the example).
This creates a 7MB file (attached test2a.xls)   which Excel2010 rejects.

If I reduce the file size by using just 4 columns but still 9000 rows. Excel has no problems reading this (attached file test2b.xls)

Please note that, in the excel8write demo, I also changed the lines used in that 9000 row loop from WriteAnsi.. to WriteUTF8Text.

I have attached a zip file that contains the failing xls file (due to attachment restrictions I can't attach both)

Cheers,
Nick

[BigChimp]

Why don't you post the source code in another post then? Saves us having to guess/reconstruct

Edit: tried to open the .xls file in LibreOffice 4.0.4=>result: 1 sheet without any contents... Also creation date in properties was something like 0000-00-00 which doesn't seem right either.

[wp]

I can confirm the behaviour in the first posting, however, it seems to depend on the number of columns and on the amount of text in each cell. With 9000 rows and a single character text in each cell, I can write more than 32, but less then 64 columns. But with a string of length 105 I cannot write even 4 columns.

You probably should post a bug report.

[grandehombre]

Why don't you post the source code in another post then? Saves us having to guess/reconstruct

Edit: tried to open the .xls file in LibreOffice 4.0.4=>result: 1 sheet without any contents... Also creation date in properties was something like 0000-00-00 which doesn't seem right either.

Please find attached 1.zip containing the modified source for excel8write. I could not include the EXE due to attachment size restrictions.
Thanks for replying!

[BigChimp]

Great, thanks! Should be no need for the exe as we should be able to compile this ourselves.

NB (for future reference): no need to include the .o file; you can fairly easily get only the source files by using project/publish project and zip up the resulting directory...)

[BigChimp]

With Lazarus 1.0.10 (and FPC 2.6.2 x86 on Windows), with  fpspreadsheet at the latest SVN version (revision 2731 date 13 February), and the program you uploaded, I get a spreadsheet that LibreOffice 4.0.4 and Excel XP (2002) can read...


Edit: See Removed attached zip... due to post below.

[wp]

But you will have an unreadable xls when you increase the number of columns. In my case, the ParamStr(0) is 43 characters long and Excel 2007 cannot read the file if it has more than 14 columns.

[grandehombre]

I am downloading the latest version from SF svn and will report back what I discover.

[grandehombre]

Bad news, I am afraid.

I downloaded the latest from SF svn   (lazarus-ccr-svn-2756.zip) and re-run the test program.
It generated a 7MB file which could not be read by either LibreOffice or Excel.

:-(

[BigChimp]

Strange. We're using the same Lazarus/compiler on the same OS. Only difference would be fpspreadsheet I suppose.

I'll send you the fpspreadsheet files I used by pm so you can compare if you want to...
Hmmm there doesn't seem to be a way to send attachments.

Uploaded everything to
http://ubuntuone.com/6l5vgablzHAZMkuPXOqTZS
as I don't want to spam the forum with too many attachments...

[grandehombre]

Ahem... it still fails.

Please note that my file contains
'D:\work\FreePascal\components\in-use, fpspreadsheet\examples\excel8demo\2\excel8write.exe'
in each of the 8 columns and generates a 7MB file (that still fails)

If your path is different, your file size will be different.
Copy the above string and use that to fill 8 columns and 9000 rows. You'll probably find that it will fail :-(

[BigChimp]

Pfff. Sure. Had overlooked that.

Why not write a test program that fills the spreadsheet with... like... a... you know... constant text!?!?

Edit: Anyway, I'd suggest reporting it as a bug. Together with a testable/repeatable test project...

[grandehombre]

>>Why not write a test program that fills the spreadsheet with... like... a... you know... constant text!?!?
I simply picked up the example program and run with it...

I'll use some constant text and report as a bug tomorrow.
Too late and too tired at the moment.

Thank you for all your help!

Cheers,
Nick

[wp]

This is similar to the old bug that José Mejuto fixed about a year ago (http://forum.lazarus.freepascal.org/index.php/topic,16716.0.html - see his posting towards the end of that thread). He speaks of a file needing "more than 2 FAT sectors", maybe there is another limitation like this -- Unfortunately my understanding of the excel file structure is too limited...

[grandehombre]

Edit: Anyway, I'd suggest reporting it as a bug. Together with a testable/repeatable test project...

Done: Mantis ID = 0024697
Lets hope for the best