Excel 2010 finds unreadable content in my openpyxl generated workbook

Issue #403 resolved
David Laudy
created an issue

Hello,

I have a workbook generated by openpyxl 2.1 with lxml which cannot be opened by Excel 2010 as it founds unreadable content.

In order to repair the workbook, Excel removes the comments I wanted to include in my workbook.

The generated workbook is attached.

Many thanks for the support!

David

Comments (10)

  1. Charlie Clark

    @David Laudy can you tell me which cells in which sheet are affected? Do you have some of the code that was used to create the file? I get the error in Excel, which as usual is not much help, but the validation tool seems to suggest that there may be a problem with the styles.

  2. David Laudy reporter

    @Charlie Clark The creation of the workbook has been done with

    workbook = Workbook(optimized_write=True)
    

    Concerning the cells and sheets, here is the list: NoeudLNG (I4), new_sheet (F4), Options (H4, F98), Contrats (L4, S4, T4, U4, V4), DEF_mappings (I4, I5)

  3. Charlie Clark

    Thanks. I would be surprised if this worked even with LXML not installed. I think I may have a solution for this but otherwise you'll have to avoid using write_only=True (the new way of writing optimized_write=True)

  4. David Laudy reporter

    @Charlie Clark I tried to write my workbook without the write_only flag but I got a traceback:

    Traceback (most recent call last):
      File "write_comments.py", line 33, in <module>
        sheet.append([cell])
      File "C:\data\workspace\visionsweb\openpyxl\worksheet\worksheet.py", line 685, in append
        cell = Cell(self, col, row_idx, content)
      File "C:\data\workspace\visionsweb\openpyxl\cell\cell.py", line 191, in __init__
        self.value = value
      File "C:\data\workspace\visionsweb\openpyxl\cell\cell.py", line 363, in value
        self._bind_value(value)
      File "C:\data\workspace\visionsweb\openpyxl\cell\cell.py", line 251, in _bind_value
        raise ValueError("Cannot convert {0} to Excel".format(value))
    ValueError: Cannot convert <Cell hello.A1> to Excel
    

    Seems that append() method only accepts list of KNOWN_TYPES but no WriteOnlyCells.

    I reproduce with the following code:

    import openpyxl
    import os
    import os.path as osp
    
    from openpyxl.cell import Cell
    from openpyxl.comments.comments import Comment
    from openpyxl.styles import Style, PatternFill, Color, colors, fills
    from openpyxl.styles.fonts import Font
    
    workbook = openpyxl.workbook.Workbook()
    
    warning_style = Style(fill=PatternFill(fill_type=fills.FILL_SOLID,
                                           start_color=Color(colors.RED),
                                           end_color=Color(colors.RED)))
    
    caution_style = Style(font=Font(color=Color(colors.YELLOW)),
                          fill=PatternFill(fill_type=fills.FILL_SOLID,
                                           start_color=Color(colors.BLACK),
                                           end_color=Color(colors.BLACK)))
    
    styles = [warning_style, caution_style]
    
    for idx, name in enumerate((u'hello', u'hello2')):
        sheet = workbook.create_sheet()
        sheet.title = name
        comment = Comment(text=name, author='visions')
        comment._width = '400pt'
        comment._height = '150pt'
        cell = Cell(sheet, value='cellule', column='A', row=1)
        cell.comment = comment
        cell.style = styles[idx]
    
        sheet.append([cell])
    
    workbook.save(osp.join(os.getcwd(), 'test_comments.xlsx'))
    
  5. Charlie Clark

    Yes, the code isn't entirely 1:1 because in standard mode you always get a cell from the sheet, whereas in write_only mode you create them only when you need them and, as a result, you have to manually assign the worksheet to the cell and also give it a position.

    for idx, name in enumerate((u'hello', u'hello2')):
        sheet = workbook.create_sheet()
        sheet.title = name
        comment = Comment(text=name, author='visions')
        comment._width = '400pt'
        comment._height = '150pt'
        cell = ws.cell(column='A', row=1, value='cellule')
        cell.comment = comment
        cell.style = styles[idx]
    

    And no need to append would be the equivalent code for a standard workbook.

    I think I have a solution for the comments issue. Can you work with a checkout of the source?

  6. Charlie Clark

    Glad to know it's working. I've also just checked in some code that will make code more compatible between modes but please don't use import os.path as osp anymore ;-)

  7. Log in to comment