Can't WriteToFile a WorkBook with more than 4 WorkSheets

[franzala]

When I try to WritetoFile a WorkBook with 5 WorkSheets, the resulting SpreadSheet fails to open with the message:

Erreur de lecture.
Erreur de format dans le fichier du sous-document content.xml à la position 1,62284(row,col).

But if I suppress one Sheet, the problem disappears.
Actually I use a WorkBook with 9 sheets and the message is the same except the position which becames 1,319737(row,col).
It seems that the figure of col is equal to 62284*(number of sheets - 4).

Is there such a limit?

My workbook at this moment if quite small (34k bytes) but it will grow rapidly later.

[franzala]

Some additional information:

I'm using LibreOffice SpreadSheets witt Lazarus 1.8.2+dfsg-3 under Ubuntu 18.04 LTS

[wp]

fpspreadsheet itself has no such limits (well ok -- the col and row indices are type Cardinal...). But when writing to file the limitations of the file format must be respected. OpenDocument ODS does not allow beyond these values (https://wiki.openoffice.org/wiki/Documentation/FAQ/Calc/Miscellaneous/What%27s_the_maximum_number_of_rows_and_cells_for_a_spreadsheet_file%3F):

    maximum number of rows: 1,048,576
    maximum number of columns: 1,024
    maximum number of sheets: 256

If I understand your error message correctly you are trying to write 62,284 columns in one case and 319,737 columns in the other case. That's way too much for ODS.

I should also emphasize that when writing huge files a 32-bit application will soon run out of memory. You must compile your application for 64-bit.

[franzala]

I'm using a 64 bit version of Lazarus (x86_64-linux.gtk2), FPC 3.0.4 (I don't know exactly if it is 32 or 64 bit, nor how to check it) with 8Go ram and I would be surprised to face memory problems especially with a very small file of only 34k.

