1 前言

最近需要頻繁讀寫 excel 文件懊烤，想通過程序?qū)?excel 文件進(jìn)行自動(dòng)化處理，發(fā)現(xiàn)使用 python 的 openpyxl 庫進(jìn)行 excel 文件讀寫實(shí)在太方便了宽堆，結(jié)構(gòu)清晰腌紧，操作簡單。本文對(duì) openpyxl 的使用進(jìn)行總結(jié)畜隶，主要包含以下內(nèi)容：

openpyxl 的介紹及 excel 文件結(jié)構(gòu)說明

工作表的讀寫處理

行列的讀寫處理

單元格的讀寫處理

2 openpyxl 及 excel 文件結(jié)構(gòu)

openpyxl 是一個(gè)對(duì) xlsx/xlsm/xltx/xltm 格式的 2010 excel 文檔進(jìn)行讀寫的 python 庫壁肋。它官網(wǎng)有詳細(xì)的文檔介紹。在進(jìn)行使用前籽慢，需先安裝并引入

# 安裝pipinstall openpyxl# 引入openpyxl 模塊importopenpyxl復(fù)制代碼

在進(jìn)行 excel 操作之前浸遗，先對(duì) excel 的文件結(jié)構(gòu)做一個(gè)簡單了解，以便于熟悉后續(xù)的操作箱亿。

這里說一句跛锌，小編是一名python開發(fā)工程師，我自己整理了一套最新的python系統(tǒng)學(xué)習(xí)教程届惋，包括從基礎(chǔ)的python腳本到web開發(fā)髓帽、爬蟲、數(shù)據(jù)分析脑豹、數(shù)據(jù)可視化氢卡、機(jī)器學(xué)習(xí)等。想要這些資料的可以關(guān)注小編晨缴，并在后臺(tái)私信小編译秦，或加學(xué)習(xí)群：930900780。

如下圖：

一個(gè) excel 文件击碗，其內(nèi)容按層次分為工作簿(文件) -> 工作表(sheet) -> 行列 -> 單元格 筑悴，對(duì)應(yīng)上圖，整個(gè) excel 文件即是一個(gè)工作簿稍途；工作簿下可以有多個(gè)工作表(如圖中的 Sheet1/test1 等等)阁吝；工作表中就是對(duì)應(yīng)的表格數(shù)據(jù)，分為行和列械拍，行是用序號(hào)表示突勇，列用大寫字母表示（也可用序號(hào)）装盯；行與列的交點(diǎn)就是每一個(gè)存儲(chǔ)數(shù)據(jù)的單元格。因此甲馋，我們對(duì) excel 表格進(jìn)行讀寫埂奈，基本按這個(gè)層次思路來操作：讀入文件，找到工作表定躏，遍歷行列账磺，定位單元格，對(duì)單元格進(jìn)行讀寫痊远。因此垮抗，會(huì)涉及到工作表、行列碧聪、單元格的讀寫操作冒版。這些操作之前，需要先把文件加載進(jìn)來逞姿，一個(gè) excel 文件就是一個(gè)工作簿 (workbook)壤玫，加載操作如下（示例中的 excel 文件為 text.xlsx）：

# 加載excel文件file_path ="E:/pythontest/test.xlsx"workbook = openpyxl.load_workbook(file_path)復(fù)制代碼

3 工作表處理

3.1 工作表讀取

工作表( sheet )會(huì)有多個(gè)，可以讀取全部的工作表哼凯，讀取單個(gè)時(shí)，可以按 sheet 名稱讀取楚里，也可以按下標(biāo)（下標(biāo)從0開始）断部。

全部工作表對(duì)象：workbook.worksheets

全部工作表名稱：workbook.sheetnames

按名稱(sheet_name)獲取工作表：workbook[sheet_name]

按下標(biāo)(i從0開始)獲取工作表：workbook.worksheets[i]

獲取正在使用的工作表：workbook.active

獲取工作表的屬性（如工作表名稱、最大行數(shù)和列數(shù)等）：sheet.title班缎、sheet.max_row蝴光、sheet.max_column

如下：

# 全部sheet對(duì)象>>> workbook.worksheets[, , ]# 全部sheet名稱>>> workbook.sheetnames['Sheet1','test1','test2']# 按名稱讀取sheet>>> workbook["Sheet1"]# 按下標(biāo)讀取>>> workbook.worksheets[0]# 獲取當(dāng)前正在使用的sheet>>> workbook.active# 獲取sheet的屬性>>> sheet_active.titleSheet1>>> sheet_active.max_row6>>> sheet_active.max_column3復(fù)制代碼

3.2 工作表添加

若需要新增工作表，按操作流程达址，先添加工作表蔑祟，再保存文件。創(chuàng)建通過create_sheet完成沉唠，創(chuàng)建后保存(save)文件疆虚，添加才能生效。

