Recent

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

wp

  • Hero Member
  • *****
  • Posts: 11854
Re: Cannot load ods file with formulas
« Reply #15 on: February 20, 2018, 09:57:40 am »
Certainly. But the problem is that '=[external_file]Sheet1!$A$19' is a formula, and something like '=[external_file]Sheet1!$A$19+1' (adding 1 to the value from the external field) is valid and must be considered to happen. Therefore the external reference must be passed to the formula parser which is not prepared for this. It certainly can be done, but it would be required that I focus on this topic which is not possible at the moment.

As for COM: This is a completely different approach, but it cannot mixed with fpspreadsheet. COM gets you the "com"plete spreadsheet functionality, but a Windows-only technology.


Angus

  • New Member
  • *
  • Posts: 16
Re: Cannot load ods file with formulas
« Reply #16 on: February 20, 2018, 10:23:33 am »
Until now, I used the macro language of libreoffice (something like starbasic), but it is very slow and sometimes nor very reliable.
I couldn't find any documentation about "Com" mentioned by Thaddy.
And my last question of my first post remains unanswered: How is the program able to evaluate a DDE-formula if this is not implemented?

Useful articles on COM by Charlie Calvert can be found here:

http://edn.embarcadero.com/article/10126

Kind regards

Angus

wp

  • Hero Member
  • *****
  • Posts: 11854
Re: Cannot load ods file with formulas
« Reply #17 on: February 20, 2018, 10:25:52 am »
DDE is just as proprietary Microsoft as COM is.
Generally I would say: yes. But in the context of Libreoffice I am not sure. LibreOffice is cross-platform, and the DDE formula does not even seem to exist in Excel (https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_DDE_function).

rvk

  • Hero Member
  • *****
  • Posts: 6110
Re: Cannot load ods file with formulas
« Reply #18 on: February 20, 2018, 10:55:59 am »
LibreOffice is cross-platform, and the DDE formula does not even seem to exist in Excel (https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_DDE_function).
Haha, no Excel doesn't seem to have that function (ever). But Access does.
https://support.office.com/en-us/article/dde-function-79e8b21c-2054-4b48-9ceb-d2cf38dc17f9

On that page there is =DDE("Excel", "Sheet1", "R1C1"). So it does seem that Excel could act as DDE-application although it does not have it for its own functions/formulas  %)

marcov

  • Administrator
  • Hero Member
  • *
  • Posts: 11383
  • FPC developer.
Re: Cannot load ods file with formulas
« Reply #19 on: February 20, 2018, 11:00:54 am »
DDE is just as proprietary Microsoft as COM is.

So it is modelled after a well known technology as COM is an simplified Corba ?

Arion58

  • New Member
  • *
  • Posts: 30
Re: Cannot load ods file with formulas
« Reply #20 on: February 20, 2018, 05:52:08 pm »
Finally, I found a manner to do my book-keeping without DDE and without NamedRanges. However, I can't do without VLOOKUP, which isn't supported either. Just a thought of mine: Would it be a good idea to introduce a read/write option indicating to read and write formulas (as strings), without trying to interpret them. I think that in my case this would do. Or would I be the only one who would use it?

wp

  • Hero Member
  • *****
  • Posts: 11854
Re: Cannot load ods file with formulas
« Reply #21 on: February 21, 2018, 12:14:18 am »
You should try the new revision 6209. It introduces a workbook option boIgnoreFormulas which - if active - can be used to write any formula to a cell and then to a file; the formula is not checked and not calculated - your responsibility... Worst case is that the destination file will not open in the Office application. Writing to file is not supported for the old xls formats because they request the formula in RPN notation which would require knowledge of the function code of this formula.

In folder "examples/other" there's a new demo, demo_ignore_formula which creates an ods file with external data and a master file containing references to these external data - I think that's what you need. It works for ods, but not for xlsx because Excel creates separate xml files for the external references which, of course, cannot be mimiced by the boIgnoreFormulas option.

See also: http://wiki.lazarus.freepascal.org/FPSpreadsheet#Unsupported_formulas

Note: This feature is experimental and certainly there's a bug here and there. Reports are welcome.
« Last Edit: February 21, 2018, 09:55:07 am by wp »

Arion58

  • New Member
  • *
  • Posts: 30
Re: Cannot load ods file with formulas
« Reply #22 on: February 21, 2018, 02:09:35 pm »
@wp: As I read your post, this is exactly what I need (and what I suggested). Thank you very much. Just one more question: Where can I find the new revision 6209, and how to install?
Unfortunately, at this very moment I don't have the time to try, but I certainly will. And I will let you know my results.

wp

  • Hero Member
  • *****
  • Posts: 11854
Re: Cannot load ods file with formulas
« Reply #23 on: February 21, 2018, 02:53:29 pm »
If you have an svn client (e.g. TortoiseSVN on Windows) you should checkout from svn://svn.code.sf.net/p/lazarus-ccr/svn/components/fpspreadsheet. Or, using the browser, you download the entire CCR repository from https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tarball and extract only the folder(s) that you need (fpspreadsheet is in components/fpspreadsheet).

