一,设置工作表字体样式
设置工作表的字体样式主要是通过Font方法
参数解释如下
参数 | 解释 |
---|---|
name | 字体名称 |
size | 字体大小 |
bold | 是否加粗 |
italic | 是否斜体 |
color | 字体颜色 |
import openpyxl
from openpyxl.styles import Font
workbook = openpyxl.load_workbook('书籍.xlsx') # 加载数据表
print(workbook.sheetnames)
# 这里由于有复制的表,我进行改名操作
sheet = workbook['复制文件']
sheet.title = 'Sheet2'
# 获取改名之后的工作表
sheet = workbook['Sheet2']
cells = sheet['A1:B5']
# 设置字体的格式
font = Font(name='微软雅黑',size=20,bold=True,italic=True,color='FF3030')
for cell in cells:
for ce in cell:
ce.font = font
workbook.save('书籍.xlsx')
效果如下:
二,设置对齐格式
设置工作表的对齐样式主要是通过Alignment方法
参数解释如下
参数 | 解释 |
---|---|
horizontal | 水平对齐方式 |
vertical | 竖直对齐方式 |
text_rotation | 字体旋转角度 |
wrap_text | 是否自动换行 |
import openpyxl
from openpyxl.styles import Alignment
workbook = openpyxl.load_workbook('书籍.xlsx')
sheet = workbook['Sheet2']
alignment = Alignment(horizontal='center',vertical='justify',text_rotation=45)
cells = sheet['A1:B5']
for cell in cells:
for ce in cell:
ce.alignment = alignment
workbook.save('书籍.xlsx')
效果如下:
三,设置边框,填充
边框使用Side方法,填充使用Border方法
Side参数style可以选择如下
{‘thick’, ‘dashDotDot’, ‘dotted’, ‘hair’, ‘mediumDashed’, ‘medium’, ‘double’, ‘dashDot’, ‘slantDashDot’, ‘mediumDashDotDot’, ‘mediumDashDot’, ‘dashed’, ‘thin’}
import openpyxl
from openpyxl.styles import Side,Border
workbook = openpyxl.load_workbook('书籍.xlsx')
print(workbook.sheetnames)
sheet = workbook['复制文件1']
cells = sheet["A2:B5"]
side = Side(style='medium',color='FFFF00')
border = Border(left=side,right=side,top=side,bottom=side)
for cell in cells:
for ce in cell:
ce.border = border
workbook.save('书籍.xlsx')
四、背景填充
1,PatternFill中fill_type的参数可选如下:
{‘gray125’, ‘lightVertical’, ‘darkHorizontal’, ‘darkUp’, ‘solid’, ‘lightGray’, ‘lightUp’, ‘lightTrellis’, ‘darkTrellis’, ‘darkDown’, ‘darkGrid’, ‘darkVertical’, ‘mediumGray’, ‘gray0625’, ‘darkGray’, ‘lightDown’, ‘lightGrid’, ‘lightHorizontal’}
2,GradientFill(stop=(‘c1’,‘c2’))
颜色从c1过度到c2
import openpyxl
from openpyxl.styles import PatternFill,GradientFill
workbook = openpyxl.load_workbook('书籍.xlsx')
sheet = workbook['复制文件1']
cells = sheet['A1:B1']
pattern_fill = PatternFill(fill_type='lightUp',fgColor='ADD8E6')
for cell in cells:
for ce in cell:
ce.fill=pattern_fill
workbook.save('书籍.xlsx')
cells2 = sheet['A2:B5']
grad_fill = GradientFill(stop=('FFE4B5','FFEFD5'))
for cell2 in cells2:
for ce2 in cell2:
ce2.fill = grad_fill
workbook.save('书籍.xlsx')
效果如下:
五,单元格的合并于拆分
1,merge_cells方法进行合并,unmerge_cells方法进行单元格拆分
参数 | 解释 |
---|---|
start_row | 起始的行号 |
start_column | 起始的列号 |
end_row | 结束的行号 |
end_column | 结束的列号 |
import openpyxl
workbook = openpyxl.load_workbook('书籍.xlsx')
sheet = workbook['复制文件1']
sheet.merge_cells(start_row=1,start_column=8,end_row=5,end_column=12)
workbook.save('书籍.xlsx')
import openpyxl
workbook = openpyxl.load_workbook('书籍.xlsx')
sheet = workbook['复制文件1']
sheet.unmerge_cells(start_row=1,start_column=8,end_row=5,end_column=12)
workbook.save('书籍.xlsx')