用openpyxl读写Excel文件

概念介绍

打开一个Excel文件的时候，首先我们会看到底部有“Sheet1”或“工作簿1”的文字，可见一个Excel文件是由一个或多个工作簿组成的。

每个工作簿的工作区，横向坐标是以字母为编号的，从A到Z;纵向是以数字为编号的，从1开始，一直往增大方向编号。由数字和字母为横纵坐标构成的每个小框叫做单元格，这是Excel的基本单位。字母和数字确定后，对应的单元格就唯一确定了；而单元格已知后，它对应的字母和数字也就确定了。

因此我们可以这样总结：

一个Excel文件由一或多个Sheet组成，而一个Sheet由字母和数字唯一表示的单元格们组成，这是一个三级的结构。下图表示一个名字为data.xlsx的Excel文件的3级层级结构。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
data.xlsx
├── Sheet1
│   ├── A1
│   ├── A2
│   ├── B1
│   └── B2
├── Sheet2
│   ├── A1
│   ├── A2
│   ├── B1
│   └── B2
└── Sheet3
    ├── A1
    ├── A2
    ├── B1
    └── B2

明白了这个结构，openpyxl的设计理念就很好理解了。

在opnepyxl里面，一个Excel文件对应着一个Workbook对象， 一个Sheet对应着一个Worksheet对象，而一个单元格对应着一个Cell对象，下面是一个最简单的例子，执行示例之前请使用pip install --user openpyxl安装openpyxl包。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# load_workbook用于从一个xlsx文件读入数据
from openpyxl import load_workbook
wb = load_workbook(r'D:\data.xlsx') # wb是data.xlsx对应的Workbook对象

print(type(wb))  # <class 'openpyxl.workbook.workbook.Workbook'>

ws1 = wb['Sheet1']  # ws1是Sheet1对应的WorkSheet对象

print(type(ws1)) # <class 'openpyxl.worksheet.worksheet.Worksheet'>

cell1 = ws1['A1']  # cell1是Sheet1中第一个单元格对应的Cell对象

print(type(cell1)) # <class 'openpyxl.cell.cell.Cell'>

print(cell1.value) # 使用.value参数来获取Cell对象对应的值

cell1.value = 666  # 使用.value参数来对单元格赋值

print(cell1.value) # 666

使用起来是不是很简单？

工作簿

无需在文件系统上创建文件即可开始使用openpyxl。只需导入Workbook课程并开始工作：

1
2
3
4
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> type(wb)
<class 'openpyxl.workbook.workbook.Workbook'>
  • 一个工作簿(Workbook)在创建的同时至少也新建了一张工作表(worksheet)，您可以使用Workbook.active属性来获取它;
  • 可以通过openpyxl.Workbook.active()调用得到正在运行的工作表
    注意：
  • 该函数调用工作表的索引(self._sheets.index(value)),默认是0，除非你修改了这个值，否则你使用该函数一直是在对第一张工作表进行操作；
  • 绑定active事件，事件触发再执行,不是直接调用active()
1
2
ws = wb.active
print(ws)   # <Worksheet "Sheet">

创建新的工作簿

您可以使用以下Workbook.create_sheet()方法创建新的工作表：，默认标题按照序列依次命名，默认插在末尾，0既是第一位

1
2
3
4
5
6
7
wb.create_sheet()
# or
wb.create_sheet('111')
# or
wb.create_sheet('111',0)
# or
ws3 = wb.create_sheet("111", -1)

查看已存在的工作簿

1
2
3
4
5
6
print(wb.sheetnames)    # ['111', 'new1']
# or
print(wb.get_sheet_names())     # ['111', 'new1']
#or
for sheet in wb:
    print(sheet.title)

修改工作簿的名称，颜色

通过Worksheet.title属性更改此名称

1
2
ws.title="new1"
print(ws)   # <Worksheet "new1">

默认情况下，包含该标题的选项卡的背景颜色为白色。您可以更改此属性RRGGBB，为Worksheet.sheet_properties.tabColor属性提供颜色代码 ：

1
ws.sheet_properties.tabColor = "1072BA"

给工作表命名后，就可以将其作为工作簿的键：

一旦你获取工作表的名字，你可以通过workbook的key或者openpyxl.workbook.Workbook.get_sheet_by_name()方法得到该工作表

1
2
3
4
ws = wb["new1"]  # 如果输入的不存在则报错找不到
# or
ws = wb.get_sheet_by_name("new1")
print(ws)	# <Worksheet "new1">

