目錄

前言

繼續搞Python對Excel檔案的處理，這次主要解決如何使用Openpyxl模組的Styles來生成一個具有個性化樣式的Excel檔案。本篇基本算是翻譯了部分實用的官方文件內容，推薦小夥伴們最好的學習方法，莫過於閱讀官方文件。: )
官當文件傳送門：http://openpyxl.readthedocs.org/en/default/styles.html#worksheet-additional-properties

系統軟體

• 系統
  • Windows 8.1
• 軟體
  • Python 3.4.3
  • IPython 4.0.0

Working with styles

使用Excel樣式函式
Introduction(簡介)
Styles are used to change the look of your data while displayed on screen. They are also used to determine the number format being used for a given cell or range of cells.
Styles是用於改變你的希望顯示的資料的樣式。也可以用於設定指定的單元格或單元區域的數字格式。

Styles can be applied to the following aspects

• font to set font size, color, underlining, etc.(能夠設定字型的大小、顏色、下劃線等屬性)
• fill to set a pattern or color gradient(能夠設定單元格的填充樣式或顏色漸變)
• border to set borders on a cell(能夠設定單元格的邊框)
• cell alignment(能夠設定單元格的對齊)
• protection(能夠設定訪問限制)

Styles模組

The following are the default values(下面是函式的引數預設值)

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

注意：
Styles are shared between objects and once they have been assigned they cannot be changed. This stops unwanted side-effects such as changing the style for lots of cells when instead of only one.
不同的物件之間是可以共享同一個Styles的，並且一旦為物件指定了Styles之後就不可以再次更改。這是為了在更改很多的單元格的Styles而不僅只是更改一個單元格時能夠避免不必要的副作用。

>>> 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)      #定義一個可以共享的Styles
>>> a1.font = ft
>>> d4.font = ft
>>>
>>> a1.font.italic = True # is not allowed 物件在指定Styles後是不允許被更改的 
>>>
>>> # If you want to change the color of a Font, you need to reassign it::
>>> # 如果你想更改個別物件的Styles需要重新定義，且只會影響到個別物件
>>> a1.font = Font(color=colors.RED, italic=True) # the change only affects A1

Copying styles

Styles can also be copied(Styles可以被複制)

>>> from openpyxl.styles import Font
>>>
>>> ft1 = Font(name=`Arial`, size=14)
>>> ft2 = ft1.copy(name="Tahoma")     #複製並修改特定屬性
>>> ft1.name
`Arial`
>>> ft2.name
`Tahoma`
>>> ft2.size # copied from the 14.0   

Basic Font Colors

Colors are usually RGB or aRGB hexvalues. The colors module contains some constants
Colors通常是RGB或者是RGB的十六進位制表示。Colors模組包含了一些常量

>>> from openpyxl.styles import Font
>>> from openpyxl.styles.colors import RED
>>> font = Font(color=RED)         #RGB
>>> font = Font(color="FFBB00")    #RGB hexvalues

There is also support for legacy indexed colors as well as themes and tints
Colors也支援索引顏色、主題和色彩

>>> from openpyxl.styles.colors import Color
>>> c = Color(indexed=32)            #legacy indexed colors 定製好的Color通過索引呼叫
>>> c = Color(theme=6, tint=0.5)

Applying Styles

Styles are applied directly to cells
Styles直接應用於單元格

>>> from openpyxl.workbook import Workbook
>>> from openpyxl.styles import Font, Fill
>>> wb = Workbook()
>>> ws = wb.active
>>> c = ws[`A1`]           #獲取單元格物件
>>> c.font = Font(size=12) #直接修改單元格物件的字型樣式

Styles can also applied to columns and rows but note that this applies only to cells created (in Excel) after the file is closed. If you want to apply styles to entire rows and columns then you must apply the style to each cell yourself. This is a restriction of the file format
Styles也可以應用於列和行，但是需要注意的是這種應用只能適用於在關閉檔案之後建立的單元格。如果你想應用Style於全部的行和列，你必須為每一個單元格都應用Style。這是由於檔案格式的制約

>>> col = ws.column_dimensions[`A`]    #獲取A列的樣式
>>> col.font = Font(bold=True)         
>>> row = ws.row_dimensions[1]         #獲取1行的樣式
>>> row.font = Font(underline="single")

Edit Page Setup

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

Edit Print Options

>>> from openpyxl.workbook import Workbook
>>>
>>> wb = Workbook()
>>> ws = wb.active
>>>
>>> ws.print_options.horizontalCentered = True     #水平居中
>>> ws.print_options.verticalCentered = True       #垂直居中

Header / Footer

Headers and footers use their own formatting language. This is fully supported when writing them.but, due to the complexity and the possibility of nesting, only partially when reading them.

頭部和尾部使用它們自身的格式化語言。當你寫的時候是完全支援的。但是由於複製性和巢狀的可能性，讓你讀取的時候可能只能讀取到一個部分。

>>> from openpyxl.workbook import Workbook
>>>
>>> wb = Workbook()
>>> ws = wb.worksheets[0]
>>> #設定檔案頭部和頁尾的樣式
>>> ws.header_footer.center_header.text = `My Excel Page`
>>> ws.header_footer.center_header.font_size = 14
>>> ws.header_footer.center_header.font_name = "Tahoma,Bold"
>>> ws.header_footer.center_header.font_color = "CC3366"