XLSX document created by FPSpreadsheet not readable by LibreOffice

[rvk]

fpsopendocument.pas uses a TDOMNode to write strings to the sheet.
That's why the translation of these illegal characters (&, <, > etc...) are automatically taken care of.

xlsxooxml.pas writes its cells directly so the illegal characters are not translated to their save (XML-compliant) counterparts like &amp;, &lt; etc...

[wp]

I see. Seems not too difficult.

Another more fundamental question in this context since I am not too experienced with xml: The ods and xlsx writers construct a super-super-long xml string which is written to file, every new part is added by string concatenation. Wouldn't it be better to construct a tree of DOM nodes?

[rvk]

I see. Seems not too difficult.

Aaah, now i look deeper i can see the ods writer uses UTF8TextToXMLText. So, indeed using that it would be simple to fix this in the .xlsx-writer as well.

fpsopendocument.pas does use TDOMNode a lot but it's mainly used for reading an ods.

Another more fundamental question in this context since I am not too experienced with xml: The ods and xlsx writers construct a super-super-long xml string which is written to file, every new part is added by string concatenation. Wouldn't it be better to construct a tree of DOM nodes?

That was my first thought too... when diving in the sourcecode of xlsxooxml.pas. (Where are the DOM nodes ?? )

But wouldn't this be a very, very big (almost total) rewrite (to use DOM nodes)?.

[wp]

But wouldn't this be a very, very big (almost total) rewrite (to use DOM nodes)?.

Certainly, but if there were benefits it would be worth the effort, in particular now when the unit is not yet too long (I remember how short fpopendocument was before I added formatting and all these things...).

So: are there any benefits?

[wp]

The illegal xml character issue is fixed in rev 3300.

[rvk]

So: are there any benefits?

Well, without being an expert in these things i could imagine that there are benefits as well as drawbacks to using DOM Nodes.

I would begin by asking the question:
"Why does the TsSpreadOpenDocReader use TDOMNode?"
Is it because it's easier to use (to read an XML) than dismembering the XML yourself with endless pos-functions? If so... that would be the first benefit... simplicity

Using DOM Nodes has the advantage of always using the standard (XML) and not having to worry if you set an > in the right place or not.

Another thing... On the dutch forum we also discussed the fact that fpspreadsheet is not suitable for very large files. When creating a file with 80.000 rows and 40 columns you'll get a out of memory error for sure (>1.8GB memory usage). Maybe with DOM Nodes this would be better (but i'm not sure). Maybe it could even be worse.

