Excel 神器 —— OpenPyXl

原文轉(zhuǎn)自：濤哥聊Python

以下文章來源于Python技術(shù) ，作者派森醬

Python技術(shù)

Python技術(shù)

image

無論是日常辦公還是編程摄悯，總是離不開 Excel赞季，用來導入導出數(shù)據(jù)，記錄數(shù)據(jù)奢驯，統(tǒng)計分析申钩，畫原型，甚至在日本有位老爺爺用 Excel 來創(chuàng)作繪畫

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

Python 下的 Excel 庫

Python 中有大量的原生和第三方 Excel 操作包狐蜕，各有所長，不過對于剛使用 Python 與 Excel 交互的同學來說卸夕，可能有點目不暇接馏鹤，所以先簡單梳理一下常見的一些 Excel 包

OpenPyXL 是個讀寫 Excel 2010 xlsx/xlsm/xltx/xltm 的 Python 庫，簡單易用娇哆，功能廣泛湃累，單元格格式/圖片/表格/公式/篩選/批注/文件保護等等功能應有盡有，圖表功能是其一大亮點
xlwings 是一個基于 BSD 授權(quán)協(xié)議的 Python 庫碍讨，可以輕松的使用 Python 操作 Excel治力，也可以在 Excel 中調(diào)用 Python，以接近 VBA 語法的實現(xiàn) Excel 編程勃黍，支持 Excel 宏宵统，并且可以作為 Web 服務器，提供 REST API 接口
pandas 數(shù)據(jù)處理是 pandas 的立身之本，Excel 作為 pandas 輸入/輸出數(shù)據(jù)的容器
win32com 從命名上就可以看出马澈，這是一個處理 windows 應用的擴展瓢省，Excel 只是該庫能實現(xiàn)的一小部分功能。該庫還支持 office 的眾多操作痊班。需要注意的是勤婚，該庫不單獨存在，可通過安裝 pypiwin32 或者 pywin32 獲取
Xlsxwriter 擁有豐富的特性涤伐，支持圖片/表格/圖表/篩選/格式/公式等馒胆，功能與 openpyxl 相似，優(yōu)點是相比 openpyxl 還支持 VBA 文件導入凝果，迷你圖等功能祝迂，缺點是不能打開/修改已有文件，意味著使用 xlsxwriter 需要從零開始
DataNitro 一個 Excel 的付費插件器净，內(nèi)嵌到 Excel 中型雳，可完全替代 VBA，在 Excel 中使用 python 腳本山害。既然被稱為 Excel 中的 python纠俭，同時可以與其他 python 庫協(xié)同。
xlutils 基于 xlrd/xlwt粗恢，老牌 python 包柑晒，算是該領(lǐng)域的先驅(qū)，功能特點中規(guī)中矩眷射，比較大的缺點是僅支持 xls 文件匙赞。

概括一下：

不想使用 GUI 而又希望賦予 Excel 更多的功能，openpyxl 與 xlsxwriter妖碉，二者可選其一涌庭；
需要進行科學計算，處理大量數(shù)據(jù)欧宜，建議 pandas+xlsxwriter 或者 pandas + openpyxl坐榆，是不錯的選擇；
想要寫 Excel 腳本冗茸，會 Python 但不會 VBA席镀，可考慮 xlwings 或 DataNitro；
win32com 功能還是性能都很強大夏漱，不過需要一定的 windows 編程經(jīng)驗才能上手豪诲，它相當于是 windows COM 的封裝，另外文檔不夠完善

OpenPyXL

OpenPyXl 幾乎可以實現(xiàn)所有的 Excel 功能挂绰，而且接口清晰屎篱，文檔豐富，學習成本相對較低，今天就以 OpenPyXL 為例交播，了解下如何操作 Excel

安裝

用 pip 安裝

pip install openpyxl

安裝成功后重虑，可以跑通下面測試：

python -c "import openpyxl"

基本概念

workbook 相當于一個 Excel 文件檔，每個被創(chuàng)建和打開的 Excel 文件都是獨立的 Workbook 對象
sheet Excel 文檔中的表單秦士，每個 Excel 文檔至少需要一個 sheet
cell 單元格缺厉，是不可分割的基本數(shù)據(jù)存儲單元

