This project has moved. For the latest updates, please go here.

Cell that contains a formula is considered not empty

Aug 31, 2011 at 7:21 PM

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() )

or 

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?

Coordinator
Aug 31, 2011 at 7:38 PM
Edited Aug 31, 2011 at 7:50 PM

There are a few things going on...

The users getting the formula instead of the value is a different issue. Right now .Value returns the formula because we can't evaluate the formulas for their results yet. As soon as we can evaluate formulas we will change this behavior to return the actual results of the formulas.

As for IsEmpty and CellsUsed, they have to work consistently with RangeUsed. The way I see it you have a worksheet with 3 headers on A1,B1, and C1 and 9 rows with formulas, when you ask for worksheet.RangeUsed() it should return "A1:C10" not "A1:C1".

Thanks for the feedback though. A lot of features have risen from conversations like this. In this particular case I think the behavior is the correct one.

/Manuel