快乐学习
前程无忧、中华英才非你莫属!

翻译篇Day1-用python读写Excel

声明:独家翻译,禁止转载!

 

翻译自: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)

打赏
赞(0) 打赏
未经允许不得转载:同乐学堂 » 翻译篇Day1-用python读写Excel

特别的技术,给特别的你!

联系QQ:1071235258QQ群:710045715

觉得文章有用就打赏一下文章作者

非常感谢你的打赏,我们将继续给力更多优质内容,让我们一起创建更加美好的网络世界!

支付宝扫一扫打赏

微信扫一扫打赏

error: Sorry,暂时内容不可复制!