writing pie chart on multiple sheets results in invalid drawing1.xml

Issue #824 resolved
Ryan Hutchison
created an issue

Hello,

When writing a pie chart on multiple sheets, I get the following repair message:

summary="Following is a list of removed parts:"><removedPart>Removed Part: /xl/drawings/drawing1.xml part.  (Drawing shape)</removedPart></removedParts></recoveryLog>

Here is the code that is creating the chart:

    def _write_pie_chart(self, worksheet_object):
        """ write pie chart to worksheet """

        ws = worksheet_object

        labels = Reference(ws, min_col=1, min_row=39, max_row=62)
        data = Reference(ws, min_col=4, min_row=38, max_row=62)
        self.pie.title = 'Cost Distribution Chart'
        self.pie.height = 14.75
        self.pie.width = 19.45
        self.pie.add_data(data, titles_from_data=True)
        self.pie.set_categories(labels)
        self.pie.legend.position = 'b'
        self.pie.style = 10
        ws.add_chart(self.pie, "I38")

This is encapsulated within a Report object. When writing out multiple sheets on this report, only the pie chart of the last sheet is shown after repair. It may also be interesting to note that in the workbook written by openpyxl, there is also a 'drawing2.xml'. However after repair there is only a 'drawing1.xml'. 'drawing1.xml' and 'drawing2.xml' are identical. There should be two drawings in this workbook, so this could be the reason only the last pie chart is shown after repair. The 'drawing1.xml' written by openpyxl looks like the following:

<wsDr 
    xmlns="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing">
    <oneCellAnchor>
        <from>
            <col>8</col>
            <colOff>0</colOff>
            <row>37</row>
            <rowOff>0</rowOff>
        </from>
        <ext cx="7002000" cy="5310000"/>
        <graphicFrame>
            <nvGraphicFramePr>
                <cNvPr id="1" name="Chart 1"/>
                <cNvGraphicFramePr/>
            </nvGraphicFramePr>
            <xfrm/>
            <a:graphic 
                xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">
                <a:graphicData uri="http://schemas.openxmlformats.org/drawingml/2006/chart">
                    <c:chart 
                        xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart" 
                        xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" r:id="rId1"/>
                </a:graphicData>
            </a:graphic>
        </graphicFrame>
        <clientData/>
    </oneCellAnchor>
</wsDr>

The 'drawing1.xml' after repair looks like the following:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<xdr:wsDr 
    xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" 
    xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">
    <xdr:oneCellAnchor>
        <xdr:from>
            <xdr:col>8</xdr:col>
            <xdr:colOff>0</xdr:colOff>
            <xdr:row>37</xdr:row>
            <xdr:rowOff>0</xdr:rowOff>
        </xdr:from>
        <xdr:ext cx="7002000" cy="5310000"/>
        <xdr:graphicFrame macro="">
            <xdr:nvGraphicFramePr>
                <xdr:cNvPr id="2" name="Chart 1"/>
                <xdr:cNvGraphicFramePr/>
            </xdr:nvGraphicFramePr>
            <xdr:xfrm>
                <a:off x="0" y="0"/>
                <a:ext cx="0" cy="0"/>
            </xdr:xfrm>
            <a:graphic>
                <a:graphicData uri="http://schemas.openxmlformats.org/drawingml/2006/chart">
                    <c:chart 
                        xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart" 
                        xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" r:id="rId1"/>
                </a:graphicData>
            </a:graphic>
        </xdr:graphicFrame>
        <xdr:clientData/>
    </xdr:oneCellAnchor>
</xdr:wsDr>

Any ideas what could be the cause?

Thank you for the help!

Comments (4)

  1. CharlieC

    Thanks for the report. The problem is probably related to the way charts are written. The OOXML specification seems to suggest that charts can be "shared" by different workbooks which is presumably why there is the indirection that sheets have a drawing in turn may have essentially placeholders for charts.

    In practice, Excel always creates new charts and we should probably do the same. I suspect that some of the code related to creating the placeholder in the drawing is what's causing the problem. Until this becomes more robust the solution is simply to create a copy of the chart using copy.deepcopy

  2. CharlieC

    Having looked at this in detail it looks like it's impossible for Excel to use the same chart object in different worksheets even if the file itself is valid. For openpyxl there would be practical but not in surmountable problems associated with this because we bind the anchor to the chart object.

    It's easy enough to raise an exception or a warning when saving the file and this is probably the easiest short term solution. Would you prefer a warning or an exception?

  3. Log in to comment