Writing data to Excel give me 'ZIP does not support timestamps before 1980'

Question

I hope to don't create any duplicate but I looked around (stack overflow and other forum) and I found some similar question but none of them solved my problem.

I have a python code that the only thing that does is query the DB, create a DataFrame in Pandas and write it to an Excel file.

The code worked without problem locally but when I introduced it in my server it start to give this error:

  File "Test.py", line 34, in <module>
    test()
  File "Test.py", line 31, in test
    ex.generate_file()
  File "/home/carlo/Test/Utility/ExportExcell.py", line 96, in generate_file
    writer.save()
  File "/usr/local/lib/python2.7/dist-packages/pandas/io/excel.py", line 1952, in save
    return self.book.close()
  File "/usr/local/lib/python2.7/dist-packages/xlsxwriter/workbook.py", line 306, in close
    self._store_workbook()
  File "/usr/local/lib/python2.7/dist-packages/xlsxwriter/workbook.py", line 677, in _store_workbook
    xlsx_file.write(os_filename, xml_filename)
  File "/usr/lib/python2.7/zipfile.py", line 1135, in write
    zinfo = ZipInfo(arcname, date_time)
  File "/usr/lib/python2.7/zipfile.py", line 305, in __init__
    raise ValueError('ZIP does not support timestamps before 1980')
ValueError: ZIP does not support timestamps before 1980

To ensure that is everything ok I printed my DataFrame and for me it looks good even because when I run it locally it geenrate an excell file without problem:

   Computer_System_Memory_Size  Count_of_HostName   Disk_Total_Size  Number_of_CPU       OS_Family
0                5736053088256                 70     6072238035456         282660         Windows
1                  96159653888                607       96630589440        2451066         vCenter
2                            0                  9                 0          36342  Virtualization
3             2469361287143424                 37  2389533519619072         149406            Unix
4                3691651514368                 90     5817485303808         363420           Linux

I don't see any timestamp here and this is part of my code:

pivot = pd.DataFrame.from_dict(pivot) #pivot= information extracted from DB

pd.to_numeric(pivot['Count_of_HostName'], downcast='signed')#try to enforce to be a numeric value in case it get confused with a datetime
pd.to_numeric(pivot['Disk_Total_Size'], downcast='signed')#try to enforce to be a numeric value in case it get confused with a datetime
pd.to_numeric(pivot['Computer_System_Memory_Size'], downcast='signed')#try to enforce to be a numeric value in case it get confused with a datetime
pd.to_numeric(pivot['Number_of_CPU'], downcast='signed')#try to enforce to be a numeric value in case it get confused with a datetime

print pivot
name = 'TempReport/Report.xlsx'#set-up file name
writer = pd.ExcelWriter(name, engine='xlsxwriter')#create excel with file name
pivot.to_excel(writer, 'Pivot', index=False)#introduce my data to excel
writer.save()#write to file, it's where it fail

Does someone know why it doesn't work in an Ubuntu 16.04 server without give me 'ZIP does not support timestamps before 1980' error? I checked many things, library version, ensure that there are no data

Answer 1

XlsxWriter set the individual XML files that make up an XLSX file with a creation date of 1/1/1980 which is (I think) the ZIP epoch and the date used by Excel. This allows binary reproducibility of files created by XlsxWriter once the same input data and metadata is used.

It sets the date as follows (for the non-in-memory zipfile.py) case:

timestamp = time.mktime((1980, 1, 1, 0, 0, 0, 0, 0, 0))
os.utime(os_filename, (timestamp, timestamp))

The error that you are seeing occurs when this fails in some way and the date is set before 1/1/1980.

I've only seen this happen once before in a situation where the user was using a container and the container had a different time to the host system.

Do you have a situation like this or where the timestamp may be set incorrectly for some reason?

Update: Try run this in the same environment as the example that fails:

import os
import time

filename = 'file.txt'
file = open(filename, 'w')
file.close()

timestamp = time.mktime((1980, 1, 1, 0, 0, 0, 0, 0, 0))
os.utime(filename, (timestamp, timestamp))

print(time.ctime(os.path.getmtime(filename)))
# Should give:
# Tue Jan  1 00:00:00 1980

Update: This issue is fixed in XlsxWriter >= 1.1.9.