[Solved] A table with many columns cannot be saved.
lazarus / 49-FPSpreadsheet
----------------------------------------------------------------------
Soner · 2023-05-16 15:46
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 · 2023-05-16 16:02
Which "old version"?
----------------------------------------------------------------------
Soner · 2023-05-16 17:45
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 · 2023-05-16 18:08
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 · 2023-05-17 11:06
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 · 2023-05-17 11:17
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 · 2023-05-17 11:54
Which application created this table? The following excerpt of the
original file shows the end of the table xml node:
Code: XML [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
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 · 2023-05-17 12:31
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 · 2023-05-17 15:29
Quote from: Soner on May 17, 2023, 02:31:53 pmLibreoffice 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?
Quote from: Soner on May 17, 2023, 02:31:53 pmBut 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 · 2023-05-17 20:58
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 · 2023-05-17 23:43
Sorry for the ignorance, but what is spready? I can't find it with
google in Argentina.
----------------------------------------------------------------------
paweld · 2023-05-18 06:25
@lainz: https://sourceforge.net/p/lazarus-
ccr/svn/HEAD/tree/applications/spready/
----------------------------------------------------------------------
lainz · 2023-05-20 12:29
Quote from: paweld on May 18, 2023, 08:25:31 am@lainz:
https://sourceforge.net/p/lazarus-
ccr/svn/HEAD/tree/applications/spready/
Thanks
----------------------------------------------------------------------
GAN · 2023-05-21 01:59
Hi Lainz, at least for Lazarus/FPC, DuckDuckGo is the best search
engine out there.
Greetings.
https://duckduckgo.com/?q=Lazarus+spready&ia=web