Arion58

  • New Member
  • *
  • Posts: 30
Re: Cannot load ods file with formulas
« Reply #24 on: February 22, 2018, 09:46:01 am »
I tried the new version of fpspreadsheet and the demo you mentioned. It seems to work first. But a strange thing happens: If I just open master.ods (with LibreOffice 4.3.3.2 under Debian Linux) and save it ("save as", because I didn't modify anything), the file is much bigger (12,5 kB instead of 3,3 kB), and when I try then to open it with fpspreadsheet I get an error message "'...(path of external.ods)'#Sheet" is not a valid worksheet name.
When I break, the Call Stack is as follows:
Code: Pascal  [Select][+][-]
  1. #0 fpc_raiseexception at :0
  2. #1 ADDWORKSHEET(0x7ffff7fb4e10, 0x7ffff7fe8d58 '''file:///home/adrian/Free%20Pascal/SpreadSheet/external.ods''#Sheet', true) at common/fpspreadsheet.pas:8570
  3. #2 READFROMSTREAM(0x7ffff7fb50d0, 0x7ffff7fe4940, 0x0, 0) at common/fpsopendocument.pas:2540
  4. #3 READFROMFILE(0x7ffff7fb50d0, 0x84e688 'master.ods', 0x0, 0) at common/fpsreaderwriter.pas:489
  5. #4 READFROMFILE(0x7ffff7fb4e10, 0x84e688 'master.ods', 5, 0x0, 0) at common/fpspreadsheet.pas:8270
  6. #5 READFROMFILE(0x7ffff7fb4e10, 0x84e688 'master.ods', 0x0, 0) at common/fpspreadsheet.pas:8333
  7. #6 TESTBUTTONCLICK(0x7ffff7fbe610, 0x7ffff7fb3e50) at unitbank.pas:133
  8. #7 CLICK(0x7ffff7fb3e50) at include/control.inc:2736
  9. #8 CLICK(0x7ffff7fb3e50) at include/buttoncontrol.inc:54
  10. #9 CLICK(0x7ffff7fb3e50) at include/buttons.inc:169
  11. #10 WMDEFAULTCLICKED(0x7ffff7fb3e50, {MSG = 66567, UNUSEDMSG = 0, WPARAM = 15135088, LPARAM = 13882960, RESULT = 140737488346144}) at include/buttoncontrol.inc:20
  12. #11 SYSTEM$_$TOBJECT_$__$$_DISPATCH$formal at :0
  13. #12 .Ld48 at :0
  14. #13 .Ld47 at :0
  15. #14 ?? at :0
  16. #15 ?? at :0
  17. #16 ?? at :0
  18. #17 ?? at :0
  19. #18 ?? at :0
  20.  

I get the same error message when I try to open my own spreadsheet files.

wp

  • Hero Member
  • *****
  • Posts: 11854
Re: Cannot load ods file with formulas
« Reply #25 on: February 22, 2018, 10:34:14 am »
The size difference is normal. FPSpreadsheet writes only the data which are absolutely necessary, but Calc writes also tons of default formats. And looking at the internal xml files it seems that it writes also a copy of the external reference table to the destination file. fpspreadsheet does not expect this when reading the file. In the new revision  6211, this copied sheet is ignored (it would not be evaluated anyway).

Arion58

  • New Member
  • *
  • Posts: 30
Re: Cannot load ods file with formulas
« Reply #26 on: February 24, 2018, 11:37:06 am »
I understand your answer concerning the file size. But what about the error message?

wp

  • Hero Member
  • *****
  • Posts: 11854
Re: Cannot load ods file with formulas
« Reply #27 on: February 24, 2018, 11:56:33 am »
Did you use the new rev 6211? There should not be an error any more. At least if you run the program outside the IDE. Inside the IDE, i.e. the debugger, there will be an exception message because the file written by Calc does contain the external reference which is still unknown to fps. Adding EExprParser and ECalcEngine to the list of ignored exceptions (Tools > Options > Debugger > Language Exceptions > Add, type EExprParser, OK, then Add, and type ECalcEngine) fixes this.

Arion58

  • New Member
  • *
  • Posts: 30
Re: Cannot load ods file with formulas
« Reply #28 on: February 24, 2018, 08:16:44 pm »
I got r6212 now, and it works for reading the file. Great! Only, when I write the file I lose the formula's, only the resulting values are written. Is there a way to preserve the formulas?

wp

  • Hero Member
  • *****
  • Posts: 11854
Re: Cannot load ods file with formulas
« Reply #29 on: February 24, 2018, 10:15:43 pm »
Please explain what you want to do, I am a bit lost now. I have the impression from the discussion so far, that there is a back-and-forth from reading to writing and back. This is always a problem because fpspreadsheet does not support all features of the file formats and after reading features of the original file will be lost, and after writing possibly again.

 

TinyPortal © 2005-2018