Forum > FPSpreadsheet

Bug. Formula row and column

(1/1)

**veb86**:

The row() formula does not work correctly.

If there are no arguments inside the formula, then it returns the number of the line in which it is written. Now if the line is empty, the calculation is not performed.

--- 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 fpsROW(var Result: TsExpressionResult; const Args: TsExprParameterArray);{ ROW( [reference] ) Returns the row number of a cell reference (starting at 1!) "reference" is a reference to a cell or range of cells. If omitted, it is assumed that the reference is the cell address in which the ROW function has been entered in. }begin Result := ErrorResult(errArgError); if Length(Args) = 0 then exit; // We don't know here which cell contains the formula. case Args[0].ResultType of rtCell : Result := IntegerResult(Args[0].ResRow + 1); rtCellRange: Result := IntegerResult(Args[0].ResCellRange.Row1 + 1); else Result := ErrorResult(errWrongType); end;end;

--- 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";}};} --- if Length(Args) = 0 then exit; // We don't know here which cell contains the formula.

If you don't know that row() and column() behave differently in libraries, this can cause errors during operation.

If the value obtained when applying the formula row() is included in min(), then the calculation causes the program to freeze.

Formula column() same error

I am attaching an example file

my code:

--- 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";}};} --- b.Options := [boReadFormulas]; b.ReadFromFile('d:\test1\row.xlsx', sfOOXML); sh := b.GetFirstWorksheet; b.CalcFormulas; // << freeze b.WriteToFile('d:\test1\444111111.xlsx', sfOOXML, true);

Description of the formula:

https://support.microsoft.com/en-us/office/row-function-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d

**wp**:

This one is tough... I have no idea how to solve this without rewriting a lot of the formula engine. The problem is that the cell from which a formula is called is listed in the argument - but when there is no argument there is no way how the formula could find out from where it was called...

**veb86**:

--- Quote from: wp on July 10, 2024, 10:55:14 am ---This one is tough... I have no idea how to solve this without rewriting a lot of the formula engine. The problem is that the cell from which a formula is called is listed in the argument - but when there is no argument there is no way how the formula could find out from where it was called...

--- End quote ---

It's not a big problem, I'll fix my calculator. Itâ€™s frustrating that the program breaks down when calculating such a formula.

Navigation

[0] Message Index