openpyxl - 简单设置单元格样式

单元格样式

---

简介

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