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

    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.



  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. Unknown reporter

    It seems as though openpyxl development is permanently stalled. Perhaps it is time to create a fork of this project?

  6. 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.

  7. 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

  8. Eric Gazoni

    fixes issue #165, #179 and #209

    • add function read_content_types to openpyxl/reader/ that reads all of the 'Override' items in [Content_Types].xml
    • import ARC_CONTENT_TYPES and read_content_types into openpyxl/reader/
    • 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) ""
    • 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>>

  9. Log in to comment