Impossible to add a predefined chart style to charts created with openpyxl

Issue #1180 resolved
laurent chriqui created an issue

I use a script to create a chart and add a title to the chart. The file opens well in excel but when I try to apply a defined style to it, it corrupts the file which can no longer be saved.

If I delete the title in excel I can then save the file successfully.

Attached a sample script and resulting excel file.

Comments (10)

  1. CharlieC

    Thanks for the report but this looks more like a bug in Excel than anything else as openpyxl produces a valid OOXML file.

  2. laurent chriqui reporter

    Nonetheless, it renders the file as "Imperfect" if one needs to stylize the charts... I had to write an excel macro to delete the title and rewrite it "excel style" so that the style doesn't corrupt the file.

  3. CharlieC

    The issue is who's responsibility is it to resolve? Please file an issue with Microsoft. If they can provide some more information about what needs fixing then I'm happy to take it on.

    In the past, through the OOXML working group, I've submitted several requests to Microsoft for clarification. It takes a while to get feedback but it does usually come.

  4. laurent chriqui reporter

    Hi,

    I contacted the MS team and here is their reply.
    https://social.msdn.microsoft.com/Forums/en-US/b37a1c97-7ded-47e8-b5eb-617ad32892c6/impossible-to-add-a-predefined-chart-style-to-charts-where-no-ltapprgt-tag-is-present-in-the?forum=os_binaryfile

    Ok, after debugging to see what Excel is actually complaining about: when Excel goes to write out the XML for the Title part, it parses down to the tx element and then looks for the properties (as you found). It’s ok that it doesn’t find them in the <a:p> block in a <a:pPr> block. But what it usually does is looks for default run/paragraph properties and it does this by looking at the chart\stylex.xml part or the theme\themex.xml part. Specifically it should be finding these under the chart\stylex.xml with these tags:

    <cs:defRPr sz="900" kern="1200"/>
    

    If you add a chart\stylex.xml part, you’ll see that these will work. My example was with Excel generated workbook and it had this part with default char props defined. We specifically look for default char props and if they’re not there, we throw an exception that leads to the message you see.

    We may need an implementation or behavior note for this because I don’t see that Chart style parts are specifically required in the package. And therefore, this is something Excel needs that isn’t specifically called out. We do talk about chart\style part in [MS-ODRAWXML] (our drawing extensions).

    Anyway, can you try adding this part with default char props and let me know if that resolves the issue?

  5. CharlieC

    Interesting and thanks for finding this out. While we could easily add such elements, but as the text notes, this is not covered by the OOXML specification, of which the implementers notes are not a part, as neither are the extensions.

    Chart styles themselves are also not part of the OOXML specification as the namespace makes clear: xmlns:cs="http://schemas.microsoft.com/office/drawing/2012/chartStyle" So the only possibility is to add an element to the theme, though it's not clear from this text where this should go.

    Basically, this is a bug in Excel that we should be able to workaround but MS should just as easily be able to fix by creating default elements if it needs them.

  6. laurent chriqui reporter

    I tested his solution, it works.

    If in chart.xml you add this tag :

    <a:pPr><a:defRPr sz="900" kern="1200"/></a:pPr>
    

    The resulting tag for the title results in :

    <a:p>
        <a:pPr>
            <a:defRPr sz="900" kern="1200"/>
        </a:pPr>
        <a:r>
            <a:rPr lang="fr-FR"/>
            <a:t>Chart Title</a:t>
        </a:r>
    </a:p>
    
  7. laurent chriqui reporter

    Yes that's what I replied them : they should correct the bug or update the specs.

    Thanks for your rapid replies.

  8. Log in to comment