Python Forum
How to subclass charts in openpyxl
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How to subclass charts in openpyxl
#1
I need to make a subclass of ScatterChart from the openpyxl module. When the chart is created directly from "openpyxl.chart.ScatterChart()", it works as expected. However, it's subclass won't load properly in Excel (2010), it is ignored and shows this error:

Error:
Removed Part: /xl/drawings/drawing1.xml part. (Drawing shape)
.
#!/usr/bin/python3
import os
import openpyxl

LOCAL = os.getcwd()


class Sheet(openpyxl.Workbook):
    def __init__(self):
        super().__init__()

class Chart(openpyxl.chart.ScatterChart):
    def __init__(self):
        super().__init__()


####################################

# This work
chart = openpyxl.chart.ScatterChart()

# This doesn't
chart = Chart()

####################################

sheet = Sheet()
sheet.active.add_chart(chart)
sheet.save(f"{LOCAL}/file.xlsx")
The content of "/xl/drawings/drawing1.xml" remain the same in both cases.

Quote:<wsDr xmlns="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"><oneCellAnchor><from><col>4</col><colOff>0</colOff><row>14</row><rowOff>0</rowOff></from><ext cx="5400000" cy="2700000"/><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>
Reply
#2
After generating two spreadsheets, one good and one bad, the content of both has been extracted. The only file that differ is "/xl/charts/chart1.xml".

Below is the content of both files:
chart1.xml (good)

<chartSpace xmlns="http://schemas.openxmlformats.org/drawingml/2006/chart">
  <chart>
    <plotArea>
      <scatterChart>
        <axId val="10"/>
        <axId val="20"/>
      </scatterChart>
      <valAx>
        <axId val="10"/>
        <scaling>
          <orientation val="minMax"/>
        </scaling>
        <axPos val="l"/>
        <majorGridlines/>
        <majorTickMark val="none"/>
        <minorTickMark val="none"/>
        <crossAx val="20"/>
      </valAx>
      <valAx>
        <axId val="20"/>
        <scaling>
          <orientation val="minMax"/>
        </scaling>
        <axPos val="l"/>
        <majorGridlines/>
        <majorTickMark val="none"/>
        <minorTickMark val="none"/>
        <crossAx val="10"/>
      </valAx>
    </plotArea>
    <legend>
      <legendPos val="r"/>
    </legend>
    <plotVisOnly val="1"/>
    <dispBlanksAs val="gap"/>
  </chart>
</chartSpace>
chart1.xml (bad)

<chartSpace xmlns="http://schemas.openxmlformats.org/drawingml/2006/chart">
  <chart>
    <plotArea>
    <scatterChart/>
    <valAx>
      <axId val="10"/>
      <scaling>
        <orientation val="minMax"/>
      </scaling>
      <axPos val="l"/>
      <majorGridlines/>
      <majorTickMark val="none"/>
      <minorTickMark val="none"/>
      <crossAx val="20"/>
    </valAx>
    <valAx>
      <axId val="20"/>
      <scaling>
        <orientation val="minMax"/>
      </scaling>
      <axPos val="l"/>
      <majorGridlines/>
      <majorTickMark val="none"/>
      <minorTickMark val="none"/>
      <crossAx val="10"/>
    </valAx>
  </plotArea>
  <legend>
    <legendPos val="r"/>
  </legend>
  <plotVisOnly val="1"/>
  <dispBlanksAs val="gap"/>
  </chart>
</chartSpace>
The difference between the two is the <scatterChart> section, which is empty in the bad file, and has this content in the good one:

      <scatterChart>
        <axId val="10"/>
        <axId val="20"/>
      </scatterChart>
Any clues about what could be causing that problem? Broadly speaking, is there another way to make a 'better' subclass in Python?
Reply
#3
After looking at the source code in openpyxl/chart/scatter_chart.py, I solved it by declaring __elements__ before __init__():

class Chart(openpyxl.chart.ScatterChart):
    __elements__ = ('scatterStyle', 'varyColors', 'ser', 'dLbls', 'axId',)

    def __init__(self):
        super().__init__()
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Parsing and summing time deltas (duration) onto bar + pie charts using pandas - - DRY Drone4four 2 583 Feb-10-2024, 06:04 PM
Last Post: Drone4four

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020