創(chuàng)建工作表满葛，若名稱相同径簿，則自動(dòng)進(jìn)行重命名：workbook.create_sheet("test3")

在指定的下標(biāo)創(chuàng)建工作表：workbook.create_sheet("test4",1)

保存文件，若文件路徑與打開的文件路徑相同嘀韧，則覆蓋篇亭；不同，則會(huì)復(fù)制原文件并保存（相當(dāng)于另存為）：workbook.save(file_path)

3.3 工作表修改

要修改工作表名稱锄贷，直接通過設(shè)置工作表的 title 即可译蒂，修改后同樣需要保存文件曼月。

# 修改工作表名稱>>> sheet1 = workbook['test1']>>> sheet1.title ='test11'# 保存文件>>> workbook.save(file_path)復(fù)制代碼

3.4 工作表刪除

刪除工作表，需要先獲取 sheet 對(duì)象柔昼，然后刪除哑芹。刪除有兩種方式，一是使用 workbook 提供的 remove 方法岳锁，也可以直接使用 python 的del進(jìn)行刪除绩衷。刪除操作后，同樣需要保存文件：

# remove刪除工作表sheet = workbook["test-1"]workbook.remove(sheet)# del操作刪除del workbook["test2"]# 保存文件workbook.save(file_path)復(fù)制代碼

4 行列處理

獲取 sheet 對(duì)象后激率，后續(xù)即可進(jìn)行行列操作咳燕，包括行列讀取，添加乒躺，刪除等招盲。

4.1 讀行列

獲取全部行和列，然后可以進(jìn)行遍歷：sheet.rows 嘉冒，sheet.columns

讀取部分行列：讀一行sheet[1],讀多行sheet[2:3]曹货，讀一列sheet['A']，讀多列sheet['B:C']

# 遍歷全部行>>> for row in sheet.rows:...? ? print(row)...(, , )(, , )....# 讀取部分行列>>> sheet[1](, , )>>> sheet["A:B"]((, , , , , ), (, , , , , ))復(fù)制代碼

4.2 添加行列

添加行列讳推，可以指定位置添加單個(gè)行列或多個(gè)行列顶籽。

直接在工作表中追加行數(shù)據(jù)：sheet.append(rowdata)

在指定 index（從1開始計(jì)算） 位置添加行列：sheet.insert_rows,sheet.insert_cols

# 在第4行插入1行空行>>> sheet.insert_rows(4)# 在第2行插入2行空行>>> sheet.insert_rows(idx=2,amount=2)# 添加一行數(shù)據(jù)到表>>> row_data = ["tom",15,"tom@test.com"]>>> sheet.append(row_data)# 保存修改內(nèi)容>>> workbook.save(file_path)復(fù)制代碼

4.3 刪除行列

刪除操作與插入行列操作方式一致，使用delete_rows及delete_cols方法银觅。

# 刪除行>>> sheet.delete_rows(2,2)>>> workbook.save(file_path)復(fù)制代碼

5 單元格處理

我們的數(shù)據(jù)最終是保存在每一個(gè)單元格（Cell）中礼饱，因此，最終我們操作數(shù)據(jù)其實(shí)就是單元格中的數(shù)據(jù)究驴，單元格中镊绪，openpyxl 使用是 Cell 對(duì)象。前面在遍歷行列數(shù)據(jù)時(shí)，可以看到輸出<Cell 'Sheet1'.A1>的內(nèi)容，這對(duì)應(yīng)的單元格對(duì)象溉奕。下面對(duì)單元格的操作進(jìn)行說明。

5.1 獲取單元格數(shù)據(jù)值及屬性值

定位獲取單元格有兩種方式：

直接指定行列名：sheet[A1]

使用 cell 函數(shù)（行列下標(biāo)從1開始）：sheet.cell(row=2,column=1)

# 指定行列坐標(biāo)獲取單元格>>> sheet["A1"]# cell函數(shù)獲取單元格>>> sheet.cell(row=1, column=1)復(fù)制代碼

獲取單元格對(duì)象后榄鉴，可以獲取數(shù)據(jù)值及其屬性，包括它所在的行列數(shù)蛉抓，坐標(biāo)牢硅，值等。

>>> cell = sheet["A2"]>>> cell.value'張三'>>> cell.coordinate'A2'>>> cell.column1>>> cell.row2復(fù)制代碼

5.2 移動(dòng)單元格

通過對(duì)單元格區(qū)域芝雪，可以向上减余、下、左惩系、右進(jìn)行移動(dòng)位岔，使用的是move_range(range,rows,cols)如筛，其中 rows 和 cols 為整數(shù)，正整數(shù)表示向下或向右抒抬，負(fù)整數(shù)為向上或向左杨刨。

# 移動(dòng)數(shù)據(jù)區(qū)域(向上移動(dòng)2行，向右移動(dòng)3列)擦剑，正整數(shù)為向下或向右妖胀，負(fù)整數(shù)為向上或向左sheet.move_range("A3:C3", rows=-2, cols=3)wb.save(file_path)復(fù)制代碼