副本

您可以在单个工作簿中创建工作表的副本：

Workbook.copy_worksheet() 方法：

1
2
source = wb.active # 获取当前单元格
target = wb.copy_worksheet(source)  # 创建副本

仅复制单元格（包括值，样式，超链接和注释）和某些工作表属性（包括尺寸，格式和属性）。不复制所有其他工作簿/工作表属性-例如图像，图表。

您也不能在工作簿之间复制工作表。如果工作簿以只读或仅写 模式打开，则不能复制工作表。

保存工作簿

1
wb.save(r'D:\date.xlsx')

数据

访问一个单元格

单元格可以直接作为工作表的键进行访问：

将返回在A1处的单元格，如果不存在将在A1新建一个。

1
c=ws['A1']

可以直接分配值：

1
ws["A1"]=a

还有Worksheet.cell()方法，这样可以使用行和列表示法访问单元格：

1
2
3
4
5
6
# 通过cell赋值
ws.cell('A1')
# or 
ws.cell(row=1,column=1,'name')
# or
ws.cell(1,2,'age')

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

由于此功能，即使您未为它们分配值，也可以滚动浏览而不是直接访问它们将在内存中全部创建它们。

就像是

1
2
3
for x in range(1,101):
   for y in range(1,101):
       ws.cell(row=x, column=y)

将在内存中创建100x100的单元，一无所有。

访问多个单元格

可以使用切片访问单元格范围：

1
2
3
4
5
6
cell_range = ws['A1':'C2']
# 行或列的范围可以类似地获得：
colC = ws['C']
col_range = ws['C:D']
row10 = ws[10]
row_range = ws[5:10]

您也可以使用以下Worksheet.iter_rows()方法：

1
2
3
4
5
6
7
8
9
>>> for row in ws.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>

同样，该Worksheet.iter_cols()方法将返回列：

1
2
3
4
5
6
7
8
9
>>> for col in ws.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>

如果需要遍历文件的所有行或列，则可以使用 Worksheet.rows属性：

如果需要遍历文件的所有行或列，则可以使用 Worksheet.rows属性：

1
2
3
4
5
6
7
8
9
10
11
12
>>> ws = wb.active
>>> ws['C9'] = 'hello world'
>>> tuple(ws.rows)
((<Cell Sheet.A1>, <Cell Sheet.B1>, <Cell Sheet.C1>),
(<Cell Sheet.A2>, <Cell Sheet.B2>, <Cell Sheet.C2>),
(<Cell Sheet.A3>, <Cell Sheet.B3>, <Cell Sheet.C3>),
(<Cell Sheet.A4>, <Cell Sheet.B4>, <Cell Sheet.C4>),
(<Cell Sheet.A5>, <Cell Sheet.B5>, <Cell Sheet.C5>),
(<Cell Sheet.A6>, <Cell Sheet.B6>, <Cell Sheet.C6>),
(<Cell Sheet.A7>, <Cell Sheet.B7>, <Cell Sheet.C7>),
(<Cell Sheet.A8>, <Cell Sheet.B8>, <Cell Sheet.C8>),
(<Cell Sheet.A9>, <Cell Sheet.B9>, <Cell Sheet.C9>))

或Worksheet.columns属性：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
>>> tuple(ws.columns)
((<Cell Sheet.A1>,
<Cell Sheet.A2>,
<Cell Sheet.A3>,
<Cell Sheet.A4>,
<Cell Sheet.A5>,
<Cell Sheet.A6>,
...
<Cell Sheet.B7>,
<Cell Sheet.B8>,
<Cell Sheet.B9>),
(<Cell Sheet.C1>,
<Cell Sheet.C2>,
<Cell Sheet.C3>,
<Cell Sheet.C4>,
<Cell Sheet.C5>,
<Cell Sheet.C6>,
<Cell Sheet.C7>,
<Cell Sheet.C8>,
<Cell Sheet.C9>))

出于性能原因，该Worksheet.iter_cols()和Worksheet.columns方法在只读模式下不可用。

获取最大行或最大列

1
2
3
# 获得最大列和最大行
print(sheet.max_row)
print(sheet.max_column)

仅值

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

1
2
3
4
5
for row in ws.values:
   for value in row:
     print(value)
# value取值
print(ws['A1'].value)

