python 操作 excel 写入图表chart示例（二）

excel中写入图表

excel 中添加 报表图

一、openpyxl 官方示例

官方文档示例地址：​​openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files — openpyxl 3.0.10 documentation​​

二、使用步骤

1.引入库

pip install openpyxl      

2.写入数据

## excel 文件读取 pip install openpyxl 
## 如果你要操作 图片引入文件中 还需要 pip install pillow 
## 引入必须的类操作 openpyxl is a Python library to read/write Excel 2010 xlsx/xlsm/xltx/xltm files.
## 文档实例：https://openpyxl.readthedocs.io/en/stable/
import openpyxl
## 引入图表
from openpyxl.chart import BarChart,BarChart3D, Reference, Series
from openpyxl.utils import get_column_letter,column_index_from_string

'''
读取文件
'''


## 读取 2007版 excel 在 路径问题上 需要注意的是  windows 文件夹需要两个 \\来表示 一个\会报错
workbook_2007 = openpyxl.load_workbook(filename="julong_test\\temp_chart.xlsx")
type(workbook_2007) 

## 获取表格sheet页名称
sheetnames_2007 = workbook_2007.sheetnames
print("sheetnames_2007:",sheetnames_2007)
## 获取sheet对象
worksheets_2007 = workbook_2007.worksheets
print("worksheets_2007:",worksheets_2007)

'''
图表
'''
## 获得活动的表格sheet
workbook0 = worksheets_2007[0]

for i in range(10):
    '''
    append(['This is A1', 'This is B1', 'This is C1'])
    **or** append({'A' : 'This is A1', 'C' : 'This is C1'})
    **or** append({1 : 'This is A1', 3 : 'This is C1'})
    '''
    ## 第一列插入值
    workbook0.append([i])

values = Reference(workbook0, min_col=1, min_row=1, max_col=1, max_row=10)
## 创建图表 柱形图表
chart = BarChart()
## 设置标题
chart.title = "Bar Chart"
## 数据添加
chart.add_data(values)

## 图表开始位置  E15
workbook0.add_chart(chart, "E15")

'''
Bar Chart 3D
'''
workbook1 = worksheets_2007[1]
for i in range(10):
    ## 第一列插入值
    workbook1.append([i])
value3D = Reference(workbook1, min_col=1, min_row=1, max_col=1, max_row=10)
char3D = BarChart3D()
## 设置标题
char3D.title = "Bar Chart 3D"
## 数据添加
char3D.add_data(value3D)
## 图表开始位置  E15
workbook1.add_chart(char3D, "E15")



workbook_2007.save(filename="julong_test\\temp_chart_1.xlsx")
workbook_2007.close()      

3.写入多个数据示例

## excel 文件读取 pip install openpyxl 
## 如果你要操作 图片引入文件中 还需要 pip install pillow 
## 引入必须的类操作 openpyxl is a Python library to read/write Excel 2010 xlsx/xlsm/xltx/xltm files.
## 文档实例：https://openpyxl.readthedocs.io/en/stable/
import openpyxl
## 引入图表
from openpyxl.chart import BarChart,BarChart3D, Reference, Series
from openpyxl.utils import get_column_letter,column_index_from_string
from copy import deepcopy
'''
读取文件
'''


## 读取 2007版 excel 在 路径问题上 需要注意的是  windows 文件夹需要两个 \\来表示 一个\会报错
workbook_2007 = openpyxl.load_workbook(filename="julong_test\\temp_chart.xlsx")
type(workbook_2007) 

## 获取表格sheet页名称
sheetnames_2007 = workbook_2007.sheetnames
print("sheetnames_2007:",sheetnames_2007)
## 获取sheet对象
worksheets_2007 = workbook_2007.worksheets
print("worksheets_2007:",worksheets_2007)
workbook = worksheets_2007[0]
## 组装数据
rows = [
    ('Number', 'Batch 1', 'Batch 2'),
    (2, 10, 30),
    (3, 40, 60),
    (4, 50, 70),
    (5, 20, 10),
    (6, 10, 40),
    (7, 50, 30),
]


for row in rows:
    workbook.append(row)


chart1 = BarChart()
chart1.type = "col"
## 样式
chart1.style = 10
## 标题
chart1.title = "Bar Chart"
## Y轴描述
chart1.y_axis.title = 'Test number'
## X轴描述
chart1.x_axis.title = 'Sample length (mm)'
'''
min_col=2 最小列, min_row=1 最小行, max_row=7 最大行, max_col=3 最大列
'''
## 数据
data = Reference(workbook, min_col=2, min_row=1, max_row=7, max_col=3)
## 索引 作为X轴 坐标
cats = Reference(workbook, min_col=1, min_row=2, max_row=7)
## 添加数据 赋值
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
## 添加图表并指定保存位置
workbook.add_chart(chart1, "A10")


## 复制图表
chart2 = deepcopy(chart1)
chart2.style = 11
chart2.type = "bar"
chart2.title = "Horizontal Bar Chart"

workbook.add_chart(chart2, "J10")


chart3 = deepcopy(chart1)
chart3.type = "col"
chart3.style = 12
chart3.grouping = "stacked"
chart3.overlap = 100
chart3.title = 'Stacked Chart'

workbook.add_chart(chart3, "A27")


chart4 = deepcopy(chart1)
chart4.type = "bar"
chart4.style = 13
chart4.grouping = "percentStacked"
chart4.overlap = 100
chart4.title = 'Percent Stacked Chart'

workbook.add_chart(chart4, "J27")
## 保存
workbook_2007.save(filename="julong_test\\temp_chart_2.xlsx")
workbook_2007.close()      

总结