python第三方库——openpyxl

Bokeh是一个Python库，用于对Excel 2010 xlsx/xlsm/xltx/xltm文件进行读写操作。

官网对该工具的介绍为：

openpyxl is a Python library to read/write Excel 2010 xlsx/xlsm/xltx/xltm files.

It was born from lack of existing library to read/write natively from Python the Office Open XML format.

All kudos to the PHPExcel team as openpyxl was initially based on PHPExcel.

官网地址为：https://foss.heptapod.net/openpyxl/openpyxl

本文档只说明某些接口的使用，作为对该工具的学习总结。

文件的打开，创建，保存

创建一个工作簿

from openpyxl import Workbook

wb = Workbook()

打开一个已经存在的工作簿

from openpyxl import load_workbook

wb = load_workbook('sample.xlsx')

在load_workbook()中存在几个可选参数：

• data_only=True表示只读取单元格的值，而不是公式
• read_only=True表示以只读模式打开Excel文件，这样可以提高读取速度
• keep_vba=True表示保留Excel文件中的宏代码

保存为工作薄

wb.save('sample.xlsx')

创建一个工作表

ws = wb.create_sheet("Mysheet") # insert at the end (default)
ws = wb.create_sheet("Mysheet", 0) # insert at first position
ws = wb.create_sheet("Mysheet", -1) # insert at the penultimate position

激活某个工作表

ws = wb.active

该值默认为0，除非手动修改活动工作表，否则通过该方法只会获取第一个工作表的引用。不然要明确指明激活的工作表，下面代码就是获取名字为new_sheet的工作表的引用：

ws = wb["new_sheet"]

重命名工作表

在使用Workbook创建工作簿时，会自动创建一个名为Sheet的工作表。并且在工作薄中创建的工作表会采用自动命名的形式，如Sheet1、Sheet2、Sheet3。如果需要对该名称进行修改，可以使用title属性进行修改，如下所示：

ws.title = "New Title"

获取工作薄中所有工作表的名称

wb.sheetnames

或者采用遍历工作薄的形式：

for sheet in wb:
    print(sheet.title)

复制工作表

new_ws = wb.copy_worksheet(ws)

单元格的操作

获取单元格的引用

cell = ws["A4"]
cell = ws.cell(row=4, column=1)

但需注意的是，ws["A4"]的引用方式和excel中单元格的表示方式相同，但是使用cell()方法时，row和column的索引都是从1开始的数字，而不是字母，这意味着，需要将A4转换为(4,1)。

获取单元格的值

value = ws["A4"].value
value = ws.cell(row=4, column=1).value

单元格赋值

ws["A4"] = 10
ws["A4"].value = 10
ws.cell(row=4, column=1, value=10)

当创建工作薄时，虽然它包含一个工作表，但是该工作表中并不包含任何单元格。只有在访问单元格时，才会创建它们。因此使用ws.cell(row=4, column=1)虽然没有赋值，但是也会在内存中创建单元格，因此要尽量避免对空单元格的引用。

插入整行或整列、删除整行或整列

ws.insert_rows(7, 3)            # 表示在原来idx=7的行前插入3行，默认一行
ws.insert_cols(2, 3)            # 表示在原来idx=2的列前插入3列，默认一列
ws.append([1, 2, 3])            # 表示在最后一行插入数据,不足的部分用None填充

ws.delete_cols(6, 3)            # 表示删除idx=6的列，删除3列，默认一列
ws.delete_rows(6, 3)            # 表示删除idx=6的行，删除3行，默认一行

访问整列或整行

cells = ws["A"]              # 获取A列的所有单元格
cells = ws[1]                # 获取第一行的所有单元格

访问矩形区域

cells = ws["A:C"]       # 获取A到C列的所有单元格
cells = ws[1:3]         # 获取第1到3行的所有单元格
cells = ws["A1:C3"]     # 获取A1到C3的所有单元格

但需要注意，对于整列或整行的访问返回的是一维元组，而对于矩形区域的访问返回的是二维元组。因此，对于矩形区域的访问，需要使用两层循环来遍历。

或者可以通过ws.iter_rows()或ws.iter_cols()来获取单元格:

for rows in ws.iter_rows():
    for cell in rows:
        print(cell.value)

for cols in ws.iter_cols(min_row=1, max_col=3, max_row=2):
    for cell in cols:
        print(cell.value)