Worksheet.iter_rows()和Worksheet.iter_cols()都可以使用values_only参数来返回单元格的值:

1
2
3
# 第一行到第二行 第二列到第三列 范围内的值
for row in ws.iter_rows(min_row=1,min_col=2,max_col=3,max_row=2,values_only=True):
    print(row)

数据存储

1
2
3
4
5
6
7
ws['A1'].value="111"
print(ws['A1'].value)  # 111
# or
ws['A2']='ppppp'
# 保存到文件
# 此操作将覆盖现有文件，而不会发出警告。
wb.save(r'D:\date.xlsx')

Saving as a stream

如果要将文件保存到流中，例如在使用Web应用程序（例如Pyramid，Flask或Django）时，只需提供以下命令即可 NamedTemporaryFile()：

1
2
3
4
5
6
7
>>> from tempfile import NamedTemporaryFile
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> with NamedTemporaryFile() as tmp:
        wb.save(tmp.name)
        tmp.seek(0)
        stream = tmp.read()

您可以指定属性template = True，以将工作簿另存为模板：

1
2
3
>>> wb = load_workbook('document.xlsx')
>>> wb.template = True
>>> wb.save('document_template.xltx')

或将此属性设置为False（默认），以另存为文档：

1
2
3
>>> wb = load_workbook('document_template.xltx')
>>> wb.template = False
>>> wb.save('document.xlsx', as_template=False)

警告

您应该监视数据属性和文档扩展名以将文档保存在文档模板中，反之亦然，否则结果表引擎将无法打开文档。

列宽行高

1
2
ws.column_dimensions['H'].width = 88.0
ws.row_dimensions['1'].width = 88.0

从文件加载

1
2
3
4
from openpyxl import load_workbook
wb=load_workbook(r'E:\qwe.xlsx')
ws=wb.active
print(ws)	# <Worksheet "Sheet1">

其他

使用数字公式

1
2
3
4
5
6
7
8
9
>>> import datetime
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws = wb.active
>>> # set date using a Python datetime
>>> ws['A1'] = datetime.datetime(2010, 7, 21)
>>>
>>> ws['A1'].number_format
'yyyy-mm-dd h:mm:ss'

使用公式

1
2
3
4
5
6
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws = wb.active
>>> # add a simple formula
>>> ws["A1"] = "=SUM(1, 1)"
>>> wb.save("formula.xlsx")

合并/取消合并单元格

1
2
3
4
5
6
7
8
9
10
11
from openpyxl.workbook import Workbook

wb = Workbook()
ws = wb.active
# merge_cells合并； unmerge_cells取消；
ws.merge_cells ('A2:D2')
ws.unmerge_cells('A2:D2')

# or equivalently
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)

插入图像

1
2
3
4
# 用Image创建
img = Image('logo.png')
# add to worksheet and anchor next to cells
ws.add_image(img, 'A1')

将数据以行的形式添加到工作薄中

1
2
3
4
5
6
7
8
9
# 将数据以行的形式添加到工作薄中
data = [
    ['Mike', '18'],
    ['Jack', '18'],
    ['Jerry', '18'],
    ['Jacky', '18'],
    ]
for row in data:
    ws.append(row)

插入和删除行和列，移动单元格的范围

插入行和列

您可以使用相关的工作表方法插入行或列：

默认值为一行或一列。例如，在第7行（在现有第7行之前）插入一行：

1
>>> ws.insert_rows(7)

删除行和列

删除列F:H：

1
>>> ws.delete_cols(6, 3)

单元格移动范围

您还可以在工作表中移动单元格区域：

1
>>> ws.move_range("D4:F10", rows=-1, cols=2)

这会将范围内的单元格D4:F10向上移动一列，向右移动两列。单元将覆盖任何现有单元。

如果单元格包含公式，则可以让openpyxl为您翻译这些公式，但是由于并非总是您想要的，因此默认情况下将其禁用。同样，仅单元格中的公式将被翻译。其他单元格或已定义名称对单元格的引用将不会更新；您可以使用 解析公式转换器执行此操作：

1
>>> ws.move_range("G4:H10", rows=1, cols=1, translate=True)

这会将公式中的相对引用在范围内移动一行一列。

Excel样式

样式可以应用于以下方面：

  • 用于设置字体大小，颜色，下划线等的字体
  • 填充以设置图案或颜色渐变
  • 边框在单元格上设置边框
  • 详细样式查看官网样式文档

