python操作Excel文件之openpyxl

最新推荐文章于 2024-08-15 09:18:38 发布

学姐的学姐

最新推荐文章于 2024-08-15 09:18:38 发布

阅读量2.5k

点赞数 2

分类专栏： python学习文章标签： python

python学习专栏收录该内容

1 篇文章 0 订阅

订阅专栏

安装和配置

安装openpyxl模块：

pip install openpyxl

若想使用插入图片的功能：

pip install pillow

知识点讲解

workbook 工作薄：一个Excel 文件是一个工作薄
worksheet 工作表：一个工作薄中可以包含多个工作表，每个工作表都有自己的工作表名
cell 单元格：工作表中的小格子，一个工作表包含多个单元格，是存储数据的最小对象

Tutorial

新建工作薄并操作

创建一个工作薄

无需单独创建文件再使用，只需引入Workbook类并直接使用：

from openpyxl import Workbook
wb = Workbook()

创建工作簿时至少包含一个工作表，可以通过openpyxl.workbook.Workbook.active()属性来访问这个工作表：

ws = wb.active

注意:这个函数使用_active_sheet_index属性，默认为0，除非你更改这个值，否则在使用这个方法时总会得到第一个工作表。

通过openpyxl.workbook.Workbook.create_sheet()可以创建新的工作表：

ws1 = wb.create_sheet("Mysheet") 
# 在已有工作表列表尾插入该表
# or
ws2 = wb.create_sheet("Mysheet", 0) 
# 将该表插入至起始位置

创建工作表时自动为其命名，命名规则为按顺序命名为Sheet, Sheet1, Sheet2, Sheet3……可以使用title属性来更改工作表名：

ws.title = "New Title"

各个工作表的背景颜色默认为白色，可以通过向sheet_properties_tabColor属性提供RRGGBB颜色编码来更改背景颜色:

ws.sheet_properties_tabColor = '1072BA'

一旦为工作表命名，就可以通过工作表名得到该工作表：

ws3 = wb["New Title"]

可以通过openpyxl.workbook.Workbook.sheetnames()属性得到所有的工作表名：

>>>print(wb.sheetnames)
['Sheet2', 'New Title', 'Sheet1']

或者循环输出各个工作表的标题：

for sheet in wb:
    print(sheet.title)

通过openpyxl.workbook.Workbook.copy_sheet()方法在一个工作薄内复制工作表：

source = wb.active
target = wb.copy_worksheet(source)
# 复制wb工作薄中的第一个工作表并追加到现有工作表列表尾 
# ['Sheet2', 'New Title', 'Sheet1', 'Sheet2 Copy']

注意:只复制单元格（包括数值、风格、超链接和评注）和工作表特征（包括维度、格式和属性），不复制其他工作表或者工作薄特征，例如：图片和表格。
注意：不能在工作薄间复制工作表。可以复制只读和只写的工作薄的工作表。

操作数据

操作一个单元格

可以通过关键字来获得单元格:

c = ws['A4']

返回A4单元格，或者创建A4单元格，如果本身不存在这个单元格的话。为单元格赋值：

ws['A4'] = 4

也可以通过openpyxl.worksheet.Worksheet.cell()方法来赋值：

d = ws.cell(row = 4, column = 2, value = 10)

注意：当创建一个工作表，不包含任何单元格。获得单元格时，自动创建单元格。

for i in range(1, 101):
    for j in range(1, 101):
        ws.cell(row = i, culumn = j) 
# 获得单元格
# 创建了一个无内容的100*100单元格

操作多个单元格

使用分片获得一定范围的单元格：

cell_range = ws['A1':'C2']

获得行、列范围：

colC = ws['C']
col_range = ws['C':'D']
row10 = ws[10]
row_range = ws[5:10]

也可以使用openpyxl.worksheet.Worksheet.iter_rows()方法：

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>

openpyxl.worksheet.Worksheet.iter_cols()方法同理：

for col in ws.iter_cols(min_col = 1, max_row = 3, max_col = 3):
    for cell in col:
        print(cell)
<Cell Sheet1.A1>
<Cell Sheet1.A2>
<Cell Sheet1.B1>
<Cell Sheet1.B2>
<Cell Sheet1.C1>
<Cell Sheet1.C2>

通过openpyxl.worksheet.Worksheet.rows()属性获得一个工作表的全部单元格：

>>>ws = wb.active
>>>ws['C9'] = 'hello world'
>>>turple(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>))

或者openpyxl.worksheet.Worksheet.columns()属性

>>>turple(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>))

数据存储

一旦创建了openpyxl.cell.Cell, 就可以为其赋值:

