errors when writing more than 52 columns

Issue #246 resolved
Anonymous created an issue

If you add more than 52 columns; e.g. like this:

import openpyxl

wb = openpyxl.Workbook( encoding = 'windows-1252' )
ws = wb.get_active_sheet()
ws.title = 'Details'

for row in range(0,1):
    for col in range(0,52):
        ws.cell(row=row, column=col).value = "hello"

wb.save('test.xlsx')

then Excel 2010 can't read the file - it says the file is corrupted and when it tries to repair the file, there are columns missing.

Having a quick look it seems there are the following problems with the generated xlsx:

• Cells must be in sequential order (based on this code sample: http://msdn.microsoft.com/en-us/library/office/cc861607.aspx), e.g. we can’t have something like this:

<c r="AA1" t="s">
  <v>0</v>
</c>
<c r="BA1" t="s">
  <v>0</v>
</c>

• incorrect usage of a namespace in core.xml. These two lines:

<ns1:created ns2:type=”dcterms:W3CDTF” xmlns:ns1=”http://purl.org/dc/terms/” xmlns:ns2=”http://www.w3.org/2001/XMLSchema-instance”>2014-01-07T20:02:08Z</ns1:created>; <ns1:modified ns2:type=” dcterms:W3CDTF” xmlns:ns1=”http://purl.org/dc/terms/” xmlns:ns2=”http://www.w3.org/2001/XMLSchema-instance”>2014-01-07T20:02:08Z</ns1:modified>;

Should be:

<ns1:created ns2:type=”ns1:W3CDTF” xmlns:ns1=”http://purl.org/dc/terms/” xmlns:ns2=”http://www.w3.org/2001/XMLSchema-instance”>2014-01-07T20:02:08Z</ns1:created>
                  <ns1:modified ns2:type=”ns1:W3CDTF” xmlns:ns1=”http://purl.org/dc/terms/” xmlns:ns2=”http://www.w3.org/2001/XMLSchema-instance”>2014-01-07T20:02:08Z</ns1:modified>

• [Content_Types].xml should use default namespace. The Microsoft validation tool crashes because of this. That said, Excel is fine with it so correcting this is not a necessity.

Have tried version 1.8, 1.9, and latest of openpyxl.

Comments (5)

  1. Steven Brown

    Oh the cell ordering is wrong, because cell.py column_index_from_string() is incorrect.

    It needs to be changed from: for i, l in enumerate(m.group(0)):

    to: for i, l in enumerate(reversed(m.group(0))):

    Old results: A = 1 Z = 26 AA = 27 AZ = 677 BA = 28 BZ = 678 ZA = 52 ZZ = 702 AAA = 703 AAZ = 17603 ABC = 2081 AZA = 1353 ZZA = 1378 ZZZ = 18278

    New results: A = 1 Z = 26 AA = 27 AZ = 52 BA = 53 BZ = 78 ZA = 677 ZZ = 702 AAA = 703 AAZ = 728 ABC = 731 AZA = 1353 ZZA = 18253 ZZZ = 18278

  2. CharlieC

    Thanks very much for spotting this! It seems the test we have failed because it only checked "J"s

    The problem with [Content_Types].xml is that it is difficult to produce what Microsoft expects using both the standard library's ElementTree and lxml. lxml has explicit support for namespace assignment using nsmap but ElementTree doesn't. I may just add support for lxml because checking with tools generally only makes sense in a test or development environment where lxml is a requirement.

  3. Log in to comment