Formula in cell

[straetch]

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][+][-]

1.   for i := FormuleTabelCoord.Y1 to FormuleTabelCoord.Y2 do  // elke rij
2.     for j := FormuleTabelCoord.X1 to FormuleTabelCoord.X2 do begin
3.       cell := WS.FindCell(i,j);
4.       s := WS.ReadFormula(cell); // lees de formule
5.       if HasFormula(cell) then t := 'With formula   ' else t := 'No formula   ';
6.       showmessage(t + inttostr(i) + '  ' + inttostr(j) + '   |' + s + '|');   // for test
7.       s := StringReplace(s,'$892;','$878;',[rfReplaceAll]);
8.       WS.WriteFormula(cell,s);
9.     end;
10.  

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]

A very basic test shows that your idea is working correctly for me:

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

1. program Project1;
2.  
3. uses
4.   SysUtils, fpspreadsheet, fpsutils, fpsallformats, fpstypes;
5. var
6.   wb: TsWorkbook;
7.   ws: TsWorksheet;
8.   s: String;
9. begin
10.   wb := TsWorkbook.Create;
11.   try
12.     ws := wb.AddWorksheet('Test');
13.     ws.WriteNumber(0, 0, 0.123);
14.     ws.WriteNumber(0, 1, 1.234);
15.     ws.WriteNumber(0, 2, 2.345);
16.     ws.WriteFormula(1, 0, 'A1');
17.    
18.     s := ws.ReadFormula(1, 0);
19.     WriteLn(s);
20.     s := StringReplace(s, 'A1', 'B1', [rfReplaceAll]);
21.     ws.WriteFormula(1, 0, s);
22.     s := ws.ReadFormula(1, 0);
23.     WriteLn(s);
24.    
25.     wb.WriteToFile('test.xlsx', true);
26.   finally
27.     wb.Free;
28.   end;
29.  
30.   ReadLn;
31. 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][+][-]

1.  s := StringReplace(s,'$892,','$878,',[rfReplaceAll]);   // comma rather than semicolon

[straetch]

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][+][-]

1.   cell := WS.FindCell(1,0);
2.   if HasFormula(cell) then t := 'With formula' else t := 'No formula';
3.   showmessage(t);
4.  

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]

Did you add the workbook option boReadFormulas?

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

1.   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]

Yes, the option was included.
This is my code for opening the spreadsheet:

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

1.   ok := true;
2.   try
3.     WB := TsWorkbook.Create;
4.     WB.ReadFromFile(DatasetBestand);
5.     WB.Options := WB.Options + [boReadFormulas];
6.     WS := WB.GetFirstWorksheet;
7.   except
8.     ok := false;
9.   end;
10.   if not ok then begin
11.     showmessage('Fout bij laden van de dataset');
12.     WB.Free;
13.  end;

The program did not crash.
In this particular case fpspreadsheet does not have to interpret the formula. Just read/modify/write.

[straetch]

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]

I don't know what you are doing. It is working. For sure...

[straetch]

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.