datetime Error When Workbook Is Saved

[jesse.c....@gmail.com]

I am trying to write to a *.xlsx file with the openpyxl module. I have downloaded it using pip install on Ubuntu 14.04 so I believe it is version 2.0.5.

I am loading in a *.xlsx file that is a Bill Of Materials template I use that was previously made in Excel and I can open it just fine in Libre Office and Kingsoft Office. My intention is to load it and fill in some cells with some strings and then save it.

My function looks like this:

def writeBOM(parts, projectname):
    '''
    Take the ordered and grouped part info and
    write it to a standard BOM and save it
    '''
    StandardBOMFILE = '/home/jesse/Digi-Parser/SampleFiles/StandardBOM.xlsx'

    wb = load_workbook(filename=StandardBOMFILE)
    sheet = wb.get_sheet_by_name('BOM')

    r = 8
    # Fill BOM
    for i, part in enumerate(parts):
       sheet.cell(row = r+i,column = 1).value = part.designator
       sheet.cell(row = r+i,column = 2).value = part.evalue + ' ' + part.package
       sheet.cell(row = r+i, column = 3).value = part.qty
    projectBOMname = projectname + 'BOM' + '.xlsx'
    wb.save(projectBOMname)

The values that I am putting into the cells are just strings.
However when I run this I get the following Error:
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
/usr/lib/python2.7/dist-packages/IPython/utils/py3compat.pyc in execfile(fname, *where)
    202             else:
    203                 filename = fname
--> 204             __builtin__.execfile(filename, *where)

/home/jesse/Digi-Parser/SheetOrganizer.py in <module>()
    232         prjpath = '/home/jesse/Digi-Parser/SampleFiles/'
    233         prjname = 'Water Use Monitor'
--> 234         things = csvToBOM(prjpath, prjname)

/home/jesse/Digi-Parser/SheetOrganizer.py in csvToBOM(projectpath, projectname)
    223         orderedBody = combineSameComponents(reorderParts(body))
    224 
--> 225         writeBOM(orderedBody, projectname)
    226 
    227 

/home/jesse/Digi-Parser/SheetOrganizer.py in writeBOM(parts, projectname)
    192        sheet.cell(row = r+i, column = 3).value = part.qty
    193     projectBOMname = projectname + 'BOM' + '.xlsx'
--> 194     wb.save(projectBOMname)
    195 
    196 

/usr/local/lib/python2.7/dist-packages/openpyxl/workbook/workbook.pyc in save(self, filename)
    265             save_dump(self, filename)
    266         else:
--> 267             save_workbook(self, filename)