for values in ws.iter_cols(min_row=1, max_col=3, max_row=2, values_only=True):
    for value in values:
        print(value)

从上面可以看出

• iter_rows()和iter_cols()方法都可以接收min_row、max_row、min_col、max_col、values_only这四个参数
• 这几个参数都是可选的，如果不指定，则默认为min_row=1、max_row=ws.max_row、min_col=1、max_col=ws.max_column，values_only=False
• iter_rows()方法按行遍历，iter_cols()方法按列遍历
• values_only = True表示只迭代值

另外ws.iter_rows()无参数的调用即可遍历所有行，ws.iter_cols()无参数的调用即可遍历所有列。等效于下面的用法：

for rows in ws.rows:
    for cell in rows:
        print(cell.value)

for cols in ws.columns:
    for cell in cols:
        print(cell.value)

不过因为性能问题，只读模式下ws.iter_cols()方法和ws.columns不可用。

而如果只是想要获取单元格中的内容，则可以使用ws.values：

for values in ws.values:
    for value in values:
        print(value)

从结果可以看出，ws.values是按行迭代的。

移动区域

ws.move_range("D4:F10", rows=-1, cols=2)        # 表示将D4:F10的单元格向上移动一行，向右移动两列，即移动到E3:G9

合并/拆分单元格

ws.merge_cells('A2:D2')
ws.unmerge_cells('A2:D2')

# 与下面的设置等效
ws.merge_cells(start_row=2, start_column=1, end_row=4, end_column=4)
ws.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4)

而对比合并后的单元格的格式设置，只需要对合并单元格的左上角单元格进行设置即可。

字体、颜色、样式

默认设置为：

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)

protection = Protection(locked=True,
                        hidden=False)

设置字体、颜色、大小

from openpyxl.styles import Font

# 斜体，红色，20号，字体为Arial，加粗
ft = Font(name='Arial', size=20, bold=True, italic=True, color='FF0000')

ws["A4"].font = ft

wb.save('sample.xlsx')

设置单元格填充

from openpyxl.styles import PatternFill

# 红色填充
ft = PatternFill(fill_type='solid', fgColor='FF0000')

ws["A3"].fill = ft

wb.save('sample.xlsx')

设置对齐方式

from openpyxl.styles import Alignment

# 左对齐，上对齐
al = Alignment(horizontal='left', vertical='top')

ws["A3"].alignment = al

wb.save('sample.xlsx')

在选择对齐方式中：

• horizontal的参数选择范围为：'fill', 'right', 'justify', 'general', 'distributed', 'center', 'centerContinuous', 'left'
• vertical的参数选择范围为：'bottom', 'top', 'distributed', 'justify', 'center'

单元格边框

from openpyxl.styles import Border, Side

left_side = Side(border_style='thin', color='FF0000')
right_side = Side(border_style='thin', color='00FF00')
top_side = Side(border_style='thin', color='0000FF')
bottom_side = Side(border_style='thin', color='FFFF00')

ws["A3"].border = Border(left=left_side, right=right_side, top=top_side, bottom=bottom_side)

wb.save('sample.xlsx')

border_style可以为：'dashDot', 'mediumDashDot', 'slantDashDot', 'thick', 'dashed', 'hair', 'mediumDashDotDot', 'medium', 'thin', 'dotted', 'double', 'mediumDashed', 'dashDotDot'

应用到整行或整列

col = ws.column_dimensions['A']
col.font = Font(bold=True)

row = ws.row_dimensions[1]
row.font = Font(underline="single")

列宽和行高

在openpyxl中，column_dimensions和rows_dimensions都是一个字典，key为列或行的索引，value为ColumnDimension或RowDimension对象。该对象为表格中每行或每列的信息，主要包括：

• width 属性：获取或设置列的宽度。
• hidden 属性：获取或设置列的隐藏状态。
• bestFit 属性：获取或设置列的自动调整大小。
• style 属性：获取或设置列的样式。
• number_format 属性：获取或设置列的数字格式。

col = ws.column_dimensions['A']
col.width = 20.0
col.hidden = False
col.bestFit = True
col.number_format = "0.00"

row = ws.row_dimensions[1]
row.height = 40.0
row.hidden = False
row.bestFit = True
row.number_format = "0.00"

页面设置

