python模块 openpyxl--操作文件excle

关于python处理excel文件的各种信息：http://www.python-excel.org/
本文使用openpyxl(3.0.7)说明文档:https://openpyxl.readthedocs.io/en/stable/

1、openpyxl模块介绍及安装

1、openpyxl模块介绍

openpyxl是一个Python库，用于读取/写入Excel 2010（xlsx/xlsm/xltx/xltm）文件。
简单易用，功能广泛，单元格格式/图片/表格/公式/筛选/批注/文件保护等等功能应有尽有，图表功能是其一大亮点，缺点是对 VBA 支持的不够好。
与其他库和应用程序相比，内存使用量相当高，大约是原始文件大小的50倍，例如，50MB Excel文件需要2.5GB。

2、openpyxl模块安装

pip install openpyxl

为了能够将图像（jpeg、png、bmp等）包含到openpyxl文件中，您还需要可以安装的“pillow”库：

pip install pillow

2、操作workbook（工作簿）

1、创建工作簿

无需在文件系统上创建文件即可开始使用openpyxl。只需导入Workbook类并开始工作。
一个工作簿总是由至少一个工作表组成，即创建工作簿时会创建一个工作表sheet。
可以通过使用该Workbook.active属性来获取它。（获得工作簿打开时默认的工作表，默认设置为0，即默认获得第一个工作表。）

示例：

from openpyxl import Workbook    #导入Workbook模块
wb = Workbook()                  #创建工作簿，同时会创建一个工作表“Sheet”。此时只是在内存中，没有写进磁盘中，<openpyxl.workbook.workbook.Workbook object at 0x000001E923621C40>
# print(tuple(wb))                     #结果是：(<Worksheet "Sheet">,)
ws = wb.active                   #获得第一个工作表对象，<Worksheet "Sheet">

2、保存工作簿

Workbook.save()方法：将在没有警告的情况下覆盖现有文件。在保存到磁盘之前，所有的操作都是在内存中进行的。
文件扩展名不会强制为xlsx或xlsm，但如果不使用官方扩展名，则使用其他应用程序打开它可能会遇到一些问题。

示例：

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
wb.save(r'C:\Users\root\Desktop\empty_book.xlsx')    #写在磁盘上，并命名为“empty_book.xlsx”

3、读写磁盘上的工作簿

load_workbook(filename, read_only=False, keep_vba=KEEP_VBA, data_only=False, keep_links=True)
- 打开给定的文件名并返回工作簿
  - filename：打开文件或文件类对象
  - read_only(bool)：为阅读而优化，内容无法编辑
  - keep_vba(bool)：保存vba内容(这并不意味着你可以使用它)
  - data_only(bool)：单元格中是公式默认单元格的值是公式(默认)，若data_only=True单元格的值是Excel存储的值（公式得到的值）
  - keep_links(bool)：是否应该保留到外部工作簿的链接。默认为True

示例：

from openpyxl import load_workbook
wb = load_workbook(r'C:\Users\root\Desktop\empty_book.xlsx')    #加载磁盘上的工作簿

4、只读模式、只写模式

有时需要打开或写入非常大的XLSX文件，而openpyxl中的常见例程将无法处理该负载。幸运的是，有两种模式使能够以（接近）恒定内存消耗读取和写入无限量的数据。

1、只读模式

如果您主要对转储工作簿的内容感兴趣，那么您可以使用openpyxl的只读模式并打开工作簿的多个实例并利用多个CPU。
openpyxl的只读模式几乎立即打开工作簿，使其适用于多个进程，这也显着减少了内存使用。

与普通工作簿不同，只读工作簿将使用延迟加载。
必须使用该close()方法显式关闭工作簿。
返回的单元格不是常规的openpyxl.cell.cell.Cell而是openpyxl.cell._read_only.ReadOnlyCell。

示例：

from openpyxl import load_workbook
wb = load_workbook('empty_book.xlsx', read_only=True)    #只读模式
ws = wb.active
for row in ws.rows:
    for cell in row:
        print(cell.value)
wb.close()    #必须使用close()关闭文件

2、只写模式

只写模式使用更快的openpyxl.worksheet._write_only.WriteOnlyWorksheet替代常规的openpyxl.worksheet.worksheet.Worksheet。当您希望转储大量数据时，请确保安装了lxml。

与普通工作簿不同，新创建的只写工作簿不包含任何工作表；必须使用create_sheet()方法专门创建工作表。
在只写工作簿中，只能使用append()添加行。不能使用cell()或iter_rows()在任意位置写入(或读取)单元格。
它能够导出无限数量的数据(甚至超过Excel实际能够处理的数量)，同时保持内存使用在10Mb以下。
只写工作簿只能保存一次。在此之后，每次尝试保存工作簿或将append()添加到现有工作表都会引发openpyxl.utils.exceptions. workbookalreadsaved异常。
在实际单元格数据之前出现在文件中的所有内容都必须在添加单元格之前创建，因为它必须在添加之前写入文件。例如，在添加单元格之前，应该设置freeze_panes。

示例：

from openpyxl import Workbook
wb = Workbook(write_only=True)   #只写模式
ws = wb.create_sheet()
for irow in range(100):
    ws.append(['%d' % i for i in range(200)])
wb.save('empty_book.xlsx')

如果你想让单元格带有样式或注释，那么使用openpyxl.cell.WriteOnlyCell()
- 这将创建一个只写的工作簿，只有一个工作表，并添加一行3个单元格:一个文本单元格，带有自定义字体和注释，一个浮点数，和一个空单元格

示例：

from openpyxl import Workbook
from openpyxl.cell import WriteOnlyCell
from openpyxl.comments import Comment
from openpyxl.styles import Font

wb = Workbook(write_only=True)
ws = wb.create_sheet()

