[Bug] Spreadsheet should handle empty cell and or/dereference correctly

[X-Ryl669]

Is there an existing issue for this?

• I have searched the existing issues

Forums discussion

https://forum.freecadweb.org/viewtopic.php?f=3&t=68899

Version

0.20 (Development)

Full version info

OS: Manjaro Linux (KDE/plasma)
Word size of FreeCAD: 64-bit
Version: 0.20.26554 (Git) AppImage
Build type: Release
Branch: (HEAD detached at 817c14b)
Hash: 817c14b16af681b06aec7d8c79f744c7a88fa3c3
Python version: 3.9.7
Qt version: 5.12.9
Coin version: 4.0.0
OCC version: 7.5.3
Locale: French/France (fr_FR)

Subproject(s) affected?

Spreadsheet

Issue description

A Spreadsheet's cell can't reference an empty cell without breaking.
For example, if you want the user to enter a matrix describing holes in a plate with cells by setting a 1 in the cells she want a hole in, then you can't compute the hole position dynamically:
Say A1:C3 are the matrix of hole, and the user entered (A1 = 1, B2 = 1, B3 = 1).
Then you can't compute A10 = A1 * pitch, A11 = A2 * pitch and so on, since A2 is empty.

Ideally, the expression parser should have a type to store an empty cell and when used for computation return an empty cell whatsoever, without breaking.

Also, it could be very useful to be able to test for an empty cell (like =IF(A1;whatever;else)). I know about A1 == 1 ? whatever:else but this also fails if A1 is empty, and also create a special empty type so we could at least do A1 == 1?A1 * pitch : Empty

Being able to also refer to a cell by its address and indirect reference it would be very useful too as in:
A1=3
A2=4
...
C10 = indirect(A1,A2)

I've seen the PR from @realthunder implementing cells[<<A>>+<<1>>] but that's only part of the solution.

Anything else?

No response

Code of Conduct

• I agree to follow this project's Code of Conduct