Recent

Author Topic: [Solved]Cannot load ods file with formulas  (Read 18488 times)

Arion58

  • New Member
  • *
  • Posts: 30
Re: Cannot load ods file with formulas
« Reply #30 on: February 25, 2018, 04:42:38 pm »
What I want to do looks - from my point of view - rather simple: I want to open a spreadsheet file, put some data (which I take from a csv file I get from my bank) into some of the sheets and let the others unchanged. Finally, I want to store the file in order to save the changes I made.
I must say that I have no idea how data is stored in a ods file, and I begin to realize that what I want to do might be much more complicated than I thought...

wp

  • Hero Member
  • *****
  • Posts: 11857
Re: Cannot load ods file with formulas
« Reply #31 on: February 25, 2018, 05:52:44 pm »
And the formula with the external reference (or the DDE you mentioned in the first posts) is contained in the initial worksheet which you read, modify and write back, or is it in the modification that you make? In other words: Will fpspreadsheet have to read and write the (unsupported) external reference? Or will it only have to write it?

Arion58

  • New Member
  • *
  • Posts: 30
Re: Cannot load ods file with formulas
« Reply #32 on: February 25, 2018, 07:04:59 pm »
The formula with the external reference (as I wrote in post #20 I managed to do it without DDE) is already in the file I read. What I add is dates, text, amounts and some local formulas as SUMs, VAT calculations and carrying forward, but only from one sheet to another in the same workbook.
Actually, what I want is that the formulas present in the workbook I read are rewritten to the spreadsheet file I save. Quite the same as if I open a file with LibreOffice, do some changes and save it again. So all what I didn't touch remains the same.

wp

  • Hero Member
  • *****
  • Posts: 11857
Re: Cannot load ods file with formulas
« Reply #33 on: February 25, 2018, 08:05:34 pm »
Thank you, now I understand.

Please try the new revision 6213. It respects boIgnoreFormulas now also when reading ods files. You must set the workbook Options boReadFormulas and boIgnoreFormulas (Kind of paradox: read, but ignore what you read...).

Try attached demo. I think this represents what you are doing: it creates an external file and a master file with a reference to the external file. Then it reads the master file with above-mentioned options, checks whether the unsupported external reference is still there, adds another formula which uses one of the external references and writes it back. I can open the modified master file in LibreOffice Calc and the external references as well as the new formula are working.
« Last Edit: February 26, 2018, 12:17:47 am by wp »

Arion58

  • New Member
  • *
  • Posts: 30
Re: Cannot load ods file with formulas
« Reply #34 on: February 26, 2018, 11:20:45 am »
Thank you very much, it works great. I take the occasion to express my admiration for people like you who do listen to others and help them.
I tried with my own file and the result is as I want it. Just one more question, but don't spend much time on it, only if you have a simple answer: In cells with a formula, it seems that the format has been a little bit modified: Initially, zero result values are not displayed in my sheets, but after saving with fpspreadsheet, there are 0's.
And a suggestion: When I got the error message about "not a valid worksheet name", this message was in a window which popped up from the IDE. It would be very handy if I could select/copy (a part of) the message for searching the internet, but I can't. Maybe this can be modified simply, or is there a good reason not to allow it?

Arion58

  • New Member
  • *
  • Posts: 30
Re: Cannot load ods file with formulas
« Reply #35 on: February 26, 2018, 11:22:26 am »
PS: If you agree, I will mark the topic as Solved.

wp

  • Hero Member
  • *****
  • Posts: 11857
Re: Cannot load ods file with formulas
« Reply #36 on: February 26, 2018, 12:02:53 pm »
In cells with a formula, it seems that the format has been a little bit modified: Initially, zero result values are not displayed in my sheets, but after saving with fpspreadsheet, there are 0's.
What is the format string? Or better: post a file with one number cell formated in the way you mention.

When I got the error message about "not a valid worksheet name", this message was in a window which popped up from the IDE. It would be very handy if I could select/copy (a part of) the message for searching the internet, but I can't. Maybe this can be modified simply, or is there a good reason not to allow it?
I think in this case the message window came up by an exception. You must write your own exception handler in Application.OnException; the message form that it shows must display the exception message in a memo - then you can copy text to the clipboard. Other errors (usually warnings only) are already caught and logged by the Workbook. After loading a file check Workbook.ErrorMsg and display it in a memo (Memo.Lines.Text := Workbook.ErrorMsg); then, again, you have full access to the text.

Arion58

  • New Member
  • *
  • Posts: 30
Re: Cannot load ods file with formulas
« Reply #37 on: February 26, 2018, 01:36:33 pm »
The added file has two interesting cells in sheet 0: A1 with the value 23, B1 with the formula "=A1-23", both are formatted as valuta, user defined. If you open the file with Calc, you see the 23 amount in A1, B1 is blank. After reading and rewriting it through fpspreadsheet, B1 shows as "0".

Arion58

  • New Member
  • *
  • Posts: 30
Re: Cannot load ods file with formulas
« Reply #38 on: February 27, 2018, 09:27:25 pm »
I think I found a bug in fpsExprParser:
When inserting a row, TsExpressionParser.SetExpression calls SetLocalizedExpression, after setting fs.DecimalSeparator := '.' and fs.ListSeparator := ','. This results in an error when the following (correct) Formula is found: ROUND((C5)/(1+0.21)*0.21;2) (Unexpected character in number:; According to the LibreOffice Documentation, ';' is the separater between the value and the decimal count.

wp

  • Hero Member
  • *****
  • Posts: 11857
Re: Cannot load ods file with formulas
« Reply #39 on: February 27, 2018, 11:34:55 pm »
No, this is taken care of because for writing ods a special parser is used which takes corrects these details. I think the problem was somewhere else:

In Excel (and Calc), the function ROUND(314.1592, 2) returns 314.16, the number is rounded to 2 decimal places. When FPSpreadsheet calculates this formula it is translated to the Pascal function  RoundTo, but here the "2" is understood as: round to the hundreds (10^2 = 100) which results in 300. The value must be -2 for rounding to the hundredths (10^-2 =0.01). The formula parser had used the same sign as in the spreadsheet formula. After flipping the fpspreadsheet sign, the result is correct now.

I wonder why this was not noticed before. There's even a unit test for rounding.

Thanks for reporting. Please test.

Arion58

  • New Member
  • *
  • Posts: 30
Re: Cannot load ods file with formulas
« Reply #40 on: February 28, 2018, 12:13:41 pm »
I still get the same error message using r6217. I will set up a demo file for you, probably this evening, because now I am at my work.

wp

  • Hero Member
  • *****
  • Posts: 11857
Re: Cannot load ods file with formulas
« Reply #41 on: February 28, 2018, 01:18:50 pm »
The added file has two interesting cells in sheet 0: A1 with the value 23, B1 with the formula "=A1-23", both are formatted as valuta, user defined. If you open the file with Calc, you see the 23 amount in A1, B1 is blank. After reading and rewriting it through fpspreadsheet, B1 shows as "0".
r6218 now supports number formats without leading zeros. If the format string is '#', for example, then a zero value is not displayed any more.

If this does not solve your issue post your format string, or a file with the issue.

Arion58

  • New Member
  • *
  • Posts: 30
Re: Cannot load ods file with formulas
« Reply #42 on: February 28, 2018, 06:53:04 pm »
For the Round-formula issue you don't need any demo file. Just execute the following program:
Code: Pascal  [Select][+][-]
  1. var Formula: String;
  2.     book:        TsWorkbook;
  3.     Sheet:      TsWorkSheet;
  4.     CellP:        PCell;
  5. begin
  6.   book := TsWorkbook.Create;
  7.   try
  8.     Sheet := book.AddWorksheet('Sheet');
  9.     Formula:= 'ROUND((A1)/(1+0.21)*0.21;2)';
  10.     CellP:= Sheet.GetCell(0,1);
  11.     Sheet.WriteFormula(CellP,Formula);
  12.     Sheet.InsertRow(0);
  13.   finally
  14.     book.Free;
  15.   end;
  16. end;
  17.  
The error will occur.

wp

  • Hero Member
  • *****
  • Posts: 11857
Re: Cannot load ods file with formulas
« Reply #43 on: February 28, 2018, 07:19:47 pm »
This formula is not correct. You must use a comma to separate the parameters, not a semicolon. The requirements of ODS are taken care of when the file is written and read.

Arion58

  • New Member
  • *
  • Posts: 30
Re: Cannot load ods file with formulas
« Reply #44 on: February 28, 2018, 07:27:17 pm »
I am not shure, I checked the documentation: https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_ROUND_function
Also I noticed that, if I enter the same formula in LibreOffice, apparently it is stored as 'ROUND(([.A1])/(1+0.21)*0.21;2)'. If I try to insert a row at a row with this formula, this results in a same sort of error, but this time concerning '[' instead of ';'.

 

TinyPortal © 2005-2018