Excel 神器 —— OpenPyXl

無(wú)論是日常辦公還是編程，總是離不開(kāi) Excel，用來(lái)導(dǎo)入導(dǎo)出數(shù)據(jù)，記錄數(shù)據(jù)，統(tǒng)計(jì)分析，畫原型，甚至在日本有位老爺爺用 Excel 來(lái)創(chuàng)作繪畫

雖然 Excel 功能強(qiáng)大，操作便利，但是有些場(chǎng)景下還是不太方便，例如 將大量數(shù)據(jù)導(dǎo)入到 Excel，將 Excel 中的數(shù)據(jù)讀取到系統(tǒng)中，或者按照某種結(jié)構(gòu)格式化下原有數(shù)據(jù)，批量處理大量 Excel 文檔等，幸運(yùn)的是，有很多 Python 庫(kù)可以幫助我們用程序來(lái)控制 Excel，完成難以手工完成的任務(wù)，現(xiàn)在就來(lái)了解下吧

Python 下的 Excel 庫(kù)

Python 中有大量的原生和第三方 Excel 操作包，各有所長(zhǎng)，不過(guò)對(duì)于剛使用 Python 與 Excel 交互的同學(xué)來(lái)說(shuō)，可能有點(diǎn)目不暇接，所以先簡(jiǎn)單梳理一下常見(jiàn)的一些 Excel 包

• OpenPyXL?是個(gè)讀寫 Excel 2010 xlsx/xlsm/xltx/xltm 的 Python 庫(kù)，簡(jiǎn)單易用，功能廣泛，單元格格式/圖片/表格/公式/篩選/批注/文件保護(hù)等等功能應(yīng)有盡有，圖表功能是其一大亮點(diǎn)
• xlwings?是一個(gè)基于 BSD 授權(quán)協(xié)議的 Python 庫(kù)，可以輕松的使用 Python 操作 Excel，也可以在 Excel 中調(diào)用 Python，以接近 VBA 語(yǔ)法的實(shí)現(xiàn) Excel 編程，支持 Excel 宏，并且可以作為 Web 服務(wù)器，提供 REST API 接口
• pandas?數(shù)據(jù)處理是 pandas 的立身之本，Excel 作為 pandas 輸入/輸出數(shù)據(jù)的容器
• win32com?從命名上就可以看出，這是一個(gè)處理 windows 應(yīng)用的擴(kuò)展，Excel 只是該庫(kù)能實(shí)現(xiàn)的一小部分功能。該庫(kù)還支持 office 的眾多操作。需要注意的是，該庫(kù)不單獨(dú)存在，可通過(guò)安裝 pypiwin32 或者 pywin32 獲取
• Xlsxwriter?擁有豐富的特性，支持圖片/表格/圖表/篩選/格式/公式等，功能與 openpyxl 相似，優(yōu)點(diǎn)是相比 openpyxl 還支持 VBA 文件導(dǎo)入，迷你圖等功能，缺點(diǎn)是不能打開(kāi)/修改已有文件，意味著使用 xlsxwriter 需要從零開(kāi)始
• DataNitro?一個(gè) Excel 的付費(fèi)插件，內(nèi)嵌到 Excel 中，可完全替代 VBA，在 Excel 中使用 python 腳本。既然被稱為 Excel 中的 python，同時(shí)可以與其他 python 庫(kù)協(xié)同。
• xlutils?基于 xlrd/xlwt，老牌 python 包，算是該領(lǐng)域的先驅(qū)，功能特點(diǎn)中規(guī)中矩，比較大的缺點(diǎn)是僅支持 xls 文件。

概括一下：

• 不想使用 GUI 而又希望賦予 Excel 更多的功能，openpyxl 與 xlsxwriter，二者可選其一；
• 需要進(jìn)行科學(xué)計(jì)算，處理大量數(shù)據(jù)，建議 pandas+xlsxwriter 或者 pandas + openpyxl，是不錯(cuò)的選擇；
• 想要寫 Excel 腳本，會(huì) Python 但不會(huì) VBA，可考慮 xlwings 或 DataNitro；
• win32com 功能還是性能都很強(qiáng)大，不過(guò)需要一定的 windows 編程經(jīng)驗(yàn)才能上手，它相當(dāng)于是 windows COM 的封裝，另外文檔不夠完善

OpenPyXL

OpenPyXl 幾乎可以實(shí)現(xiàn)所有的 Excel 功能，而且接口清晰，文檔豐富，學(xué)習(xí)成本相對(duì)較低，今天就以 OpenPyXL 為例，了解下如何操作 Excel

