PYTHON - openpyxl （四）

1.1 字体Font

类型：openpyxl.styles.fonts.Font

sheet["a1"].font = Font(name='字体'，size=字号, ...)

name 字体，中文字体前面加u
size 字号大小
bold = True / False 粗体
italic = True / False 斜体
underline = None / single / double 下划线（没有，单，双）
strike=True / False 删除线
vertAlign = None / superscript / subscript
color = rrggbb 颜色

from openpyxl.styles import Font

wb: Workbook = load_workbook("d:/test.xlsx", data_only=True)
sh: Worksheet = wb.active
font_object = Font(name=u'宋体', size=25, underline='single',vertAlign='superscript', color='00ff00')
sh["a1"].font = font_object
sh["a1"] = "中国"

1.2 对齐 Alignment

类型：openpyxl.styles.alignment.Alignment

sheet["a1"].alignment  = Alignment(horizontal=None, vertical=None,
                 textRotation=0, wrapText=None, ...)

horizontal = general / justify / right / centerContinuous / distributed / fill / center / left
- 水平对齐方式：常规，两端对齐，右，跨列居中，分散对齐，填充，居中，靠左
vertical = center / top / bottom/ justify / distributed
- 垂直对齐方式：中，上，下，两端，分散
textRotation = 旋转角度
wrapText = True / False 自动换行
shrink_to_fit = True / False 是否缩小字体填充
indent = 缩进，在电子表格中增加缩进量，减小缩进量

from openpyxl.styles import Alignment

wb: Workbook = load_workbook("d:/test.xlsx", data_only=True)
sh: Worksheet = wb.active
align = Alignment(horizontal='right')
sh["a1"] = "中国"
sh["a1"].alignment = align

1.3 边框：Side, Border

类型：

openpyxl.styles.borders.Side
openpyxl.styles.borders.Border

Side(style=线条样式, color=边线颜色)

线条样式：slantDashDot、dashDotDot、dotted、double、dashed、thick、medium、mediumDashDotDot、thin、dashDot、hair、mediumDashDot、mediumDashed

sheet["a1"].border = Border(left=Side, right=Side, top=Side, bottom=Side)

定义，上下左右边框的样式

from openpyxl.styles import Side, Border

from openpyxl.workbook import Workbook
from openpyxl.worksheet.worksheet import Worksheet

wb: Workbook = load_workbook("d:/test.xlsx", data_only=True)
sh: Worksheet = wb.active
sh["a1"] = "中国"
side1 = Side(style='thin', color='ff0000')
side2 = Side(style='double', color='00ff00')
border = Border(left=side1, top=side2, bottom=side2, right=side1)
for row in sh["a1:b5"]:
    for col in row:
        col.border = border

1.4 填充 PatternFill

用于填充纯色和图案

类型：openpyxl.styles.fills.PatternFill

sheet["a1"].fill = PatternFill(fill_type=填充类型, start_color=前景色, end_color=背景色)

填充类型：

None，不填充
solid，实心
darkGray， 75%灰
mediumGray， 50%灰
lightGray， 25%灰
gray125， 12.5%灰
gray0625， 6.25%灰
darkHorizontal，水平条纹
darkVertical，垂直条纹
darkDown，逆对角线条纹
darkUp, 对角线条纹
darkGrid, 对角线剖面线
darkTrellis, 粗对角线剖面线
lightHorizontal , 细水平条纹
lightVertical , 细垂直条纹
lightDown，细逆对角线条纹
lightUp, 细对角线条纹
lightGrid, 细水平剖面线
lightTrellis, 细对角线剖面线

from openpyxl.styles importPatternFill

wb: Workbook = load_workbook("d:/test.xlsx", data_only=True)
sh: Worksheet = wb.active
p = PatternFill(fill_type='lightTrellis', start_color='ff0000')
for item in sh["a1:b5"]:
    for cell in item:
        cell.fill = p

wb.save("1.xlsx")

对于前景色和背景色：

对于纯色：用前景色绘制
对于图案：用前景色绘制，如果指定背景色，则在背景色块上绘制图案。

1.5 锁定单元格和隐藏公式

类型： openpyxl.styles.protection.Protection

cell.protection = Protection(locked=True, hidden=False)

locked = True / False 是否锁定
hidden = True / False 是否隐藏公式

注意：只有在保护工作表的时候才有效。

1.6 行高和列宽

sh.row_dimensions[行].height = 200
sh.column_dimensions[列].width = 100

行用数字表示
列用字母表示

wb: Workbook = load_workbook("d:/test.xlsx", data_only=True)
sh: Worksheet = wb.active
sh.row_dimensions[1].height = 30
sh.column_dimensions["b"].width = 50

1.7 自定义样式

可以定义多个样式对象，之后添加到工作簿中，用到的时候赋值即可。

类型：class openpyxl.styles.named_styles.NamedStyle

NamedStyle(self,
             name="Normal",
             font=Font(),
             fill=PatternFill(),
             border=Border(),
             alignment=Alignment(),
             number_format=None,
             protection=Protection(),
             builtinId=None,
             hidden=False,
             xfId=None,
            ):

name是起一个名字
其它参加可以设置样式


wb: Workbook = load_workbook("d:/test.xlsx", data_only=True)
sh: Worksheet = wb.active

# 定义两个样式
a = NamedStyle("title", font=Font(name=u'黑体', size=14, bold=True, color='ff0000'))
b = NamedStyle("normal", font=Font(name=u'宋体', size=12, bold=False))

# 添加样式到工作簿
wb.add_named_style(a)
wb.add_named_style(b)

# 标题（第一行）应用样式
for cell in sh[1]:
    cell.style = "title"

# 正文（其它行）应用样式
rows = sh.rows
next(rows)
for row in rows:
    for cell in row:
        cell.style = "normal"