(I saw some mention about files >4GB possible with the new zipper.pp but i don't see that happening with the current memory usage of fpspreadsheet. In that case there should be some swapping to disc mechanism build in)

fpspreadsheet is also slow with writing very large spreadsheet (i.e. >40.000 rows 20 colums). Perhaps also due to the string build-up (but again i'm not sure).

As a possible drawback... You are adding another layer to the process which could contain bugs, but my short experience here is that these are very quickly resolved.

The illegal xml character issue is fixed in rev 3300.

Thanks 

[BigChimp]

I saw some mention about files >4GB possible with the new zipper.pp but i don't see that happening with the current memory usage of fpspreadsheet.

While the zip64 support I added for FPC trunk does support this kind of file sizes, TZipper uses streams which are fixed to 2GB IIRC... so that's not a possibility at this time...

See http://wiki.lazarus.freepascal.org/FPC_New_Features_Trunk#TZipper_support_for_zip64_format

[wp]

fpspreadsheet is not suitable for very large files

I would not say that in general: I just tried a 65000 rows x 100 columns xls file (biff8), and it saves fine within 10 seconds; if I re-save it from Excel2007 or LibreOffice it takes 17 seconds - not too bad, although in task manager they hardly show an increase of memory usage while fpspreadsheet has a nasty peak probably because we have a very inefficient memory consumption per cell (all formatting saved per cell instead of referencing a format list).

[rvk]

I just tried a 65000 rows x 100 columns xls file (biff8), and it saves fine within 10 seconds;

What is the memory usage just before the .WriteToFile? For 65000x100 cells it is about 1,2GB here.

if I re-save it from Excel2007 or LibreOffice it takes 17 seconds - not too bad

Did you try sfOOXML 

Here the writing of biff8 is no problem (in 6 seconds) but during the .WriteToFile (sfOOXML) i get an out of memory error AFTER 5.5 minutes (330 seconds !!!!). (below some sample code)
(i also just tried sfOpenDocument and that one also seems to be taking forever, it's still going... i will investigate this further tomorrow)

So it seems spreadsheets of 70.000 to 80.000 rows are about as much as fpspreadsheet can handle. If not for the slowness of writing (which only occurs with .xlsx and .ods for me) then certainly for the memory usage.

(Using WriteNumber instead of WriteUTF8Text the memory usage is somewhat better, about 1.0GB, but it still craps out when writing to sfOOXML)

Code: [Select]

procedure TForm1.Button1Click(Sender: TObject);
var
  MyWorkbook: TsWorkbook;
  MyWorksheet: TsWorksheet;
  i, j: integer;
begin
  MyWorkbook := TsWorkbook.Create;
  MyWorksheet := MyWorkbook.AddWorksheet('My Worksheet');
  for i := 0 to 65000 do
  begin
    Label1.Caption := IntToStr(i);
    Application.ProcessMessages;
    for j := 0 to 100 do
    begin
      MyWorksheet.WriteUTF8Text(i, j, 'aj' + IntToStr(i) + ' ' + IntToStr(j));
    end;
  end;
  ShowMessage('ready #1');
  //MyWorkbook.WriteToFile('c:\temp\test' + STR_EXCEL_EXTENSION, sfExcel8, true);
  //MyWorkbook.WriteToFile('c:\temp\test' + STR_OPENDOCUMENT_CALC_EXTENSION, sfOpenDocument);
  MyWorkbook.WriteToFile('c:\temp\test' + STR_OOXML_EXCEL_EXTENSION, sfOOXML);
  ShowMessage('ready #2');
  MyWorkbook.Free;
  Close;
end;

I'm not well enough known with DOM to know if it has memory swapping capabilities. I only know that SAX does handle XML differently by reading it sequentially thus using less memory but that has its disadvantages too.

(wow... the sfOpenDocument-write is still busy after 8 minutes.... with 25% usage for project1.exe... i'll check on this tomorrow)

[wp]

Did you try sfOOXM

No, because I had no reason to question your observations.

I think I try the step to replace the string operations of the current OOXML writer with nodes. I'm no specialist with the internal details of memory management, but to me a string is a contiguous array of bytes. When a second string is appended to the first one the memory allocated to the first string is not sufficient any more. Therefore, memory corresponding to the sum of both strings (plus, maybe, some spare) is allocated, the first string is copied to the new locattion, and the second string is copied to where the first string ends. Therefore, during this time we need twice the memory! In case of DOM nodes, however, I'd suppose that there is no need for a contiguous array in memory, the nodes can be anywhere. Certainly, there are additional pointers to the nodes which would increase memory load again. But I'd guess that the memory available for very big files is increased by roughly a factor 2.

[marcov]

You might also want to check out tstringbuilder  IIRC it was meant for webframeworks that constantly add strings to a big string.

[Mike.Cornflake]

Did you try sfOOXM

I think I try the step to replace the string operations of the current OOXML writer with nodes. I'm no specialist with the internal details of memory management, but to me a string is a contiguous array of bytes. When a second string is appended to the first one the memory allocated to the first string is not sufficient any more. Therefore, memory corresponding to the sum of both strings (plus, maybe, some spare) is allocated, the first string is copied to the new locattion, and the second string is copied to where the first string ends. Therefore, during this time we need twice the memory! In case of DOM nodes, however, I'd suppose that there is no need for a contiguous array in memory, the nodes can be anywhere. Certainly, there are additional pointers to the nodes which would increase memory load again. But I'd guess that the memory available for very big files is increased by roughly a factor 2.

I'd be very interested in the results of your work switching over to DOM.  The work on ODT also works by building strings.  For the DOCX writer, I switched to TStringLists instead (my trials at the time with DOM resulted in significant slowdowns in the writer code (as in approx 10 times slower building up the DOM)).  Going on this conversation here, I'm beginning to regret that decision.

You queried earlier about advantages or disadvantages of DOM verses String.  One large advantage I can see using the DOM is increasing the readability of the code (and thus the maintainability).  Another is the reader is more easily coded with a DOM, and having both the writer and reader using similar code again improves maintainability...

[rvk]

At this moment i'm debugging fpsopendocument.pas to see where it is slow writing to a .ods with 65000x100 cells.

If i do a bleep (debugging is a bitch ) at every 100 rows i get a bleep every 12 seconds.
100 rows x 100 cells in 12 seconds ???
Yeah, i can imagine 65000 rows will take a while.

I'm still checking but i think the problem lies in the internal structure of fpspreadsheet (TAVGLVLTreeNode ??).
For example in the writing of sfOpenDocument a .FindRow is done for every row.
I can't find this call in sfExcel5 so i guess that format is just written sequentially without calling .Findrow each time)

So if the way the writing of sfOpenDocument and sfOOXML is changed in the same way as sfExcel5 the speed-problem will be solved (i think).

If there is going to be work done with DOM Nodes this needs to be taken into consideration.
(No expensive searching of the internal treenodes because if there are more than a few thousand nodes this structure is not efficient at all)

[wp]

a .FindRow is done for every row

No, this can't be a blocking point for standard files because rows are only created if their height is different from default. Therefore, the FindRows should exit immediately.

In my opinion, the slow speed here again is a consequence of the string approach for writing - for every string added the total string has to be copied to a new location! For a large file, this will grow up to several hundreds of MB. The call to WriteNumber, for example, contains 11 "+" signs! This is called again and again for every cell, and the string to be copied every time is growing and growing... I am more and more convinced that this must be bottle-neck.

I started to rewrite XLSXOOXML with DOM nodes. It's quite some typing but it's doable.

[rvk]

As a test i commented out the line 2839, rev.3301, fpsopendocument.pas:

Code: [Select]

//Result := Result + FCellContent;"
As we expected that one is increasingly slower when adding more to the Result-string.
(Maybe a streamwriter would be more efficient)

But i still had a major slowdown.
I changed the following line to exclude the test of GetCellCountInRow (line 2785, rev 3301, fpsopendocument.pas)

Code: [Select]

if false and (ASheet.GetCellCountInRow(r) = 0) then begin
Now the code was done in 11 seconds (instead of me having to abort after 3 minutes)
(of course with an invalid .ods but it's just to show where the slowdown is)

So besides the slowdown of adding string to string the GetCellCountInRow is very inefficient.
(Internally that function consists of lots of FindCell and FindCol's)

I started to rewrite XLSXOOXML with DOM nodes. It's quite some typing but it's doable.

Can't wait to test it