安裝

用 pip 安裝

pip install openpyxl

安裝成功后，可以跑通下面測(cè)試：

python -c "import openpyxl"

基本概念

• workbook 相當(dāng)于一個(gè) Excel 文件檔，每個(gè)被創(chuàng)建和打開(kāi)的 Excel 文件都是獨(dú)立的 Workbook 對(duì)象
• sheet Excel 文檔中的表單，每個(gè) Excel 文檔至少需要一個(gè) sheet
• cell 單元格，是不可分割的基本數(shù)據(jù)存儲(chǔ)單元

小試牛刀

先來(lái)看跑個(gè)測(cè)試

from openpyxl import Workbook# 創(chuàng)建一個(gè) workbookwb = Workbook()# 獲取被激活的 worksheetws = wb.active# 設(shè)置單元格內(nèi)容ws['A1'] = 42# 設(shè)置一行內(nèi)容ws.append([1, 2, 3])# python 數(shù)據(jù)類型可以被自動(dòng)轉(zhuǎn)換import datetimews['A2'] = datetime.datetime.now()# 保存 Excel 文件wb.save("sample.xlsx")

需要注意的是：

• 新創(chuàng)建的 workbook 對(duì)象，會(huì)自帶一個(gè)名為 Sheet 的表單，Office Excel 新建會(huì)創(chuàng)建 3 個(gè)
• 創(chuàng)建的 workbook 會(huì)將第一個(gè)?表單?激活，通過(guò) wb.active 獲取引用
• 像?python-docx?work 庫(kù)一樣，save 方法會(huì)立即保存，不會(huì)有任何提示，建議選擇不同文件名來(lái)保存

常用功能

OpenPyXl 功能很多，從單元格處理到圖表展示，涵蓋了幾乎全部的 Excel 功能，這里就一些常用的功能做展示，更多的用法可以參考 OpenPyXl 文檔（文末參考里有鏈接）

創(chuàng)建和打開(kāi) Excel

小試牛刀部分看到了如何創(chuàng)建一個(gè) Excel

如果要加載一個(gè)已存在的 Excel 文件，需要用?load_workbook?方法，給定文件路徑，返回 workbook 對(duì)象：

from openpyxl import load_workbook
wb = load_workbook('test.xlsx')
# 顯示文檔中包含的 表單 名稱print(wb.sheetnames)

load_workbook?除了參數(shù)?filename外為還有一些有用的參數(shù)：

• read_only：是否為只讀模式，對(duì)于超大型文件，要提升效率有幫助
• keep_vba?：是否保留 vba 代碼，即打開(kāi) Excel 文件時(shí)，開(kāi)啟并保留宏
• guess_types：是否做在讀取單元格數(shù)據(jù)類型時(shí)，做類型判斷
• data_only：是否將公式轉(zhuǎn)換為結(jié)果，即包含公式的單元格，是否顯示最近的計(jì)算結(jié)果
• keep_links：是否保留外部鏈接

操作 sheet

from openpyxl import Workbookwb = Workbook()ws = wb.active
ws1 = wb.create_sheet("sheet")  #創(chuàng)建一個(gè) sheet 名為 sheetws1.title = "新表單"  # 設(shè)置 sheet 標(biāo)題ws2 = wb.create_sheet("mysheet", 0) # 創(chuàng)建一個(gè) sheet，插入到最前面 默認(rèn)插在后面ws2.title = u"你好"  # 設(shè)置 sheet 標(biāo)題
ws1.sheet_properties.tabColor = "1072BA"  # 設(shè)置 sheet 標(biāo)簽背景色
# 獲取 sheetws3 = wb.get_sheet_by_name(u"你好")ws4 = wb['New Title']
# 復(fù)制 sheetws1_copy = wb.copy_worksheet(ws1)
# 刪除 sheetwb.remove(ws1)

• 每個(gè) Workbook 中都有一個(gè)被激活的 sheet，一般都是第一個(gè)，可以通過(guò) active 直接獲取
• 可以通過(guò) sheet 名來(lái)獲取 sheet 對(duì)象
• 創(chuàng)建 sheet時(shí)需要提供 sheet 名稱參數(shù)，如果該名稱的 sheet 已經(jīng)存在，則會(huì)在名稱后添加 1，再有重復(fù)添加 2，以此類推
• 獲得 sheet 對(duì)象后，可以設(shè)置 名稱（title），背景色等屬性
• 同一個(gè) Workbook 對(duì)象中，可以復(fù)制 sheet，需要將源 sheet 對(duì)象作為參數(shù)，復(fù)制的新 sheet 會(huì)在最末尾
• 可以刪除一個(gè) sheet，參數(shù)是目標(biāo) sheet 對(duì)象

