目录
1 批量调整字体、样式
1.1 获取字体样式
cell.font.属性
from openpyxl.styles import Font
from openpyxl import load_workbook
workbook = load_workbook(filename='写入表格.xlsx')
sheet = workbook.active
cell = sheet['A1']
font = cell.font
print('font.name是:',font.name,'font.size是:',font.size,'是否粗体:',font.bold,'是否斜体:',font.italic)
1.2 修改字体样式、
font = Font(name=‘字体名称’,size =字体大小,bold = 是否粗体,italic = 是否斜体,color =‘字体颜色’)
from openpyxl.styles import Font
from openpyxl import load_workbook
workbook = load_workbook(filename='写入表格.xlsx')
sheet = workbook.active
cell = sheet['A1']
font = Font(name='宋体',size =12,bold = True,italic = True,color ='FF0000')
cell.font =font
workbook.save(filename='写入表格.xlsx')
1.3 设置单元格格式分类
可以通过设置单元格的风格来设置单元格格式分类
[openpyxl所支持的excel的单元格格式][https://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/styles/numbers.html?highlight=openpyxl.styles.numbers]
from openpyxl.styles import Font
from openpyxl import load_workbook
workbook = load_workbook(filename='写入表格.xlsx')
sheet = workbook.active
cell = sheet['A1']
# 设置单元格分类为百分比
cell.style = '百分比'
cell.style = '常规'
# 常规单元格
cell.number_format = 'General'
# 百分比单元格
cell.number_format = '0.00%'
# 科学计数法
cell.number_format = '0.00E+00'
workbook.save(filename='写入表格.xlsx')
1.4 设置对齐样式
Alignment(horizontal=水平对齐模式,vertical=垂直对齐模式,text_rotation=旋转角度,wrap_text=是否自动换行)
from openpyxl.styles import Alignment
from openpyxl import load_workbook
workbook = load_workbook(filename='写入表格.xlsx')
sheet = workbook.active
cell = sheet['A1']
alignment = Alignment(horizontal='center',vertical='center',text_rotation=45)
cell.alignment =alignment
workbook.save(filename='写入表格.xlsx')
horizontal：'distributed', 'justify','center','left';'fill', 'centerContinuous','right",'general'
vertical：'bottom', 'distributed','justify','center';'top'
wrap_text：布尔类型，设置是否自动换行
textRotation：整数类型，设置文本旋转角度，最大值180
1.5 设置边框样式
side = Side(style=边线样式,color=边线颜色)
border = Border(left=左边线样式,right=右边线样式,top=上边线样式,bottom=下边线样式)
from openpyxl.styles import Side, Border
from openpyxl import load_workbook
workbook = load_workbook(filename='写入表格.xlsx')
sheet = workbook.active
cell = sheet['A1']
side = Side(style='thin',color='FF000000')
border = Border(left=side,right=side,top=side,bottom=side)
cell.border=border
workbook.save(filename='写入表格.xlsx')
side.style：'double','mediumDashDotDot','slantDashDot','dashDotDot','dotted', 'hair', 'mediumDashed', 'dashed' 'dashDot' ,'thin', 'Dash Dot', 'medium', 'thick'
1.6 设置填充颜色
- 纯色填充
PatternFill(fill_type=填充样式,fgColor=填充颜色)
- 渐变颜色填充
GradientFill(stop=(渐变颜色1,渐变颜色2,渐变颜色3…))
from openpyxl.styles import PatternFill, GradientFill
from openpyxl import load_workbook
workbook = load_workbook(filename='写入表格.xlsx')
sheet = workbook.active
cell_a1 = sheet['A1']
pattern_fill = PatternFill(fill_type='solid',fgColor='00B0F0')
cell_a1.fill = pattern_fill
cell_b4 = sheet['B4']
gradient_fill = GradientFill(stop=('FFFFFF', '99CCFF', '000000'))
cell_b4.fill = gradient_fill
workbook.save(filename='写入表格.xlsx')
1.7 设置行高和列高
.row_dimensions[行编号].height = 行高
.column_dimensions[列编号].width = 列宽
from openpyxl.styles import PatternFill, GradientFill
from openpyxl import load_workbook
workbook = load_workbook(filename='写入表格.xlsx')
sheet = workbook.active
sheet.row_dimensions[1].height = 50
sheet.column_dimensions['B'].width = 20
workbook.save(filename='写入表格.xlsx')
1.8 合并单元格
sheet.merge_cells(‘合并的范围’)
sheet.merge_cells(start_row=起始行号,start_column=起始列号,end_row=结束行号,end_column=结束列号)
from openpyxl.styles import PatternFill, GradientFill
from openpyxl import load_workbook
workbook = load_workbook(filename='写入表格.xlsx')
sheet = workbook.active
sheet.merge_cells('C1:C2')
sheet.merge_cells(start_row=7,start_column=1,end_row=8,end_column=4)
workbook.save(filename='写入表格.xlsx')