cell = WriteOnlyCell(ws, value="hello world")
cell.font = Font(name='Courier', size=36)
cell.comment = Comment(text="A comment", author="Author's Name")
ws.append([cell, 3.14, None])
wb.save('empty_book.xlsx')

3、操作sheet（工作表）

1、创建工作表sheet

create_sheet(self, title=None, index=None)。title(str)：可选参数，工作表的名字；index(int)：可选参数，工作表插入的位置（从0开始）。
工作表在创建时会自动命名为“Sheet”。工作表名重复时按顺序编号（Sheet、Sheet1、Sheet2、...）。

示例：

from openpyxl import Workbook
wb = Workbook()                         #创建工作簿，同时会创建一个工作表“Sheet”

ws1 = wb.create_sheet()                 #创建一个工作表，默认工作表名“Sheet1”，默认放在当前现有的工作表的最后面
ws2 = wb.create_sheet('Mysheet1', 1)    #创建一个工作表“Mysheet1”，指定放在第一个工作表的后面
ws3 = wb.create_sheet('Mysheet2', 1)    #创建一个工作表“Mysheet2”，指定放在第一个工作表的后面
wb.save('empty_book.xlsx')

2、工作表的其他常用操作

获取工作表对象的方式
- 创建工作表时，将工作表对象赋值给一个变量。示例：ws2 = wb.create_sheet('Mysheet1')
- 将工作簿当作字典，工作表名是字典的键。示例：ws2 = wb['Mysheet1']
复制工作表的注意事项
- 仅复制单元格（包括值、样式、超链接和注释）和某些工作表属性（包括尺寸、格式和属性）。不会复制所有其他工作簿/工作表属性 - 例如图像、图表。
- 也不能在工作簿之间复制工作表。如果工作簿以只读或只写模式打开，则无法复制工作表。

示例：

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws2 = wb.create_sheet('Mysheet1')

wb.copy_worksheet(ws)       #复制工作表
wb.move_sheet('Sheet',1)    #移动工作表
wb.remove(ws2)              #删除工作表
# del wb['Mysheet1']        #删除工作表
print(wb.index(ws))         #返回工作表的索引
print(wb.sheetnames)        #返回所有工作表名称列表，可获得工作表名称wb[wb.sheetnames[1]].title
print(wb.worksheets)        #返回所有工作表对象列表，可获得工作表名称wb.worksheets[1].title

ws.title = "New Title"                     #修改工作表的名称
ws.sheet_properties.tabColor = 'FF0000'    #修改工作表的名称选项卡的背景颜色
wb.save('empty_book.xlsx')

<<<
0
['Sheet', 'Sheet Copy']
[<Worksheet "Sheet">, <Worksheet "Sheet Copy">]

3、循环浏览工作表

from openpyxl import Workbook
wb = Workbook()
ws2 = wb.create_sheet('Mysheet')
for sheet in wb:         #循环浏览工作表
    print(sheet.title)

<<<
Sheet
Mysheet

4、操作cell（单元格）

在内存中创建工作表时，它不包含任何单元格。它们是在第一次访问时创建的。

1、访问一个单元格（获取单元格对象）

ws['A4']单元格可以直接作为工作表的键进行访问，这将返回A4处的单元格，如果尚不存在，则创建一个。
Worksheet.cell(row, column, value=None)方法

示例：

from openpyxl import Workbook
wb = Workbook()
ws = wb.active

ws['A4'] = 4                             #单元格“A4”赋值为4
ws.cell(row=4, column=2, value=8)        #单元格“B4”赋值为8
print(ws['A4'].value)                    #取出单元格“A4”的值
print(ws.cell(row=4, column=2).value)    #取出单元格“B4”的值
wb.save('empty_book.xlsx')

2、访问多个单元格（获取单元格对象）

1、使用切片访问单元格范围

可以访问到范围内的所有单元格，即在内存中将创建该范围的所有单元格。

示例：

from openpyxl import Workbook
wb = Workbook()
ws = wb.active

cell_range = ws['A1':'C3']    #在内存中创建出来的这些单元格。返回一个元组，每个元素都是行中的每个单元格对象组成的元组
for row in cell_range:
    print(row)                #行中每个单元格对象组成的元组
    for cell in row:
        print(cell)           #单元格对象

<<<
(<Cell 'Sheet'.A1>, <Cell 'Sheet'.B1>, <Cell 'Sheet'.C1>)
<Cell 'Sheet'.A1>
......
(<Cell 'Sheet'.A2>, <Cell 'Sheet'.B2>, <Cell 'Sheet'.C2>)
......
(<Cell 'Sheet'.A3>, <Cell 'Sheet'.B3>, <Cell 'Sheet'.C3>)
......
<Cell 'Sheet'.C3>

2、切片的方式获得行或列的范围

注意，若是才创建的工作表，他们执行后仅仅在内存中创建了部分单元格。若是整列仅创建第一行，若是整行仅创建第一列。

示例：

colC = ws['C']
print(colC)         #(<Cell 'Sheet'.C1>,)
col_range = ws['C:D']
print(col_range)    #((<Cell 'Sheet'.C1>,), (<Cell 'Sheet'.D1>,))
row10 = ws[10]
print(row10)        #(<Cell 'Sheet'.A10>,)
row_range = ws[5:10]
print(row_range)    #((<Cell 'Sheet'.A5>,), (<Cell 'Sheet'.A6>,), (<Cell 'Sheet'.A7>,), (<Cell 'Sheet'.A8>,), (<Cell 'Sheet'.A9>,), (<Cell 'Sheet'.A10>,))

3、Worksheet.iter_rows()方法，以行获取单元格对象

示例：

from openpyxl import Workbook

wb = Workbook()
ws1 = wb.create_sheet()

for row in ws1.iter_rows(min_row=1, max_col=3, max_row=2):
    for cell in row:
        print(cell)

