Cell as Date format with no value errors on read

Issue #343 resolved
stringfellow created an issue

See trace; first discovered by attempting to read a cell written by Excel with these properties.

In [1]: import openpyxl

In [2]: wb = openpyxl.workbook.Workbook()

In [3]: a1 = wb.active['A1']

In [4]: a1.value = None

In [5]: a1.style = openpyxl.styles.Style()

In [6]: a1.data_type = a1.TYPE_NUMERIC

In [7]: a1.number_format = 'dd-mm-yy'

In [8]: a1.value
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-8-7ddd53192eab> in <module>()
----> 1 a1.value

/Users/spike/Code/openpyxl/openpyxl/cell/cell.pyc in value(self)
    347         value = self._value
    348         if self.is_date():
--> 349             value = from_excel(value, self.base_date)
    350         return value
    351

/Users/spike/Code/openpyxl/openpyxl/compat/functools.pyc in wrapper(*args, **kwds)
    120                         stats[HITS] += 1
    121                         return result
--> 122                 result = user_function(*args, **kwds)
    123                 with lock:
    124                     root, = nonlocal_root

/Users/spike/Code/openpyxl/openpyxl/date_time.pyc in from_excel(value, offset)
    113 @lru_cache()
    114 def from_excel(value, offset=CALENDAR_WINDOWS_1900):
--> 115     parts = list(jd2gcal(MJD_0, value + offset - MJD_0))
    116     fractions = value - int(value)
    117     diff = datetime.timedelta(days=fractions)

TypeError: coercing to Unicode: need string or buffer, float found

Comments (13)

  1. stringfellow reporter

    I've issued a PR for this - wasn't totally sure what it should do but hopefully a sensible default.

  2. CharlieC

    Thanks for trying to solve this but it's a non-issue. As long as you don't try and pretend that None is numeric then you won't get an error. Just set the number format and be happy.

  3. stringfellow reporter

    It is an issue because this is how Excel writes out certain cells. Thus, if you try to read that file in, it breaks...

  4. stringfellow reporter

    (Imagine the alternative - having to wrap every single call to cell.value in a try... except...)

  5. CharlieC

    Please provide a sample file. If this is related to reading cells then it should probably be handled in the reader module so that the example you provide would still break in client code.

    We might also think about making the data_type.setter private because you can cause all kinds of problems with your suggestion.

  6. stringfellow reporter

    Sure - the test was really just that - a test to replicate the Excel output (I thought it would be better to replicate the exact behaviour using the library).

  7. CharlieC

    Thanks for the file. In this case the test was wrong - this behaviour shouldn't be possible in client code as you can set a number format for an empty cell. It suggests that the parsing code is wrong which wouldn't surprise me though I've recently refactored it and hope that will make it easier to fix this. Excel's typing system is optimistic rather than truly strong but it's also majorly broken by relying of formatting for dates and times rather than a native type.

  8. CharlieC

    You might want to look at #380 and the related pull request. If you're still having problems then we need sample code and or files. This is cannot occur in the same way anymore because we no longer treat empty cells as strings. We've also added "bail" clauses to empty cells formatted as datetimes to prevent exceptions being raised when the Python value is requested.

  9. Log in to comment