After edit of file the formulas are lost, what is wrong with this
code?
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
SunyD · 2017-05-19 11:39

Hello,
After edit of file formulas lost, what is wrong with this code?
This does not happen with spready application.

Here is the sample code to test:
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
excelexport1;uses sysutils, laz_fpspreadsheet,fpspreadsheet,
fpsTypes;var  aWbk: TsWorkbook;     aWst: TsWorksheet;   
 aDir:string;begin  //create edit save close, open edit save test 
aDir:=ExtractFilePath(ParamStr(0));  aWbk := TsWorkbook.Create; 
aWbk.AddWorksheet('Tabelle1');  if aWbk.GetWorksheetCount=0 then
aWbk.AddWorksheet('Tabelle1');   if aWbk.GetWorksheetCount>0 then aWst
:= aWbk.GetWorksheetByIndex(0)  else aWst := nil;  try  if
Assigned(aWst) then begin    aWst.WriteText(0,4,'Formulas lost?');   
aWst.WriteNumber(1,1,1); aWst.WriteNumber(1,2,2);   
aWst.WriteFormula(1,3,'B2+C2');   
aWst.WriteFormula(1,4,'SUM(B2:D2)');   
aWbk.WriteToFile(aDir+'test7.ods', sfOpenDocument, true);  end; 
aWbk.Free;   Writeln('Document created here:
'+aDir+'test7.ods'+LineEnding+          'Open, view and close
it.'+LineEnding+          'Press [enter] when you are ready to edit
it.'+LineEnding);  ReadLn;   //NOw reopen and edit it  aWbk :=
TsWorkbook.Create;  aWbk.ReadFromFile(aDir+'test7.ods',
sfOpenDocument);  if aWbk.GetWorksheetCount>0 then aWst :=
aWbk.GetWorksheetByIndex(0)  else aWst := nil;//  if Assigned(aWst)
then begin    aWst.WriteText(0,1,TimeToStr(now));   
aWbk.WriteToFile(aDir+'test7.ods', sfOpenDocument, true); 
end;  finally  aWbk.Free; end; Writeln('Now reopen it and check the
formulas.'+LineEnding+         'Press [enter] to close the
application.'+LineEnding); ReadLn;end.

----------------------------------------------------------------------
SunyD · 2017-05-19 11:51

This is very weird. 
If you  edit and save the produced document from the examle above in
spready or libre office then the formulas are not lost. (I mean before
the example reopens, edits and saves it).

----------------------------------------------------------------------
wp · 2017-05-19 11:57

Since fpspreadsheet does not support all formulas provided by the big
spreadsheet applications there's always a risk that reading a file
with formulas may crash the application. Therefore, I had decided a
long time ago to turn formula support off by default. In order to read
formulas you must add boReadFormuas to the Options of the workbook. In
order to calculate formulas whenever cell content changes you must add
boAutocalc to these Options. And in order to calculate formulas before
a workbook is saved you must add boCalcBeforeSaving. All this is
described in
http://wiki.lazarus.freepascal.org/FPSpreadsheet#Formulas.

So, you only must add boReadFormulas before you reopen the workbook in
your program. The Office applications see the formulas even without
this step.

----------------------------------------------------------------------
SunyD · 2017-05-19 12:05

Thanks, sorry I don't saw it on wiki.