PythonModule_openpyxl_styles樣式處理
目錄
前言
繼續搞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"
相關文章
- webpack對樣式的處理Web
- 《深入react技術棧》之樣式處理React
- echarts3.0非同步資料載入之series樣式處理EchartsS3非同步
- webpack4+babel7入門到精通(二、樣式的處理)WebBabel
- 這樣也行,在lambda表示式中優雅的處理checked exceptionException
- 陣列處理函式陣列函式
- 當 Vue 處理陣列與處理純物件的方式一樣Vue陣列物件
- 不平衡樣本的處理
- echarts圖表漸變色 、及X軸滾動條樣式處理Echarts
- Flink處理函式實戰之四:視窗處理函式
- 分散式事務處理方案,微服事務處理方案分散式
- Flink處理函式實戰之五:CoProcessFunction(雙流處理)函式Function
- Laravel 分散式事務處理Laravel分散式
- 插入式註解處理器
- 數字影像處理-取樣量化(Matlab)Matlab
- CSS歷理 初始化樣式CSS
- springcloud分散式事務處理 LCNSpringGCCloud分散式
- Sanic 處理函式修飾器函式
- mongoDB中聚合函式java處理MongoDB函式Java
- JavaScript 註冊事件處理函式JavaScript事件函式
- 正規表示式處理批量插入
- 異常處理與推導式
- GaussDB(分散式)例項故障處理分散式
- echarts 繫結事件處理函式Echarts事件函式
- C語言之字串處理函式C語言字串函式
- 怎樣在 Laravel 中處理前端認證Laravel前端
- 大資料處理過程是怎樣大資料
- 常見處理器MCU、MPU、DSP、FPGA等嵌入式處理器FPGA
- uni-app入門教程(3)資料繫結、樣式繫結和事件處理APP事件
- unity中取樣深度圖的結果處理Unity
- [Python影像處理] 三十.影像量化及取樣處理萬字詳細總結(推薦)Python
- JavaScript 批量註冊事件處理函式JavaScript事件函式
- 使用正規表示式處理金額
- 13.SpringCloudSeata處理分散式事務SpringGCCloud分散式
- SpringCloud Alibaba Seata處理分散式事務SpringGCCloud分散式
- Oracle分散式事務典型案例處理Oracle分散式
- JavaScript 非同步函式的 Promisification 處理JavaScript非同步函式
- 六、函式、包和錯誤處理函式
- [Python影象處理] 一.影象處理基礎知識及OpenCV入門函式PythonOpenCV函式