Pivot Chart from xlsx file is being modified to Normal Chart when saved

Issue #1232 resolved
Waldo Boshoff created an issue

Using 2.6.1

Process we follow to mimic a small reporting engine on our database using openpyxl:

  1. Open template.xlsx file which contains presetup pivots reading from a empty datasheet and charts linked to those pivots (pivotcharts) to create an excel dashboard.

  2. Using openpyxl to populate the datasheet with data from a specified query

  3. Closing up the modified xlsx and allowing the user to download and open the workbook

  4. On open the Pivot will refresh (setting on pivot table) and pull in the new data in to the pivot. Up to this point everything works

  5. The charts linked to the pivot however suddenly become unlinked, and now reference only a portion of the original pivot table, skewing the view of the pivot table.

Any ideas on how to keep the pivot chart referencing the pivot to update with the pivot when it is refreshed on open?

Comments (11)

  1. CharlieC

    I haven't looked at this in any detail but PivotCharts are distinct objects and currently not supported by openpyxl. It's doable if you want to give it a go.

  2. Waldo Boshoff reporter

    Not sure where to start, but let me give it a go. Hopefully a PR emerges after some weeks

  3. Waldo Boshoff reporter

    Having quite a time trying to get on top of the code structure.

    Some questions:

    1. openpyxl.chart.chartspace.PivotSource is the closest I have come from the chart side. Ideally, pointing a chart to a specific pivot will link the chart to that pivot. What is this functionality currently used for, or how can I utilize this to solve my problem

    2. Approaching it from the pivot table side: openpyxl.pivot.table.ChartFormat seems to link the pivot to a specified chart. What is this functionality currently used for?

    3. Which approach do you think will be the most rewarding in building out?

    Best

  4. CharlieC

    The good news is that openpyxl largely implements the OOXML specification. The bad news is that the OOXML specification often doesn't explain how things are supposed to work, specifically how different elements fit together, so some degree of reverse engineering and a lot of patience is required.

    I don't have any relevant files so I don't really know what components are required. If you do, it should be possible to generate the relevant classes for parsing and serialising. Putting the parts together is normally a process of following the relationships, though these can be both implicit and explicit.

  5. Waldo Boshoff reporter
  6. CharlieC

    There's no etiquette about this but I do provide professional support including feature development for the library.

  7. Log in to comment