5.3 合并拆分單元格

對(duì)于跨行和跨列，需要對(duì)單元格進(jìn)行合并惠勒，使用的是merge_cells(range_string, start_row, start_column, end_row, end_column)赚抡。如果要合并的單元格都有數(shù)據(jù)，只會(huì)保留左上角的數(shù)據(jù)纠屋，其他則丟棄涂臣。合并及拆分都可以通過行列坐標(biāo)（如A1）或者行列下標(biāo)（如1,2）進(jìn)行。

# 單元格合并售担，使用范圍坐標(biāo)sheet.merge_cells("A2:B3")# 單元格合并赁遗，指定行列下標(biāo)（下標(biāo)從1開始）sheet.merge_cells(start_row=5, start_column=3, end_row=7, end_column=4)wb.save(file_path)# 拆分單元格sheet.unmerge_cells("A2:B3")sheet.unmerge_cells(start_row=5, start_column=3, end_row=7, end_column=4)# 保存文件wb.save(file_path)復(fù)制代碼

5.4 寫入單元格

對(duì)單元格值進(jìn)行修改和寫入，直接對(duì)cell.value進(jìn)行賦值即可族铆。這里需要注意的是岩四，可以寫入 excel 公式，具體公式與 excel 中用到公式一致哥攘，另外剖煌，若是寫入公式，讀取時(shí)獲取到的 value 值也是公式献丑，而非公式值。

# 寫入值cell.value="張三"# 寫入公式(求平均值)cell.value="=AVERAGE(B2:B6)"復(fù)制代碼

5.5 設(shè)置單元格格式

單元格的格式包括行高侠姑，列寬创橄，字體、邊框莽红、對(duì)齊方式妥畏、填充顏色等。這些都在 openpyxl 的 styles 模塊中安吁。

行高/列寬：row_dimensions[row_num].height = xx醉蚁，sheet.column_dimensions[col_name].width = xx

字體( Font 對(duì)象)：包括字段名稱，大小鬼店、加粗网棍、斜體、顏色等妇智，F(xiàn)ont(name="微軟雅黑", size=20, bold=True, italic=True, color="000000")

邊框（ Border 對(duì)象和 Side 對(duì)象）：邊框每一條邊的格式大小/顏色Side(style="thin", color="000000")滥玷，通過邊構(gòu)建邊框?qū)ο螅築order(left=side, right=side, top=side, bottom=side)

對(duì)齊（ Alignment 對(duì)象）：垂直和水平對(duì)齊方向氏身，是否自動(dòng)換行。Alignment(horizontal="center", vertical="center", wrap_text=True)

填充顏色惑畴，分為普通顏色填充和漸變顏色填充：PatternFill(fill_type="solid", fgColor="FF0000")和 GradientFill(stop=("FF0000", "FD1111", "000000"))

# 設(shè)置行高和列寬sheet.row_dimensions[1].height = 50sheet.column_dimensions["A"].width = 20# 設(shè)置單元格字體cell = sheet["A1"]current_font = cell.fontfont = Font(name="微軟雅黑", size=20, bold=True, italic=True, color="000000")cell.font = font# 設(shè)置邊框(細(xì)邊蛋欣，黑色)side_style = Side(style="thin", color="000000")border = Border(left=side_style, right=side_style, top=side_style, bottom=side_style)cell.border = border# 居中對(duì)齊，自動(dòng)換行cell_alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)cell.alignment = cell_alignment# 填充顏色（紅色填充如贷，和紅色到黑色漸變填充）p_fill = PatternFill(fill_type="solid", fgColor="FF0000")g_fill = GradientFill(stop=("FF0000","FD1111","000000"))cell.fill = p_fillsheet["B1"].fill = g_fill復(fù)制代碼

最后注意的是陷虎，這些修改操作最后都需要通過保存操作（wb.save(file_path)）才能生效。

6 總結(jié)

通過上面的講解杠袱，了解如何使用 python 的 openpyxl 庫對(duì) excel 文檔的處理操作尚猿，可以發(fā)現(xiàn)它的操作邏輯相當(dāng)是清晰簡單的，符合的我們使用 excel 的習(xí)慣霞掺。處理流程基本是加載文件谊路、定位需要處理的工作表、行菩彬、列及單元格缠劝。對(duì)它們進(jìn)行讀、寫骗灶、修改格式等操作惨恭。因此，如果有自動(dòng)化處理 excel 文件的需求耙旦，用 openpyxl 吧脱羡，但它限制只能處理 2010 格式的 excel 文檔，對(duì)于舊格式（ xls ）的建議都統(tǒng)一換為新的格式再操作免都，或者也可以使用 xlrd 和 xlwt 模塊操作锉罐。

本文章素材來源于網(wǎng)絡(luò)，如有侵權(quán)請(qǐng)聯(lián)系刪除绕娘。