I do not try to write 62 284 columns, at least not voluntarily; it is the error message wich mentions this figures and as indicated in my post, they depend probably on the number of WorkSheet in the WorkBook (the number of columns mentionned in the message is equal to 62 284 multiplied by (the number of WorkSheet less 4 and 4 is exactly the limit over which the writing fails).

I thought first that it could be related with the fact that some sheets are empty for now, but whith no empty WorkSheet the problem does not disappear.

It is also to notice that reading the file works apparently without problem; the problem seems to be specific to the writing process.

Here is the code with the uses clause:

Code: Pascal  [Select][+][-]

1. uses
2.   Classes,SysUtils,FileUtil,Forms,Controls,Graphics,Dialogs,StdCtrls,LazUTF8,
3.   FPSpreadsheet,fpsCSV,xlsxOOXML,fpsopendocument,fpstypes,fpsutils;
4.  
5. procedure TFotest7.Btn1Click(Sender: TObject);
6.  
7. begin
8.   OD1.Title:='fichier compta ods         ';
9.   OD1.Filter:='fichier ods|*.ods';
10.   if OD1.Execute then
11.     ss:=OD1.FileName;
12.   wb1:=TsWorkbook.Create;
13.   wb1.ReadFromFile(ss,sfOpenDocument);
14.   ws1:=wb1.GetWorksheetByName('Param');
15.   ss1:=ws1.ReadAsText(70,0);  //nom
16.   ws1.WriteText(2,0,'chang');
17.   ws1.WriteText(68,0,ss1);
18.   wb1.WriteToFile(ss,sfOpenDocument,true);
19.   wb1.Free;
20.   Edit1.Text:='fini';
21. end;  
22.  
23.  

[wp]

I do not see how your code snippet relates to your description.

Please post a full compilable program which creates a file unreadable by Calc. I need to see how many rows and columns you write, and how many sheets are used. Please add the pas, lfm, lpi and lpr files to a common zip which you can upload under "Attachments and other options"; do not add the exe, ppu, o and other compiler-generated files. I think you can use dummy data to fill the worksheets, and try to keep the demo program as simple as possible, I do not want to spend hours in understanding it.

[franzala]

In between I continued to search, especially if a specific  WorkSheet was causing the problem; and it's the case: the problem occurs whith a file containing only that WorkSheet (the message is slightly changed at the end: the position at the end of the message becames 1,41008).
Since the Worksheet is not in one of the allowed file types, I can't attach it but if there is another way and if you had the time to have a look, I'm ready to send it.

The file's size is 30ko, it contains 29 columns and 58 rows; there are no formulas in the sheet for the moment but I will have to add some in a next step. I suppose the relatively complex formatting with colors for background, borders, text and merged cells is probably the main origin of the problem.

I will try to simplify that formating and check if I can reach a level which will be accepted by FPS.

It would be helpful if you could give me some hints to do that, especially which kind of formatting is the most difficult to manage in FPSpreadSheet; my first idea is to reduce the number of merged cells and to suppress some colored borders and backgrounds but any other idea of useful changes would be very welcome.

Thanks in advance for any help, hoping my request is not too far from what is accepted in this forum.

[Sieben]

Since the Worksheet is not in one of the allowed file types

Just zip it up...

[franzala]

Here is the zip file with the problematic file. Thanks to Sieben

[franzala]

I have tried on another PC and it works perfectly including with the 9 WorkSheets.

The problem is located in my computer; I will have to recompile FPSpreadSheet and maybe Lazarus.

Sorry for the inconvenience.

[franzala]

I still have the same problem : unable to write fo file a workbook with more than 4 spreadsheets, despite a complete re-installation of ubuntu and Lazarus.

I have completely re-installed ubuntu 18.04.10 with the option which formats the destination partition
then I have installed Lazarus 2.0.10 (with the debian packages)
FPC 3.2.0 Revision SVN universionned directory x86-64-linux-gtk2
FPS 1.12
and LibreOffice 6.0.7.3.

Attached is the project (.pas, .lfm, .lpi and .lpr files) and the two workbooks, the first with 4 Spreadsheets for which the WriteToFile works without problem, and the second with 5 Spreadsheets for which  the  WriteToFile does not work ; more precisely the program ends apparently without error but when I try to open the file, LibreOffice displays  the following error message :   
   read error
   format error in the file of the sub-document (in french it is: sous-document) content.xml
   at position 1,62833(row,col)

The project is quite simple : first you have to select the number of spreadsheets (i.e. the workbook) and then it reads a cell, writes it in another cell (an empty one) and writes the workbook to file with the same name.

I tested the same project on another PC under windows and it works without problem for both workboks, and also with 9 spreadsheets.

Do you have any idea about the origin of that problem ?

[wp]

As expected, it is working for me too, on Windows 10 and on Ubuntu 18.04 ... I have no idea what's wrong here.

Just a comment on your code (I think it is not the reason for the trouble): Your code allows to create FWorkbook each time a radiobutton is clicked, but you destroy it only in the OnDestroy event of the form, i.e. only when the program closes. This means that you create a memory leak with every click on a radio button. When you need the workbook only for the local code in the RadioButtonClick you should make the workbook a local variable and destroy it before exiting the click event (use a try-finally block). Or, or when you need the workbook for other purposes as well, you must destroy the previously created instance before creating a new one:

Code: Pascal  [Select][+][-]

1. procedure TForm1.RadioGroup1Click(Sender: TObject);
2. var
3.   f1,s,ss: String;
4.   ws1:TSWorkSheet;
5. begin
6.    if RadioGroup1.ItemIndex=0 then f1:=ExtractFilePath(ParamStr(0))+'4sheet.ods'
7.   else f1:=ExtractFilePath(ParamStr(0))+'5sheet.ods';
8.   if FileExists(f1) then
9.     try
10.       FWorkbook.Free;          // <------------- ADDED.
11.       FWorkbook := TsWorkbook.Create;
12.       FWorkbook.ReadFromFile(f1,sfOpenDocument);
13.       ws1:=FWorkbook.GetWorksheetByName('Param');
14.       ss:=ws1.ReadAsText(2,1);  //'Banque'
15.       E1.Text:=ss;
16.       ws1.WriteText(2,2,ss);
17.       FWorkbook.WriteToFile(f1,sfOpenDocument,true);
18.       s := FWorkbook.ErrorMsg;
19.       if s <> '' then ShowMessage(s);
20.     except
21.       on E:Exception do
22.         ShowMessage('Error writing "' + f1 + '": ' + LineEnding + E.Message);
23.     end
24.   else ShowMessage('file missing '+f1);
25. end;

[franzala]

OK, I feared it too.

Do you know where I could find documentation or tutos to check methodically my installation in ordre to correct what is wrong.