[Solved]Cannot load ods file with formulas

[Arion58]

I try to load a spreadsheet from a ods file using fpspreadsheet. First I did just like this:

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

1. var
2.   WB:              TsWorkBook;
3. begin
4.   WB := TsWorkbook.Create;
5.   WB.ReadFromFile(OdsFName);
6.  

This results in a spreadsheet without formulas; they are all evaluated and the cells contain only the resulting values. So I decided to try the option boReadFormulas which I added to WB.Options before reading. Now I get an error message "Unknown identifier: DDE". Actually, in my file, I use a DDE-function to fetch a value from another spreadsheet file, like this: '=DDE("soffice";"file:///path/to/file.ods";"NameOfCell")'. Why isn't this formula recognized? And, if it isn't, how can it be evaluated in the first case?

[wp]

DDE is not implemented in fpspreadsheet. I thought this is a Windows-only technology, and to be honest I don't even know how it works internally, and I have never used it in my own Excel files. But of course you can try to implement the formula on your own and upload a patch (formula support is in unit fpsfunc).

But at least you should prepare a simple file with such a DDE formula and upload it here for me (rename its extension to zip to bypass the forum software). FPSpreadsheet should not crash when that unknown formula is detected, and I can try to fix it although I will not implement the formula.

[Thaddy]

DDE is even deprecated by Microsoft. Since COM days... which is like 1997. Do not use it or expect it to work. Right now, DDE is re-implemented as a fall-back on top of COM and a fall-back.
Note COM is NOT legacy (as of 2018!), merely complicated....for lazy people...very lazy people...

Alternative is to re-install WIN95 in a VM.

DDE is a predecessor to COM which in itself opened up better technologies.

[Arion58]

What I intend to do with DDE is to fetch a value from another ods. I couldn't find another way at that moment to do this. Is there any? If I can replace DDE by something better, I'll do it immediately.
By the way: My OS is Linux Hydrogen (Debian). I use spreadsheets for my book-keeping, with calc of libreoffice. I do a lot with macro's, but have decided to do a part of it with Lazarus.

[wp]

What I intend to do with DDE is to fetch a value from another ods.

Normally you would write a formula with an external reference like '=[external_file]Sheet1!$A$19' (Excel-syntax, but LibreOffice Calc should be similar). But this does not work in fpspreadsheet either, because external references are not supported yet.

Why don't you use the builtin macro language of the office application?

[Arion58]

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?

[wp]

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?

It should ignore it. If the application which created the file is able to execute the DDE formula it normally writes the formula result into the formula cell; and when fpspreadsheet reads the file it will see the result and use this instead of the formula. If the generator application does not write the result fpspreadsheet will get nothing else than an empty cell.

The problem is that the program is crashing according to your post, and in order to be able to fix this I asked you for a small demo file containing a DDE formula.

[Arion58]

Sorry that I did not respond yet to your request. I thought it might not be necessary if I find an alternative for DDE. I try to write a simple example, but didn't succeed yet. Thanks anyway for your help.
May I ask another question related to my problem? How can I access Named Ranges in fpspreadsheet? I searched, but couldn't find anything about it.

[Arion58]

Now I succeeded to produce an example. Only you will have to adjust the absolute path in DDE.ods cell A1.

[wp]

Sorry no luck: cell A1 on the DDE sheet is empty.

Named ranges - guess what... - are not supported either.

[Arion58]

Sorry for my mistake. Just forgot to save DDE.ods...
New try.

[Thaddy]

Arion, here is a very simple example that uses (late bound) COM:

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

1. {$IFNDEF WINDOWS}{$ERROR This is for Windows Only!}{$ENDIF}
2. uses sysutils, activex, comobj, variants;
3. var
4.   Cols: integer;
5.   Rows: integer;      
6.   Excel, XLSheet: Variant;
7.   failure: Integer;
8. begin
9.   failure:=0;
10.   try
11.     Excel:=CreateOleObject('Excel.Application');
12.   except
13.     failure:=1;
14.   end;
15.   if failure = 0 then
16.   begin
17.     Excel.Visible:=False;
18.     Excel.WorkBooks.Open(<Excell_Filename>); //<----- put your own filename here
19.     XLSheet := Excel.Worksheets[1];
20.     Cols := XLSheet.UsedRange.Columns.Count;
21.     Rows := XLSheet.UsedRange.Rows.Count;
22.  
23.     //Insert Data
24.     Excel.Cells[1, 1].Value := 'SwissDelphiCenter.ch';
25.     Excel.Cells[2, 1].Value := 'http://www.swissdelphicenter.ch';
26.     Excel.Cells[3, 1].Value := FormatDateTime('dd-mmm-yyyy', Now);
27.  
28.     Excel.Range['A2', 'D2'].Value := VarArrayOf([1, 10, 100, 1000]);
29.  
30.     // Save the Workbook
31.     Excel.SaveAs(Excell_Filename);
32.  
33.     Excel.Workbooks.Close;
34.     Excel.Quit;
35.     Excel:=Unassigned;
36.   end;
37. end.

This is written / found by SwissDelphiCenter and originally for Delphi, but it works in Freepascal too. COM is much more powerful than DDE.

[rvk]

Arion, here is a very simple example that uses (late bound) COM:

And how does that relate to fpspreadsheet or work when there is no Excel on the computer (or on Linux)?

(Looking at the filename in the DDE function, Arion is on Linux)

[rvk]

I think =[external_file]Sheet1!$A$19 is more likely to be implemented than DDE().

With DDE there is a application-name given which acts as DDE server (along with the topic and item names). So those parameters would need to be passed on to that application. Of course fpspreadsheet could do that but starting soffice.exe or excel.exe doesn't work on other platforms if they are not installed there.

[external_file] would be more useful (and is more used). fpspreadsheet could open a second spreadsheet with the source and act like soffice does. Although this could be slow and memory consuming.

I'm not sure how far fpspreadsheet is with implementing user-defined functions. In that case DDE() and/or [external_file] could be created as plugin by someone who really wants to implement it. So instead of ignoring the DDE(), it could pass it on (as complete string) to some user-defined code. (Also not sure if there is really a demand for it though)

[Thaddy]

Arion, here is a very simple example that uses (late bound) COM:

And how does that relate to fpspreadsheet or work when there is no Excel on the computer (or on Linux)?

(Looking at the filename in the DDE function, Arion is on Linux)

DDE is just as proprietary Microsoft as COM is.