openpyxl

吃草的魚仔 2018-05-07

展開全文

單元格樣式

簡介

openpyxl的單元格樣式由6種屬性決定，每一種都是一個類，需要注意的是這在對樣式屬性賦值時，樣式實例一經(jīng)創(chuàng)建，樣式實例的屬性就不可更改，只能新建樣式實例。如下所示（中文是我自己翻譯的，不太準確，領(lǐng)會精神）：

font(字體類)：字號、字體顏色、下劃線等
fill(填充類)：顏色等
border(邊框類)：設(shè)置單元格邊框
alignment(位置類)：對齊方式
number_format(格式類)：數(shù)據(jù)格式
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)

以上幾種樣式（字體、填充、邊框、位置和保護）實例一旦被創(chuàng)建實例的屬性就不可更改，只能重新創(chuàng)建實例。

>>> 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 # 不會生效，樣式實例一旦創(chuàng)建，實例的屬性就不可改變
>>>
>>> # 若要改變樣式，必須新建樣式實例
>>>
>>> a1.font = Font(color=colors.RED, italic=True) # 創(chuàng)建字體實例（紅色、斜體，其他屬性默認）

復制樣式

樣式可以被復制：

>>> 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

基本字體顏色

字體顏色有一些顏色常量，可以直接調(diào)用:

from openpyxl.styles import Font
from openpyxl.styles.colors import RED
font = Font(color=RED)
font = Font(color="00FFBB00")

也可以按索引創(chuàng)建實例：

from openpyxl.styles.colors import Color
c = Color(indexed=32)
c = Color(theme=6, tint=0.5)

說到顏色，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)

可以對整行整列設(shè)置樣式，前提是單元格已創(chuàng)建。

col = ws.column_dimensions['A']
col.font = Font(bold=True)
row = ws.row_dimensions[1]
row.font = Font(underline="single")

數(shù)據(jù)格式

數(shù)據(jù)格式屬性number_format的值是字符串類型，不為對象，直接賦值即可。
openpyxl內(nèi)置了一些數(shù)據(jù)格式查看openpyxl.styles.numbers，也支持excel自定義格式，以下兩種方式效果相同：

# 使用openpyxl內(nèi)置的格式
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

# 直接使用字符串
ws.cell['D2].number_format = 'General'
ws.cell(row=2, column=4).number_format = 'd-mmm-yy'

編輯頁面設(shè)置

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

編輯打印選項

from openpyxl.workbook import Workbook

wb = Workbook()
ws = wb.active

ws.print_options.horizontalCentered = True
ws.print_options.verticalCentered = True