<<<
<Cell 'Sheet1'.A1>
<Cell 'Sheet1'.B1>
<Cell 'Sheet1'.C1>
<Cell 'Sheet1'.A2>
<Cell 'Sheet1'.B2>
<Cell 'Sheet1'.C2>

4、Worksheet.iter_cols()方法，以列获取单元格对象（只读模式下不可用）

示例：

from openpyxl import Workbook

wb = Workbook()
ws1 = wb.create_sheet()

for col in ws1.iter_cols(min_row=1, max_col=3, max_row=2):
    for cell in col:
        print(cell)

<<<
<Cell 'Sheet1'.A1>
<Cell 'Sheet1'.A2>
<Cell 'Sheet1'.B1>
<Cell 'Sheet1'.B2>
<Cell 'Sheet1'.C1>
<Cell 'Sheet1'.C2>

5、遍历文件的所有行或列

Worksheet.rows属性，遍历文件的所有行。
Worksheet.columns属性，遍历文件的所有列。（在只读模式下不可用）

示例：

from openpyxl import Workbook

wb = Workbook()
ws1 = wb.create_sheet()

ws1['C3'] = 'hello world'
print(tuple(ws1.rows))       #以行遍历文件
print(tuple(ws1.columns))    #以列遍历文件

<<<
((<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>), (<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>), (<Cell 'Sheet1'.A3>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.C3>))
((<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.A2>, <Cell 'Sheet1'.A3>), (<Cell 'Sheet1'.B1>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.B3>), (<Cell 'Sheet1'.C1>, <Cell 'Sheet1'.C2>, <Cell 'Sheet1'.C3>))

3、仅返回单元格的值

如果您只想要工作表中的值，您可以使用该Worksheet.values属性。这将遍历工作表中的所有行，但仅返回单元格值。

示例：

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

Worksheet.iter_rows()和Worksheet.iter_cols()可以采取的values_only参数，只返回单元格的值

示例：

from openpyxl import Workbook
wb = Workbook()
ws = wb.active

for row in ws.iter_rows(min_row=1, max_col=3, max_row=2, values_only=True):
    print('row--', row)
for col in ws.iter_cols(min_row=1, max_col=3, max_row=2, values_only=True):
    print('col--', col)

<<<
row-- (None, None, None)
row-- (None, None, None)
col-- (None, None)
col-- (None, None)
col-- (None, None)

4、单元格的常用操作

1、获取一个单元格的方法

cell(row, column, value=None)
- 根据给定的坐标返回一个单元格对象。
- 第一次访问时，调用cell在内存中创建单元格。
- 参数
  - row(int)：单元格的行索引
  - column(int)：单元格的列索引
  - value(numeric或time或string或bool或none)：单元格的值

示例：

c3 = ws.cell(3, 3)          #获取单元格对象    #<Cell 'Sheet1'.C3>
c3 = ws.cell(3, 3, 'c3')    #获取单元格对象，并将单元格值改为‘c3’
c3.value = 'c3'             #修改单元格的值
c3val = c3.value            #获取单元格的值

2、获取多个单元格的对象或值的方法

iter_cols( min_col=None , max_col=None , min_row=None , max_row=None , values_only=False ) #（只读模式下不可用）
- 按列从工作表中生成单元格。使用行和列的索引指定迭代范围。
- 如果未指定索引，则范围从A1开始。
- 如果工作表中没有单元格，将返回一个空元组。
- 参数
  - min_col(int)：最小列索引（基于1的索引）；
  - min_row(int)：最小行索引（基于1的索引）；
  - max_col(int)：最大列索引（基于1的索引）；
  - max_row(int)：最大行索引（基于1的索引）；
  - values_only(bool)：是否只应返回单元格值，默认是False返回单元格对象，若为True则返回单元格值。
iter_rows( min_row=None , max_row=None , min_col=None , max_col=None , values_only=False )
- 按行从工作表中生成单元格。使用行和列的索引指定迭代范围。

示例：

icol = ws.iter_cols(min_row=2, max_row=3, min_col=2, max_col=3)                          #返回单元格对象生成器    #<generator object Worksheet._cells_by_col at 0x000001A1CF0DDCF0>
print(tuple(icol))        #结果是：((<Cell 'Sheet1'.B2>, <Cell 'Sheet1'.B3>), (<Cell 'Sheet1'.C2>, <Cell 'Sheet1'.C3>))

icol_val = ws.iter_cols(min_row=2, max_row=3, min_col=2, max_col=3, values_only=True)    #返回单元格值的生成器    #<generator object Worksheet._cells_by_col at 0x000001A1CF0DD0B0>
print(tuple(icol_val))    #结果是：(('B2', 'B3'), ('C2', 'C3'))

3、获取表中的所有单元格对象

rows
- 以行遍历文件，返回所有单元格
columns #（在只读模式下不可用）
- 以列遍历文件，返回所有单元格

示例：

rs = ws.rows         #返回单元格对象生成器    #<generator object Worksheet._cells_by_row at 0x0000025DF5E1F040>
print(tuple(rs))     #结果是：((<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>, <Cell 'Sheet1'.D1>, <Cell 'Sheet1'.E1>), (<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>, <Cell 'Sheet1'.D2>, <Cell 'Sheet1'.E2>), (<Cell 'Sheet1'.A3>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.C3>, <Cell 'Sheet1'.D3>, <Cell 'Sheet1'.E3>), (<Cell 'Sheet1'.A4>, <Cell 'Sheet1'.B4>, <Cell 'Sheet1'.C4>, <Cell 'Sheet1'.D4>, <Cell 'Sheet1'.E4>), (<Cell 'Sheet1'.A5>, <Cell 'Sheet1'.B5>, <Cell 'Sheet1'.C5>, <Cell 'Sheet1'.D5>, <Cell 'Sheet1'.E5>))