c.value = 'hello world'
print(c.value) # hello world
d.value = 4
print(d.value)  # 4

也可以使用其他格式和接口为其赋值：

wb = Workbook(guess_types = True)
c.value = '12%'
print(c.value)  # 0.12
d.value = 12.30
print(d.value)  # 12.3

import datetime
d.value = datetime.datetime.now()
print(d.value)  # datetime.datetime(2010, 9, 10, 22, 25, 18)

保存为文件

使用openpyxl.workbook.Workbook.save()方法来保存一个工作薄：

wb = Workbook()
wb.save('balance.xlsx')

注意：自动覆盖已有同名文件。

保存为流

当你想将文件保存为流，例如，当使用 Pyramid, Flask 和 Django等web程序时，你可以提供NamedTemporaryFile：

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，来将一个工作薄存为模版：

wb = load_workbook('document.xlsx')
wb.template = True
wb.save('document_template.xlsx')

或设置为False来存为文件：

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

注意：
保存文件时，你应该控制数据属性和文件扩展名，否则MS等办公文具打不开这些文件。

wb = load_workbook('document.xlsx')  
# 需要保存为*.xlsx
wb.save('new_document.xlsm') 
# MS Excel 打不开该文件
# or
# 需要指明 keep_vba=True
wb = load_workbook('document.xlsm')
wb.save('new_document.xlsm')
# MS Excel打不开
# or
wb = load_workbook('document.xltm', keep_vba=True)
# 保持扩展名为 *.xltm.
wb.save('new_document.xlsm')
# MS Excel 打不开

载入已有文件

通过载入openpyxl.load_workbook() 来打开已有工作薄：

from openpyxl import load_workbook
wb2 =load_workbook('balance.xlsx')
print

Cookbook

简单应用

写一个工作薄

from openpyxl import Workbook
from openpyxl.compat import range
from openpyxl.utils import get_column_letter

wb = Workbook()

dest_filename = 'empty_book.xlsx'

ws1 = wb.active
ws1.title = "range names"

for row in range(1, 40):
     ws1.append(range(600))
# ws1工作表中存在40*600单元格，每行的内容相同，为从0到599

ws2 = wb.create_sheet(title="Pi")

ws2['F5'] = 3.14

ws3 = wb.create_sheet(title="Data")
for row in range(10, 20):
     for col in range(27, 54):
         _ = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))
# 为单元格赋值该列的字母内容 

print(ws3['AA10'].value) # AA

wb.save(filename = dest_filename)

读取已有工作薄

from openpyxl import load_workbook
wb = load_workbook(filename = 'empty_book.xlsx')
sheet_ranges = wb['range names']
print(sheet_ranges['D18'].value) # 3

注意：
load_workbook()方法的几个属性：
guess_types:读取单元格时使类型接口有效或者无效（默认为无效）；
data_only:控制带有公式表示的单元格是显示为公式（默认）或者为上一次使用Excel打开表格时的数据；
keep_vba:控制是否保护Visual Basic元素（默认不保护），如果保护，则不能被编辑。

注意
使用openpyxl读取Excel文件，无法读取到图片和表格，所以使用相同名字保存工作薄时，会失去图片和表格。

使用数字格式

import datetime
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
# 使用python datetime赋值
ws['A1'] = datetime.datetime(2010, 7, 21)

ws['A1'].number_format
# yyyy-mm-dd h:mm:ss

wb.guess_types = True
# 数字加 % 表示百分数
ws['B1'] = '3.14%'
wb.guess_types = False
ws['B1'].value
# 0.031400000000000004

ws['B1'].number_format
# 0%

使用公式

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 一个简单的公式
ws['A1'] = '=SUM(1,1)'
wb.save("formula.xlsx")

openpyxl不会计算公式，但是可以判断是否存在这么一个公式：

>>> from openpyxl.utils import FORMULAE
>>> "HEX2DEC" in FORMULAE
True

合并／拆开单元格

合并单元格时，保留左上角的单元格，其他全部删除：

from openpyxl.workbook import Workbook

wb = Workbook()
ws = wb.active

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 import Workbook
from openpyxl.drawing.image import  Image

wb = Workbook()
ws = wb.active
ws['A1'] = 'you should see one logo below'

img = Image('logo.png')

ws.add_image(img, 'A1')
wb.save('logo.xlsx')

折叠（隐藏）

import openpyxl

wb = openpyxl.Workbook()
ws = wb.create_sheet()
ws.column_dimensions.group('A', 'D', hidden = True) 
# A-D列被隐藏
ws.row_dimensions.group(1, 10, hidden = True)
# 1-10行被隐藏

wb.save('group.xlsx')