使用[pyopenxl]操作excel批量修改单元格样式:边框、字体、对齐、列宽
提示:在python的诸多模块中,openpyxl使用最广也最方便,本文就openpyxl批量设置单元格格式,如字体、对齐、边框等略作记录与介绍,也是本人在做项目的过程中遇到的并解决的问题。
1 用openpyxl创建工作表
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
def createWorkBook():
"""创建工作簿"""
# 创建工作簿
wb = Workbook()
# 激活工作表
st = wb.active
# 添加表头
st.append(['列1', '列2', '列3', '列4'])
# 添加表格内容
st.append([1, 2, 3, 4])
st.append([1, 2, 3, 4])
st.append([1, 2, 3, 4])
st.append([1, 2, 3, 4])
return wb, st
2 批量修改单元格样式
2.1 代码
wb, st = createWorkBook()
def setCellStyle(st):
"""
设置单元格格式
params: st:就是第一步创建的工作表
"""
# 边框
border = Border(
left=Side(border_style='thin', color='000000'),
right=Side(border_style='thin', color='000000'),
top=Side(border_style='thin', color='000000'),
bottom=Side(border_style='thin', color='000000'),
)
# 对齐
alignment = Alignment(
horizontal='center',
vertical='center',
text_rotation=0,
indent=0
)
# 字体
font = Font(
name='宋体',
size=16,
bold=True,
italic=False,
strike=False,
color='000000'
)
for row, row_ind in zip(st.iter_rows(), range(1, st.max_row + 1)):
for cell in row:
# 设置边框
st[cell.coordinate].border = border
# 设置居中对齐
st[cell.coordinate].alignment = alignment
# 行高40
st.row_dimensions[row_ind].height = 40
# 设置字体
st[cell.coordinate].font = font
# 设置列宽
st.column_dimensions['A'].width = 8
st.column_dimensions['B'].width = 8
st.column_dimensions['C'].width = 8
st.column_dimensions['D'].width = 8
2.2 效果
3 保存完工
wb.save("处理结果.xlsx")
wb.close()