Python3 openpyxl庫

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")
openpyxl_sample.png

通用方案

通用讀取類

創(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) 方法中的 value0,即使用此方法獲得第一個工作表昔搂,我們可以修改此值玲销。也可以使用 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"
openpyxl_tabcolor.png

給工作表命名后贤斜,就可以將其作為工作簿的鍵值,以指向?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-onlywrite-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.rowsWorksheet.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_rowmax_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)用程序(PyramidFlask骇两、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)
openpyxl_writeaworkbook_3.png
openpyxl_writeaworkbook_2.png
openpyxl_writeaworkbook_1.png

讀工作簿

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')
openpyxl_foldoutline.png
最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末瘫拣,一起剝皮案震驚了整個濱河市,隨后出現(xiàn)的幾起案子告喊,更是在濱河造成了極大的恐慌麸拄,老刑警劉巖派昧,帶你破解...
    沈念sama閱讀 212,884評論 6 492
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件,死亡現(xiàn)場離奇詭異拢切,居然都是意外死亡蒂萎,警方通過查閱死者的電腦和手機(jī),發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,755評論 3 385
  • 文/潘曉璐 我一進(jìn)店門淮椰,熙熙樓的掌柜王于貴愁眉苦臉地迎上來五慈,“玉大人,你說我怎么就攤上這事主穗⌒豪梗” “怎么了?”我有些...
    開封第一講書人閱讀 158,369評論 0 348
  • 文/不壞的土叔 我叫張陵黔牵,是天一觀的道長聪轿。 經(jīng)常有香客問我,道長猾浦,這世上最難降的妖魔是什么陆错? 我笑而不...
    開封第一講書人閱讀 56,799評論 1 285
  • 正文 為了忘掉前任,我火速辦了婚禮金赦,結(jié)果婚禮上音瓷,老公的妹妹穿的比我還像新娘。我一直安慰自己夹抗,他們只是感情好绳慎,可當(dāng)我...
    茶點故事閱讀 65,910評論 6 386
  • 文/花漫 我一把揭開白布。 她就那樣靜靜地躺著漠烧,像睡著了一般杏愤。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上已脓,一...
    開封第一講書人閱讀 50,096評論 1 291
  • 那天珊楼,我揣著相機(jī)與錄音,去河邊找鬼度液。 笑死厕宗,一個胖子當(dāng)著我的面吹牛,可吹牛的內(nèi)容都是我干的堕担。 我是一名探鬼主播已慢,決...
    沈念sama閱讀 39,159評論 3 411
  • 文/蒼蘭香墨 我猛地睜開眼,長吁一口氣:“原來是場噩夢啊……” “哼霹购!你這毒婦竟也來了佑惠?” 一聲冷哼從身側(cè)響起,我...
    開封第一講書人閱讀 37,917評論 0 268
  • 序言:老撾萬榮一對情侶失蹤,失蹤者是張志新(化名)和其女友劉穎膜楷,沒想到半個月后乍丈,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體,經(jīng)...
    沈念sama閱讀 44,360評論 1 303
  • 正文 獨居荒郊野嶺守林人離奇死亡把将,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 36,673評論 2 327
  • 正文 我和宋清朗相戀三年,在試婚紗的時候發(fā)現(xiàn)自己被綠了忆矛。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片察蹲。...
    茶點故事閱讀 38,814評論 1 341
  • 序言:一個原本活蹦亂跳的男人離奇死亡,死狀恐怖催训,靈堂內(nèi)的尸體忽然破棺而出洽议,到底是詐尸還是另有隱情,我是刑警寧澤漫拭,帶...
    沈念sama閱讀 34,509評論 4 334
  • 正文 年R本政府宣布亚兄,位于F島的核電站,受9級特大地震影響采驻,放射性物質(zhì)發(fā)生泄漏审胚。R本人自食惡果不足惜,卻給世界環(huán)境...
    茶點故事閱讀 40,156評論 3 317
  • 文/蒙蒙 一礼旅、第九天 我趴在偏房一處隱蔽的房頂上張望膳叨。 院中可真熱鬧,春花似錦痘系、人聲如沸菲嘴。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,882評論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽龄坪。三九已至,卻和暖如春复唤,著一層夾襖步出監(jiān)牢的瞬間健田,已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 32,123評論 1 267
  • 我被黑心中介騙來泰國打工苟穆, 沒想到剛下飛機(jī)就差點兒被人妖公主榨干…… 1. 我叫王不留抄课,地道東北人。 一個月前我還...
    沈念sama閱讀 46,641評論 2 362
  • 正文 我出身青樓雳旅,卻偏偏與公主長得像跟磨,于是被迫代替她去往敵國和親。 傳聞我的和親對象是個殘疾皇子攒盈,可洞房花燭夜當(dāng)晚...
    茶點故事閱讀 43,728評論 2 351