Error on empty date cell

Issue #380 resolved
Adam Morris created an issue

Excel allows setting the number format of an empty cell. If the cell is set to the date format, the value is None, and the attempt to read the value raises a Type Error, as it can't add the calendar offset to None.

Not sure I understand the from_excel() function in date_time.init fully - would it be an acceptable patch just to return None in this case if the value is None?

from openpyxl import load_workbook
wb = load_workbook("test_date.xlsx")
ws = wb.active
ws.cell('A1').value
# datetime.datetime(2014, 1, 31, 0, 0)
ws.cell('A2').value

## Returns Error:
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "openpyxl/cell/cell.py", line 347, in value
    value = from_excel(value, self.base_date)
  File "openpyxl/compat/functools.py", line 122, in wrapper
    result = user_function(*args, **kwds)
  File "openpyxl/date_time/__init__.py", line 54, in from_excel
    parts = list(jd2gcal(MJD_0, value + offset - MJD_0))
TypeError: unsupported operand type(s) for +: 'NoneType' and 'float'

Comments (4)

  1. CharlieC

    I guess it's important to catch the exception. I am slightly worried about applying a format to None especially in the light of today's discussion on empty cells. But as Excel insists on treating dates as integer (there is actually a cell-type d for datetimes, it's just that nobody seems to use it).

  2. Log in to comment