单元格样式
简介
openpyxl的单元格样式由6种属性决定，每一种都是一个类。如下所示（大概意思）：
- font(字体类)：字号、字体颜色、下划线等
- fill(填充类)：颜色等
- border(边框类)：设置单元格边框
- alignment(位置类)：对齐方式
- number_format(格式类)：数据格式
- protection(保护类)：写保护
单元格默认样式如下：
-
>>> from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
-
>>> font = Font(name= 'Calibri',
-
... size= 11,
-
... bold= False,
-
... italic= False,
-
... vertAlign= None,
-
... underline= 'none',
-
... strike= False,
-
... color= 'FF000000')
-
>>> fill = PatternFill(fill_type= None,
-
... start_color= 'FFFFFFFF',
-
... end_color= 'FF000000')
-
>>> border = Border(left=Side(border_style= None,
-
... color= 'FF000000'),
-
... right=Side(border_style= None,
-
... color= 'FF000000'),
-
... top=Side(border_style= None,
-
... color= 'FF000000'),
-
... bottom=Side(border_style= None,
-
... color= 'FF000000'),
-
... diagonal=Side(border_style= None,
-
... color= 'FF000000'),
-
... diagonal_direction= 0,
-
... outline=Side(border_style= None,
-
... color= 'FF000000'),
-
... vertical=Side(border_style= None,
-
... color= 'FF000000'),
-
... horizontal=Side(border_style= None,
-
... color= 'FF000000')
-
... )
-
>>> alignment=Alignment(horizontal= 'general',
-
... vertical= 'bottom',
-
... text_rotation= 0,
-
... wrap_text= False,
-
... shrink_to_fit= False,
-
... indent= 0)
-
>>> number_format = 'General'
-
>>> protection = Protection(locked= True,
-
... hidden= False)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
以上几种样式（字体、填充、边框、位置和保护）实例一旦被创建实例的属性就不可更改，只能重新创建实例。
-
>>> from openpyxl.styles import colors
-
>>> from openpyxl.styles import Font, Color
-
>>> from openpyxl.styles import colors
-
>>> from openpyxl import Workbook
-
>>> wb = Workbook()
-
>>> ws = wb.active
-
>>>
-
>>> a1 = ws[ 'A1']
-
>>> d4 = ws[ 'D4']
-
>>> ft = Font(color=colors.RED)
-
>>> a1.font = ft
-
>>> d4.font = ft
-
>>>
-
>>> a1.font.italic = True # 不会生效，样式实例一旦创建，实例的属性就不可改变
-
>>>
-
>>> # 若要改变样式，必须新建样式实例
-
>>>
-
>>> a1.font = Font(color=colors.RED, italic= True) # 创建字体实例（红色、斜体，其他属性默认）
-
复制样式
样式可以被复制：
-
>>> from openpyxl.styles import Font
-
>>>
-
>>> ft1 = Font(name= 'Arial', size= 14)
-
>>> ft2 = ft1.copy(name= "Tahoma") # 复制时指定字体为“Tahoma”，其他属性均复制自ft1
-
>>> ft1.name
-
'Arial'
-
>>> ft2.name
-
'Tahoma'
-
>>> ft2.size
-
14.0
基本字体颜色
字体颜色有一些颜色常量，可以直接调用:
-
from openpyxl.styles import Font
-
from openpyxl.styles.colors import RED
-
font = Font(color=RED)
-
font = Font(color= "00FFBB00")
- 1
- 2
- 3
- 4
也可以按索引创建实例：
-
from openpyxl.styles.colors import Color
-
c = Color(indexed= 32)
-
c = Color(theme= 6, tint= 0.5)
- 1
- 2
- 3
说到颜色，V2.3.5版本必须使用“00XXXXXX”的格式，不能使用“#XXXXXX”的格式。如果颜色不生效请查看版本后修改。
应用样式
可以直接应用到单元格:
-
from openpyxl.workbook import Workbook
-
from openpyxl.styles import Font, Fill
-
wb = Workbook()
-
ws = wb.active
-
c = ws[ 'A1']
-
c.font = Font(size= 12)
- 1
- 2
- 3
- 4
- 5
- 6
可以对整行整列设置样式，前提是单元格已创建。
-
col = ws .column_dimensions[ 'A']
-
col .font = Font(bold= True)
-
row = ws .row_dimensions[ 1]
-
row .font = Font(underline= "single")
数据格式
数据格式属性number_format的值是字符串类型，不为对象，直接赋值即可。
openpyxl内置了一些数据格式查看openpyxl.styles.numbers，也支持excel自定义格式，以下两种方式效果相同：-
# 使用openpyxl内置的格式
-
from openpyxl.styles import numbers
-
ws.cell[ 'D2'].number_format = numbers.FORMAT_GENERAL
-
ws.cell(row= 2, column= 4).number_format = numbers.FORMAT_DATE_XLSX15
- 1
- 2
- 3
- 4
-
# 直接使用字符串
-
ws.cell[ 'D2].number_format = 'General '
-
ws.cell(row=2, column=4).number_format = 'd-mmm-yy '
- 1
- 2
- 3
编辑页面设置
-
from openpyxl .workbook import Workbook
-
-
wb = Workbook()
-
ws = wb .active
-
-
ws .page_setup .orientation = ws .ORIENTATION_LANDSCAPE
-
ws .page_setup .paperSize = ws .PAPERSIZE_TABLOID
-
ws .page_setup .fitToHeight = 0
-
ws .page_setup .fitToWidth = 1
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
编辑打印选项
-
from openpyxl .workbook import Workbook
-
-
wb = Workbook()
-
ws = wb .active
-
-
ws .print_options .horizontalCentered = True
-
ws .print_options .verticalCentered = True
-
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
