* * *

Author Topic: After edit of file the formulas are lost, what is wrong with this code?  (Read 1401 times)


  • Guest
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]
  1. program excelexport1;
  2. uses sysutils, laz_fpspreadsheet,fpspreadsheet, fpsTypes;
  3. var  aWbk: TsWorkbook;
  4.      aWst: TsWorksheet;
  5.      aDir:string;
  6. begin
  7.   //create edit save close, open edit save test
  8.   aDir:=ExtractFilePath(ParamStr(0));
  9.   aWbk := TsWorkbook.Create;
  10.   aWbk.AddWorksheet('Tabelle1');
  11.   if aWbk.GetWorksheetCount=0 then aWbk.AddWorksheet('Tabelle1');
  13.   if aWbk.GetWorksheetCount>0 then aWst := aWbk.GetWorksheetByIndex(0)
  14.   else aWst := nil;
  16.  try
  17.   if Assigned(aWst) then begin
  18.     aWst.WriteText(0,4,'Formulas lost?');
  19.     aWst.WriteNumber(1,1,1); aWst.WriteNumber(1,2,2);
  20.     aWst.WriteFormula(1,3,'B2+C2');
  21.     aWst.WriteFormula(1,4,'SUM(B2:D2)');
  22.     aWbk.WriteToFile(aDir+'test7.ods', sfOpenDocument, true);
  23.   end;
  24.   aWbk.Free;
  26.   Writeln('Document created here: '+aDir+'test7.ods'+LineEnding+
  27.           'Open, view and close it.'+LineEnding+
  28.           'Press [enter] when you are ready to edit it.'+LineEnding);
  29.   ReadLn;
  31.   //NOw reopen and edit it
  32.   aWbk := TsWorkbook.Create;
  33.   aWbk.ReadFromFile(aDir+'test7.ods', sfOpenDocument);
  34.   if aWbk.GetWorksheetCount>0 then aWst := aWbk.GetWorksheetByIndex(0)
  35.   else aWst := nil;//
  36.   if Assigned(aWst) then begin
  37.     aWst.WriteText(0,1,TimeToStr(now));
  38.     aWbk.WriteToFile(aDir+'test7.ods', sfOpenDocument, true);
  39.   end;
  41.  finally
  42.   aWbk.Free;
  43.  end;
  44.  Writeln('Now reopen it and check the formulas.'+LineEnding+
  45.          'Press [enter] to close the application.'+LineEnding);
  46.  ReadLn;
  47. end.


  • Guest
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). 


  • Hero Member
  • *****
  • Posts: 4633
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.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10


  • Guest
Thanks, sorry I don't saw it on wiki.  :-[



Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus