CODEWITHSUNDEEP
pythonpython-3.xchartsopenpyxl
Gilles Criton
Gilles Criton

Reputation: 781

How to set up the Graphical Properties of Chart Area using openpyxl

I would like to change the background color of Chart_area as well as remove all borders. Regarding the background color, I have tried the solution suggested: How to set Background Color of Plot Area of Chart using openpyxl but this solution doesn't work for any charts i.e. BarChart, AreaChart, LineChart, ScatterChart, RadarChart etc... I tried two openpyxl versions: 2.4.7, 2.4.9. without success.

# setup the chart
chart = LineChart()
# test to remove border line 
*****************************************************
chart.graphical_properties = 
GraphicalProperties(ln=LineProperties(noFill=True))
chart.height = 9
chart.width = 13.4
props = GraphicalProperties(solidFill="f2f2f2")

# setup and append the first series
list_names = ['Fund', 'Market', 'Benchmark']
data = Reference(ws2, min_col=2, min_row=1, max_col=n + 1, max_row=p + 
1)

chart.x_axis.number_format = 'mmm yy'
chart.x_axis.majorTimeUnit = "months"
chart.add_data(data, titles_from_data=True)
dates = Reference(ws2, min_col=1, min_row=2, max_col=1, max_row=1 + p)
chart.set_categories(dates)
# Style the lines
chart.series[0].graphicalProperties.line.solidFill = "7E3F00"
chart.series[1].graphicalProperties.line.solidFill = "45788C"
chart.series[2].graphicalProperties.line.solidFill = "8BADD9"
chart.plot_area.graphicalProperties = props
# test to change background color 
*****************************************************
chart.graphical_properties = props
ws1.add_chart(chart, "B14")

I also tried to remove the borders of chart_area. I found this post: openpyxl - Ability to remove border from charts?. But I don't understand how do I handle to modify the library. I am a bit surprised that there is no simpler way.

Many thanks in advance for your help.

Upvotes: 2

Views: 5112

Answers (1)

user2511
user2511

Reputation: 181

This process may be helpful guidance to find an answer.

  1. Open a new excel spreadsheet and create a chart of interest with default settings. Save this with filename "default-chart-saved-by-excel.xlsx".
  2. Now modify the chart by changing the background color to purple (hex code 'AA00AA'). Save this with filename "redBG-chart-saved-by-excel.xlsx" and close excel.
  3. Open the default excel file using a zip program (7zip or similar) and extract the xl > charts > chart1.xml file to your working folder. Rename it to "default-chart-saved-by-excel.xml" Repeat for the "redBG" case.
  4. Open both of the xml files with an XML editor of choice (some web browsers work well) and look for the chunk of xml code that has changed by searching for the purple hex code 'AA00AA' That chunk is what you want openpyxl to target.

Upvotes: 5

Related Questions