Python3 openpyxl庫
用于讀取和寫入 Excel 2010 xlsx/xlsm/xltx/xltm 文件断盛。通過 pip install openpyxl
命令下載 openpyxl 庫实束。
演示代碼
通過下面的演示代碼,可以快速創(chuàng)建一個名為 sample.xlsx 的表格文件:
from openpyxl import Workbook
import datetime
wb = Workbook()
# 獲取活動工作表
ws = wb.active
# 數(shù)據(jù)可以直接分配到單元格
ws['A1'] = 42
# 行也可以追加
ws.append([1, 2, 3])
# Python類型將自動轉(zhuǎn)換
ws['A2'] = datetime.datetime.now()
# 保存文件
wb.save("sample.xlsx")
通用方案
通用讀取類
創(chuàng)建一個 excel_to_dict.py
文件希停,寫入下面的代碼:
from os.path import isfile
from openpyxl import load_workbook
class ExcelToDict:
"""
將Excel文件對象轉(zhuǎn)成Python字典對象
"""
def __init__(self, file_dir, title_row=0):
# 工作簿文件的路徑
self.file_dir = file_dir
# 標(biāo)題行位于第幾行
self.title_row = int(title_row)
self.data_dict = {}
self.work_book = None
def open_object(self):
"""打開工作簿對象"""
valid = isfile(self.file_dir)
# file_dir指向的文件是否不存在
if not valid:
raise Exception('文件路徑 {0} 不存在'.format(self.file_dir))
self.work_book = load_workbook(filename=self.file_dir)
def read_excel(self):
"""讀取工作簿數(shù)據(jù)"""
if not self.work_book:
raise Exception('需要先調(diào)用 open_object() 方法以打開工作簿對象')
for sheet_name in self.work_book.sheetnames:
# 每個工作表的字典
data_dict_sheet = {'title_row': [], 'value_row': {}}
# 獲取工作表對象
ws = self.work_book[sheet_name]
# 預(yù)先創(chuàng)建工作表中每一行的字典
for i in range(ws.max_row - 1 - self.title_row):
data_dict_sheet['value_row'][i] = {}
# 遍歷所有列
columns = tuple(ws.columns)
for column in columns:
# 每一列的標(biāo)題
title = column[self.title_row].value
# 記錄每列的標(biāo)題
data_dict_sheet['title_row'].append(title)
row_num = 0
# 遍歷每一列中的所有值
for col in column:
# 忽略每一列的標(biāo)題行
if column.index(col) <= self.title_row:
continue
data_dict_sheet['value_row'][row_num][title] = col.value
row_num += 1
# 記錄每個工作表的數(shù)據(jù)字段
self.data_dict[sheet_name] = data_dict_sheet
def check(self, check_item=None, sheet_name=None, sheet_index=0):
"""
在所選工作表中校驗是否包含業(yè)務(wù)需要的所有標(biāo)題名稱
:param check_item: 所選工作表中需要校驗的標(biāo)題列表
:param sheet_name: 以名稱形式選擇工作表(優(yōu)先)
:param sheet_index: 以下標(biāo)形式選擇工作表
:return: {'result': True, 'exception': None}
"""
if not self.data_dict:
return {'result': False, 'exception': '需要先調(diào)用 read_excel() 方法以讀取工作簿數(shù)據(jù)'}
if check_item is None:
check_item = []
if sheet_name:
if sheet_name not in self.data_dict:
return {'result': False, 'exception': '不存在名為 {0} 的工作表'.format(sheet_name)}
# 直接獲得對應(yīng)的工作表數(shù)據(jù)
data_sheet = self.data_dict[sheet_name]
else:
# 通過下標(biāo)獲取對應(yīng)的工作表名稱
data_dict_keys = tuple(self.data_dict.keys())
if len(data_dict_keys) <= int(sheet_index):
return {'result': False, 'exception': '不存在下標(biāo)為 {0} 的工作表'.format(sheet_index)}
_sheet_name = data_dict_keys[int(sheet_index)]
# 間接獲得對應(yīng)的工作表數(shù)據(jù)
data_sheet = self.data_dict[_sheet_name]
# 判斷工作表中是否包含業(yè)務(wù)需要的所有標(biāo)題
if not set(check_item).issubset(set(data_sheet['title_row'])):
return {'result': False, 'exception': '工作表中未包含業(yè)務(wù)需要的 {0} 標(biāo)題'.format(check_item)}
return {'result': True, 'exception': None}
if __name__ == '__main__':
excel_to_dict = ExcelToDict('C:/Users/hekaiyou/Desktop/新建 Microsoft Excel 工作表.xlsx')
excel_to_dict.open_object()
print('工作簿對象:', excel_to_dict.work_book)
excel_to_dict.read_excel()
print('工作簿數(shù)據(jù):', excel_to_dict.data_dict)
print('工作簿校驗(異常演示):', excel_to_dict.check(['標(biāo)題四']))
print('工作簿校驗(正常演示):', excel_to_dict.check(['標(biāo)題一', '標(biāo)題二']))
運行上面的代碼,命令行輸出效果如下:
工作簿對象: <openpyxl.workbook.workbook.Workbook object at 0x000001D5CBAF9308>
工作簿數(shù)據(jù): {'Sheet1': {'title_row': ['標(biāo)題一', '標(biāo)題二', '標(biāo)題三'], 'value_row': {0: {'標(biāo)題一': '一1', '標(biāo)題二': '二1', '標(biāo)題三': '三1'}, 1: {'標(biāo)題一': '一2', '標(biāo)題二': '二2', '標(biāo)題三': '三2'}}}}
工作簿校驗(異常演示): {'result': False, 'exception': "工作表中未包含業(yè)務(wù)需要的 ['標(biāo)題四'] 標(biāo)題"}
工作簿校驗(正常演示): {'result': True, 'exception': None}
創(chuàng)建工作簿
不需要提前在文件系統(tǒng)上創(chuàng)建文件奢人,直接就可以使用 openpyxl
開始創(chuàng)建表格攻走。先導(dǎo)入 Workbook
類殷勘,再使用 Workbook.active
方法獲取一個工作表:
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
默認(rèn)情況下 Workbook.active(value)
方法中的 value
為 0,即使用此方法獲得第一個工作表昔搂,我們可以修改此值玲销。也可以使用 Workbook.create_sheet()
方法創(chuàng)建新的工作表:
# 在末尾插入(默認(rèn))
ws1 = wb.create_sheet("Mysheet")
# 插入第一個位置
ws2 = wb.create_sheet("Mysheet", 0)
# 倒數(shù)第二個位置插入
ws3 = wb.create_sheet("Mysheet", -1)
我們可以隨時通過 Worksheet.title
屬性更改工作表名稱:
ws.title = "New Title"
默認(rèn)情況下,工作表選項卡的背景顏色為白色摘符,我們可以通過 Worksheet.sheet_properties.tabColor
屬性修改顏色:
ws.sheet_properties.tabColor = "1072BA"
給工作表命名后贤斜,就可以將其作為工作簿的鍵值,以指向?qū)?yīng)的工作表逛裤,并可以使用 Workbook.sheetname
屬性查看工作簿中所有工作表的名稱蠢古,亦可以遍歷工作表:
ws3 = wb["New Title"]
print(wb.sheetnames) # ['Sheet2', 'New Title', 'Sheet1']
for sheet in wb:
print(sheet.title)
我們可以復(fù)制某個工作簿,創(chuàng)建一個副本别凹。該行為僅復(fù)制單元格(值、樣式洽糟、超鏈接炉菲、注釋)和某些工作表屬性(尺寸堕战、格式、屬性)拍霜,如果工作簿以 read-only 或 write-only 只讀模式打開嘱丢,則不能復(fù)制工作表:
source = wb.active
target = wb.copy_worksheet(source)
操作數(shù)據(jù)
單元格可以直接作為工作表中的鍵值進(jìn)行訪問,例如返回 A4 處的單元格祠饺,如果不存在則創(chuàng)建一個單元格越驻,可以直接分配值:
c = ws['A4']
ws['A4'] = 4
一個單元格
通過 Worksheet.cell()
方法可以使用 行 和 列 定位要訪問的單元格:
d = ws.cell(row=4, column=2, value=10)
多個單元格
我們可以通過切片訪問單元格范圍,行或列的范圍可以用類似方法獲得:
cell_range = ws['A1':'C2']
colC = ws['C']
col_range = ws['C:D']
row10 = ws[10]
row_range = ws[5:10]
也可以使用 Worksheet.iter_rows()
或 Worksheet.iter_cols()
方法獲取行道偷、列缀旁,但是由于性能原因,這兩個方法在 只讀 模式下不可用:
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>
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>
如果需要遍歷所有行或列勺鸦,則可以使用 Worksheet.rows
或 Worksheet.columns
屬性并巍,但是同樣在 只讀 模式下不可用:
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>))
tuple(ws.columns)
# ((<Cell Sheet.A1>,
# <Cell Sheet.A2>,
# <Cell Sheet.A3>,
# <Cell Sheet.A4>,
# <Cell Sheet.A5>,
# <Cell Sheet.A6>,
# ...
# <Cell Sheet.C9>))
僅值
如果只需要工作表中的值,則可以使用 Worksheet.columns
屬性换途,這會遍歷工作表中的所有行懊渡,但僅返回單元格的值:
for row in ws.values:
for value in row:
print(value)
通過 Worksheet.iter_rows()
并 Worksheet.iter_cols()
可以獲取 values_only
參數(shù),只返回單元格的值:
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)
如果我們只需要工作表的 最大行數(shù) 和 最大列數(shù)坎吻,可以使用 max_row
和 max_column
屬性獲取八秃,例如一個4行15列的工作表:
print(ws.max_row) # 4
print(ws.max_column) # 15
數(shù)據(jù)存儲
有了 Cell
就可以為其分配一個值:
c.value = 'hello, world'
print(c.value) # 'hello, world'
d.value = 3.14
print(d.value) # 3.14
保存到文件
保存工作簿的最簡單漠秋、安全的方法是使用對象的 Workbook.save()
方法:
wb = Workbook()
wb.save('balances.xlsx')
如果文件已經(jīng)存在,此操作將覆蓋現(xiàn)有文件肾档,不會拋出異常或警告漓拾。
另存為流
如果要將文件保存到流中阁最,例如在使用Web應(yīng)用程序(Pyramid、Flask骇两、Django)時速种,只需使用 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.xltx')
或?qū)?template
屬性設(shè)置為 False
(默認(rèn))低千,以另存為文檔:
wb = load_workbook('document_template.xltx')
wb.template = False
wb.save('document.xlsx', as_template=False)
從文件加載
可以通過 openpyxl.load_workbook()
打開現(xiàn)有的工作簿:
from openpyxl import load_workbook
wb2 = load_workbook('test.xlsx')
print(wb2.sheetnames) # ['Sheet2', 'New Title', 'Sheet1']
實例
寫工作簿
from openpyxl import Workbook
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) # 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
使用數(shù)字格式
import datetime
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
# 使用 Python datetime 設(shè)置日期
ws['A1'] = datetime.datetime(2010, 7, 21)
ws['A1'].number_format # 'yyyy-mm-dd h:mm:ss'
使用公式
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
# 添加一個簡單的公式
ws["A1"] = "=SUM(1, 1)"
wb.save("formula.xlsx")
需要注意配阵,函數(shù)名稱必須為 英文 名稱,函數(shù)參數(shù)必須用 逗號 分隔示血。openpyxl
不會評估公式棋傍,但是可以檢查公式的名稱:
from openpyxl.utils import FORMULAE
print("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 three logos below'
# 創(chuàng)建圖像
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)
ws.row_dimensions.group(1,10, hidden=True)
wb.save('group.xlsx')