### Bookstore

 Computer Math and Games in Pascal (preview) Lazarus Handbook

### Author Topic: Formula in cell  (Read 743 times)

#### straetch

• Jr. Member
• Posts: 75
##### Formula in cell
« on: April 15, 2022, 02:31:21 pm »
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.

#### wp

• Hero Member
• Posts: 10297
##### Re: Formula in cell
« Reply #1 on: April 15, 2022, 08:01:41 pm »
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
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.
19.     WriteLn(s);
20.     s := StringReplace(s, 'A1', 'B1', [rfReplaceAll]);
21.     ws.WriteFormula(1, 0, s);
23.     WriteLn(s);
24.
25.     wb.WriteToFile('test.xlsx', true);
26.   finally
27.     wb.Free;
28.   end;
29.
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
« Last Edit: April 15, 2022, 11:41:14 pm by wp »

#### straetch

• Jr. Member
• Posts: 75
##### Re: Formula in cell
« Reply #2 on: April 16, 2022, 09:39:21 am »
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

• Hero Member
• Posts: 10297
##### Re: Formula in cell
« Reply #3 on: April 16, 2022, 11:58:14 am »
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

• Jr. Member
• Posts: 75
##### Re: Formula in cell
« Reply #4 on: April 17, 2022, 08:24:56 am »
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;
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

• Jr. Member
• Posts: 75
##### Re: Formula in cell
« Reply #5 on: April 17, 2022, 12:32:17 pm »
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

• Hero Member
• Posts: 10297
##### Re: Formula in cell
« Reply #6 on: April 17, 2022, 12:40:53 pm »
I don't know what you are doing. It is working. For sure...

#### straetch

• Jr. Member
• Posts: 75
##### Re: Formula in cell
« Reply #7 on: April 18, 2022, 09:56:24 am »
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.