python办公——excel（二）

一，设置工作表字体样式

设置工作表的字体样式主要是通过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')