Formula in cell lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- straetch · 2022-04-15 12:31 I have a long spreadsheet with formulae such as "=AANTALLEN.ALS($A$3:$ A$892;2;$D$3:$D$892;"Meerderheid";$E$3:$E$892;"Positief")". The range has changed, so I have to manually change $892 by $878 in the formulae everywhere. I want to use fpspreadsheet to automate this. This is my code: 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   for i := FormuleTabelCoord.Y1 to FormuleTabelCoord.Y2 do  // elke rij    for j := FormuleTabelCoord.X1 to FormuleTabelCoord.X2 do begin      cell := WS.FindCell(i,j);      s := WS.ReadFormula(cell); // lees de formule    if HasFormula(cell) then t := 'With formula   ' else t := 'No formula   ';      showmessage(t + inttostr(i) + '  ' + inttostr(j) + '   |' + s + '|');   // for test      s := StringReplace(s,'$892;','$878;',[rfReplaceAll]);      WS.WriteFormula(cell,s);    end;  The processed cells do not contain the changed formula. The message says "No formula". Ihe calculated result of the formula is in the cell when verified with Excel, not the formula. No errors are flagged. Using fpspreadfsheet 1.12.0.0 ---------------------------------------------------------------------- wp · 2022-04-15 18:01 A very basic test shows that your idea is working correctly for me: 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; uses  SysUtils, fpspreadsheet, fpsutils, fpsallformats, fpstypes;var  wb: TsWorkbook;  ws: TsWorksheet;  s: String;begin  wb := TsWorkbook.Create;  try    ws := wb.AddWorksheet('Test');    ws.WriteNumber(0, 0, 0.123);    ws.WriteNumber(0, 1, 1.234);    ws.WriteNumber(0, 2, 2.345);    ws.WriteFormula(1, 0, 'A1');        s := ws.ReadFormula(1, 0);    WriteLn(s);    s := StringReplace(s, 'A1', 'B1', [rfReplaceAll]);    ws.WriteFormula(1, 0, s);    s := ws.ReadFormula(1, 0);    WriteLn(s);        wb.WriteToFile('test.xlsx', true);  finally    wb.Free;  end;    ReadLn;end. Why do you have a semicolon after the cell addresses? If Excel in your local version uses this as a list separator, you should know that fpspreadsheet is not localized, i.e. the list separator is a comma internally. And this is used also by Excel in their files, although the list separator may appear differently in the gui. So, maybe replacing the StringReplace line by the following code fixes the issue for you: 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  s := StringReplace(s,'$892,','$878,',[rfReplaceAll]);   // comma rather than semicolon ---------------------------------------------------------------------- straetch · 2022-04-16 07:39 The problem is with a spreadsheet that is created and filled with Excel. In Excel: Fill in A1 the value 1. Fill in A2 the value =A1+2 . The value 3 is shown in the spreadsheet. When the file is opened with fpspreadsheet : 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   cell := WS.FindCell(1,0);  if HasFormula(cell) then t := 'With formula' else t := 'No formula';  showmessage(t); It says" No formula". So it seems fpspreadsheet does not recognise a formula entered in Excel. Thanks for the hint on locales. Together with the wiki it gives me a better insight. ---------------------------------------------------------------------- wp · 2022-04-16 09:58 Did you add the workbook option boReadFormulas? 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   workbook.Options := workbook.Options + [boReadFormulas]; fpspreadsheet does not read formulas by default, because formula support is only partial and crashing the reading process due to an unknown formula must be avoided. ---------------------------------------------------------------------- straetch · 2022-04-17 06:24 Yes, the option was included. This is my code for opening the spreadsheet: 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   ok := true;  try    WB := TsWorkbook.Create;    WB.ReadFromFile(DatasetBestand);    WB.Options := WB.Options + [boReadFormulas];    WS := WB.GetFirstWorksheet;  except    ok := false;  end;  if not ok then begin    showmessage('Fout bij laden van de dataset');    WB.Free; end;The program did not crash. In this particular case fpspreadsheet does not have to interpret the formula. Just read/modify/write. ---------------------------------------------------------------------- straetch · 2022-04-17 10:32 From your explanation it seems obvious that WB.Options := WB.Options + [boReadFormulas]; must be located before WB.ReadFromFile(DatasetBestand);  . When I switch both statements in the same example I get an exception class 'EExprParser' with message 'unknown identifier COUNTIFS' ---------------------------------------------------------------------- wp · 2022-04-17 10:40 I don't know what you are doing. It is working. For sure... ---------------------------------------------------------------------- straetch · 2022-04-18 07:56 I confirm your code is working. My mistake: I cleaned an existing spreadsheet for my example and did not notice a remaining complex formula in a hidden column. This happens when under time pressure. Lesson learned. My apologies.