cols = ws.columns    #返回单元格对象生成器    #<generator object Worksheet._cells_by_col at 0x0000025DF5E1FC80>
print(tuple(cols))   #结果是：((<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.A2>, <Cell 'Sheet1'.A3>, <Cell 'Sheet1'.A4>, <Cell 'Sheet1'.A5>), (<Cell 'Sheet1'.B1>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.B4>, <Cell 'Sheet1'.B5>), (<Cell 'Sheet1'.C1>, <Cell 'Sheet1'.C2>, <Cell 'Sheet1'.C3>, <Cell 'Sheet1'.C4>, <Cell 'Sheet1'.C5>), (<Cell 'Sheet1'.D1>, <Cell 'Sheet1'.D2>, <Cell 'Sheet1'.D3>, <Cell 'Sheet1'.D4>, <Cell 'Sheet1'.D5>), (<Cell 'Sheet1'.E1>, <Cell 'Sheet1'.E2>, <Cell 'Sheet1'.E3>, <Cell 'Sheet1'.E4>, <Cell 'Sheet1'.E5>))

4、获取表中的所有单元格值

values
- 按行生成工作表中的所有单元格值

示例：

vs = ws.values      #返回单元格对象生成器    #<generator object Worksheet.values at 0x000002543827DCF0>
print(tuple(vs))    #结果是：(('A1', 'B1', 'C1', 'D1', 'E1'), ('A2', 'B2', 'C2', 'D2', 'E2'), ('A3', 'B3', 'C3', 'D3', 'E3'), ('A4', 'B4', 'C4', 'D4', 'E4'), ('A5', 'B5', 'C5', 'D5', 'E5'))

5、在表的最后添加一行

append(iterable)
- 在当前工作表的底部附加一组值（在最后添加一行）。
  - 如果是列表：从第一列开始按顺序添加所有值
  - 如果是字典：将值分配给键（数字或字母）指示的列

示例：

ws.append(['This is A1', 'This is B1', 'This is C1'])    #列表
ws.append({'A': 'This is A1', 'C': 'This is C1'})        #字典
ws.append({1: 'This is A1', 3: 'This is C1'})            #字典

6、删除、插入列或行

delete_cols(idx, amount=1)
- 删除一列或多列。从第idx列开始删除amount列（包含idx）
delete_rows(idx, amount=1)
- 删除一行或多行，从第idx行开始删除amount行（包含idx）
insert_cols(idx, amount=1)
- 插入一列或多列，在第idx列前插入amount列
insert_rows(idx, amount=1)
- 插入一行或多行，在第idx行前插入amount行

示例：

ws.delete_cols(2, 2)    # 删除一列或多列，从第2列开始删除2列（包含第2列）
ws.delete_rows(2, 2)    # 删除一行或多行，从第2行开始删除2行
ws.insert_cols(2, 2)    # 插入一列或多列，在第2列前插入2列
ws.insert_rows(2, 2)    # 插入一行或多行，在第2行前插入2行

7、移动单元格

move_range( cell_range , rows=0 , cols=0 , translate=False )
- 按行数或列数移动单元格范围。
- 现有单元格将被覆盖。公式和参考资料将不会更新。
- 如果rows > 0，则向下移动；如果rows < 0，则向上移动。
- 如果 cols > 0，则向右移动，如果 cols < 0，则向左移动。
- 若translate=False移动时单元格中的公式被重新解析到新的单元格。若translate=True只移动单元格中公式的值。

示例：

ws.move_range("A1:C3", rows=3, cols=3)                    #将“A1:C3”范围的单元格向下移动3行，向右移动3列
ws.move_range("A1:C3", rows=3, cols=3, translate=True)    #将范围内公式中的相对引用移动3行和3列。

8、合并单元格

当您合并单元格时，除了左上角的所有单元格都将从工作表中删除。
merge_cells(range_string=None, start_row=None, start_column=None, end_row=None, end_column=None)
- 在单元格范围上设置合并。Range 是一个单元格区域（例如 A1:E1）
unmerge_cells(range_string=None, start_row=None, start_column=None, end_row=None, end_column=None)
- 取消单元格的合并。Range 是一个单元格区域（例如 A1:E1）

示例：

ws.merge_cells('A2:B4')      #合并单元格
ws.unmerge_cells('A2:B4')    #取消单元格的合并    #注意，范围要一致
#两种方式的结果一样
ws.merge_cells(start_row=2, start_column=1, end_row=4, end_column=2)
ws.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=2)

9、返回行、列的标识

calculate_dimension()
- 返回包含数据的所有单元格的最小边界范围（例如“A1:M24”）
dimensions
- 返回包含数据的所有单元格的最小边界范围（例如“A1:M24”）
max_column
- 包含数据的最大列索引（从 1 开始）
max_row
- 包含数据的最大行索引（从 1 开始）
min_column
- 包含数据的最小列索引（从 1 开始）
min_row
- 包含数据的最小行索引（从 1 开始）

示例：

print(ws.calculate_dimension())    #结果是：A1:H5
print(ws.dimensions)               #结果是：A1:H5
print(ws.max_column)               #结果是：8
print(ws.max_row)                  #结果是：5
print(ws.min_column)               #结果是：1
print(ws.min_row)                  #结果是：1

10、插入图片

aadd_image(img, anchor=None)
- 向工作表添加图像。可选参数anchor为左上角锚点提供一个单元格

示例：

from openpyxl import Workbook
from openpyxl.drawing.image import Image
wb = Workbook()
ws = wb.active
ws['A1'] = 'You should see three logos below'
img = Image('logo.png')    #创建图片对象
ws.add_image(img, 'B2')    #将图片插入工作表
wb.save('empty_book.xlsx')

