D
Click here to add graphs https://openpyxl.readthedocs.io/en/stable/charts/secondary.html Homec1 += c2as in the source of the questionchart += chart2 The following two points can be adjusted by adjusting the column of the data to be displayed.*"100-10を200-10を300-10"* Line graph of "gokei線base"It seems that you can do as follows.####Changesimport 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","300-10"を積上げ棒グラフ
data2 = Reference(sh, min_col=5, max_col=6, min_row=2, max_row=sh.max_row) #### ※"gokei","base"を折れ線グラフ
chart = BarChart()
chart.type = "col"
chart.grouping = "stacked"
chart.overlap = 100
chart.title = "メインタイトル"
chart.y_axis.title = "数量"
chart.legend.position = 'b'
chart.add_data(data1, titles_from_data=True)
chart.set_categories(labels)
chart2 = LineChart()
chart2.add_data(data2, titles_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 #### 複合グラフ
sh.add_chart(chart, "I2")
sh.add_chart(chart2, "I20") #### 2つ目の単独は表示しない
wb.save("./sample.xlsx")
The following:DataLabelListIt seems that you can use it.I could hide the graph, but I didn't see it right, so I tried to display only the minimum size point and draw the linkage line.*In the end, I want to hide the line graph of "gokei" and set the data labelIt is a question that you can refer directly to this question. https://www.shibutan-bloomers.com/python_libraly_openpyxl-8/2947/ https://www.shibutan-bloomers.com/python_libraly_openpyxl-10/3260/ This is not OpenPyXl. https://anthonysmoak.com/2020/12/29/add-total-values-for-stacked-column-and-stacked-bar-charts-in-excel/ OpenPyXl https://openpyxl.readthedocs.io/en/stable/charts/line.html https://openpyxl.readthedocs.io/en/stable/api/openpyxl.chart.marker.html#openpyxl.chart.marker.Marker https://openpyxl.readthedocs.io/en/stable/api/openpyxl.chart.label.html?highlight=datalabellist#openpyxl.chart.label.DataLabelList I added/modify 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","300-10"を積上げ棒グラフ
"gokei"と"base"の表示形式を分けるために分割
data2 = Reference(sh, min_col=5, max_col=5, min_row=2, max_row=sh.max_row) #### ※"gokei"を折れ線グラフ
data3 = Reference(sh, min_col=6, max_col=6, min_row=2, max_row=sh.max_row) #### ※"base"を折れ線グラフ
chart = BarChart()
chart.type = "col"
chart.grouping = "stacked"
chart.overlap = 100
chart.title = "メインタイトル"
chart.y_axis.title = "数量"
chart.legend.position = 'b'
chart.add_data(data1, titles_from_data=True)
chart.set_categories(labels)
chart2 = LineChart() #### "gokei"
chart2.add_data(data2, titles_from_data=True)
chart3 = LineChart() #### "base"
chart3.add_data(data3, titles_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
"gokei" を最小サイズの点で表示し、連結の線を表示せず、数値をラベル表示する
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
ここまで
chart += chart2 #### "gokei"を合成
chart += chart3 #### "base"を合成
sh.add_chart(chart, "I2")
sh.add_chart(chart2, "I20") #### 2つ目の単独は表示しない
wb.save("./sample.xlsx")