/usr/local/lib/python2.7/dist-packages/openpyxl/writer/excel.pyc in save_workbook(workbook, filename)
    183     """
    184     writer = ExcelWriter(workbook)
--> 185     writer.save(filename)
    186     return True
    187 

/usr/local/lib/python2.7/dist-packages/openpyxl/writer/excel.pyc in save(self, filename)
    166         """Write data into the archive."""
    167         archive = ZipFile(filename, 'w', ZIP_DEFLATED)
--> 168         self.write_data(archive)
    169         archive.close()
    170 

/usr/local/lib/python2.7/dist-packages/openpyxl/writer/excel.pyc in write_data(self, archive)
     78         archive.writestr(ARC_WORKBOOK_RELS, write_workbook_rels(self.workbook))
     79         archive.writestr(ARC_APP, write_properties_app(self.workbook))
---> 80         archive.writestr(ARC_CORE, write_properties_core(self.workbook.properties))
     81         if self.workbook.loaded_theme:
     82             archive.writestr(ARC_THEME, self.workbook.loaded_theme)

/usr/local/lib/python2.7/dist-packages/openpyxl/writer/workbook.pyc in write_properties_core(properties)
     65     SubElement(root, '{%s}created' % DCTERMS_NS,
     66                {'{%s}type' % XSI_NS: '%s:W3CDTF' % DCTERMS_PREFIX}).text = \
---> 67                    datetime_to_W3CDTF(properties.created)
     68     SubElement(root, '{%s}modified' % DCTERMS_NS,
     69                {'{%s}type' % XSI_NS: '%s:W3CDTF' % DCTERMS_PREFIX}).text = \

/usr/local/lib/python2.7/dist-packages/openpyxl/date_time.pyc in datetime_to_W3CDTF(dt)
     54 def datetime_to_W3CDTF(dt):
     55     """Convert from a datetime to a timestamp string."""
---> 56     return datetime.datetime.strftime(dt, W3CDTF_FORMAT)
     57 
     58 

ValueError: year=1899 is before 1900; the datetime strftime() methods require year >= 1900

I can not figure out how to fix this. I don't need to have Excel installed on my computer do I? It seems the issue is in the date_time.py file in the openpyxl package and that the variable 'dt' is set to 1899 for some reason.
Thank you for any help.

[Charlie Clark]

Am .08.2014, 12:23 Uhr, schrieb <jesse.c....@gmail.com>:

> ValueError: year=1899 is before 1900; the datetime strftime() methods
> require year >= 1900
>>
>>
> I can not figure out how to fix this. I don't need to have Excel
> installed
> on my computer do I? It seems the issue is in the date_time.py file in
> the
> openpyxl package and that the variable 'dt' is set to 1899 for some
> reason.
> Thank you for any help.

Interesting. Trying to think where the value may have come from. Any you
can supply a sample file? Excel itself simply cannot cope with dates
before 1900 but that isn't the issue here.

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[Jesse Millwood]

Yep, attached are the files I was using.

The SheetOrganizer.py is the the python script I wrote to take the part information from the .csv file. It reorders and organizes the part information. It should then load in the StandardBOM.xlsx file and write to some cells and save it as a new *.xlsx file. 

[Charlie Clark]

Am .08.2014, 12:43 Uhr, schrieb Jesse Millwood
<jesse.c....@gmail.com>:

> Yep, attached are the files I was using.
> The SheetOrganizer.py is the the python script I wrote to take the part
> information from the .csv file. It reorders and organizes the part
> information. It should then load in the StandardBOM.xlsx file and write
> to
> some cells and save it as a new *.xlsx file.

Thanks, FWIW your script works fine on my machine though I had to change
it a bit – it won't run as it stands on Python 3. In particular you
shouldn't read CSV files in binary mode.

This is in the properties:
<dcterms:created
xsi:type=\"dcterms:W3CDTF\">1899-12-30T00:00:00Z</dcterms:created></>

This is presumably parsing fine:

wb = load_workbook("StandardBOM.xlsx")
dt = wb.properties.created
print(dt)
datetime.datetime(1899, 12, 30, 0, 0)

Presumably you get an error if you try and convert this back using the
standard library

t = time.strftime(dt, '%Y-%m-%dT%H:%M:%SZ')

Which means there's a problem with your Python installation, I think.
However, I also think that whatever is creating the file (there are traces
of LibreOffice in there) isn't doing such a great job writing in such
nonsense.

What version of Python are you using?

[Jesse Millwood]

I was using Python 2.7 but I made a virtual env and ran it in Python 3.4 and (with some minor modifications) it saves now. I read the csv file in 'rt' mode. I still cannot open the file created in Kingsoft but I can open it in LibreOffice (The colors are all off) but that is something I can look into later. It is functional now. Thank You very much!

[Charlie Clark]

Am .08.2014, 20:40 Uhr, schrieb Jesse Millwood
<jesse.c....@gmail.com>:

> I was using Python 2.7 but I made a virtual env and ran it in Python 3.4
> and (with some minor modifications) it saves now.

Good because I'm not particularly keen on providing a workaround for this
part of the standard library. Document properties is the only place where
this kind of formatting occurs and I think we can rule out genuine Excel
files from the 19th century! ;-)

> I read the csv file in
> 'rt' mode. I still cannot open the file created in Kingsoft but I can
> open
> it in LibreOffice (The colors are all off) but that is something I can
> look
> into later. It is functional now.

Oh, yeah I noticed the styles get screwed up a bit. I did a quick
comparison of the before and after and there are changes in pretty every
part of the file. It seems your original files makes use of unnamed "named
styles" which openpyxl currently ignores. But there are also some invalid
elements.

Kingsoft is the office that runs on Android, right?

> Thank You very much!

Glad it's working for you.

[Jesse Millwood]

Ahh thank you for that! I will have to change the original styles. It is a BOM template that some one else made. How did you compare the styles? Iterate through the cells and print the styles? 

I think Kingsoft does run on android. I just keep it on my Linux laptop because sometimes it can handle some Microsoft Office files that LibreOffice can't. 

Thanks Again!

Jesse

--
You received this message because you are subscribed to a topic in the Google Groups "openpyxl-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/openpyxl-users/N1leVwqw8jQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to openpyxl-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Charlie Clark]

Am .08.2014, 21:02 Uhr, schrieb Jesse Millwood
<jesse.c....@gmail.com>:

> Ahh thank you for that! I will have to change the original styles. It is
> a
> BOM template that some one else made. How did you compare the styles?

I use the Microsoft OpenXML SDK 2.5 Productivity Tool - so you'll need a
Windows VM for that. Makes it easy to inspect the zip archives that are
.xlsx files.

I think the file could do with being setup in Excel. I've just run it
through Excel for Mac and the results are slightly less-screwed up than
before but still a mess given how we ignore the user-defined styles. Now
that I know what those are, there's a chance that they'll make it into 2.2.

> I think Kingsoft does run on android. I just keep it on my Linux laptop
> because sometimes it can handle some Microsoft Office files that
> LibreOffice can't.

Not come across that. Though I dumped LibreOffice for OpenOffice a while
back as it was just too unstable. There's not a Mac OS version so I'll
have to give it a miss.

[Jesse Millwood]

given how we ignore the user-defined styles

I tried editing my ​

 StandardBOM.xlsx

​ file in Microsoft Excel and changing the cell styles of any cell that is colored to a cell style chosen in the ribbon menu at the top and the output file just didn't have any coloring. Is that still a user-defined style?

Now that I know what those are, there's a chance that they'll make it into 2.2

That would be very cool! 

Not come across that 

I mainly mean the formatting is what LibreOffice has trouble with sometimes and KingSoft seems to come through.  

[Charlie Clark]

Am .08.2014, 14:18 Uhr, schrieb Jesse Millwood
<jesse.c....@gmail.com>:

> I tried editing my ​
> StandardBOM.xlsx
> <https://docs.google.com/file/d/0BzYAOGnjUjU3ZjJ1bEtUdGd3ZUE/edit?usp=drive_web>

> ​ file in Microsoft Excel and changing the cell styles of any cell that
> is
> colored to a cell style chosen in the ribbon menu at the top and the
> output
> file just didn't have any coloring. Is that still a user-defined style?

I think so. Even with more than three years practice I still can't get
used to the ribbon – though it sucks less on Mac OS. In the format section
you can pick styles a bit like you might in Word (if Word didn't go around
generating ones based on the stuff you've already applied). I'm enclosing
a screenshot of the ones I see in your file. Supporting them should be
easy enough to do. Just fiddly and confusing to deal with due to the way
they've been implemented: it's very difficult to tell (standard) cell
styles and named styles apart. It might have made sense in the spec to
have named styles only with all formatting, which, apart in-cell
formatting, is always implemented via styles simply generating generic
numbered styles.

The generated file works fine for me (with borked styles) in both
OpenOffice and LibreOffice.