openpyxl官方手冊

教程 Tutorial

創(chuàng)建excel文件 Create a workbook

There is no need to create a file on the filesystem to get started with openpyxl. Just import the Workbook class and start work:
開始使用openpyxl時，無需在文件系統(tǒng)中創(chuàng)建文件沽甥，只要導(dǎo)入workbook類就可以了：

>>> from openpyxl import Workbook
>>> wb = Workbook()

A workbook is always created with at least one worksheet. You can get it by using the Workbook.active property:
至少有一個工作表在工作簿創(chuàng)建后,可以通過Workbook.active屬性來定位到工作表：

>>> ws = wb.active

Note
This is set to 0 by default. Unless you modify its value, you will always get the first worksheet by using this method.
該工作簿的默認(rèn)索引是從0開始胰默。除非索引值被修改，否則使用這個方法將總是獲取第一個工作表。

You can create new worksheets using the Workbook.create_sheet() method:
可以使用Workbook.create_sheet()方法來創(chuàng)建新工作表

>>> ws1 = wb.create_sheet("Mysheet") # 插入到最后 (默認(rèn))

>>> ws2 = wb.create_sheet("Mysheet", 0) # 插入到最前  

>>> ws3 = wb.create_sheet("Mysheet", -1) # 插入到倒數(shù)第二

Sheets are given a name automatically when they are created. They are numbered in sequence (Sheet, Sheet1, Sheet2, …). You can change this name at any time with the Worksheet.title property:
工作表將在創(chuàng)建時按照數(shù)字序列自動命名（如Sheet，Sheet1，Sheet2悬槽，……）∷才ǎ可以在任何時候通過Worksheet.title屬性修改工作表名：

>>>ws.title = "New Title"

The background color of the tab holding this title is white by default. You can change this providing an RRGGBB color code to the Worksheet.sheet_properties.tabColor attribute:
創(chuàng)建的工作表的標(biāo)簽背景色默認(rèn)是白色初婆。可以通過在Worksheet.sheet_properties.tabColor對象中設(shè)置RRGGBB格式的顏色代碼進(jìn)行修改：

>>>ws.sheet_properties.tabColor = "1072BA"

Once you gave a worksheet a name, you can get it as a key of the workbook:
當(dāng)設(shè)置了worksheet名稱，可以將名稱作為工作表的索引：

>>> ws3 = wb["New Title"]

You can review the names of all worksheets of the workbook with the Workbook.sheetname attribute
可以通過Workbook.sheetname對象來查看工作簿中所有工作表的名稱

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

You can loop through worksheets
可以遍歷整個工作簿：

>>> for sheet in wb:
...     print(sheet.title)

You can create copies of worksheets within a single workbook:
Workbook.copy_worksheet() method:
可以使用Workbook.copy_worksheet()方法來創(chuàng)建一個工作簿中所有表的副本：

>>> source = wb.active
>>> target = wb.copy_worksheet(source)

Note
Only cells (including values, styles, hyperlinks and comments) and certain worksheet attribues (including dimensions, format and properties) are copied. All other workbook / worksheet attributes are not copied - e.g. Images, Charts.
只有單元格（包括值磅叛、樣式屑咳、超鏈接、備注）和一些工作表對象（包括尺寸弊琴、格式和參數(shù)）會被復(fù)制兆龙。其他屬性不會被復(fù)制，如圖片敲董、圖表紫皇。

You also cannot copy worksheets between workbooks. You cannot copy a worksheet if the workbook is open in read-only or write-only mode.
無法在兩個工作簿中復(fù)制工作表。當(dāng)工作簿處于只讀或只寫狀態(tài)時也無法復(fù)制工作表臣缀。

數(shù)據(jù)操作 Playing with data

訪問一個單元格 Accessing one cell

Now we know how to get a worksheet, we can start modifying cells content. Cells can be accessed directly as keys of the worksheet:
現(xiàn)在我們知道如何獲取一個工作表坝橡，我們可以開始修改單元格內(nèi)容泻帮。單元格可以通過工作表中的索引直接訪問：

>>> c = ws['A4']

This will return the cell at A4, or create one if it does not exist yet. Values can be directly assigned:
這將返回位于“A4”的單元格內(nèi)容精置，如果不存在則創(chuàng)建一個÷嘣樱可以直接對單元格進(jìn)行賦值：

>>> ws['A4'] = 4

There is also the Worksheet.cell() method.
這是Worksheet.cell()的方法脂倦。
This provides access to cells using row and column notation:
工具支持通過行列號訪問單元格：

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

