#849 Type Error When Loading Excel with Conditional Formatting in Pivot Table

Issue #849 resolved
Ray
created an issue

*.xlsx file cannot be loaded when there are conditional formatting in pivot table. My code is simple as :

wb_main = load_workbook('Book2.xlsx')

with error:

    raise TypeError('expected ' + str(self.expected_type))
TypeError: expected <type 'basestring'>

It can only be loaded when read_only mode is True. However I might need to append data to the file. Please help!

Comments (20)

  1. Ray reporter

    Thank you Charlie. I am using 2.4.8 but I have tried 2.5 and it had the same problem.

    I wish to provide the sample file but the sample file is with confidential information. When I was trying to reproduce the problem, I had some new discover about the bug:

    1. I cannot reproduce the problem by creating another pivot table with conditional format because OP works normally, but...

    2. The reason why I conclude the bug was caused by conditional format is because if I choose in Excel: Home->Conditional Formatting->Clear Rules->Clear all in the sheet or the pivot table, OP would not encounter such problem .

    3. However, if I go into Home->Conditional Formatting->Manage Rules, then delete all rules that can be seen from there, the problem remained. I seriously not sure why this happens because I am not an Excel expert.

    I have attached the sample file with same format but no problem. Hope you can help me on this. I guess it might be a bug of Excel but affects OP seriously. Thank you!

  2. Charlie Clark

    If the problem occurs with 2.4 then it is not directly related to pivot tables as support for these was introduced in 2.5-a1. If the file provided does raise the exception when opened by openpyxl then it will not be useful for resolving the problem.

    Please provide the full traceback you get when opening the file.

  3. Ray reporter

    Then it might be the problem of certain uncleaned conditional formatting of Excel. Will the below traceback help?

    Traceback (most recent call last):
      File "C:/Users/*/PycharmProjects/PythonProjects/pyxl_test.py", line 21, in <module>
        wb_main = load_workbook('Book2.xlsx')
      File "C:\Users\*\AppData\Local\Continuum\Anaconda2\lib\site-packages\openpyxl\reader\excel.py", line 245, in load_workbook
        ws_parser.parse()
      File "C:\Users\*\AppData\Local\Continuum\Anaconda2\lib\site-packages\openpyxl\reader\worksheet.py", line 125, in parse
        dispatcher[tag_name](element)
      File "C:\Users\*\AppData\Local\Continuum\Anaconda2\lib\site-packages\openpyxl\reader\worksheet.py", line 284, in parser_conditional_formatting
        cf = ConditionalFormatting.from_tree(element)
      File "C:\Users\*\AppData\Local\Continuum\Anaconda2\lib\site-packages\openpyxl\descriptors\serialisable.py", line 92, in from_tree
        return cls(**attrib)
      File "C:\Users\*\AppData\Local\Continuum\Anaconda2\lib\site-packages\openpyxl\formatting\formatting.py", line 28, in __init__
        self.sqref = sqref
      File "C:\Users\*\AppData\Local\Continuum\Anaconda2\lib\site-packages\openpyxl\descriptors\base.py", line 44, in __set__
        raise TypeError('expected ' + str(self.expected_type))
    TypeError: expected <type 'basestring'>
    
  4. Ray reporter

    Hello Charlie,

    I have tried to reproduced the problem but really cannot. I guess it might be some excel internal error when creating the file. Everything will work after I clear the conditional formatting and add them again. Sorry I couldn't provide the sample because it only occur on the page containing some confidential data. Thank you so much for the help and maintaining such a great library. :)

  5. Charlie Clark

    Thanks for the file. Looks like the bug is related to the conditional formatting on the worksheet with the pivot table which has two definitions, one with a cell range and one without but with the same definition. This is easy enough to work around but I'm not sure what the point of the format without a cell range is and whether it's okay to simply remove it.

     <conditionalFormatting pivot="1">
      <cfRule type="colorScale" priority="2">
       <colorScale>
        <cfvo type="min"/>
        <cfvo type="percentile" val="50"/>
        <cfvo type="max"/>
        <color rgb="FFF8696B"/>
        <color rgb="FFFFEB84"/>
        <color rgb="FF63BE7B"/>
       </colorScale>
      </cfRule>
     </conditionalFormatting>
     <conditionalFormatting pivot="1" sqref="G5:G24">
      <cfRule type="colorScale" priority="1">
       <colorScale>
        <cfvo type="min"/>
        <cfvo type="percentile" val="50"/>
        <cfvo type="max"/>
        <color rgb="FFF8696B"/>
        <color rgb="FFFFEB84"/>
        <color rgb="FF63BE7B"/>
       </colorScale>
      </cfRule>
    
  6. Ray reporter

    Thanks for looking into the details of the problem! I am also not sure how could this happen... For me I have never done any settings this way. Now I guess the reason why deleting all rules in "manage conditional format" cannot solve the problem is because the formatting without cell range cannot be detected by that (Even Excel doesn't let user to manage this type of rule!). It might be caused by some messy operations or errors in excel... Definitely I believe it's okay to remove it, since those formatting is not useful at all. So other users will be able to work around with OP easily. Will you update this to the main branch or somewhere else? Thank you so much!

  7. Charlie Clark

    Who knows? Excel may to this automatically when creating formats in connection with pivot tables. The specification doesn't help much here and you wouldn't expect it to matter much but it obviously does somehow. I'll have to check whether pivot tables expect anything special but seeing as the only formats that the GUI exposes are ones that have cell ranges, it would make sense to drop ones without them. Should be able to push a change soon so that you'll be able to work with a checkout.

  8. Charlie Clark

    It looks like Pivot Tables have dependencies on individual conditional formats so removing them would probably cause Excel to throw a fit even if the format is effectively unused. Fortunately, the change to allow an empty range of cells has no apparent side-effects so can be pushed. Do you know how the formats were created? I'd like to be able to reproduce it.

  9. Charlie Clark

    So, of course the patch for 2.4 is quite easy so I'll push it this evening. Unfortunately, 2.5 will need more work because Excel does get all upset if "pivot=1" is missing from the conditional formats. :-/

  10. Log in to comment