工作表中页面设置可以通过page_setup属性进行设置，page_setup属性是一个PageSetup对象，该对象包含了页面设置的各种属性，如纸张大小、方向、页边距等。

以下是一些常用的 PageSetup 类的属性和方法：

• orientation 属性：设置页面的方向，可以是 PageSetup.ORIENTATION_PORTRAIT（纵向）或 PageSetup.ORIENTATION_LANDSCAPE（横向）。
• paperSize 属性：设置纸张大小，可以是预定义的常量，如 PageSetup.PAPERSIZE_A4。
• leftMargin、rightMargin、topMargin、bottomMargin 属性：设置工作表的左、右、上、下页边距。
• printArea 属性：设置打印区域。

示例代码如下：

# 设置页面方向为横向
ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE

# 设置纸张大小为A4
ws.page_setup.paperSize = ws.PAPERSIZE_A4

# 设置边距
ws.page_setup.leftMargin = 0.5
ws.page_setup.rightMargin = 0.5
ws.page_setup.topMargin = 0.5
ws.page_setup.bottomMargin = 0.5

# 设置打印区域
ws.page_setup.printArea = 'A1:F20'

# 保存工作簿
wb.save("sample.xlsx")

样式保存

上面的格式配置可以保存为NamedStyle对象，然后应用到单元格上：

from openpyxl.styles import NamedStyle

my_style = NamedStyle(name='my_style')
my_style.font = Font(bold=True, size=20)
my_style.alignment = Alignment(horizontal='center', vertical='center')

wb.add_named_style(my_style)        # 创建样式后，可以将该样式注册到工作簿中
# ws["A1"].style = my_style           # 将样式赋值给单元格也可以完成自动注册

# 注册完成后，就可以通过以下方式直接使用样式
ws["A1"].style = "my_style"

内建样式

openpyxl中还存在很多内建样式，用户可以直接通过样式名称使用，详情可查看openpyxl.styles.builtins。

‘Normal’：就是无样式

Number formats：

• ‘Comma’
• ‘Comma [0]’
• ‘Currency’
• ‘Currency [0]’
• ‘Percent’

Informative：

• ‘Calculation’
• ‘Total’
• ‘Note’
• ‘Warning Text’
• ‘Explanatory Text’

Text styles：

• ‘Title’
• ‘Headline 1’
• ‘Headline 2’
• ‘Headline 3’
• ‘Headline 4’
• ‘Hyperlink’
• ‘Followed Hyperlink’
• ‘Linked Cell’

Comparisons：

• ‘Input’
• ‘Output’
• ‘Check Cell’
• ‘Good’
• ‘Bad’
• ‘Neutral’

Highlights：

• ‘Accent1’
• ‘20 % - Accent1’
• ‘40 % - Accent1’
• ‘60 % - Accent1’
• ‘Accent2’
• ‘20 % - Accent2’
• ‘40 % - Accent2’
• ‘60 % - Accent2’
• ‘Accent3’
• ‘20 % - Accent3’
• ‘40 % - Accent3’
• ‘60 % - Accent3’
• ‘Accent4’
• ‘20 % - Accent4’
• ‘40 % - Accent4’
• ‘60 % - Accent4’
• ‘Accent5’
• ‘20 % - Accent5’
• ‘40 % - Accent5’
• ‘60 % - Accent5’
• ‘Accent6’
• ‘20 % - Accent6’
• ‘40 % - Accent6’
• ‘60 % - Accent6’
• ‘Pandas’

图表

图表实际使用的并不多，这里只是简单介绍一下，详细的可以参考官方文档。

from openpyxl.chart import BarChart, Series, Reference

# 创建一个柱状图,指定图表类型为col,标题为Bar Chart,横坐标为time,纵坐标为value
chart = BarChart()
chart.type = "col"
chart.title = "Bar Chart"
chart.x_axis.title = 'time'
chart.y_axis.title = 'value'
chart.legend = None

# 设置数据来源
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=6)
cats = Reference(ws, min_col=1, min_row=2, max_row=6)

# 将数据添加到图表中
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)

# 将图表添加到E1单元格中
ws.add_chart(chart, "E1")
wb.save('sample.xlsx')

上面内容只是对openpyxl的一些常见的部分进行了简单的介绍，其他更多内容，如过滤、排序、数据有效性验证等，可以参考官方文档。