示例如何实现 “创建excel文件、创建活动表,设置单元格的值以及格式,根据单元格内容绘制图表” 等功能。
# https://openpyxl.readthedocs.io/en/stable/pandas.html
import openpyxl
from openpyxl.styles import Font, PatternFill, Border
from openpyxl.styles import colors
from openpyxl.chart import ScatterChart,Reference,Series
fileName = '创建excel.xlsx'
workBook = openpyxl.load_workbook(fileName,data_only=False)
# 激活一个活动表格,并将[A1] 单元格内容设置成A1,颜色设置成红色,粗体
# 保存活动表的名称为格式设置
workSheet = workBook.create_sheet('合并单元格')
fill = openpyxl.styles.PatternFill("solid", fgColor="1874CD")
boldRedFont = Font(name='Times New Roman', bold=True,color="9871AD")
workSheet['A1'] = 'A1'
workSheet['A1'].font = boldRedFont
workSheet['A1'].fill = fill
workSheet.title = '格式设置'
# 创建一个新的活动表,名称设置为‘合并单元格’,
# 合并 A1:B2 的单元格
# 并将合并后的单元格赋值为“合并”
workSheet2 = workBook.create_sheet('合并单元格')
workSheet2.merge_cells('A1:B2')
workSheet2['A1'] = '合并'
workSheet2.cell(row = 3,column=1,value='补充测试')
# 创建一个新的活动表格,并在表格中添加图表
workSheet3 = workBook.create_sheet('图表示例')
rows = [
['Size', 'Batch 1', 'Batch 2'],
[2, 40, 30],
[3, 40, 25],
[4, 50, 30],
[5, 30, 25],
[6, 25, 35],
[7, 20, 40],
]
for row in rows:
workSheet3.append(row)
chart = ScatterChart()
chart.title = "Scatter Chart"
chart.style = 13
chart.x_axis.title = 'Size'
chart.y_axis.title = 'Percentage'
xvalues = Reference(workSheet3, min_col=1, min_row=2, max_row=7)
for i in range(2, 4):
values = Reference(workSheet3, min_col=i, min_row=1, max_row=7)
series = Series(values, xvalues, title_from_data=True)
chart.series.append(series)
workSheet3.add_chart(chart, "A10")
workBook.save(fileName)
workBook.close()