小試牛刀

先來看跑個測試

from openpyxl import Workbook

需要注意的是：

新創(chuàng)建的 workbook 對象，會自帶一個名為 Sheet 的表單伍宦，Office Excel 新建會創(chuàng)建 3 個
創(chuàng)建的 workbook 會將第一個 表單 激活芽死，通過 wb.active 獲取引用
像 python-docx work 庫一樣乏梁，save 方法會立即保存次洼，不會有任何提示，建議選擇不同文件名來保存

常用功能

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

創(chuàng)建和打開 Excel

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

如果要加載一個已存在的 Excel 文件亥啦，需要用 load_workbook 方法，給定文件路徑练链，返回 workbook 對象：

from openpyxl import load_workbook

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

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

操作 sheet

from openpyxl import Workbook

每個 Workbook 中都有一個被激活的 sheet，一般都是第一個潮模，可以通過 active 直接獲取
可以通過 sheet 名來獲取 sheet 對象
創(chuàng)建 sheet時需要提供 sheet 名稱參數(shù)亮蛔，如果該名稱的 sheet 已經(jīng)存在，則會在名稱后添加 1擎厢，再有重復添加 2究流，以此類推
獲得 sheet 對象后，可以設(shè)置名稱（title）动遭，背景色等屬性
同一個 Workbook 對象中芬探，可以復制 sheet，需要將源 sheet 對象作為參數(shù)沽损，復制的新 sheet 會在最末尾
可以刪除一個 sheet灯节，參數(shù)是目標 sheet 對象

操作單元格

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

OpenPyXl 可以操作單個單元格，也可以批量操作單元格

單獨操作

單獨操作炎疆，即通過 Excel 單元格名稱或者行列坐標獲取單元格卡骂，進行操作

ws1 = wb.create_sheet("Mysheet")  #創(chuàng)建一個sheet

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

批量操作

需要一下子操作多個單元格時形入，可以用批量操作來提高效率

指定行列

# 操作單列

所有行或者列

# 所有行

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

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

合并單元格

# 合并

sheet 對象的 merge_cells 方法是合并單元格全跨，unmerge_cells 是解除合并
分別有兩種參數(shù)形式，一種是用單元格名稱方式指定亿遂，另一種是通過命名參數(shù)指定
注意：對于沒有合并過單元格的位置調(diào)用 unmerge_cells 時會報錯

單元格格式

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

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

from openpyxl.styles import Font, PatternFill, Border, Side, Alignment, Protection

引入字體類
用 cell 方法浓若，為單元格設(shè)置值的同時，設(shè)置格式
每種格式都有特定的屬性蛇数，為其設(shè)置特定的格式對象
數(shù)字格式有點區(qū)別挪钓，通過設(shè)置格式名稱來完成，numbers.FORMAT_PERCENTAGE 是個字符串
Border 類耳舅，需要配合 Side 類使用碌上，它們都在 openpyxl.styles 中定義
需要注意的是，單元格樣式屬性只能通過樣式對象賦予浦徊，而無法通過樣式屬性來修改馏予，例如 ws.cell(1, 1).font.color = '00FF00' 會報錯，如果真要換盔性，需要重新創(chuàng)建一個樣式實體霞丧，重新賦值

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

font = Font(bold=True)

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

圖表

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

柱狀圖

from openpyxl import Workbook

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

image

圓餅圖

from openpyxl import Workbook

引入餅圖類 PieChart 和數(shù)據(jù)應用類 Reference
創(chuàng)建圖表數(shù)據(jù)
創(chuàng)建圖表對象茫打，設(shè)置圖表標題
定義標簽數(shù)據(jù)引用和數(shù)據(jù)引用居触，并將其加入到圖表
將圖表對象添加到 sheet 的指定位置

image

總結(jié)

今天以 OpenPyXl 庫為例妖混，了解了 Python 操作 Excel 的基本方法，限于篇幅轮洋，無法全面的清晰的介紹更多功能制市，期望通過這篇短文，激發(fā)起您多程序化操作 Excel 的興趣弊予，讓讓工作祥楣、學習更高效，就如那句名言一樣：“ 人生苦短汉柒，我用 Python”

參考

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