python: writing data to excel file but "cannot close file"?

Question

so I have this pandas data table that I wish to write into an excel workbook. I'm using pyexcelerate package. Here's my code:

from pyexcelerate import Workbook

def df_to_excel(df, path, sheet_name='Sheet 1'):
    data = [df.columns.tolist(), ] + df.values.tolist()
    wb = Workbook()
    wb.new_sheet(sheet_name, data=data)
    wb.save(path)

df_to_excel(df=MergedDataAll, path=merged_file)

I didn't have this problem before when I used this same code; but today this error kept popping up:

Traceback (most recent call last):
  File "C:/Users/y/Documents/python codes/DataMerging.py", line 71, in <module>
    df_to_excel(df=MergedDataAll, path=merged_file)
  File "C:/Users/y/Documents/python codes/DataMerging.py", line 68, in df_to_excel
    wb.save(path)
  File "C:\Users\y\AppData\Roaming\Python\Python36\site-packages\pyexcelerate\Workbook.py", line 82, in save
    self._save(fp)
  File "C:\Users\y\AppData\Roaming\Python\Python36\site-packages\pyexcelerate\Workbook.py", line 78, in _save
    self._writer.save(file_handle)
  File "C:\Users\y\AppData\Roaming\Python\Python36\site-packages\pyexcelerate\Writer.py", line 58, in save
    for s in sheetStream:
  File "C:\Users\y\AppData\Roaming\Python\Python36\site-packages\jinja2\environment.py", line 1045, in generate
    yield self.environment.handle_exception(exc_info, True)
  File "C:\Users\y\AppData\Roaming\Python\Python36\site-packages\jinja2\environment.py", line 780, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "C:\Users\y\AppData\Roaming\Python\Python36\site-packages\jinja2\_compat.py", line 37, in reraise
    raise value.with_traceback(tb)
  File "C:\Users\y\AppData\Roaming\Python\Python36\site-packages\pyexcelerate\templates\xl\worksheets\sheet.xml", line 11, in top-level template code
    <sheetData>{% for x, row in worksheet.get_xml_data() %}{{ worksheet.get_row_xml_string(x) }}{% for cell in row %}{{ cell }}{% endfor %}</row>{% endfor %}</sheetData>
  File "C:\Users\y\AppData\Roaming\Python\Python36\site-packages\pyexcelerate\Worksheet.py", line 265, in get_xml_data
    row_data.append(self.__get_cell_data(cell, x, y, style))
  File "C:\Users\y\AppData\Roaming\Python\Python36\site-packages\pyexcelerate\Worksheet.py", line 188, in __get_cell_data
    z = '"><v>%s</v></c>' % (DataTypes.to_excel_date(cell))
  File "C:\Users\y\AppData\Roaming\Python\Python36\site-packages\pyexcelerate\DataTypes.py", line 57, in to_excel_date
    excel_date = delta.days + (float(delta.seconds) + float(delta.microseconds) / 1E6) / (60 * 60 * 24) + 1
AttributeError: 'NaTType' object has no attribute 'days'
Exception ignored in: <bound method ZipFile.__del__ of <zipfile.ZipFile [closed]>>
Traceback (most recent call last):
  File "C:\Users\y\AppData\Local\Programs\Python\Python36-32\lib\zipfile.py", line 1663, in __del__
  File "C:\Users\y\AppData\Local\Programs\Python\Python36-32\lib\zipfile.py", line 1680, in close
ValueError: seek of closed file

I tried googling on this, and used something similar to

wd.close()
wd._archive_.close()

but both are not useful.

here're some reference posts I've looked into:

Openpyxl does not close Excel workbook in read only mode

Python to close a workbook using win32com

Closing files in openpyxl

https://github.com/python-pillow/Pillow/issues/1630

I think you've got it backwards - you're attempting to seek on a file that's already closed. What is `merged_file` and has it been closed prior to entering this function? — Shadow, Dec 07 '17 at 00:30
I'm not *certain* I'm reading the exception correctly, but I think the first "real" error you're encountering is not the zip file seek thing, but rather `AttributeError: 'NaTType' object has no attribute 'days'` while the code is trying to write out a date. Can you check your data, and make sure you don't have a bogus value in it where a date should be (or that might be mistaken for a date even if it means something else)? — Blckknght, Dec 07 '17 at 00:35
@Shadow the merged_file is a file path to the name of the file i am writing this file as — alwaysaskingquestions, Dec 07 '17 at 00:41
@Blckknght I'm a bit confused... you are saying i have a column that's supposed to be dates but are not? or the other way around as it's not date but it's attempting to write out the data as date? — alwaysaskingquestions, Dec 07 '17 at 00:42
I don't really know why it's trying to do that, but the `AttributeError` is happening in a call to `DataTypes.to_excel_date(cell)`, where some data value is being written as a date. The error suggests that the value is not a date, but the excel writing code thinks it is. I have no idea if the underlying issue is that the value is bad (it should be a real date) or if the issue is that the writer shouldn't be trying to treat it that way (it should be written some other way). You might be able to look at what `NaTType` is, to help you narrow down what the value triggering the issue is. — Blckknght, Dec 07 '17 at 00:59
@Blckknght okay so i do have one column that has data type "dtype: datetime64[ns]" do you think this is causing the issue? — alwaysaskingquestions, Dec 07 '17 at 01:24
I'd guess you have a row with an invalid value for that column. A quick google shows that `NaTType` is the type Pandas uses for "Not a Time" (equivalent to NaN (Not a Number) in numeric fields). You may need to convert that value to something else, or filter out the row, since it looks like the excel translation code can't handle it by itself. — Blckknght, Dec 07 '17 at 01:30
@Blckknght Strange thing is, if I write the columns individually into a separate workbook, then everything is fine!!! only when they are put together. so this eliminates bad data right? — alwaysaskingquestions, Dec 07 '17 at 16:59

score 0 · Answer 1 · answered Dec 07 '17 at 19:09

I figured it out finally! So I thought I might as well share my finding.

The answer was from this post:

'NaTType' object has no attribute 'days'

Basically upgrading the pandas library solved this issue...

pip install --upgrade pandas

Special thanks to Blckknght for pointing out the NaTType issue; I wouldn't have looked more into this if he didn't point it out.

python: writing data to excel file but "cannot close file"?

1 Answers1