如何用Python中的openpyxl操作Excel？
1
初識與安裝
1.安裝
$?pip install openpyxl2.一個簡單創(chuàng)建例子
from?openpyxl import?Workbook
wb = Workbook()
# 激活 worksheet
ws = wb.active
# 數(shù)據(jù)可以直接分配到單元格中
ws[ 'A1'?] = 42
# 可以附加行，從第一列開始附加
ws.append([ 1?, 2?, 3?])
# Python 類型會被自動轉(zhuǎn)換
import?datetime
ws[ 'A3'?] = datetime.datetime.now().strftime( "%Y-%m-%d"?)
# 保存文件
wb.save( "sample.xlsx"?)打開查看Excel如下：
3.workbook
使用openpyxl無需在文件系統(tǒng)上創(chuàng)建文件，只需導(dǎo)入 Workbook 類并開始使用。
from?openpyxl import?Workbook
wb = Workbook()4.worksheet
一個workbook至少創(chuàng)建一個worksheet。
通過openpyxl.workbook.Workbook.active()得到worksheet.
ws?= wb.active注意：該方法使用_active_sheet_index屬性, 默認會設(shè)置0，也就是第一個worksheet。除非手動修改，否則使用active方法得到都是第一個worksheet。
你也可以創(chuàng)建worksheets，通過 openpyxl.workbook.Workbook.create_sheet()?方法：
>>> ws1 = wb.create_sheet( "Mysheet"?) #插入到最后(default)
#或者
>>> ws2 = wb.create_sheet( "Mysheet"?, 0) #插入到最開始的位置創(chuàng)建的sheet的名稱會自動創(chuàng)建，按照sheet，sheet1，sheet2自動增長，通過title屬性可以修改其名稱。
ws.title = "New Title"默認的sheet的tab是白色的，可以通過 RRGGBB顏色來修改sheet_properties.tabColor屬性從而修改sheet tab按鈕的顏色:
ws.sheet_properties.tabColor = "1072BA"當你設(shè)置了sheet的名稱，可以將其看成workbook中的一個key。也可以使用openpyxl.workbook.Workbook.get_sheet_by_name()?方法
>>> ws3 = wb[ "New Title"?]
>>> ws4 = wb.get_sheet_by_name( "New Title"?)
>>> ws is?ws3 is?ws4
True查看workbook中的所有worksheets名稱:
openpyxl.workbook.Workbook.get_sheet_names()
>>> print(wb.sheetnames)
[ 'Sheet2'?, 'New Title'?, 'Sheet1'?]遍歷worksheets：
>>> for?sheet in?wb:
... ????print(sheet.title)2
操作數(shù)據(jù)
1.訪問單元格
單元格可以看作是worksheet的key，通過key去訪問單元格中的數(shù)據(jù)
>>> c = ws[ 'A4'?]直接返回A4單元格，如果不存在則會自動創(chuàng)建一個。
2.指定單元格的值
>>> ws[ 'A4'?] = 4 #直接賦值使用openpyxl.worksheet.Worksheet.cell()方法操作某行某列的某個值：
>>> d = ws.cell(row=4, column=2, value=10)注意：當worksheet在內(nèi)存中被創(chuàng)建時,是沒有包含cells的，cells是在首次訪問時創(chuàng)建;可以循環(huán)在內(nèi)存中創(chuàng)建cells，這時不指定他們的值也會創(chuàng)建該cells些：(創(chuàng)建100x100cells)
>>> for?i in?range(1,101):
... ???????for?j in?range(1,101):
... ???????????ws.cell(row=i, column=j)3.訪問許多cells
通過切片Ranges指定許多cells
>>> cell_range = ws[ 'A1'?: 'C2'?]同樣也可以Ranges rows 或者columns :
>>> 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)
| | | | | | 如果你需要遍歷所有文件的行或列,可以使用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>))4.保存文件
最簡單最安全的方法保存workbook是使用openpyxl.workbook.Workbook對象的?openpyxl.workbook.Workbook.save()方法:
>>> wb = Workbook()
>>> wb.save( 'balances.xlsx'?)保存的默認位置在python的根目錄下。
注意：會自動覆蓋已經(jīng)存在文件名的文件。
5.從文件中導(dǎo)入
像寫一樣我們可以導(dǎo)入openpyxl.load_workbook()已經(jīng)存在的workbook:
>>> from?openpyxl import?load_workbook
>>> wb2 = load_workbook( 'test.xlsx'?)
>>> print?wb2.get_sheet_names()
[ 'Sheet2'?, 'New Title'?, 'Sheet1'?]3
常用實例
詳情參考官方使用文檔：
http://openpyxl.readthedocs.io/en/default/usage.html
1.寫入例子一
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from?openpyxl import?Workbook
wb = Workbook()
# 激活 worksheet
ws = wb.active
# 數(shù)據(jù)可以直接分配到單元格中
ws[ 'A1'?] = 42
# 可以附加行，從第一列開始附加
ws.append([ 1?, 2?, 3?])
# Python 類型會被自動轉(zhuǎn)換
import?datetime
ws[ 'A3'?] = datetime.datetime.now().strftime( "%Y-%m-%d"?)
# 保存文件
wb.save( "sample.xlsx"?)2.寫入例子二
#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
http://openpyxl.readthedocs.io/en/default/usage.html
"""
# workbook相關(guān)
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)
wb.save(filename = dest_filename)3.讀取例子一
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from?openpyxl.reader.excel import?load_workbook
import?json
# 讀取excel2007文件
wb = load_workbook(filename=r'test_book.xlsx')
# 顯示有多少張表
print?"Worksheet range(s):", wb.get_named_ranges()
print?"Worksheet name(s):", wb.get_sheet_names()
# 取第一張表
sheetnames = wb.get_sheet_names()
ws = wb.get_sheet_by_name(sheetnames[0])
# 顯示表名，表行數(shù)，表列數(shù)
print?"Work Sheet Titile:", ws.title
print?"Work Sheet Rows:", ws.max_row
print?"Work Sheet Cols:", ws.max_column
# 建立存儲數(shù)據(jù)的字典
data_dic = {}
# 把數(shù)據(jù)存到字典中
for?rx in?range(1, ws.max_row + 1):
???temp_list = []
???pid = rx
???w1 = ws.cell(row=rx, column=1).value
???w2 = ws.cell(row=rx, column=2).value
???w3 = ws.cell(row=rx, column=3).value
???w4 = ws.cell(row=rx, column=4).value
???temp_list = [w1, w2, w3, w4]
???data_dic[pid] = temp_list
# 打印字典數(shù)據(jù)個數(shù)
print?'Total:%d'?% len(data_dic)
print?json.dumps(data_dic, encoding="UTF-8", ensure_ascii=False)讀取結(jié)果：
Worksheet range(s): []
Worksheet name(s): [u?'\u6d3b\u52a8\u8868'?, u?'\u7528\u6237\u4fe1\u606f'?, u?'Sheet3'?]
Work Sheet Titile: 活動表
Work Sheet Rows:?3
Work Sheet Cols:?5
Total:3
{ "1"?: [ "張三"?, 18, "男"?, "廣州"?], "2"?: [ "李四"?, 20, "女"?, "湖北"?], "3"?: [ "王五"?, 25, "女"?, "北京"?]}4.使用公式
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws = wb.active
>>> # add a simple formula
>>> ws[ "A1"?] = "=SUM(1, 1)"
>>> wb.save( "formula.xlsx"?)-?END -
本文為轉(zhuǎn)載分享&推薦閱讀，若侵權(quán)請聯(lián)系后臺刪除