同乐学堂声明：独家翻译，禁止转载！
翻译自：http://openpyxl.readthedocs.io/
一、介绍
Openpyxl是一个用于阅读和编写Excel 2010 xlsx / xlsm / xltx / xltm文件的Python库，可以从Python本身读取/写入Office Open XML格式。
学什么程序都要从helloworld学起
helloworld.py
# -*- coding: utf-8 -*-
from openpyxl import Workbook
wb = Workbook()
# 获取活动工作表
ws = wb.active
# 数据可以直接分配给单元格
ws['A1'] = 42
# 也可以追加行
ws.append([1, 2, 3])
# Python 类型将自动转换
import datetime
ws['A2'] = datetime.datetime.now()
# 保存到当前目录下名字叫，"sample.xlsx
wb.save("sample.xlsx")
学编程的经验是：程序没有欺骗，只有0、1。
第二章 在内存中操作工作簿
创建一个工作簿
没有必要在文件系统上创建一个文件以开始使用openpyxl。 只需导入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，...）。您可以随时使用title属性更改此名称：
ws.title = "New Title"
默认情况下，保存此标题的选项卡的背景颜色为白色。您可以更改此提供RRGGBB颜色代码到sheet_properties.tabColor属性：
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_worksheet() 方法：
>>> source = wb.active >>> target = wb.copy_worksheet(source)
注意
只能复制单元格和样式。您不能在工作簿之间复制工作表。
您可以在工作簿中复制工作表
玩数据
访问一个单元格
现在我们知道如何访问工作表，我们可以开始修改单元格内容。
单元格可以作为工作表的键直接访问
>>> c = ws [ '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, column=j)
将在内存中创建100x100单元格，没有任何内容。
访问许多单元格
单元格可以使用切片
>>> 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_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>
如果您需要遍历文件的所有行或列，您可以使用该 openpyxl.worksheet.Worksheet.rows()属性：
>>> 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>))
或openpyxl.worksheet.Worksheet.columns()属性：
>>> 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>))
数据存储
一旦我们有一个openpyxl.cell.Cell，我们可以给它一个值：
>>> c.value = 'hello, world' >>> print(c.value) 'hello, world' >>> d.value = 3.14 >>> print(d.value) 3.14
您还可以启用类型和格式推断：
>>> wb = Workbook(guess_types=True) >>> c.value = '12%' >>> print(c.value) 0.12 >>> import datetime >>> d.value = datetime.datetime.now() >>> print d.value datetime.datetime(2010, 9, 10, 22, 25, 18) >>> c.value = '31.50' >>> print(c.value) 31.5
保存到文件
保存工作簿的最简单和最安全的方法是使用对象的 openpyxl.workbook.Workbook.save()方法openpyxl.workbook.Workbook：
>>> wb = Workbook()
>>> wb.save('balances.xlsx')
警告
此操作将覆盖现有文件而不发出警告。
注意
扩展名不被强制为xlsx或xlsm，尽管如果不使用官方扩展名，则可能会遇到一些麻烦，直接与另一个应用程序打开。
由于OOXML文件基本上是ZIP文件，您也可以使用.zip结束文件名，并将其与您最喜爱的ZIP存档管理器打开。
您可以指定属性template = True，将工作簿保存为模板：
>>> wb = load_workbook('document.xlsx')
>>> wb.template = True
>>> wb.save('document_template.xltx')
或将此属性设置为False（默认），以保存为文档：
>>> wb = load_workbook('document_template.xltx')
>>> wb.template = False
>>> wb.save('document.xlsx', as_template=False)
警告
您应该监视文档模板中保存文档的数据属性和文档扩展名，反之亦然，否则结果表引擎无法打开文档。
注意
以下将失败：
>>> wb = load_workbook('document.xlsx')
>>> # Need to save with the extension *.xlsx
>>> wb.save('new_document.xlsm')
>>> # MS Excel can't open the document
>>>
>>> # or
>>>
>>> # Need specify attribute keep_vba=True
>>> wb = load_workbook('document.xlsm')
>>> wb.save('new_document.xlsm')
>>> # MS Excel will not open the document
>>>
>>> # or
>>>
>>> wb = load_workbook('document.xltm', keep_vba=True)
>>> # If we need a template document, then we must specify extension as *.xltm.
>>> wb.save('new_document.xlsm')
>>> # MS Excel will not open the document
从文件加载
与写作一样，您可以导入openpyxl.load_workbook()以打开现有的工作簿：
>>> from openpyxl import load_workbook
>>> wb2 = load_workbook('test.xlsx')
>>> print wb2.get_sheet_names()
['Sheet2', 'New Title', 'Sheet1']
三、简单案例
新建个工作簿
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))
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)
阅读现有的工作簿
from openpyxl import load_workbook wb = load_workbook(filename = 'empty_book.xlsx') sheet_ranges = wb['range names'] print(sheet_ranges['D18'].value)
使用数字格式化
import datetime from openpyxl import Workbook wb = Workbook() ws = wb.active # 设置日期使用Python datetime ws['A1'] = datetime.datetime(2010, 7, 21) print(ws['A1'].number_format) # 可以根据具体情况启用类型推断 wb.guess_types = True # 设置百分比使用字符串后跟百分号 ws['B1'] = '3.14%' wb.guess_types = False print(ws['B1'].value) print(ws['B1'].number_format)
使用公式,合并，取消单元格
合并单元格只留左上角的值，剩下的值将被删除。
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
# add a simple formula
ws["A1"] = "=SUM(2, 1)"
# ws.merge_cells('A1:B1')
# ws.unmerge_cells('A1:B1')
# or
# ws.merge_cells(start_row=2,start_column=1,end_row=2,end_column=4)
# ws.unmerge_cells(start_row=2,start_column=1,end_row=2,end_column=4)
wb.save("formula.xlsx")
注意，您必须使用英文名称作为函数，函数参数必须用逗号分隔而不是其他标点符号，例如分号。
openpyxl从不评估公式，但可以检查公式的名称：
>>> from openpyxl.utils import FORMULAE >>> "HEX2DEC" in FORMULAE True
如果你试图使用一个不知道的公式，这可能是因为你使用的公式没有包含在初始规范中。这样的公式必须以.xlfn。
插入图片
from openpyxl import Workbook
from openpyxl.drawing.image import Image
wb = Workbook()
ws = wb.active
ws['A1'] = 'You should see three logos below'
# create an image
img = Image('logo.png')
# add to worksheet and anchor next to cells
ws.add_image(img, 'A1')
wb.save('logo.xlsx')
结合 Pandas and NumPy 库进行实战使用，生成多维报表。
from openpyxl.cell.cell import WriteOnlyCell
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl import Workbook
import numpy as np
import pandas as pd
wb = Workbook(write_only=True)
ws = wb.create_sheet()
cell = WriteOnlyCell(ws)
cell.style = 'Pandas'
def format_first_row(row, cell):
for c in row:
cell.value = c
yield cell
dates = pd.date_range("20170914",periods=6)
df = pd.DataFrame(np.random.rand(6,4),index=dates,columns=list('ABCD'))
rows = dataframe_to_rows(df)
first_row = format_first_row(next(rows), cell)
ws.append(first_row)
for row in rows:
row = list(row)
cell.value = row[0]
row[0] = cell
ws.append(row)
wb.save("openpyxl_stream.xlsx")
运行结果
略
将工作表转换为数据框
要将工作表转换为Dataframe，可以使用values属性。如果工作表没有标题或索引，这很简单：
df = DataFrame(ws.values)
如果工作表确实有标题或索引，例如由Pandas创建的标题或索引，那么需要更多的工作：
data = ws.values cols = next(data)[1:] data = list(data) idx = [r[0] for r in data] data = (islice(r, 1, None) for r in data) df = DataFrame(data, index=idx, columns=cols)
微信扫一扫，打赏作者吧～
