[Solved]Cannot load ods file with formulas lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- Arion58 · 2018-02-19 14:58 I try to load a spreadsheet from a ods file using fpspreadsheet. First I did just like this:Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i var  WB:              TsWorkBook;begin  WB := TsWorkbook.Create;  WB.ReadFromFile(OdsFName); 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 · 2018-02-19 15:34 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 · 2018-02-19 16:38 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 · 2018-02-19 17:06 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 · 2018-02-19 19:36 Quote from: Arion58 on February 19, 2018, 06:06:45 pmWhat 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 · 2018-02-19 20:11 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 · 2018-02-19 20:28 Quote from: Arion58 on February 19, 2018, 09:11:47 pmAnd 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 · 2018-02-19 20:50 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 · 2018-02-19 20:58 Now I succeeded to produce an example. Only you will have to adjust the absolute path in DDE.ods cell A1. ---------------------------------------------------------------------- wp · 2018-02-19 22:09 Sorry no luck: cell A1 on the DDE sheet is empty. Named ranges - guess what... - are not supported either. ---------------------------------------------------------------------- Arion58 · 2018-02-19 22:16 Sorry for my mistake. Just forgot to save DDE.ods... New try. ---------------------------------------------------------------------- Thaddy · 2018-02-20 08:19 Arion, here is a very simple example that uses (late bound) COM: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i {$IFNDEF WINDOWS}{$ERROR This is for Windows Only!}{$ENDIF}uses sysutils, activex, comobj, variants;var  Cols: integer;  Rows: integer;        Excel, XLSheet: Variant;  failure: Integer;begin  failure:=0;  try    Excel:=CreateOleObject('Excel.Application');  except    failure:=1;  end;  if failure = 0 then  begin    Excel.Visible:=False;    Excel.WorkBooks.Open(); //<----- put your own filename here    XLSheet := Excel.Worksheets[1];    Cols := XLSheet.UsedRange.Columns.Count;    Rows := XLSheet.UsedRange.Rows.Count;     //Insert Data    Excel.Cells[1, 1].Value := 'SwissDelphiCenter.ch';    Excel.Cells[2, 1].Value := 'http://www.swissdelphicenter.ch';    Excel.Cells[3, 1].Value := FormatDateTime('dd-mmm-yyyy', Now);     Excel.Range['A2', 'D2'].Value := VarArrayOf([1, 10, 100, 1000]);     // Save the Workbook    Excel.SaveAs(Excell_Filename);     Excel.Workbooks.Close;    Excel.Quit;    Excel:=Unassigned;  end;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 · 2018-02-20 08:28 Quote from: Thaddy on February 20, 2018, 09:19:26 amArion, 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 · 2018-02-20 08:40 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 · 2018-02-20 08:55 Quote from: rvk on February 20, 2018, 09:28:26 amQuote from: Thaddy on February 20, 2018, 09:19:26 amArion, 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. ---------------------------------------------------------------------- wp · 2018-02-20 08:57 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 · 2018-02-20 09:23 Quote from: Arion58 on February 19, 2018, 09:11:47 pmUntil 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 · 2018-02-20 09:25 Quote from: Thaddy on February 20, 2018, 09:55:22 amDDE 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 · 2018-02-20 09:55 Quote from: wp on February 20, 2018, 10:25:52 amLibreOffice is cross- platform, and the DDE formula does not even seem to exist in Excel (ht tps://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_DDE_functio n). 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 · 2018-02-20 10:00 Quote from: Thaddy on February 20, 2018, 09:55:22 amDDE is just as proprietary Microsoft as COM is. So it is modelled after a well known technology as COM is an simplified Corba ? ---------------------------------------------------------------------- Arion58 · 2018-02-20 16:52 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 · 2018-02-20 23:14 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. ---------------------------------------------------------------------- Arion58 · 2018-02-21 13:09 @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 · 2018-02-21 13:53 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 · 2018-02-22 08:46 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i #0 fpc_raiseexception at :0#1 ADDWORKSHEET(0x7ffff7fb4e10, 0x7ffff7fe8d58 '''file:///home/adrian/Free%20Pascal/SpreadSheet/external.ods''#Sheet' , true) at common/fpspreadsheet.pas:8570#2 READFROMSTREAM(0x7ffff7fb50d0, 0x7ffff7fe4940, 0x0, 0) at common/fpsopendocument.pas:2540#3 READFROMFILE(0x7ffff7fb50d0, 0x84e688 'master.ods', 0x0, 0) at common/fpsreaderwriter.pas:489#4 READFROMFILE(0x7ffff7fb4e10, 0x84e688 'master.ods', 5, 0x0, 0) at common/fpspreadsheet.pas:8270#5 READFROMFILE(0x7ffff7fb4e10, 0x84e688 'master.ods', 0x0, 0) at common/fpspreadsheet.pas:8333#6 TESTBUTTONCLICK(0x7ffff7fbe610, 0x7ffff7fb3e50) at unitbank.pas:133#7 CLICK(0x7ffff7fb3e50) at include/control.inc:2736#8 CLICK(0x7ffff7fb3e50) at include/buttoncontrol.inc:54#9 CLICK(0x7ffff7fb3e50) at include/buttons.inc:169#10 WMDEFAULTCLICKED(0x7ffff7fb3e50, {MSG = 66567, UNUSEDMSG = 0, WPARAM = 15135088, LPARAM = 13882960, RESULT = 140737488346144}) at include/buttoncontrol.inc:20#11 SYSTEM$_$TOBJECT_$__$$_DISPATCH$formal at :0#12 .Ld48 at :0#13 .Ld47 at :0#14 ?? at :0#15 ?? at :0#16 ?? at :0#17 ?? at :0#18 ?? at :0  I get the same error message when I try to open my own spreadsheet files. ---------------------------------------------------------------------- wp · 2018-02-22 09:34 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 · 2018-02-24 10:37 I understand your answer concerning the file size. But what about the error message? ---------------------------------------------------------------------- wp · 2018-02-24 10:56 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 · 2018-02-24 19:16 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 · 2018-02-24 21:15 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. ---------------------------------------------------------------------- Arion58 · 2018-02-25 15:42 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 · 2018-02-25 16:52 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 · 2018-02-25 18:04 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 · 2018-02-25 19:05 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. ---------------------------------------------------------------------- Arion58 · 2018-02-26 10:20 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 · 2018-02-26 10:22 PS: If you agree, I will mark the topic as Solved. ---------------------------------------------------------------------- wp · 2018-02-26 11:02 Quote from: Arion58 on February 26, 2018, 11:20:45 am 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. Quote from: Arion58 on February 26, 2018, 11:20:45 amWhen 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 · 2018-02-26 12:36 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 · 2018-02-27 20:27 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 · 2018-02-27 22:34 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 · 2018-02-28 11:13 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 · 2018-02-28 12:18 Quote from: Arion58 on February 26, 2018, 01:36:33 pmThe 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 · 2018-02-28 17:53 For the Round-formula issue you don't need any demo file. Just execute the following program:Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i var Formula: String;    book:        TsWorkbook;    Sheet:      TsWorkSheet;    CellP:        PCell;begin  book := TsWorkbook.Create;  try    Sheet := book.AddWorksheet('Sheet');    Formula:= 'ROUND((A1)/(1+0.21)*0.21;2)';    CellP:= Sheet.GetCell(0,1);    Sheet.WriteFormula(CellP,Formula);    Sheet.InsertRow(0);  finally    book.Free;  end;end; The error will occur. ---------------------------------------------------------------------- wp · 2018-02-28 18:19 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 · 2018-02-28 18:27 I am not shure, I checked the documentation: https://wiki.openoffice.o rg/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 ';'. ---------------------------------------------------------------------- wp · 2018-02-28 19:06 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 · 2018-02-28 20:12 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 · 2018-02-28 21:33 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 · 2018-03-01 11:07 I have created a small ods file with the following program:Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i var Formula,OdsFName: String;    book:             TsWorkbook;    Sheet:          TsWorkSheet;    CellP:            PCell;begin  OdsFName:= BuchDir + 'RoundDemo.ods';  book := TsWorkbook.Create;  try    Sheet := book.AddWorksheet('Sheet');    Formula:= 'ROUND((A1)/(1+0.21)*0.21;2)';    CellP:= Sheet.GetCell(0,1);    Sheet.WriteFormula(CellP,Formula);    Formula:= 'ROUND((A1)/(1+0.21)*0.21,2)';    CellP:= Sheet.GetCell(0,2);    Sheet.WriteFormula(CellP,Formula);    book.Options:= book.Options + [boIgnoreFormulas,boReadFormulas];    book.WriteToFile(OdsFName,sfOpenDocument,TRUE);  finally    book.Free;  end; 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 · 2018-03-01 13:44 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i program project1; {$mode objfpc}{$H+} uses  fpspreadsheet, fpstypes, fpsopendocument; var OdsFName: String;    book: TsWorkbook;    Sheet: TsWorkSheet;    CellP: PCell;begin  OdsFName:= 'RoundDemo.ods';  book := TsWorkbook.Create;  try    book.Options:= book.Options + [boIgnoreFormulas,boReadFormulas];    Sheet := book.AddWorksheet('Sheet');     Sheet.WriteNumber(0, 0, 3.141592);   // *** List separator SEMICOLON ***    // ods syntax for formula    Sheet.WriteFormula(0, 1, 'of:=ROUND([.A1]/(1+0.21)*0.21;2)'); // exactly like in file    Sheet.WriteFormula(0, 2, 'of:=ROUND(A1/(1+0.21)*0.21;2)');     // Excel syntax for formula    Sheet.WriteFormula(0, 3, '=ROUND([.A1]/(1+0.21)*0.21;2)');    Sheet.WriteFormula(0, 4, '=ROUND(A1/(1+0.21)*0.21;2)');     // fpspreadsheet syntax for formula    Sheet.WriteFormula(0, 5, 'ROUND([.A1]/(1+0.21)*0.21;2)');    Sheet.WriteFormula(0, 6, 'ROUND(A1/(1+0.21)*0.21;2)');     // **** List separator COMMA ***    // ods syntax for formula    Sheet.WriteFormula(1, 1, 'of:=ROUND([.A1]/(1+0.21)*0.21,2)');    Sheet.WriteFormula(1, 2, 'of:=ROUND(A1/(1+0.21)*0.21,2)');     // Excel syntax for formula    Sheet.WriteFormula(1, 3, '=ROUND([.A1]/(1+0.21)*0.21,2)');    Sheet.WriteFormula(1, 4, '=ROUND(A1/(1+0.21)*0.21,2)');     // fpspreadsheet syntax for formula    Sheet.WriteFormula(1, 5, 'ROUND([.A1]/(1+0.21)*0.21,2)');    Sheet.WriteFormula(1, 6, 'ROUND(A1/(1+0.21)*0.21,2)');     book.WriteToFile(OdsFName,sfOpenDocument,TRUE);  finally    book.Free;  end;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 · 2018-03-01 15:19 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 · 2018-03-01 15:38 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 · 2018-03-01 15:50 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 · 2018-03-01 16:10 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 · 2018-03-01 16:48 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 · 2018-03-01 23:28 Quote from: Arion58 on March 01, 2018, 05:48:16 pmShould 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.