11、注释

Openpyxl目前只支持读取和写入注释。注释维度（注释框的大小）在读取时丢失，但可以写入。如果使用read_only=True，则当前不支持（读写）注释。

1、向一个单元格添加注释

注释有一个文本属性和一个作者属性，这两个属性都必须设置
- def __init__(self, text, author, height=79, width=144)
如果将相同的注释分配给多个单元格，那么openpyxl将自动创建副本
可以指定注释维度。注释维度以像素为单位。

示例：

from openpyxl import load_workbook
from openpyxl.comments import Comment
wb = load_workbook('empty_book.xlsx')
ws = wb.active

comment = Comment('This is the comment text ', 'Comment Author')    #创建一个注释对象
comment.width = 300
comment.height = 50
ws["A1"].comment = comment                                          #向单元格“A1”添加注释
ws["B1"].comment = comment

print(ws["A1"].comment is comment)                                  #结果是：True
print(ws["B2"].comment is comment)                                  #结果是：False    #两个单元格的注释对象不是同一个
print(ws["A1"].comment.text)                                        #结果是：This is the comment text    #打印单元格“A1”的注释的text属性
print(ws["A1"].comment.author)                                      #结果是：Comment Author
wb.save('empty_book.xlsx')

如果需要，openpyxl.utils.units包含帮助函数，用于从其他度量(如mm或点)转换为像素

示例：

from openpyxl.utils import units
comment.width = units.points_to_pixels(100)
comment.height = units.points_to_pixels(100)

2、加载和保存注释

加载工作簿时注释将自动存储在各自单元格的注释属性中。但注释的格式化信息，如字体大小、粗体和斜体，以及注释容器框的原始尺寸和位置都将丢失。
保存工作簿时保留在工作簿中的注释将自动保存到工作簿文件中。

5、使用样式

样式用于更改显示在屏幕上的数据的外观。它们还用于确定数字的格式。
有两种样式：单元格样式和命名样式，也称为样式模板。
- 单元格样式是在对象之间共享的，一旦它们被分配，就不能更改。这就避免了一些不必要的副作用，比如当只有一个单元格发生变化时，就会改变很多单元格的样式。
- 与单元格样式相反，命名样式是可变的。当您想要同时对许多不同的单元格应用格式时，它们是有意义的。注意：将命名样式分配给单元格后，对样式的其他更改将不会影响该单元格。
样式可以应用于以下几个方面：
- font 设置字体大小、颜色、下划线等。
- 填充以设置图案或颜色渐变
- 边框在单元格上设置边框
- 单元格对齐
- 保护

1、单元格样式之字体

#字体参数的默认值
from openpyxl.styles import Font
font = Font(name='Calibri',      #字体样式，例如：黑体、宋体
            size=11,             #字体大小
            bold=False,          #是否加粗
            italic=False,        #是否倾斜
            underline='none',    #下划线。可选的参数有：none无下划线，single单下划线，double双下划线，singleAccounting满格单下划线，doubleAccounting满格双下划线
            strike=False,        #是否加中横线
            vertAlign=None,      #纵向对齐方式，superscript在上方, subscript在下方, baseline基线
            color='FF000000')    #字体颜色

示例：

from openpyxl import load_workbook
from openpyxl.styles import Font

wb = load_workbook('empty_book.xlsx')
ws = wb.active
font1 = Font(name='宋体', size=11, bold=True, color='FF000000')      #创建字体样式对象，宋体、11号、加粗、黑色
font2 = Font(name='宋体', size=16, italic=True, color='00FF0000')    #创建字体样式对象，宋体、16号、斜体、红色
a1 = ws['A1']
a2 = ws['A2']
a1.value = 'a1'
a2.value = 'a2'
a1.font = font1    #将字体样式对象赋给单元格的font属性
a2.font = font2
wb.save('empty_book.xlsx')

2、单元格样式之（区域）填充

#单元格填充参数的默认值
from openpyxl.styles import PatternFill
fill = PatternFill(fill_type=None,
                   start_color='FFFFFFFF',
                   end_color='FF000000')

fill = PatternFill(patternType=None, fgColor=Color(), bgColor=Color(), fill_type=None, start_color=None, end_color=None)
- patternType、fill_type功能相同，在样式中的区域填充模式。
  - 注意:如果不指定fill_type，其他属性将无效!
  - 值必须是'gray0625', 'darkGray', 'darkDown', 'lightVertical', 'lightUp', 'mediumGray', 'darkGrid', 'darkUp', 'solid', 'darkVertical', 'gray125', 'lightGray', 'lightGrid', 'lightHorizontal', 'darkTrellis', 'lightDown', 'darkHorizontal', 'lightTrellis'之一
- fgColor、start_color作用相同，前景色。示例：fgColor='0000FF00'
- bgColor、end_color作用相同，背景色。示例：bgColor='0000FF00'

填充模式和前景色、背景色
- 前景色时，颜色是图案的颜色。背景色时，颜色是背景的颜色，图案颜色是黑色。

示例：获得上面的图片内容

from openpyxl import load_workbook
from openpyxl.styles import PatternFill
wb = load_workbook('empty_book.xlsx')
ws = wb.active

filltype = ['gray0625', 'darkGray', 'darkDown', 'lightVertical', 'lightUp', 'mediumGray', 'darkGrid', 'darkUp', 'solid',
            'darkVertical', 'gray125', 'lightGray', 'lightGrid', 'lightHorizontal', 'darkTrellis', 'lightDown',
            'darkHorizontal', 'lightTrellis']                                #单元格的18种填充模式
i = 0
for rowi in range(1, 5):
    for coli in range(1, 6):
        try:
            a1 = ws.cell(row=rowi, column=coli, value=filltype[i])           #单元格对象
            fill = PatternFill(fill_type=filltype[i], fgColor='0000FF00')    #创建单元格填充模式对象
            a1.fill = fill                                                   #将单元格填充模式对象赋给单元格fill属性
            i += 1
        except:
            break
