[Solved]Cannot load ods file with formulas

[wp]

Normally I would say: you must not use the Calc formula syntax. FPSpreadsheet mostly follows Excel syntax. This means:
- cell references A1 (no square brackets)
- cell range A1:B4
- cell reference to another sheet
- floating point number with decimal point
- parameter separator in formulas: comma
- English function names

But here things are complicated. I think that this issue is related to the other issue with the external cell references which made me introduce the boIgnoreformulas option - I assume that this option will be on here too. In this case, of course, you are responsible for the formulas, you must write them according to the requirements of calc. So, yes, you need the semicolon, you (maybe) need the square brackets etc (Calc is very tolerant, though). To learn the syntax in the files, rename an ods file to zip, unzip it, and load the file contents.xml into an editor (ideally, an xml viewer). Browse throught the content.

But then, why does fpspreadsheet report the error in your demo even if boIgnoreFormulas is used. I think this is due to the line in which you insert a new row. This triggers a quite complex analysis of the formulas because formulas may have to be adapted due to changed cell addresses.

OK - you could say: fpspreadsheet should leave the formulas alone, it's been told to do so. But then your formulas will be wrong
.
I think the essence is: Don't insert/delete a row or column in Ignore-formula-mode. You will have troube either way. I'll have to think about it: maybe crash the insert/delete in this case with a clear error message.

[Arion58]

Thank you for your explanation. Unfortunately, I must admit that I don't understand all off it. Mostly the last paragraph. Anyway, I will try your "trick" with ods - zip.
I have tried the formula with comma instead of semicolon. But then I have another issue, probably related to localisation. My locale is NL, where the decimalSeparator is a comma. When I load a file with a formula as 'ROUND((A1)/(1+0.21)*0.21,2)' that I wrote through fpspreadsheet, then the first '0.21' is localized to '0,21', but not the second, so the formula cannot be interpreted correctly. This is why I chose for semicolon.

[wp]

I cannot imagine that this problem cannot be solved. Could you prepare a simplified master and external file? Upload the files to the forum. Describe the steps you want to do. Then I can have a look at this issue and write a demo how to get your changes into the file.

[Arion58]

I have created a small ods file with the following program:

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

1. var Formula,OdsFName: String;
2.     book:             TsWorkbook;
3.     Sheet:            TsWorkSheet;
4.     CellP:            PCell;
5. begin
6.   OdsFName:= BuchDir + 'RoundDemo.ods';
7.   book := TsWorkbook.Create;
8.   try
9.     Sheet := book.AddWorksheet('Sheet');
10.     Formula:= 'ROUND((A1)/(1+0.21)*0.21;2)';
11.     CellP:= Sheet.GetCell(0,1);
12.     Sheet.WriteFormula(CellP,Formula);
13.     Formula:= 'ROUND((A1)/(1+0.21)*0.21,2)';
14.     CellP:= Sheet.GetCell(0,2);
15.     Sheet.WriteFormula(CellP,Formula);
16.     book.Options:= book.Options + [boIgnoreFormulas,boReadFormulas];
17.     book.WriteToFile(OdsFName,sfOpenDocument,TRUE);
18.   finally
19.     book.Free;
20.   end;
21.  

I have looked into the file by means of an XML viewer, the formulas are stored exactly as I put them (one with ';', one with ','). When I open the file with LibreOffice Calc, I see what RounDemo.png shows.

[wp]

The following code creates a file which Calc can read. It writes always the same formula, but in a different syntax each case. The first row has the semicolon as list separator, the second row the comma. It varies the formula syntax (OpenDocument wants a formula to begin with 'of:=', Excel requires '=', and fpspreadsheet  nothing) and the cell reference syntax (Opendocument: [.A1], Excel: A1).

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

