Issue #607 resolved
Luke Lee
created an issue

I've written the value float('nan') to a spreadsheet, and it doesn't seem like NaN is represented correctly. The cell comes back as 0 after opening the resulting spreadsheet. Should NaN be represented something like this in Excel?

Comments (5)

  1. Charlie Clark

    Support for NaN will be available in 2.4 and it will be the equivalent to None no special value. At the moment it will be serialised just as 'NaN'.

    NaN in Python tends to occur in NumPy arrays. It allows mathematical operations to be performed safely while always returning NaN.

  2. Luke Lee reporter

    Thanks for the quick response! I look forward to 2.4. NaN is more common when using numpy, but doesn't there need to be a distinction between None and NaN in the resulting spreadsheet?

    What will the spreadsheet show in the cell that NaN was written to when opened in Excel?

    I think Excel formulas might know how to ignore #!NUM instead of getting an error if some other 'not a number' value is used.

  3. Charlie Clark

    #!NUM is an error code, NaN is technically a number – the equivalent of NULL – that can be used in arrays. So the two are not the same. Excel will just see an empty cell because it does not have an NaN concept. This might lead to #!NUM errors but that's not openpyxl's business.

  4. Log in to comment