openpyxl 3.0.7 中文教程

openpyxl 3.0.7 中文教程

python Execl 处理库教程 https://openpyxl-chinese-docs.readthedocs.io/zh-cn/latest/tutorial.html#id2

案例：

生成表格，设置单元格样式

from openpyxl import Workbook
from openpyxl.styles import Font, Border, Side, PatternFill# 创建一个新的工作簿
wb = Workbook()
# 获取活动的工作表
ws = wb.active# 定义表头样式
header_font = Font(color='FFFFFF', bold=True)
header_fill = PatternFill(start_color='0000FF', end_color='0000FF', fill_type='solid')
header_border = Border(left=Side(style='thick'),right=Side(style='thick'),top=Side(style='thick'),bottom=Side(style='thick'))# 定义普通单元格样式
cell_border = Border(left=Side(style='thin'),right=Side(style='thin'),top=Side(style='thin'),bottom=Side(style='thin'))# 设置表头
headers = ['姓名', '年龄', '性别']
ws.append(headers)# 应用表头样式
for cell in ws[1]:cell.font = header_fontcell.fill = header_fillcell.border = header_border# 添加一些数据行
data = [('张三', 25, '男'),('李四', 30, '女'),('王五', 28, '男')
]
for row in data:ws.append(row)# 应用普通单元格样式
for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=1, max_col=ws.max_column):for cell in row:cell.border = cell_border# 保存工作簿
wb.save('example.xlsx')

生成图表

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference, Series
from openpyxl.chart.label import DataLabelList# 创建一个新的工作簿
wb = Workbook()
# 获取活动的工作表
ws = wb.active# 添加数据
data = [['月份', '产品A', '产品B', '产品C'],[1, 10, 20, 30],[2, 15, 25, 35],[3, 20, 30, 40],[4, 25, 35, 45],[5, 30, 40, 50],
]# 将数据添加到工作表中
for row in data:ws.append(row)# 创建堆叠条形图
chart = BarChart()
chart.type = "bar"
chart.grouping = "standard"# 设置数据范围
categories = Reference(ws, min_col=1, min_row=2, max_row=6)
values = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=6)# 添加数据系列
chart.add_data(values, titles_from_data=True)
chart.set_categories(categories)# 设置图表标题
chart.title = "堆叠条形图"
chart.x_axis.title = "月份"
chart.y_axis.title = "销售额"# 添加值标签
for series in chart.series:series.dLbls = DataLabelList()series.dLbls.showVal = True# 设置 x 轴和 y 轴刻度
chart.y_axis.scaling.min = 0
chart.y_axis.scaling.max = 60
chart.y_axis.majorUnit = 10  # 设置 y 轴主刻度间隔为 10# 设置 x 轴刻度间隔
chart.x_axis.tickLblSkip = 1  # 每个类别都显示标签# 将图表添加到工作表中
ws.add_chart(chart, "F2")# 保存工作簿
wb.save('stacked_bar_chart_with_ticks.xlsx')

复制单元格样式

from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Sidedef copy_style(from_cell, to_cell):"""复制一个单元格的样式到另一个单元格。:param from_cell: 源单元格:param to_cell: 目标单元格"""if from_cell.has_style:to_cell.font = Font(name=from_cell.font.name,size=from_cell.font.size,bold=from_cell.font.bold,italic=from_cell.font.italic,vertAlign=from_cell.font.vertAlign,underline=from_cell.font.underline,strike=from_cell.font.strike,color=from_cell.font.color)to_cell.fill = PatternFill(fill_type=from_cell.fill.fill_type,start_color=from_cell.fill.start_color,end_color=from_cell.fill.end_color)to_cell.border = Border(left=Side(border_style=from_cell.border.left.style, color=from_cell.border.left.color),right=Side(border_style=from_cell.border.right.style, color=from_cell.border.right.color),top=Side(border_style=from_cell.border.top.style, color=from_cell.border.top.color),bottom=Side(border_style=from_cell.border.bottom.style, color=from_cell.border.bottom.color))to_cell.alignment = Alignment(horizontal=from_cell.alignment.horizontal,vertical=from_cell.alignment.vertical,text_rotation=from_cell.alignment.text_rotation,wrap_text=from_cell.alignment.wrap_text,shrink_to_fit=from_cell.alignment.shrink_to_fit,indent=from_cell.alignment.indent)# 如果需要复制数字格式等其他样式属性，可以在这里添加更多的复制操作# 加载现有的工作簿
wb = load_workbook('example.xlsx')
ws = wb.active# 假设我们要从 A1 单元格复制样式到 G1 单元格
copy_style(ws['A1'], ws['G1'])# 保存工作簿
wb.save('example_copied_styles.xlsx')