Note
When a worksheet is created in memory, it contains no cells. They are created when first accessed.
當(dāng)在內(nèi)存中創(chuàng)建工作表后，表中不包含任何單元格元莫。單元格將在第一次訪問時創(chuàng)建赖阻。

Warning
Because of this feature, scrolling through cells instead of accessing them directly will create them all in memory, even if you don’t assign them a value.Something like
因為這種特性，遍歷而不是訪問這些單元格將在內(nèi)存中全部創(chuàng)建它們踱蠢，即使并沒有給它們賦值火欧。比如說

>>>for x in range(1,101):  

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

訪問多個單元格 Accessing many cells

Ranges of cells can be accessed using slicing:
可以通過切片訪問一個范圍內(nèi)的單元格：

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

Ranges of rows or columns can be obtained similarly:
行或列的單元格也可以通過類似的方法訪問：

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

You can also use the Worksheet.iter_rows() method:
同樣也可以使用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>

Likewise the Worksheet.iter_cols() method will return columns:
類似的，使用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>

Note
For performance reasons the Worksheet.iter_cols() method is not available in read-only mode.
出于性能考慮茎截，Worksheet.iter_cols()方法不支持在只讀模式使用

If you need to iterate through all the rows or columns of a file, you can instead use the Worksheet.rows property:
如果需要遍歷文件內(nèi)的所有行和列苇侵，可以使用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>))

or the Worksheet.columns property:
或者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>))

Note
For performance reasons the Worksheet.columns property is not available in read-only mode.
處于性能原因，Worksheet.columns屬性不支持只讀模式

取值 Values only

If you just want the values from a worksheet you can use the Worksheet.values property. This iterates over all the rows in a worksheet but returns just the cell values:
如果只需要從工作表中獲取值企锌，可以使用Worksheet.values屬性榆浓。這將遍歷工作表中所有行，但只返回單元格值：

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

Both Worksheet.iter_rows() and Worksheet.iter_cols() can take the values_only parameter to return just the cell’s value:
Worksheet.iter_rows()和Worksheet.iter_cols()可以只返回單元格值：

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

(None, None, None)
(None, None, None)

賦值 Data storage

Once we have a Cell, we can assign it a value:
當(dāng)我們創(chuàng)建了一個單元格對象撕攒，我們可以對其賦值：

>>> c.value = 'hello, world'
>>> print(c.value)
'hello, world'

>>> d.value = 3.14
>>> print(d.value)
3.14

保存 Saving to a file

The simplest and safest way to save a workbook is by using the Workbook.save() method of the Workbook object:
對Workbook對象使用Workbook.save() 方法可以簡單安全的保存工作簿：

>>> wb = Workbook()
>>> wb.save('balances.xlsx')

Warning
This operation will overwrite existing files without warning.
該操作將覆蓋同名文件陡鹃，而不會有任何警告

Note
The filename extension is not forced to be xlsx or xlsm, although you might have some trouble opening it directly with another application if you don’t use an official extension.
文件擴(kuò)展名不強(qiáng)制為xlsx或xlsm，如果你沒有使用常用的擴(kuò)展名抖坪，在使用其他應(yīng)用打開該文件時可能存在一些異常萍鲸。

As OOXML files are basically ZIP files, you can also open it with your favourite ZIP archive manager.
因為OOXML文件是基于zip文件，你也可以使用常用的解壓軟件打開擦俐。

以流方式存儲 Saving as a stream

If you want to save the file to a stream, e.g. when using a web application such as Pyramid, Flask or Django then you can simply provide a NamedTemporaryFile():
如果需要通過流方式存儲文件脊阴，比如使用web應(yīng)用如Pyramid,Flask或Django，你可以使用NamedTemporaryFile()方法：

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

or set this attribute to False (default), to save as a document:
或設(shè)置這個對象為False：

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

Warning
You should monitor the data attributes and document extensions for saving documents in the document templates and vice versa, otherwise the result table engine can not open the document.

Note
The following will fail:

>>> 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

讀取文件 Loading from a file

The same way as writing, you can use the openpyxl.load_workbook() to open an existing workbook:
和寫操作一樣，可以使用openpyxl.load_workbook()打開存在的工作簿：

>>> from openpyxl import load_workbook
>>> wb2 = load_workbook('test.xlsx')
>>> print wb2.sheetnames
['Sheet2', 'New Title', 'Sheet1']