单元格样式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
>>> from openpyxl.styles import colors
>>> from openpyxl.styles import Font, Color
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws = wb.active
>>>
>>> a1 = ws['A1']
>>> d4 = ws['D4']
>>> ft = Font(color="FF0000")
>>> a1.font = ft
>>> d4.font = ft
>>>
>>> a1.font.italic = True # is not allowed # doctest: +SKIP
>>>
>>> # If you want to change the color of a Font, you need to reassign it::
>>>
>>> a1.font = Font(color="FF0000", italic=True) # the change only affects A1

复制样式

样式也可以复制

1
2
3
4
5
6
7
8
9
10
11
12
>>> from openpyxl.styles import Font
>>> from copy import copy
>>>
>>> ft1 = Font(name='Arial', size=14)
>>> ft2 = copy(ft1)
>>> ft2.name = "Tahoma"
>>> ft1.name
'Arial'
>>> ft2.name
'Tahoma'
>>> ft2.size # copied from the
14.0

应用样式

样式直接应用于单元格

1
2
3
4
5
6
>>> from openpyxl.workbook import Workbook
>>> from openpyxl.styles import Font, Fill
>>> wb = Workbook()
>>> ws = wb.active
>>> c = ws['A1']
>>> c.font = Font(size=12)

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

1
2
3
4
>>> col = ws.column_dimensions['A']
>>> col.font = Font(bold=True)
>>> row = ws.row_dimensions[1]
>>> row.font = Font(underline="single")

命名样式

官网给的教程不生效，这是自己搜索出来的简便写法

1
2
3
from openpyxl.styles import Font
import re
myStyle = Font(bold=True,size=12,color='00800000')  # 加粗，字体，字体颜色

应用样式

1
2
3
4
5
6
7
ws['A1'].font = mystyle

# 给A1,B1,C1,A3,B3....添加定义好的myStyle样式
# eg: leibie_hangList = [1,3,7,8,11,66,88]
for hang_digit in leibie_hangList:
    for abc in ['A', 'B', 'C']:
        ws['{}{}'.format(abc,hang_digit)].font = myStyle

设置单元格颜色

介绍之前，我们在写代码时，要先导入openpyxl，并创建一个新sheet。

1
2
3
4
5
6
import openpyxl

from openpyxl.styles import PatternFill

workbook = openpyxl.Workbook()
sheet = workbook.create_sheet(index=0, title="Report")

方法一：使用fgColor填充指定单元格前景色

1
2
3
4
sheet.append(['A','B','C'])
sheet.append(['D','E','F'])
orange_fill = PatternFill(fill_type='solid', fgColor="FFC125")
sheet.cell(row=1, column=1).fill = orange_fill

注意：fill_type为填充类型，如果不写的话，则没有效果。

方法二：使用start_color和end_color填充指定单元格颜色

1
2
3
4
5
sheet.append(['A','B','C'])
sheet.append(['D','E','F'])

green_fill = PatternFill(start_color="AACF91", end_color="AACF91", fill_type="solid")
sheet.cell(row=1, column=2).fill = green_fill

注意：end_color可以省略，如果start_color和end_color的值不一致，则显示start_color指定颜色

方法三：使用bgcolor填充指定单元格背景色

1
2
3
4
5
6
7
8
sheet.append(['A','B','C'])
sheet.append(['D','E','F'])

green_fill = PatternFill(bgColor="AACF91", fill_type="solid")
# or
green_fill = PatternFill("solid", fgColor="1874CD")

sheet.cell(row=1, column=2).fill = green_fill

注意：不管bgColor赋什么值，背景色都为黑色。如果bgColor和fgColor同时设定，那么显示fgColor指定的颜色

方法四：填充指定列或行的背景色、

1
2
3
green_fill = PatternFill(fill_type="solid", fgColor="AACF91")
sheet.column_dimensions['A'].fill = green_fill	# 填充指定列的背景色
sheet.row_dimensions[1].fill = green_fill	# 填充指定行的背景色

注意：sheet.column_dimensions是打开sheet的所有列的集合，使用sheet中的列名来访问，比如A、B、C…

注意：sheet.column_dimensions是打开sheet的所有行的集合，使用sheet中的行号来访问，指定0行的话，不报错，但是没有任何效果

RGB颜色值

https://openpyxl.readthedocs.io/en/stable/styles.html

官方文档

先总结到这里了，其他请参考：官方文档