wb.save('empty_book.xlsx')

3、单元格样式之边框

#单元格边框参数的默认值
from openpyxl.styles import Border, Side
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')
               )

1、边框选项

设置单元格边框的样子
Side(style=None, color=None, border_style=None)
- 注意:如果不指定border_style，其他属性将无效!
- border_style、style功能一样，值必须是'hair', 'dashDotDot', 'dashDot', 'thin', 'mediumDashed', 'slantDashDot', 'dotted', 'dashed', 'medium', 'thick', 'mediumDashDot', 'double', 'mediumDashDotDot'之一

2、边框定位

边框在单元格的位置（上、下、左、右等）
Border(left=None, right=None, top=None, bottom=None, diagonal=None, diagonal_direction=None, vertical=None, horizontal=None, diagonalUp=False, diagonalDown=False, outline=True, start=None, end=None)

示例1：

from openpyxl import load_workbook
from openpyxl.styles import Border, Side
wb = load_workbook('empty_book.xlsx')
ws = wb.active

side_thin = Side(border_style='thin', color="00FF0000")            #创建一个边框对象（红色单实线边框）
side_double = Side(border_style='double', color="00000000")        #创建一个边框对象（黑色双实线边框）
ws['B2'].border = Border(left=side_thin, right=side_thin, top=side_double, bottom=side_double, diagonalUp=True,
                         diagonalDown=True)                        #左右上下（单元格左右红色单实线边框，上下黑色双实线边框）
ws['C3'].border = Border(diagonal=side_thin, diagonalDown=True)    #斜杠
ws['D4'].border = Border(diagonal=side_double, diagonalUp=True)    #反斜杠
ws['E5'].border = Border(start=side_thin, end=side_double)         #左右
wb.save('empty_book.xlsx')

示例2：获得上面的图片内容

from openpyxl import load_workbook
from openpyxl.styles import Border, Side
wb = load_workbook('empty_book.xlsx')
ws = wb.active

borderstyle = ['hair', 'dashDotDot', 'dashDot', 'thin', 'mediumDashed', 'slantDashDot', 'dotted', 'dashed', 'medium',
               'thick', 'mediumDashDot', 'double', 'mediumDashDotDot']
i = 0
for rowi in range(1, 5):
    for coli in range(1, 5):
        try:
            cell = ws.cell(row=rowi, column=coli, value=borderstyle[i])    #单元格对象
            side = Side(border_style=borderstyle[i], color="00FF0000")     #边框对象
            cell.border = Border(bottom=side)                              #将边框放在单元格下面
            i += 1
        except:
            break
wb.save('empty_book.xlsx')

4、单元格样式之对齐

#单元格对齐参数的默认值
from openpyxl.styles import Alignment
alignment=Alignment(horizontal='general',
                    vertical='bottom',
                    text_rotation=0,
                    wrap_text=False,
                    shrink_to_fit=False,
                    indent=0)

Alignment(horizontal=None, vertical=None, textRotation=0, wrapText=None, shrinkToFit=None, indent=0, relativeIndent=0, justifyLastLine=None, readingOrder=0, text_rotation=None, wrap_text=None, shrink_to_fit=None, mergeCell=None)
- horizontal(水平)的值必须是general(常规),center(居中),left(居左),right(居右),distributed(分布整个单元格),fill(充满单元格，内容重复),justify,centerContinuous之一

- vertical(垂直)的值必须是top,center,bottom,justify,distributed。

- textRotation(旋转文本)的范围是[0-180]
- wrapText(文本换行)，若为True则换行，若为False则不换行。（其他的参数可能会影响它）
- shrinkToFit(压缩至适当尺寸)，将文本缩小至单元格可以显示全部
- indent=0，单元格的左边将留出空白

示例：获得下面的图片内容

from openpyxl import load_workbook
from openpyxl.styles import Alignment
wb = load_workbook('empty_book.xlsx')
ws = wb.active

ws.cell(1, 1, value='A').alignment = Alignment(horizontal='center')
ws.cell(1, 2, value='B').alignment = Alignment(vertical='top')
ws.cell(1, 3, value='C').alignment = Alignment(textRotation=30)
ws.cell(1, 4, value='DDDDDDDDDDDDDDDDDDDDD').alignment = Alignment(shrinkToFit=True)
wb.save('empty_book.xlsx')

5、渐变填充区域

GradientFill(type="linear", degree=0, left=0, right=0, top=0, bottom=0, stop=())
两种渐变填充类型：
- type='linear'渐变是stop指定的一组颜色，横跨一个区域的长度。可以提供一个颜色列表，它们之间的距离相等。
  - 默认情况下渐变是从左到右的，但是这个方向可以通过degree属性进行修改。
- type='path'渐变从区域的每个边缘应用一个线性渐变。
  - 属性top, right, bottom, left指定从各自边界填充的范围。因此top="0.2"将填充单元格顶部的20%。

示例：

from openpyxl import load_workbook
from openpyxl.styles import GradientFill
wb = load_workbook('empty_book.xlsx')
ws = wb.active

filla = GradientFill(type="linear", degree=0, stop=("000000", "00FF00", "FF0000"))
fillb = GradientFill(type="path", left=0.2, right=0.2, top=0.4, bottom=0.2, stop=("000000", "00FF00", "FF0000"))
ws['A1'].fill = filla
ws['B1'].fill = fillb
wb.save('empty_book.xlsx')

6、应用单元格样式

1、样式应用于单元格

from openpyxl.workbook import Workbook
from openpyxl.styles import Font, Fill
wb = Workbook()
ws = wb.active
c = ws['A1']
c.font = Font(size=12)

