empty cells are showing .Text as "NaN" using NPPlus in C#

Question

Our client gives Excel files as input and we rip the text out to do stuff with. Whatever they've done is causing some cells to have the .Text property of NaN, but show as blank when viewed through Excel itself.

My helper class does a very simple loop through the populated cells (loop omitted from sample code)

var package = new ExcelPackage(stream)
var workSheet = package.Workbook.Worksheets[parameters.workSheetNumber];
var cell = workSheet.Cells[row, column];
value = cell.Text;

Here I was thinking .Text meant it would show the text representation shown by Excel when viewed through the app, but for this case it does not. Is there a way to get the actual text representation? Or, if not, is there a way to confirm if NaN is there because it's not a number, or there because the cell has the literal text value of NaN?

Answer 1

The problem, ultimately, is differentiating between a text value of "NaN" and a numerical object value that's represented as NaN. The prior would indicate the cells text value is NaN, the latter would indicate the cell thinks that it both is and isn't a number and is having a fit.

Running my input file through the code in debug mode, the cell's .Text property showed "NaN" in every instance, but the .Value property did change. The faulty cells all had the value type as double.

The conservative solution is to check for NaN doubles

if (cell.Text == "NaN" && cell.Value.GetType() == typeof(double))
    value = "";
else
    value = cell.Text;

The above might fail from NaN due to other numeric types

if (cell.Text == "NaN" && cell.Value.GetType() != typeof(string))
    value = "";
else
    value = cell.Text;

The above would fix NaN for any case where NaN wasn't provided as a literal string in the cell. The downside to this solution, at least with regards to the original problem, is that there might possibly be cell value types where NaN would display in the Excel Display causing us to alter the value in the other direction.

I've run my code against data from many different clients without encountering this issue though, so while there might be hypothetical problems with both solutions, whatever this client's doing is rare enough that both solutions should be sufficient.