[Solved] A table with many columns cannot be saved.

[Soner]

A table with many columns cannot be saved.
Memory usage increases from 8,5 MB to over 900 MB then "Out of Memory" exception fires.

The old version from fpsreadsheat works without error.

Does anyone know how to fix this error?

Example project with ods-workbook included.

[wp]

Which "old version"?

[Soner]

The directory creation date January 2021, but it is copy from my home pc. Maybe it ist from summer/autumn 2020.
I can tell you more when I go home tomorrow night.

[wp]

Looking at it in detail, this is not so important since detection of table size in ods files has not changed very much. It always has been a pain: the xml nodes duplicate the last unused column by "number-columns-repeated" attributes up to the specified maximum column count, and the same with the rows. Since fpspreadsheet creates a cell record for every table cell it is important that these superfluous cells are not considered. Now I have an idea how to approach this issue differently. Please give me some time.

[Soner]

If you save the workbook "cp-mai.ods" as xlsx- or xls-Format from spready then the file is larger while if you save it from Libreoffice it is smaller.  Libreoffice ~20KB, spready ~200 KB.

[Soner]

And the memory usage of TsWorksheetGrid is with this workbook huge, spready ~334 MB vs. Libreoffice ~100 MB.

Also the table is not really big, it is only 47 columns and 48 rows.

[wp]

Which application created this table? The following excerpt of the original file shows the end of the table xml node:

Code: XML  [Select][+][-]

1.         <table:table-row table:style-name="ro3" table:number-rows-repeated="22">
2.           <table:table-cell table:number-columns-repeated="1016"/>
3.         </table:table-row>
4.         <table:table-row table:style-name="ro2"
5.           table:number-rows-repeated="983037">
6.           <table:table-cell table:number-columns-repeated="1016"/>
7.         </table:table-row>
8.         <table:table-row table:style-name="ro2">
9.           <table:table-cell table:number-columns-repeated="1016"/>
10.         </table:table-row>
11.       </table:table>

As you can see the exporting application added 22 + 983037 + 1 = 983060 dummy rows and 1016 columns at the end of the user's data block!

The ods reader now seeks for "rows-repeated" at the end of the data block and deletes them from the DOM before cell reading takes place. The problem is that it is difficult during cell reading to distinguish whether a row contains just formatted, but otherwise empty cells, or whether a row is a filler row added by the exporting application at the end of the data block.

[Soner]

Libreoffice created it.  It looks like workbook fault.
I opened the table in Libreoffice saved it first as xlsx-format then ods-format. At the end I opened the new ods-file with spready and my i application saved it without error. Memory consumption is also normal.

But It's strange that the old version from fpspreadsheet/spready worked without error.

[wp]

Libreoffice created it.  [...] I opened the table in Libreoffice saved it first as xlsx-format then ods-format. At the end I opened the new ods-file with spready and my i application saved it without error. Memory consumption is also normal.

Created a simple ods file with LO/Calc - it does not contain the extra rows and columns. I know that they are added when Excel writes an ods file. So, maybe you did this: You created the file originally by LO/Calc, opened it in Excel and saved it from Excel back to ods format?

But It's strange that the old version from fpspreadsheet/spready worked without error.

I do remember that I worked on the "columns-repeated"/"rows-repeated" issue not too long ago. Maybe it was introduced here? Or my fix brought up another bug which was hidden so far? Unfortunately nothing related strikes my eyes in the svn commit notes.

[Soner]

I don't use excel anymore, maybe my boss. But I think this buggy table is created with  TsWorksheetGrid. I try to recreate this buggy table on friday and report it here. Tomorrow is holiday  and I have not access to computer at work. Thank you very much for your help.

[lainz]

Sorry for the ignorance, but what is spready? I can't find it with google in Argentina.

[paweld]

@lainz: https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/applications/spready/

[lainz]

@lainz: https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/applications/spready/

Thanks

[GAN]

Hi Lainz, at least for Lazarus/FPC, DuckDuckGo is the best search engine out there.

Greetings.

https://duckduckgo.com/?q=Lazarus+spready&ia=web