I provide my users with an Excel file as a template and they insert the data. Two of the columns have a formula that displays a data value based on other columns in the row. In my code when I am processing the rows I check to see that the row is not empty
by either doing something like
if ( ! row.IsEmpty() )
if ( row.CellsUsed().Count() > 0 )
These conditions succeeded even on rows which have no data in them --- they just have the two columns which have formulas in them. I know that Excel treats a formula in a cell the same way it treats a data value in a cell. To Excel, the formula IS the data
value. Therefore, if the cell contains a formula, the cell is not empty.
To my mind, a formula is not the same as a data value. To my mind, a formula OPERATES on a cell by INSERTING a data value. To my mind, a cell could be referenced by a formula and still be empty. But that's not the way Excel views formulas. I have
seen other discussions where the posters complain that when they access a cell's value they end up retrieving the formula and not retrieving the data value. So I see that I am not the only one who finds Excel's treatment of formulas awkward and unexpected.
In my case, when I want to see if the row is not empty, I do this
if ( row.CellsUsed().Count() > 2 )
I doubt if it makes sense to put a flag on all those methods like IsEmpty(), CellsUsed(), LastRowUsed(), LastCellUsed(), etc. so that they don't count cells with formulas as being "used". I also wouldn't consider this a bug in ClosedXML, because
this is the way Excel does things. I just want to point out that I find Excel's way of doing this as awkward and non-intuitive.
Anyway, what do you think about this discussion?