2024年5月27日 星期一

在Excel工作表中新增漁產品交易折線圖

上一篇文章：自動化Python+Excel建立漁產品交易行情樞紐分析表


範例一、新增交易量折線圖

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
from openpyxl import load_workbook
from openpyxl.chart import LineChart, Reference

wb = load_workbook("AquaticTransData_pivot.xlsx")
ws = wb.active 
data = Reference(ws,
                 min_col = 2, 
                 min_row = 1,
                 max_row = 12)  
chart = LineChart()
chart.add_data(data,
               titles_from_data=True)
labels = Reference(ws, min_col=1, min_row=2, max_row=12)
chart.set_categories(labels)
chart.title = "漁產品交易行情圖表"
chart.x_axis.title = "交易日期"
chart.y_axis.title = "交易量"
ws.add_chart(chart, "E1")
wb.save("AquaticTransData_pivot_graph.xlsx")
wb.close()

執行結果：

範例二、交易量用長條圖、平均價用折線圖

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
from openpyxl import load_workbook
from openpyxl.chart import LineChart, BarChart, Reference

wb = load_workbook("AquaticTransData_pivot.xlsx")
ws = wb.active 
data = Reference(ws,
                 min_col = 2,
                 min_row = 1,
                 max_row = 12)  
chart = BarChart()
chart.add_data(data,
               titles_from_data=True)
labels = Reference(ws, min_col=1, min_row=2, max_row=12)
chart.set_categories(labels)
chart.title = "漁產品交易行情圖表"
chart.x_axis.title = "交易日期"
chart.y_axis.title = "交易量"
chart.y_axis.majorGridlines = None


data2 = Reference(ws,
                 min_col = 3,
                 min_row = 1,
                 max_row = 12)  
chart2 = LineChart()
chart2.add_data(data2,
               titles_from_data=True)
labels2 = Reference(ws, min_col=1, min_row=2, max_row=12)
chart2.set_categories(labels2)
chart2.y_axis.title = "平均價"
chart2.y_axis.axId = 200 #要給一個獨特的ID
chart2.y_axis.crosses = "max"

chart+=chart2

ws.add_chart(chart, "E1")
wb.save("AquaticTransData_pivot_graph.xlsx")
wb.close()

執行結果：



張貼者： 於
標籤： , ,

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)