how to remove a formula but keep the value in the cell with
FPSpreadsheet
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
franzala · 2021-11-18 18:18

I'm looking for a way to remove the formula in a cell but keep the
value in that cell with FPSpreadsheet.

Thanks in advance for any help.

----------------------------------------------------------------------
wp · 2021-11-18 18:34

Simply call Worksheet.DeleteFormula(cell) for the cell with the
formula to be deleted. Make sure that formulas already are calculated
before doing this: either add boAutoCalc to the Workbook.Options, or
call Workbook.CalcFormulas.

The following test project puts the numbers 1 and 2 into cells A1 and
A2 and the formula "=A1+A2" into cell A3, auto-calculates the formula
and lists the cells. Then the formula is deleted as described, and the
sheet is listed again: the formula is gone, but the result remains.

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  fpsTypes, fpspreadsheet, fpsUtils;var  workbook:
TsWorkbook;  worksheet: TsWorksheet;  cell: PCell;  r: Cardinal;begin 
workbook := TsWorkbook.Create;  workbook.Options := workbook.Options +
[boAutoCalc];   worksheet := workbook.AddWorksheet('Test'); 
worksheet.WriteNumber(0, 0, 1.0);  worksheet.WriteNumber(1, 0, 2.0); 
cell := worksheet.WriteFormula(2, 0, '=A1+A2');   for r := 0 to 2 do 
begin    Write(GetCellString(r, 0), ': value ',
worksheet.ReadAsText(r, 0));    cell := worksheet.FindCell(r, 0);   
if HasFormula(cell) then      WriteLn(' formula: ',
worksheet.ReadFormula(cell))    else      WriteLn(' formula: none'); 
end;  WriteLn;    worksheet.DeleteFormula(cell);    for r := 0 to 2
do  begin    Write(GetCellString(r, 0), ': value ',
worksheet.ReadAsText(r, 0));    cell := worksheet.FindCell(r, 0);   
if HasFormula(cell) then      WriteLn(' formula: ',
worksheet.ReadFormula(cell))    else      WriteLn(' formula: none'); 
end;      workbook.Free;    ReadLn;end.

----------------------------------------------------------------------
franzala · 2021-11-21 17:03

Thanks a lot, I will do so.