操作單元格

單元格（cell）是 Excel 中存放數(shù)據(jù)的最小單元，就是圖形界面中的一個(gè)個(gè)小格子

OpenPyXl 可以操作單個(gè)單元格，也可以批量操作單元格

單獨(dú)操作

單獨(dú)操作，即通過(guò) Excel 單元格名稱或者行列坐標(biāo)獲取單元格，進(jìn)行操作

ws1 = wb.create_sheet("Mysheet")  #創(chuàng)建一個(gè)sheet# 通過(guò)單元格名稱設(shè)置ws1["A1"]=123.11ws1["B2"]="你好"
# 通過(guò)行列坐標(biāo)設(shè)置d = ws1.cell(row=4, column=2, value=10)

• 可以通過(guò)單元格名稱設(shè)置，類似于 sheet 的某種屬性
• 也可以通過(guò)行列坐標(biāo)類設(shè)置

批量操作

需要一下子操作多個(gè)單元格時(shí)，可以用批量操作來(lái)提高效率

• 指定行列

# 操作單列for cell in ws["A"]:    print(cell.value)# 操作單行for cell in ws["1"]:    print(cell.value)# 操作多列for column in ws['A:C']:    for cell in column:        print(cell.value)# 操作多行for row in ws['1:3']:    for cell in row:        print(cell.value)# 指定范圍for row in ws['A1:C3']:    for cell in row:        print(cell.value)

• 所有行或者列

# 所有行for row in ws.iter_rows():    for cell in row:        print(cell.value)#?所有列for column in ws.iter_cols():    for cell in column:        print(cell.value)

• 設(shè)置整行數(shù)據(jù)

ws.append((1,2,3))

合并單元格

# 合并ws.merge_cells('A2:D2')# 解除合并ws.unmerge_cells('A2:D2')
ws.merge_cells(start_row=2,start_column=1,end_row=2,end_column=4)ws.unmerge_cells(start_row=2,start_column=1,end_row=2,end_column=4)

• sheet 對(duì)象的 merge_cells 方法是合并單元格，unmerge_cells 是解除合并
• 分別有兩種參數(shù)形式，一種是用單元格名稱方式指定，另一種是通過(guò)命名參數(shù)指定
• 注意：對(duì)于沒(méi)有合并過(guò)單元格的位置調(diào)用 unmerge_cells 時(shí)會(huì)報(bào)錯(cuò)

單元格格式

OpenPyXl 用6種類來(lái)設(shè)置單元格的樣式

• NumberFormat?數(shù)字
• Alignment?對(duì)齊
• Font?字體
• Border?邊框
• PatternFill?填充
• Protection?保護(hù)

from openpyxl.styles import Font, PatternFill, Border, Side, Alignment, Protectionfrom openpyxl.styles import numbers
wb = Workbook()ws = wb.activews.cell(row=1, column=1, value='宋體').font = Font(name=u'宋體', size=12, bold=True, color='FF0000')ws.cell(row=2, column=2, value='右對(duì)齊').alignment = Alignment(horizontal='right')ws.cell(row=3, column=3, value='填充漸變色').fill = PatternFill(fill_type='solid', start_color='FF0000')ws.cell(row=4, column=4, value='設(shè)置邊線').border = Border(left=Side(border_style='thin', color='FF0000'), right= Side(border_style='thin', color='FF0000'))ws.cell(row=5, column=5, value='受保護(hù)的').protection = Protection(locked=True, hidden=True)ws.cell(row=6, column=6, value=0.54).number_format =numbers.FORMAT_PERCENTAGE

• 引入字體類
• 用 cell 方法，為單元格設(shè)置值的同時(shí)，設(shè)置格式
• 每種格式都有特定的屬性，為其設(shè)置特定的格式對(duì)象
• 數(shù)字格式有點(diǎn)區(qū)別，通過(guò)設(shè)置格式名稱來(lái)完成，numbers.FORMAT_PERCENTAGE 是個(gè)字符串
• Border 類，需要配合 Side 類使用，它們都在 openpyxl.styles 中定義
• 需要注意的是，單元格樣式屬性只能通過(guò)樣式對(duì)象賦予，而無(wú)法通過(guò)樣式屬性來(lái)修改，例如?ws.cell(1, 1).font.color = '00FF00'?會(huì)報(bào)錯(cuò)，如果真要換，需要重新創(chuàng)建一個(gè)樣式實(shí)體，重新賦值

