last worksheet is invisible

Issue #165 resolved
Unknown
created an issue

I've encountered the following strange problem. I have an Excel 2007 workbook that contains 19 worksheets. When I check the names of the worksheets via book.get_sheet_names(), only the first 18 worksheets are listed. If I move the
last worksheet to the beginning, its name appears, but the new last worksheet is
no longer listed. If I delete a worksheet, so that there are only 18, then only
the first 17 are listed. I've read that openpyxl has issues with workbooks that
contain charts, but the "invisible" worksheet does not contain a chart.

Comments (12)

  1. CharlieC

    Philip can you list the version of openpyxl you are running?

    Do any of the sheets have non-ASCII characters in them?

    Can the error be replicated with a new file which just contains 19 otherwise empty sheets with the same names? If so it would be great if you could attach that file to this issue.

  2. Unknown reporter

    (Reply via phil...@gmail.com):

    Hello Charlie,

    My apologies for the slow response. (My son is recovering from surgery).

    I'm using version 1.5.8 of openpyxl.

    Re. non-ASCII characters: How can I determine this?

    I will try to replicate the issue with a file that contains empty
    worksheets.

    Thanks!

    Phillip

  3. Unknown reporter

    I have a good demonstration case. Here is my code:

    import openpyxl
    book= openpyxl.load_workbook('openpyxl_bug.xlsx')
    print(book.worksheets)

    The workbook has two sheets, but openpyxl reports that there is only one. The workbook is attached.

  4. CharlieC

    Thanks, and sorry for the delay. I can confirm the behaviour but I think the problem is probably related to #179 - openpyxl seems to struggle with worksheets that are charts.

    I think it's fair to say that chart support at the moment is incomplete. Can you see if this works if you have the chart embedded in another sheet?

  5. CharlieC

    Development isn't stalled. The owner had to take some time off but has been making changes and accepting patches recently.

    That doesn't mean forks can't be made and work done. I've just submitted a pull request for some other stuff but slightly lower level than this.

  6. CharlieC

    Okay, as previously noted: the behaviour is because openpyxl does not know how to treat sheets that are charts. The logic for detecting sheets is fairly primitive as it assumes that for each sheet there is a corresponding worksheet file. Not only is this not the case with chart sheets but once it has misidentified a chart the error may prevent loading subsequent sheets.

    A solution first requires a way of handling chart sheets. I suspect that this is straightforward if not trivial.

    How sheets are identified

  7. Eric Gazoni

    fixes issue #165, #179 and #209

    • add function read_content_types to openpyxl/reader/workbook.py that reads all
      of the 'Override' items in [Content_Types].xml
    • import ARC_CONTENT_TYPES and read_content_types into openpyxl/reader/excel.py
    • create constants VALID_CHARTSHEET and VALID_WORKSHEET that have the
      ContentType tag specified in [Content_Types].xml for chartsheets and worksheets
    • call read_content_types immediately before read_sheet_titles
    • before iterating over sheet_names create new list called worksheet_names that
      contains only the list of sheets that are actually worksheets, by comparing their
      type to VALID_WORKSHEET
    • import basic debug logging, use standard message format (from Doug Hellmann)
      "http://doughellmann.com/2007/05/pymotw-logging.html"
    • add show_debug_log optional arg to load_workbook & pass it on to Lload_workbook
    • add Sphinx autodoc text for show_debug_log
    • if show_debug_log, then output name of each worksheet before it's loaded

    → <<cset 498594f25594>>

  8. Log in to comment