前言
继续搞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)
>>>
- 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
- 40
- 41
- 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
- 40
- 41
注意：
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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
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
- 1
- 2
- 3
- 4
- 1
- 2
- 3
- 4
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)
- 1
- 2
- 3
- 1
- 2
- 3
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) #直接修改单元格对象的字体样式
- 1
- 2
- 3
- 4
- 5
- 6
- 1
- 2
- 3
- 4
- 5
- 6
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")
- 1
- 2
- 3
- 4
- 1
- 2
- 3
- 4
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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
Edit Print Options
>>> from openpyxl.workbook import Workbook
>>>
>>> wb = Workbook()
>>> ws = wb.active
>>>
>>> ws.print_options.horizontalCentered = True #水平居中
>>> ws.print_options.verticalCentered = True #垂直居中
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 1
- 2
- 3
- 4
- 5
- 6
- 7
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"转载：http://blog.csdn.net/jmilk/article/details/50444829