上面展示的是單個(gè)單元格格式的設(shè)置，也可以批量設(shè)置，有兩種方式，一種是循環(huán)范圍內(nèi)的所有單元格，逐個(gè)設(shè)置，另一種是對(duì)整列或者整行設(shè)置：

font = Font(bold=True)
# 遍歷范圍內(nèi)的單元格for row in ws['A1:C3']:    for cell in row:        cell.font = font
# 設(shè)置整行row = ws.row_dimensions[1]row.font = font
# 設(shè)置整列column = ws.column_dimensions["A"]column.font = font

更多樣式類的定義和參數(shù)，可參 OpenPyXl 文檔

圖表

圖表是 Excel 中很重要的部分，作為數(shù)據(jù)可視化的高效工具，利用 OpenPyXl 可以用編程的方式，在 Excel 中制作圖表，創(chuàng)建過(guò)程和直接在 Excel 中差不多，下面以柱狀圖和圓餅圖為例做演示

柱狀圖

from openpyxl import Workbookfrom openpyxl.chart import BarChart, Reference
wb = Workbook()ws = wb.active
rows = [    ('月份', '蘋果', '香蕉'),    (1, 43, 25),    (2, 10, 30),    (3, 40, 60),    (4, 50, 70),    (5, 20, 10),    (6, 10, 40),    (7, 50, 30),]
for row in rows:    ws.append(row)
chart1 = BarChart()chart1.type = "col"chart1.style = 10chart1.title = "銷量柱狀圖"chart1.y_axis.title = '銷量'chart1.x_axis.title = '月份'
data = Reference(ws, min_col=2, min_row=1, max_row=8, max_col=3)series = Reference(ws, min_col=1, min_row=2, max_row=8)chart1.add_data(data, titles_from_data=True)chart1.set_categories(series)ws.add_chart(chart1, "A10")

• 引入柱狀圖類 BarChart 和 數(shù)據(jù)應(yīng)用類 Reference
• 創(chuàng)建 Workbook，并為活動(dòng) Sheet 添加數(shù)據(jù)
• 創(chuàng)建柱狀圖對(duì)象，設(shè)置圖表屬性，type 為?col?為列狀圖，bar?為水平圖
• 創(chuàng)建數(shù)據(jù)引用對(duì)象，指定從那個(gè) sheet 以及數(shù)據(jù)范圍
• 創(chuàng)建系列數(shù)據(jù)引用對(duì)象
• 將數(shù)據(jù)和系列加入到圖表對(duì)象中
• 最后將圖表對(duì)象用 add_chart 添加到 sheet 里

圓餅圖

from openpyxl import Workbookfrom openpyxl.chart import PieChart, Reference
data = [    ['水果', '銷量'],    ['蘋果', 50],    ['櫻桃', 30],    ['橘子', 10],    ['香蕉', 40],]
wb = Workbook()ws = wb.active
for row in data:    ws.append(row)
pie = PieChart()pie.title = "水果銷量占比"labels = Reference(ws, min_col=1, min_row=2, max_row=5)data = Reference(ws, min_col=2, min_row=1, max_row=5)pie.add_data(data, titles_from_data=True)pie.set_categories(labels)
ws.add_chart(pie, "D1")

• 引入餅圖類 PieChart 和 數(shù)據(jù)應(yīng)用類 Reference
• 創(chuàng)建圖表數(shù)據(jù)
• 創(chuàng)建圖表對(duì)象，設(shè)置圖表標(biāo)題
• 定義標(biāo)簽數(shù)據(jù)引用和數(shù)據(jù)引用，并將其加入到圖表
• 將圖表對(duì)象添加到 sheet 的指定位置

總結(jié)

今天以 OpenPyXl 庫(kù)為例，了解了 Python 操作 Excel 的基本方法，限于篇幅，無(wú)法全面的清晰的介紹更多功能，期望通過(guò)這篇短文，激發(fā)起您多程序化操作 Excel 的興趣，讓讓工作、學(xué)習(xí)更高效，就如那句名言一樣：“ 人生苦短，我用 Python”

參考

• OpenPyXl 文檔 https://openpyxl.readthedocs.io
• Excel 作畫 https://zhuanlan.zhihu.com/p/34917620
• https://www.jianshu.com/p/be1ed0c5218e
• https://www.douban.com/note/706513912/
  https://blog.csdn.net/weixin_41595432/article/details/79349995

用Python制作可視化GUI界面，順便實(shí)現(xiàn)自動(dòng)分類整理文件！
安裝這5個(gè)插件后，PyCharm好用到起飛！