2、样式应用于列和行

请注意，这仅适用于文件关闭后（在Excel中）创建的单元格。如果要将样式应用于整个行和列，则必须自己将样式应用于每个单元格

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

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

3、样式应用于合并单元格

合并单元格的行为与其他单元格对象类似。它的值和格式在其左上角的单元格中定义。要更改整个合并单元格的边框，请更改其左上角单元格的边框。格式化是为了写作而生成的。
问题：要执行两次才将边框作用于整个合并单元格上。

示例：

from openpyxl import load_workbook
from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment
wb = load_workbook('empty_book.xlsx')
ws = wb.active
cells = ws.merge_cells('B2:F4')
cell = ws['B2']

thin = Side(border_style="thin", color="000000")
double = Side(border_style="double", color="ff0000")

cell.value = "My Cell"
cell.font = Font(b=True, color="FF0000")
cell.border = Border(top=double, left=thin, right=thin, bottom=double)
cell.fill = PatternFill("solid", fgColor="FFFFFF")
# cell.fill = GradientFill(stop=("00FF00", "000000"))
cell.alignment = Alignment(horizontal="center", vertical="center")
wb.save('empty_book.xlsx')

7、创建命名样式

在工作簿中注册了命名样式后，只需通过名称就可以引用它。

#创建命名样式
from openpyxl.styles import NamedStyle, Font, Border, Side
highlight = NamedStyle(name="highlight")                           #创建命名样式对象
highlight.font = Font(bold=True, size=20)                          #将字体样式添加到命名样式中
bd = Side(style='thick', color="000000")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)    #将边框样式添加到命名样式中
#将其注册到工作簿中
wb.add_named_style(highlight)
#注册后，仅使用名称分配样式
ws['D5'].style = 'highlight'

#命名样式也可以在第一次分配给单元格时自动注册
ws['A1'].style = highlight

8、使用内置样式

该规范包括一些也可以使用的内置样式。不幸的是，这些样式的名称是以本地化的形式存储的。openpyxl只会识别英文名称，并且只能和这里写的完全一样。（https://openpyxl.readthedocs.io/en/stable/styles.html#using-builtin-styles）

示例：

from openpyxl import load_workbook
wb = load_workbook('empty_book.xlsx')
ws = wb.active
ws['A1'].style = 'Percent'
ws['B2'].style = 'Title'
ws['C3'].style = 'Headline 1'
wb.save('empty_book.xlsx')

6、条件格式

Excel支持三种不同类型的条件格式:内置、标准和自定义。
内置样式将特定的规则与预定义的样式结合起来。
标准条件格式将特定的规则与自定义格式结合起来。
此外，还可以定义使用差异样式应用自定义格式的自定义公式。

内置条件格式有三种:
- ColorScale（色阶）
- IconSet（图标集）
- DataBar（数据条）
  - DataBarRule(start_type=None, start_value=None, end_type=None, end_value=None, color=None, showValue=None, minLength=None, maxLength=None)
内置格式包含格式化设置序列，将类型与整数组合起来进行比较。可能的类型是:'num', 'percent', 'max', 'min', 'formula', 'percentile'

1、色阶

ColorScaleRule(start_type=None, start_value=None, start_color=None, mid_type=None, mid_value=None, mid_color=None, end_type=None, end_value=None, end_color=None)
- XXX_type可能是:'num', 'percent', 'max', 'min', 'formula', 'percentile'

示例：

from openpyxl import load_workbook
from openpyxl.styles import PatternFill
from openpyxl.formatting.rule import ColorScaleRule
wb = load_workbook('empty_book.xlsx')
ws = wb.active

#使用2种色阶
ws.conditional_formatting.add('A1:A100',
                              ColorScaleRule(start_type='max', start_color='FF0000',
                                             end_type='min', end_color='0000FF')
                              )

#使用3种色阶
ws.conditional_formatting.add('B1:B100',
                              ColorScaleRule(start_type='percentile', start_value=10, start_color='FF0000',
                                             mid_type='percentile', mid_value=60, mid_color='0000FF',
                                             end_type='percentile', end_value=90, end_color='FFFF00')
                              )

for coli in range(1,3):
    for rowi in range(1,100):
        ws.cell(row=rowi,column=coli,value=rowi)
wb.save('empty_book.xlsx')

2、图标集

方便创建图标集规则功能
IconSetRule(icon_style=None, type=None, values=None, showValue=None, percent=None, reverse=None)
- icon_style的值是：'3Flags', '3Signs', '3Arrows', '3TrafficLights2', '3TrafficLights1', '3ArrowsGray', '3Symbols', '3Symbols2', '4TrafficLights', '4Rating', '4Arrows', '4ArrowsGray', '4RedToBlack','5Arrows', '5Rating', '5ArrowsGray', '5Quarters'
- type可能是:'num', 'percent', 'max', 'min', 'formula', 'percentile'

示例1：

from openpyxl import Workbook
from openpyxl.formatting.rule import IconSetRule
wb = Workbook()
ws = wb.active

ws.conditional_formatting.add('A1:A100',
                              IconSetRule(icon_style='5Rating', type='percent', values=[0, 20, 40, 60, 80], showValue=None, percent=None, reverse=None)
                              )
wb.save('empty_book.xlsx')

示例2：获得上面的图片内容

from openpyxl import Workbook
from openpyxl.formatting.rule import IconSetRule
from openpyxl.utils import get_column_letter
wb = Workbook()
ws = wb.active

image = ['3Flags', '3Signs', '3Arrows', '3TrafficLights2', '3TrafficLights1', '3ArrowsGray', '3Symbols', '3Symbols2',
         '4TrafficLights', '4Rating', '4Arrows', '4ArrowsGray', '4RedToBlack', '5Arrows', '5Rating', '5ArrowsGray',
         '5Quarters']
