python 如何向 excel 中写入某些内容?(二)
1. 批量调整字体和样式
1 .1修改字体样式
Font(name=字体名称,size=字体大小,bold=是否加粗,italic=是否斜体,color=字体颜色)
from openpyxl.styles import Font
from openpyxl import load_workbook
workbook = load_workbook(filename="a.xlsx")
sheet = workbook.active
cell = sheet["A1"]
font = Font(name="微软雅黑",size=20,bold=True,italic=True,color="FF0000")
cell.font = font
workbook.save(filename = "a.xlsx")
"""
这个 color 是 RGB 的 16 进制表示
"""
1.2 获取表格中格子的字体样式
from openpyxl.styles import Font
from openpyxl import load_workbook
workbook = load_workbook(filename="a.xlsx")
sheet = workbook.active
cell = sheet["A1"]
font = cell.font
print(font.name, font.size, font.bold, font.italic, font.color)
1 .3 设置对齐样式
Alignment
(horizontal=水平对齐模式,vertical=垂直对齐模式,text_rotation=旋转角度,wrap_text=是否自动换行)- 水平对齐:
‘distributed',‘justify',‘center',‘leftfill', *‘centerContinuous',‘right,‘general';
- 垂直对齐:
‘bottom',‘distributed',‘justify',‘center',‘top';
from openpyxl.styles import Alignment
from openpyxl import load_workbook
workbook = load_workbook(filename="a.xlsx")
sheet = workbook.active
cell = sheet["A1"]
alignment = Alignment(horizontal="center",vertical="center",text_rotation=45,wrap_text=True)
cell.alignment = alignment
workbook.save(filename = "a.xlsx")
1 .4 设置边框样式
Side
(style=边线样式,color=边线颜色)Border
(left=左边线样式,right=右边线样式,top=上边线样式,bottom=下边线样式)style
参数的种类:'double, 'mediumDashDotDot', 'slantDashDot', 'dashDotDot','dotted','hair', 'mediumDashed, 'dashed', 'dashDot', 'thin', 'mediumDashDot','medium', 'thick'
from openpyxl.styles import Side,Border
from openpyxl import load_workbook
workbook = load_workbook(filename="a.xlsx")
sheet = workbook.active
cell = sheet["D6"]
side1 = Side(style="thin",color="FF0000")
side2 = Side(style="thick",color="FFFF0000")
border = Border(left=side1,right=side1,top=side2,bottom=side2)
cell.border = border
workbook.save(filename = "a.xlsx")
1 .5 设置填充样式
PatternFill
(fill_type=填充样式,fgColor=填充颜色)GradientFill
(stop=(渐变颜色 1,渐变颜色 2……))
from openpyxl.styles import PatternFill,GradientFill
from openpyxl import load_workbook
workbook = load_workbook(filename="a.xlsx")
sheet = workbook.active
cell_b9 = sheet["B9"]
pattern_fill = PatternFill(fill_type="solid",fgColor="99ccff")
cell_b9.fill = pattern_fill
cell_b10 = sheet["B10"]
gradient_fill = GradientFill(stop=("FFFFFF","99ccff","000000"))
cell_b10.fill = gradient_fill
workbook.save(filename = "a.xlsx")
1 .6 设置行高和列宽
.row_dimensions
[行编号].height = 行高.column_dimensions
[列编号].width = 列宽
from openpyxl import load_workbook
workbook = load_workbook(filename="a.xlsx")
sheet = workbook.active
# 设置第 1 行的高度
sheet.row_dimensions[1].height = 50
# 设置 B 列的宽度
sheet.column_dimensions["B"].width = 20
workbook.save(filename = "a.xlsx")
"""
sheet.row_dimensions.height = 50
sheet.column_dimensions.width = 30
这两句代码,是将整个表的行高设置为 50,列宽设置为 30;
"""
1 .7 合并单元格
.merge_cells
(待合并的格子编号).merge_cells
(start_row=起始行号,start_column=起始列号,end_row=结束行号,end_column=结束列号)
from openpyxl import load_workbook
workbook = load_workbook(filename="a.xlsx")
sheet = workbook.active
sheet.merge_cells("C1:D2")
sheet.merge_cells(start_row=7,start_column=1,end_row=8,end_column=3)
workbook.save(filename = "a.xlsx")
当然,也有“取消合并单元格”,用法一致。
.unmerge_cells
(待合并的格子编号).unmerge_cells
(start_row=起始行号,start_column=起始列号,end_row=结束行号,end_column=结束列号)