Newly introduced KeyError problem in cell.py _set_time_format method

Issue #1093 resolved
Marc Skov Madsen
created an issue

The code has just been changed to

def _set_time_format(self, value): """Set number format for Python date or time""" fmts = { datetime.datetime:numbers.FORMAT_DATE_DATETIME, datetime.date:numbers.FORMAT_DATE_YYYYMMDD2, datetime.time:numbers.FORMAT_DATE_TIME6, datetime.timedelta:numbers.FORMAT_DATE_TIMEDELTA, } self.number_format = fmts[type(value)]

from (v 2.5.5)

def _set_time_format(self, value): """Set number format for Python date or time""" if isinstance(value, datetime.datetime): self.number_format = numbers.FORMAT_DATE_DATETIME elif isinstance(value, datetime.date): self.number_format = numbers.FORMAT_DATE_YYYYMMDD2 elif isinstance(value, datetime.time): self.number_format = numbers.FORMAT_DATE_TIME6 elif isinstance(value, datetime.timedelta): self.number_format = numbers.FORMAT_DATE_TIMEDELTA return value

You can see that version 2.6 will throw key error when the value does not have one of the above types.

I have experienced this key error when using pandas.to_excel method

df.to_excel(writer, "output")

The problem is that my value is of type pandas.TimeStamp and thus not recognized. Please return value when the type is not recognized. Thanks

Comments (7)

  1. CharlieC

    Not even an alpha of 2.6 has been released so you should not be using it in any kind of production environment.

    If you are using it, the least you should be doing is frequently pulling and updating and be prepared to submit pull requests. Other people have families, too.

  2. ac24

    Hi, have just come across this issue, believe it may have been introduced in 9b0dbd6 or later, so it may not be isolated to 2.6. I am running 2.5.6 (and pandas 0.23.4) and have experiences this issue when using .to_excel with a dataframe containing pd.Timestamp columns. I had to convert to datetime.date to write successfully to the sheet.

    Code to reproduce:

    import pandas as pd
    from openpyxl import Workbook
    
    wb = Workbook()
    ws = wb.active
    rows = [
        ['Date', 'Batch 1', 'Batch 2', 'Batch 3'],
        [pd.Timestamp(2015,9, 1), 40, 30, 25],
    ]
    
    for row in rows:
        ws.append(row)
    

    Stack trace:

    ---------------------------------------------------------------------------
    KeyError                                  Traceback (most recent call last)
    <ipython-input-1-95db65cb948a> in <module>()
         10 
         11 for row in rows:
    ---> 12     ws.append(row)
    
    ~/anaconda/lib/python3.6/site-packages/openpyxl/worksheet/worksheet.py in append(self, iterable)
        775                     cell.row = row_idx
        776                 else:
    --> 777                     cell = Cell(self, row=row_idx, col_idx=col_idx, value=content)
        778                 self._cells[(row_idx, col_idx)] = cell
        779 
    
    ~/anaconda/lib/python3.6/site-packages/openpyxl/cell/cell.py in __init__(self, worksheet, column, row, value, col_idx, style_array)
        113         self.data_type = 'n'
        114         if value is not None:
    --> 115             self.value = value
        116         self._comment = None
        117         if column is not None:
    
    ~/anaconda/lib/python3.6/site-packages/openpyxl/cell/cell.py in value(self, value)
        289     def value(self, value):
        290         """Set the value and infer type and display options."""
    --> 291         self._bind_value(value)
        292 
        293     @property
    
    ~/anaconda/lib/python3.6/site-packages/openpyxl/cell/cell.py in _bind_value(self, value)
        191         elif isinstance(value, TIME_TYPES):
        192             if not is_date_format(self.number_format):
    --> 193                 self._set_time_format(value)
        194             self.data_type = "d"
        195 
    
    ~/anaconda/lib/python3.6/site-packages/openpyxl/cell/cell.py in _set_time_format(self, value)
        275             datetime.timedelta:numbers.FORMAT_DATE_TIMEDELTA,
        276                 }
    --> 277         self.number_format = fmts[type(value)]
        278 
        279     @property
    
    KeyError: <class 'pandas._libs.tslibs.timestamps.Timestamp'>
    

    Reverting to 2.5.5 will allow this code to run without KeyError.

  3. CharlieC

    Change log says 2.5.7. BTW 2.6 will contain considerable performance improvements for reading files that should allow you to read XLSX files with openpyxl instead of xlrd. But I don't know if anyone wants to unpick the current Pandas code for this…

    If anyone is interested then they should get in touch by e-email.

  4. Log in to comment