i = 0
for coli in range(1, 18):
    col = get_column_letter(coli)            #将列索引由数字转换为字母，例如1-->A
    rangestring = '{0}2:{0}6'.format(col)    #获得单元格范围，例如A1:A6
    ws.conditional_formatting.add(rangestring,
                                  IconSetRule(image[i], 'formula', [2, 3, 4, 5, 6], showValue=None, percent=None, reverse=None)
                                  )
    for rowi in range(1, 7):
        if rowi == 1:                        #每列的第一行打印icon_style的值
            ws.cell(row=rowi, column=coli, value=image[i])
        else:
            ws.cell(row=rowi, column=coli, value=rowi)
    i += 1
wb.save('empty_book.xlsx')

3、数据条

DataBarRule(start_type=None, start_value=None, end_type=None, end_value=None, color=None, showValue=None, minLength=None, maxLength=None)
- XXX_type可能是:'num', 'percent', 'max', 'min', 'formula', 'percentile'

示例：

from openpyxl import Workbook
from openpyxl.formatting.rule import DataBarRule
wb = Workbook()
ws = wb.active

ws.conditional_formatting.add('A1:D100',
                              DataBarRule(start_type='percentile', start_value=60, end_type='percentile',
                                          end_value='90', color="FF638EC6", showValue="None", minLength=None,
                                          maxLength=None)
                              )
wb.save('empty_book.xlsx')

4、基于单元格比较

添加基于单元格比较的条件格式
CellIsRule(operator=None, formula=None, stopIfTrue=None, font=None, border=None, fill=None) #字体、边框、填充
- operator的值是字典的值：{">": "greaterThan", ">=": "greaterThanOrEqual", "<": "lessThan", "<=": "lessThanOrEqual", "=": "equal", "==": "equal", "!=": "notEqual"}
- formula的值也可以是个序列。例如：formula = [44]

示例：

from openpyxl import load_workbook
from openpyxl.styles import PatternFill
from openpyxl.formatting.rule import CellIsRule
wb = load_workbook('empty_book.xlsx')
ws = wb.active

redFill = PatternFill(start_color='EE1111', end_color='EE1111', fill_type='solid')
#每列大于第一个单元格的标红
ws.conditional_formatting.add('A2:B100',
                              CellIsRule(operator='greaterThan', formula=['A$1'], stopIfTrue=True, fill=redFill))
#在[1,5]之内的标红
ws.conditional_formatting.add('C1:F100',
                              CellIsRule(operator='between', formula=['1', '5'], stopIfTrue=True, fill=redFill))
ws['A1'] = 10
ws['B1'] = 10
for coli in range(1, 7):
    for rowi in range(1, 101):
        ws.cell(row=rowi, column=coli, value=rowi)
wb.save('empty_book.xlsx')

5、使用公式格式化

FormulaRule(formula=None, stopIfTrue=None, font=None, border=None, fill=None) #字体、边框、填充

示例：

from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font, Border
from openpyxl.formatting.rule import FormulaRule
wb = load_workbook('empty_book.xlsx')
ws = wb.active

redFill = PatternFill(start_color='EE1111', end_color='EE1111', fill_type='solid')
#若单元格是空，颜色为红
ws.conditional_formatting.add('A1:B100',
                              FormulaRule(formula=['ISBLANK(A1)'], stopIfTrue=True, fill=redFill))
myFont = Font()
myBorder = Border()
#若单元格是3，颜色为红（若formula=['A1=0']时，比较特殊，注意为空的情况）
ws.conditional_formatting.add('A1:C100',
            FormulaRule(formula=['A1=3'], font=myFont, border=myBorder, fill=redFill))
for coli in range(1, 2):
    for rowi in range(1, 101):
        ws.cell(row=rowi, column=coli, value=rowi)
wb.save('empty_book.xlsx')

图表

https://openpyxl.readthedocs.io/en/stable/charts/introduction.html

posted @ 2021-05-30 12:28 麦恒阅读(1982) 评论(0) 编辑收藏举报

刷新页面返回顶部

麦恒

python模块 openpyxl--操作文件excle

1、openpyxl模块介绍及安装

1、openpyxl模块介绍

2、openpyxl模块安装

2、操作workbook（工作簿）

1、创建工作簿

2、保存工作簿

3、读写磁盘上的工作簿

4、只读模式、只写模式

1、只读模式

2、只写模式

3、操作sheet（工作表）

1、创建工作表sheet

2、工作表的其他常用操作

3、循环浏览工作表

4、操作cell（单元格）

1、访问一个单元格（获取单元格对象）

2、访问多个单元格（获取单元格对象）

1、使用切片访问单元格范围

2、切片的方式获得行或列的范围

3、Worksheet.iter_rows()方法，以行获取单元格对象

4、Worksheet.iter_cols()方法，以列获取单元格对象（只读模式下不可用）

5、遍历文件的所有行或列

3、仅返回单元格的值

4、单元格的常用操作

1、获取一个单元格的方法

2、获取多个单元格的对象或值的方法

3、获取表中的所有单元格对象

4、获取表中的所有单元格值

5、在表的最后添加一行

6、删除、插入列或行

7、移动单元格

8、合并单元格

9、返回行、列的标识

10、插入图片

11、注释

5、使用样式

1、单元格样式之字体

2、单元格样式之（区域）填充

3、单元格样式之边框

1、边框选项

2、边框定位

4、单元格样式之对齐

5、渐变填充区域

6、应用单元格样式

1、样式应用于单元格

2、 样式应用于列和行

3、样式应用于合并单元格

7、创建命名样式

8、使用内置样式

6、条件格式

1、色阶

2、图标集

3、数据条

4、基于单元格比较

5、使用公式格式化

图表

公告

2、样式应用于列和行