Sorted columns and scatter charts not linked
Created originally on Bitbucket by EnthusiasticStudent (Eric Jensen)
Sorting data in excel updates what data is shown in charts created with excel, but not in charts created with openpyxl.
A minimum working example:
#!python
import numpy as np
from openpyxl import *
from random import random
from openpyxl.utils.cell import get_column_letter
from openpyxl.chart import (
LineChart,
BarChart,
ScatterChart,
Reference,
Series,
)
from openpyxl.drawing.text import CharacterProperties
wb = Workbook()
ws = wb.create_sheet()
ws.title = 'interactiveChart'
num = 9
ws.cell(column=1, row=2, value='X')
ws.cell(column=2, row=2, value='Y')
for i in range(num+1):
ws.cell(column=1, row=3+i, value=random()*100)
ws.cell(column=2, row=3+i, value='=A{0}*3+4+ABS(5/(11-A{0}))+ABS(10/(35-A{0}))+ABS(30/(67-A{0}))'.format(3+i))
textSize = 10
modeChart = ScatterChart()
modeChart.title = 'Resonance'
modeChart.title.tx.rich.p[0].r.rPr = CharacterProperties(sz=textSize*100, b=True)
modeChart.style = 48
modeChart.x_axis.title = "X"
modeChart.x_axis.title.tx.rich.p[0].r.rPr = CharacterProperties(sz=textSize*100, b=True)
modeChart.y_axis.title = "Y"
modeChart.y_axis.title.tx.rich.p[0].r.rPr = CharacterProperties(sz=textSize*100, b=True)
modeChart.legend = None
xvalues = Reference(ws, min_col=1, min_row=2, max_row=num+3)
yvalues = Reference(ws, min_col=2, min_row=2, max_row=num+3)
series = Series(yvalues, xvalues, title_from_data=False, title='Resonace')
modeChart.series.append(series)
s1 = modeChart.series[0]
s1.marker.symbol = "diamond"
s1.marker.graphicalProperties.solidFill = "6495ED"
s1.marker.graphicalProperties.line.solidFill = "6495ED"
s1.graphicalProperties.line.noFill = True
modeChart.x_axis.tickLblPos = "low"
modeChart.y_axis.tickLblPos = "low"
modeChart.width = 12
modeChart.height = 7
ws.add_chart(modeChart, "F6")
ws.auto_filter.ref = 'A2:B{}'.format(num+3)
ws = wb.get_sheet_by_name("Sheet")
wb.remove_sheet(ws)
wb.save('aTest.xlsx')
I have tried to examine the .xlsx files to see what differences there are. First I noted that there is no calcChain file in the openpyxl .xlsx file, and it is not referenced in the -rels file. I do not think this is the issue since the calcChain file in the excel-generated .xlsx file only lists the cells containing the calculated y values (see above code). Comparing the two chart1.xml files I note that all the tags are prefaced by "c:" in the excel-generated file and that some of the schema references are different. The excel-generated chart1.xml file also contains the value of every datapoint, listed individually, while the openpyxl chart1.xml only contains a range reference.
I'd like to be able to solve this myself, but I do not know how to change the xml files in the .xlsx files. Perhaps if someone could give me a quick guide to doing this I could use it to identify what causes this link and possible generate a pull request down the line?
Attachments: aTest.xlsx