I would like to create a composite graph that displays stacked bars and line graphs in one graph area using python and openpyxl.Please tell me how to deal with it.
Also, is it not supported to create composite graphs using openpyxl in the first place?
import openpyxl
from openpyxl.chart import BarChart, LineChart, Reference
from openpyxl.chart.plotarea import DataTable
wb=openpyxl.load_workbook("./sample.xlsx")
sh=wb ['list']
labels=Reference(sh,min_col=1,max_col=1,min_row=3,max_row=sh.max_row)
data1=Reference(sh,min_col=2,max_col=6,min_row=2,max_row=sh.max_row)
data2=Reference(sh,min_col=5,max_col=6,min_row=2,max_row=sh.max_row)
chart = BarChart()
chart.type="col"
chart.grouping="stacked"
chart.overlap=100
chart.title="Main Title"
chart.y_axis.title = "Quantity"
chart.legend.position='b'
chart.add_data(data1,title_from_data=True)
chart.set_categories (labels)
chart2 = LineChart()
chart2.add_data(data2,title_from_data=True)
chart.height=10
chart.width = 15
# datatable
chart.plot_area.dTable=DataTable()
chart.plot_area.dTable.showHorzBorder=True
chart.plot_area.dTable.showVertBorder=True
chart.plot_area.dTable.showOutline=True
chart.plot_area.dTable.showKeys=True
sh.add_chart(chart, "I2")
sh.add_chart(chart2, "I20")
wb.save("./sample.xlsx")
title
date 100-10 200-10 300-10 gokeibase
9/16 15 48 41 104 50
9/17 15 48 46 109 50
9/18 15 50 50 115 50
9/19 15 50 49 114 50
9/20 15 49 49 113 50
"Loading bar graph of "100-10", "200-10", and "300-10"
" Line graph of gokei and base
*Finally, I want to hide the line graph of gokei and set the data label
If you want to superimpose the graphs, you can use chart+=chart2 for the source of the question, as shown in c1+=c2 on this page Adding a second axi.
In addition, the following two points can be done by adjusting the column of data to be displayed.
"Loading bar graph of "100-10", "200-10", and "300-10"
" Line graph of gokei and base
I think you can do it as follows. The change is that ### is added.
import openpyxl
from openpyxl.chart import BarChart, LineChart, Reference
from openpyxl.chart.plotarea import DataTable
wb=openpyxl.load_workbook("./sample.xlsx")
sh=wb ['list']
labels=Reference(sh,min_col=1,max_col=1,min_row=3,max_row=sh.max_row)
#### data1=Reference(sh,min_col=2,max_col=6,min_row=2,max_row=sh.max_row)
#### data2=Reference(sh,min_col=5,max_col=6,min_row=2,max_row=sh.max_row)
Data1=Reference(sh,min_col=2,max_col=4,min_row=2,max_row=sh.max_row)####*"100-10","200-10", and "300-10" stacked bar graph
data2=Reference(sh,min_col=5,max_col=6,min_row=2,max_row=sh.max_row)#### "line graph of "gokei", "base"
chart = BarChart()
chart.type="col"
chart.grouping="stacked"
chart.overlap=100
chart.title="Main Title"
chart.y_axis.title = "Quantity"
chart.legend.position='b'
chart.add_data(data1,title_from_data=True)
chart.set_categories (labels)
chart2 = LineChart()
chart2.add_data(data2,title_from_data=True)
chart.height=10
chart.width = 15
# datatable
chart.plot_area.dTable=DataTable()
chart.plot_area.dTable.showHorzBorder=True
chart.plot_area.dTable.showVertBorder=True
chart.plot_area.dTable.showOutline=True
chart.plot_area.dTable.showKeys=True
chart+=chart2### composite graph
sh.add_chart(chart, "I2")
#### sh.add_chart(chart2, "I20")#### Do not display the second single
wb.save("./sample.xlsx")
In addition, I think we can do the following by using DataLabelList.
It may be possible to hide the graph, but I couldn't find it right away, so I tried to display only the minimum size points so that there were no connecting lines.
*Finally, I want to hide the line graph of gokei and set the data label
I don't know if this question will help you directly, but it will be a good resource.
How to create an Excel graph in the Python library (openpyxl) [Explanation]
How to create Excel bar graph with Python library (openpyxl) and design recipe [thorough commentary]
This is probably an article for only Excel, not OpenPyXl.
Add Total Values for Stacked Column and Stacked Bar Charts in Excel
OpenPyXl will help you with this.
Line Charts
class openpyxl.chart.marker.Marker
class openpyxl.chart.label.DataLabelList
I tried adding/modifying to the first one as follows.
import openpyxl
from openpyxl.chart import BarChart, LineChart, Reference
from openpyxl.chart.label import DataLabel, DataLabelList
from openpyxl.chart.plotarea import DataTable
wb=openpyxl.load_workbook("./sample.xlsx")
sh=wb ['list']
labels=Reference(sh,min_col=1,max_col=1,min_row=3,max_row=sh.max_row)
#### data1=Reference(sh,min_col=2,max_col=6,min_row=2,max_row=sh.max_row)
#### data2=Reference(sh,min_col=5,max_col=6,min_row=2,max_row=sh.max_row)
Data1=Reference(sh,min_col=2,max_col=4,min_row=2,max_row=sh.max_row)####*"100-10","200-10", and "300-10" stacked bar graph
#### Split to separate "gokei" and "base" display formats ####
data2=Reference(sh,min_col=5,max_col=5,min_row=2,max_row=sh.max_row)#### "Gokei is a line graph
data3=Reference(sh,min_col=6,max_col=6,min_row=2,max_row=sh.max_row)####* Line graph of "base"
chart = BarChart()
chart.type="col"
chart.grouping="stacked"
chart.overlap=100
chart.title="Main Title"
chart.y_axis.title = "Quantity"
chart.legend.position='b'
chart.add_data(data1,title_from_data=True)
chart.set_categories (labels)
chart2 = LineChart()#### "gokeyi"
chart2.add_data(data2,title_from_data=True)
chart3 = LineChart()###"base"
chart3.add_data(data3,title_from_data=True)####
chart.height=10
chart.width = 15
# datatable
chart.plot_area.dTable=DataTable()
chart.plot_area.dTable.showHorzBorder=True
chart.plot_area.dTable.showVertBorder=True
chart.plot_area.dTable.showOutline=True
chart.plot_area.dTable.showKeys=True
#### Display "gokei" as the minimum size point, no connecting lines, and label numbers
s1 = chart2.series [0]
s1.marker.symbol="dot"
s1.marker.size=2
s1.graphicalProperties.line.noFill=True
chart2.dataLabels=DataLabelList(dLblPos='t')
chart2.dataLabels.showVal=True
#### up to here
"Combine chart+=chart2####" gokei"
synthesize chart+=chart3###"base"
sh.add_chart(chart, "I2")
#### sh.add_chart(chart2, "I20")#### Do not display the second single
wb.save("./sample.xlsx")
© 2024 OneMinuteCode. All rights reserved.