Forum > FPSpreadsheet

Precedents and Dependents

**jollytall**:

I read in the Wiki that the formulas are stored in a special tree for faster calculations. Using that, is there an easy way to find those cells that depend on a certain cell and those that this cell is dependent on? I guess that information is already available somewhere, so would be a great help to access it, rather than doing it myself.

Thanks,

**wp**:

Formula calculation in fpspreadsheet is not optimized at all: when any cell changes all formulas are recalculated. A formula calculation chain is not implemented.

**jollytall**:

Thanks,

I started to write a routine to check (i) the formula of my cell and extract from it all the cells it refers to (precedents), and (ii) go through all cells and check if their formula refers to my cell (dependents) but it is not so easy.

I wrote a simplified parser but I am sure most (if not all) of those steps are already written. Could you suggest a way to easily write e.g. a routine that checks one string (a formula) and quickly gives back if it refers to a given cell (probably another string). I did it, but had to do a lot of messy parsing with operators, brackets, functions, etc, and to make sure that e.g. A1 is not found in a formula =A11. It would be great if I could access some of the internal function, around function parsers. E.g. even the most elementary TsExpressionScanner.IsAlpha is a protected method and thus cannot be accessed, while it could even be a class method as it does not use any other part of the class.

Best would be a function, like tCell.Refers(aCell : pCell) : boolean; Isn't there such a function? I would assume you need it when e.g. you move a cell and change all those cells that depend on it.

**wp**:

The formula parser of fpspreadsheet is a variant of the FPExpressionParser coming with FPC (https://wiki.lazarus.freepascal.org/How_To_Use_TFPExpressionParser). It creates a tree of "expression nodes" from a formula string. One extension is the introduction of specific cell and cell range nodes which contain information on the cell or cell range used in that particular element of the expression.

The expression parser has a method IterateNodes() in which the entire tree is traversed recursively so that every node can be visited.

--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} --- function IterateNodes(AProc: TsExprNodeProc; AData1, AData2: Pointer): boolean; What happens with each node found depends on the function parameter AProc (signature: procedure(ANode: TsExprNode; AData1, AData2: Pointer; var MustRebuildFormulas: Boolean)). AData1 and AData2 are general-purpose parameters provided by the calling function. The task is to check each node whether it is a cell node and then to collect the cells found in a list. The list can be passed for example in parameter AData1.

Finally the question how to find the formulas: The worksheet collects all formulas in a list accessible in its property Formulas. Each formula record contains the parsed expression as element Parser.

Therefore, the final solution is as follows:

--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---procedure CollectCells(ANode: TsExprNode; AData1, AData2: Pointer; var MustRebuildFormulas: Boolean);var List: TStringList; cellStr: String; cellNode: TsCellExprNode; rngNode: TsCellRangeExprNode; rng: TsCellRange; r, c: Cardinal;begin List := TStringList(AData1); if ANode.NodeType = rtCell then begin cellNode := TsCellExprNode(ANode); cellStr := GetCellString(cellNode.Row, cellNode.Col); List.Add(cellStr); end else if ANode.NodeType = rtCellRange then begin rngNode := TsCellRangeExprNode(ANode); rng := rngNode.Range; for r := rng.Row1 to rng.Row2 do for c := rng.Col1 to rng.Col2 do begin cellStr := GetCellString(r, c); List.Add(cellStr); end; end;end; procedure TForm1.Button1Click(Sender: TObject);var workbook: TsWorkbook; worksheet: TsWorksheet; f: PsFormula; L: TStringList; i: Integer;begin ... for f in worksheet.Formulas do begin Memo1.Lines.Add('Formula in cell ' + GetCellString(f^.Row, f^.Col) + ': =' + f^.Text); L := TStringList.Create; f^.Parser.IterateNodes(@CollectCells, L, nil); Memo1.Lines.Add(' Used cells: ' + L.CommaText); Memo1.Lines.Add(''); L.Free; end;end; See attached project as a worked-out example.

But note that the code is a bit simplified because it only considers formulas which have all referenced cells in the same worksheet in which also the cell with the formula itself resides. I am leaving the extension to the more general case of 3d-formulas to you.

**jollytall**:

Thanks, it is great. I just made a small function for myself.

--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---function Precedents(aFormula : tSFormula) : tStringList; begin result := tStringList.Create; result.Duplicates := dupIgnore; aFormula.Parser.IterateNodes(@CollectCells, result, nil); end; Ideally something like this could be included in the tsFormula, however that is - as I see - a much more general thing and it is only a record, so not even a descendant advanced record can be created from it. Anyway it works as a standalone utility.

I guess the Dependents is a bit more complicated. Am I right that the only way is to iterate through all formulas and check if our cell is in the Precedent list?

Navigation

[0] Message Index

[#] Next page