1. program project1;
2.  
3. {$mode objfpc}{$H+}
4.  
5. uses
6.   fpspreadsheet, fpstypes, fpsopendocument;
7.  
8. var OdsFName: String;
9.     book: TsWorkbook;
10.     Sheet: TsWorkSheet;
11.     CellP: PCell;
12. begin
13.   OdsFName:= 'RoundDemo.ods';
14.   book := TsWorkbook.Create;
15.   try
16.     book.Options:= book.Options + [boIgnoreFormulas,boReadFormulas];
17.     Sheet := book.AddWorksheet('Sheet');
18.  
19.     Sheet.WriteNumber(0, 0, 3.141592);
20.  
21.     // *** List separator SEMICOLON ***
22.     // ods syntax for formula
23.     Sheet.WriteFormula(0, 1, 'of:=ROUND([.A1]/(1+0.21)*0.21;2)'); // exactly like in file
24.     Sheet.WriteFormula(0, 2, 'of:=ROUND(A1/(1+0.21)*0.21;2)');
25.  
26.     // Excel syntax for formula
27.     Sheet.WriteFormula(0, 3, '=ROUND([.A1]/(1+0.21)*0.21;2)');
28.     Sheet.WriteFormula(0, 4, '=ROUND(A1/(1+0.21)*0.21;2)');
29.  
30.     // fpspreadsheet syntax for formula
31.     Sheet.WriteFormula(0, 5, 'ROUND([.A1]/(1+0.21)*0.21;2)');
32.     Sheet.WriteFormula(0, 6, 'ROUND(A1/(1+0.21)*0.21;2)');
33.  
34.     // **** List separator COMMA ***
35.     // ods syntax for formula
36.     Sheet.WriteFormula(1, 1, 'of:=ROUND([.A1]/(1+0.21)*0.21,2)');
37.     Sheet.WriteFormula(1, 2, 'of:=ROUND(A1/(1+0.21)*0.21,2)');
38.  
39.     // Excel syntax for formula
40.     Sheet.WriteFormula(1, 3, '=ROUND([.A1]/(1+0.21)*0.21,2)');
41.     Sheet.WriteFormula(1, 4, '=ROUND(A1/(1+0.21)*0.21,2)');
42.  
43.     // fpspreadsheet syntax for formula
44.     Sheet.WriteFormula(1, 5, 'ROUND([.A1]/(1+0.21)*0.21,2)');
45.     Sheet.WriteFormula(1, 6, 'ROUND(A1/(1+0.21)*0.21,2)');
46.  
47.     book.WriteToFile(OdsFName,sfOpenDocument,TRUE);
48.   finally
49.     book.Free;
50.   end;
51. end.

When the created file "RoundDemo.ods" is opened in Calc it can be seen that Calc is very tolerant regarding formula and cell ref syntax, but absolutely insists on the semicolon as list separator.

P.S.
I never can open your attached ods files which you provide as .tar.gz. Why don't you just rename the extension to .zip instead of .ods (it's a renamed zip anyway)?

[Arion58]

I tried your program, but it doesn't work for me (See RoundDemo1.png). I add the ods file, too, this time just renamed as zip.

[wp]

Sorry, I forgot to mention that you must use the new revision because the old one always adds a "=" in front of the formula. And I realized that sourceforge is down (again...), and forgot to upload the new revision...

In the meantime you can copy the attached fpsopendocument.pas into your fpspreadsheet installation to get this fix (folder "source/common"). With it, the formulas in the first row of the worksheet should work, those in the second row will not work

[Arion58]

Yes, now the first row is OK. Only, I knew this already, because it is the way I did it. But now I still have SetLocalizedExpression complaining about the semicolon when inserting a row.
That the second row doesn't show correctly, could this be a bug in LibreOffice? BTW, I am using two versions: 5.4.5.1 and 4.3.3.2, under Linux, with Dutch localization. They both do the same in this case.

[wp]

I think we're moving in circles...

How does SetLocalizedExpression come into play? I thought you told fpspreadsheet to ignore formulas. Is this happening because you still try to insert rows? As I told you this will not work because fpspreadsheet MUST look at formulas in order to find out whether cell addresses in formulas have to be adapted. Inserting/deleting rows and columns on the one hand and ignoring formulas on the other hand are contradicting operations, you cannot have both. You somehow must prepare your master workbook such that it is not required to insert rows.

I don't think that the errors in the second row are a bug in LibreOffice. It's just that these formulas do not follow their specification. They want to have a semicolon to separate formula elements, and that's all that counts.

[Arion58]

Thank you, you are right. I think I can find a manner to do what I want to do without the need to insert rows. Should I mark the topic as solved?

[wp]

Should I mark the topic as solved?

I don't know. Is it solved for you? If yes, edit your first post and put a "[SOLVED]" in front of its title.