可能是全網最完整的 Python 操作 Excel庫總結！
大家好，我是早起。
在之前的辦公自動化系列文章中，我已經對Python操作Excel的幾個常用庫openpyxl、xlrd/xlwt、xlwings、xlsxwriter等進行了詳細的講解。
為了進一步帶大家了解各個庫的異同，從而在不同場景下可以靈活使用，本文將橫向比較7個可以操作 Excel 文件的常用模塊，在比較各模塊常用操作的同時進行鞏固學習!
首先讓我們來整體把握下不同庫的特點
- xlrd、xlwt、xlutils 各自的功能都有局限性，但三者互為補充，覆蓋了Excel文件尤其是 .xls 文件的操作。xlwt 可以生成 .xls 文件，xlrd 可以讀取已經存在的 .xls 文件，xlutils 連接 xlrd 和 xlwt 兩個模塊，使用戶可以同時讀寫一個 .xls 文件。簡單來說，xlrd 負責讀、xlwt 負責寫、xlutils 負責提供輔助和銜接
- xlwings 能夠非常方便的讀寫 Excel 文件中的數據，并且能夠進行單元格格式的修改
- XlsxWriter 是一個用來寫 .xlsx 文件格式的模塊。它可以用來寫文本、數字、公式并支持單元格格式化、圖片、圖表、文檔配置、自動過濾等特性。但不能用來讀取和修改 Excel 文件
- openpyxl 通過 工作簿 “workbook - 工作表 sheet - 單元格 cell” 的模式對 .xlsx 文件進行讀、寫、改，并且可以調整樣式
- pandas 大家都不陌生，是進行數據處理和分析的強大模塊，有時也可以用來自動化處理Excel
如果你懶得看詳細的對比過程，可以直接看最后的總結圖，然后拉到文末收藏點贊就算學會了
一、安裝
7個模塊均為非標準庫，因此都需要在命令行中 pip 進行安裝：
- pip install xlrd
- pip install xlwt
- pip install xlutils
- pip install xlwings
- pip install XlsxWriter
- pip install openpyxl
- pip install pandas
二、模塊導入
多數模塊可以直接通過名字導入，有些模塊約定俗稱會使用縮寫：
- import xlrd
- import xlwt
- import xlwings as xw
- import xlsxwriter
- import openpyxl
- import pandas as pd
xlutils 模塊是 xlrd 和 xlwt 之間的橋梁，最核心的作用是拷貝一份通過 xlrd 讀取到內存中的 .xls 對象，然后再拷貝對象上通過 xlwt 修改 .xls 表格的內容。xlutils 可以將 xlrd 的 Book 對象復制轉換為 xlwt 的Workbook 對象，具體使用時通常導入的是模塊中的 copy 子模塊：
- import xlutils.copy
三、讀取 Excel 文件
3.1 獲取文件
并不是所有7個模塊都可以讀取 Excel 文件，而即使能讀取Excel文件也要分不同后綴名進行討論，具體如下：
- xlwt、xlutils、XlsxWriter 不能讀取文件
- xlrd 可以讀取 .xls 和 .xlsx 文件
- xlwings 可以讀取 .xls 和 .xlsx 文件
- openpyxl 可以讀取 .xlsx 文件
- pandas 可以讀取 .xls 和 .xlsx 文件
下面使用兩個大小均為 10MB 的 .xls 和 .xlsx 文件進行測試：
- xls_path = r'C:\xxx\Desktop\test.xls'
- xlsx_path = r'C:\xxx\Desktop\test.xlsx'
3.1.1 xlrd 讀取文件
xlrd 可以讀取 .xls 和 .xlsx 文件
- xls = xlrd.open_workbook(xls_path)
- xlsx = xlrd.open_workbook(xlsx_path)
3.1.2 xlwings 讀取文件
xlwings 直接對接的是 apps，也就是 Excel 應用程序，然后才是工作簿 books 和工作表 sheets，xlwings 需要安裝有 Excel 應用程序的環境xlwings 可以讀取 .xls 和 .xlsx 文件
- app = xw.App(visible=True, add_book=False) # 程序可見，只打開不新建工作薄
- app.display_alerts = False # 警告關閉
- app.screen_updating = False # 屏幕更新關閉
- # wb = app.books.open(xls_path)
- wb = app.books.open(xlsx_path)
- wb.save() # 保存文件
- wb.close() # 關閉文件
- app.quit() # 關閉程序
3.1.3 openpyxl 讀取文件
openpyxl 可以讀取 .xlsx 文件
- wb = openpyxl.load_workbook(xlsx_path)
如果讀取 .xls 文件會報錯：
- wb = openpyxl.load_workbook(xls_path)
openpyxl.utils.exceptions.InvalidFileException: openpyxl does not support the old .xls file format, please use xlrd to read this file, or convert it to the more recent .xlsx file format.
3.1.4 pandas 讀取文件
pandas 可以讀取 .xls 和 .xlsx 文件
- xls = pd.read_excel(xls_path, sheet_name='Sheet1')
- xlsx = pd.read_excel(xlsx_path, sheet_name='Sheet1')
接下來比較四個模塊在同一配置電腦下讀取 10MB .xlsx 文件的時間(運行3次求平均值)，所用代碼為：
- import time
- import xxx
- time_start = time.time()
- xxx
- time_end = time.time()
- print('time cost: ', time_end-time_start, 's')
最后測試的結果是，xlwings 讀取 10MB 文件最快，xlrd 次之，openpyxl 最慢(因電腦而異，結果僅供參考)
讀入 Excel 文件部分的表格總結如下：
3.2 獲取工作表
針對上述4個可以讀取 Excel 文件的模塊，進一步討論其獲取工作表 sheet 的方式
3.2.1 xlrd 獲取工作表
可以通過 sheet 名查找：
- sheet = xlsx.sheet_by_name("Sheet1")
也可通過索引查找：
- sheet = xlsx.sheet_by_index(0)
3.2.2 xlwings 獲取工作表
xlwings 的工作表分為活動工作表以及指定工作簿下的特定工作表：
- sheet = xw.sheets.active # 在活動工作簿
- sheet = wb.sheets.active # 在特定工作簿
3.2.3 openpyxl 獲取工作表
.active 方法默認獲取工作簿的第一張工作表
- sheet = wb.active
另外也可以通過工作表名指定獲取工作表：
- sheet = wb['Sheet1']
3.2.4 pandas 獲取工作表
單獨獲取工作表完全沒有 pandas 什么事情，因為讀取文件的同時已經且必須指定工作表才能讀?。?/p>
- xlsx = pd.read_excel(xlsx_path, sheet_name='Sheet1')
四、創建 Excel 文件
簡單總結創建 Excel 文件的情況：
- xlrd、xlutils 不能創建 Excel 文件
- xlwt 只能創建 .xls 文件，不能創建 .xlsx 文件
- xlwings可以創建 .xls 和 .xlsx 文件
- XlsxWriter 可以創建 .xlsx 文件
- openpyxl 可以創建 .xls 和 .xlsx 文件
- pandas 沒有創建 Excel 的概念，但可以存儲時產生 .xls 或 .xlsx 文件
4.1 xlwt 創建文件
xlwt 只能創建 .xls 文件，不能創建 .xlsx 文件
- xls = xlwt.Workbook(encoding= 'ascii')
- # 創建新的sheet表
- worksheet = xls.add_sheet("Sheet1")
4.2 xlwings 創建文件
xlwings可以創建 .xls 和 .xlsx 文件，只需要最后保存時寫清楚后綴即可。使用如下命令：
- wb = app.books.add()
無論是新建還是打開都需要保存工作簿、關閉工作簿、關閉程序，即：
- wb.save(path + r'\new_practice.xlsx')
- wb.close()
- app.quit()
4.3. XlsxWriter 創建文件
XlsxWriter 可以創建 .xlsx 文件：
- xlsx = xlsxwriter.Workbook()
- # 添加工作表
- sheet = xlsx .add_worksheet('Sheet1')
4.4 openpyxl 創建文件
openpyxl可以創建 .xls 和 .xlsx 文件，只需要最后保存時寫清楚后綴即可。使用如下命令：
- wb = Workbook()
- # 新工作簿中指定即創建工作表
- sheet = wb.active
4.5. pandas 創建文件
pandas 只需要最后轉存時寫清楚后綴即可。實際上比較抽象，pandas 并不需要一開始先創建一個 Excel 文件，可以圍繞數據框做各式操作后用 .to_excel 命令再用 .xls 或者 .xlsx 做文件后綴。如果一定要產生一個空白 Excel 文件可以用如下命令：
- df = pd.DataFrame([])
- df.to_excel(r'C:\xxx\test1.xlsx')
五、保存文件
簡單總結保存 Excel 文件的情況：
- xlrd 不能保存 Excel 文件
- xlwt 可以保存 .xls 文件
- xlutils 可以將 xlrd 對象復制為 xlwt 對象后保存 .xls 文件
- xlwings可以保存 .xls 和 .xlsx 文件
- XlsxWriter 可以保存 .xlsx 文件
- openpyxl 可以保存 .xlsx 文件
- pandas 可以保存 .xls 或 .xlsx 文件
5.1 xlwt 保存文件
xlwt 可以保存 .xls 文件
- # xls = xlwt.Workbook(encoding= 'ascii')
- # worksheet = xls.add_sheet("Sheet1")
- xls.save("new_table.xls")
5.2 xlutils 保存文件
xlutils 可以將 xlrd 對象復制為 xlwt 對象后保存 .xls 文件
- # xls_path = r'C:\xxxx\test.xls'
- # xls = xlrd.open_workbook(xls_path)
- xls_xlutils = xlutils.copy.copy(xls)
- xls_xlutils.save('new_text.xls')
5.3 xlwings 保存文件
xlwings可以保存 .xls 和 .xlsx 文件
- # wb = app.books.open(xls_path)
- wb = app.books.open(xlsx_path)
- wb.save() # 保存文件
- wb.close() # 關閉文件
- app.quit() # 關閉程序
5.4 XlsxWriter 保存文件
XlsxWriter 可以保存 .xlsx 文件，.close 命令執行后文件關閉的同時保存：
- # xlsx = xlsxwriter.Workbook()
- # sheet = xlsx .add_worksheet('Sheet1')
- xlsx.close()
5.5 openoyxl 保存文件
openpyxl 可以保存 .xlsx 文件
- # wb = openpyxl.load_workbook(xlsx_path)
- # wb = Workbook()
- # sheet = wb.active
- wb.save('new_test.xlsx')
6. pandas 保存文件
pandas 可以保存 .xls 或 .xlsx 文件
- df1 = pd.DataFrame([1, 2, 3])
- df2 = pd.DataFrame([1, 2, 4])
- df1.to_excel(r'C:\xxxx\test1.xls')
- df2.to_excel(r'C:\xxxx\test2.xlsx')
六、獲取單元格的值
獲取單元格的值基本前提是能夠讀取文件，因此基本圍繞 xlrd、xlwings、openpyxl、pandas 介紹。xlutils 由于能夠復制一份 .xls 因此也可以使用和 xlrd 完全一樣的讀取單元格方法。
6.1. xlrd/xlutils 獲取單元格
xlutils 因為是直接拷貝一份 xlrd 適用的對象，讀取單元格使用的方法和 xlrd 完全一樣。xlwt 沒有讀取單元格的能力
- # xls = xlrd.open_workbook(xls_path)
- # sheet = xlsx.sheet_by_name("Sheet1")
- value = sheet.cell_value(4, 6) # 第5行第7列的單元格
- print(value)
- rows = table.row_values(4)
- cols = table.col_values(6)
- for cell in rows:
- print(cell)
6.2. xlwings 獲取單元格
- # app = xw.App(visible=True, add_book=False)
- # app.display_alerts = False
- # app.screen_updating = False
- # wb = app.books.open(xls_path)
- # sheet = wb.sheets.active
- # 獲取單個單元格的值
- A1 = sheet.range('A1').value
- print(A1)
- # 獲取橫向或縱向多個單元格的值，返回列表
- A1_A3 = sheet.range('A1:A3').value
- print(A1_A3)
- # 獲取給定范圍內多個單元格的值，返回嵌套列表，按行為列表
- A1_C4 = sheet.range('A1:C4').value
- print(A1_C4)
- # 獲取單個單元格的值
- A1 = sheet.range('A1').value
- print(A1)
- # 獲取橫向或縱向多個單元格的值，返回列表
- A1_A3 = sheet.range('A1:A3').value
- print(A1_A3)
- # 獲取給定范圍內多個單元格的值，返回嵌套列表，按行為列表
- A1_C4 = sheet.range('A1:C4').value
- print(A1_C4)
6.3 openpyxl 獲取單元格
- # wb = openpyxl.load_workbook(xlsx_path)
- # wb = Workbook()
- # sheet = wb.active
- # 一、指定坐標范圍的值
- cells = sheet['A1:B5']
- # 二、指定列的值
- cells = sheet['A']
- cells = sheet['A:C']
- # 三、指定行的值
- cells = sheet[5]
- cells = sheet[5:7]
- # 獲取單元格的值
- for cell in cells:
- print(cell.value)
6.4 pandas 獲取單元格的值
pandas 讀取 Excel 文件后即將它轉換為數據框對象，解析內容的方法基本是 pandas 體系中的知識點，如 .iloc() .loc() .ix() 等：
- print(df1.iloc[0:1, [1]])
- print(df1.loc['b'])
- print(df2.ix['a', 'a']) # 有些版本取消了ix，可以用iat
七、寫入數據
還是先簡單總結對 Excel 文件寫入數據的情況：
- xlrd 不能寫入數據
- xlwt 可以寫入數據
- xlutils 可以借用 xlwt 方法寫入數據
- xlwings可以寫入數據
- XlsxWriter 可以寫入數據
- openpyxl 可以寫入數據
- pandas 將 Excel 文件讀取為數據框后，是抽象出數據框層面進行操作，沒有了對 Excel 進行單元格寫入和修改的概念
7.1. xlwt/xlutils 寫入數據
- # xls = xlrd.open_workbook(xls_path)
- # xls_xlutils = xlutils.copy.copy(xls)
- # sheet = xls_xlutils.sheet_by_name("Sheet1")
- # value = sheet.cell_value(4, 6)
- # print(value)
- sheet.write(4, 6, "新內容")
7.2 xlwings 寫入數據
- # app = xw.App(visible=True, add_book=False)
- # app.display_alerts = False
- # app.screen_updating = False
- # wb = app.books.open(xls_path)
- # sheet = wb.sheets.active
- # 寫入 1 個單元格
- sheet.range('A2').value = '大明'
- # 一行或一列寫入多個單元格
- # 橫向寫入A1:C1
- sheet.range('A1').value = [1,2,3]
- # 縱向寫入A1:A3
- sheet.range('A1').options(transpose=True).value = [1,2,3]
- # 寫入范圍內多個單元格
- sheet.range('A1').options(expand='table').value = [[1,2,3], [4,5,6]]
7.3 XlsxWriter 寫入數據
代碼中的 new_format 是之前預設好的樣式，下文會進行介紹
- # xlsx = xlsxwriter.Workbook()
- # sheet = xlsx .add_worksheet('Sheet1')
- # 一、寫入單個單元格
- sheet.write(row, col, data, new_format)
- # A1:從A1單元格開始插入數據，按行插入
- sheet.write_row('A1', data, new_format)
- # A1:從A1單元格開始插入數據，按列插入
- sheet.write_column('A1', data, new_format)
7.4. openpyxl 寫入數據
- # wb = openpyxl.load_workbook(xlsx_path)
- # wb = Workbook()
- # sheet = wb.active
- # 一、寫入單元格
- cell = sheet['A1']
- cell.value = '業務需求'
- # 二、寫入一行或多行數據
- data1 = [1, 2, 3]
- sheet.append(data1)
- data2 = [[1, 2, 3], [4, 5, 6]]
- sheet.append(data2)
八、樣式調整
依舊簡單總結對 Excel 文件樣式調整的情況：
- xlrd、xlutils 不能調整樣式 (也可以說 xlutils 可以，只不過是借用了 xlwt 的方法)
- xlwt 可以調整樣式
- xlwings可以調整樣式
- XlsxWriter 可以調整樣式
- openpyxl 可以調整樣式
- pandas 不能調整樣式
8.1 xlwt 調整樣式
xlwt 支持調整字體、邊框、顏色等樣式
- # 字體部分
- # 初始化樣式
- style1 = xlwt.XFStyle()
- # 為樣式創建字體
- font = xlwt.Font()
- font.name = 'Times New Roman' #字體
- font.bold = True #加粗
- font.underline = True #下劃線
- font.italic = True #斜體
- # 設置樣式
- style1.font = font
- # 使用樣式
- sheet.write(4, 6, "新內容1", style1)
- # 邊框部分
- borders = xlwt.Borders()
- # 設置線型
- borders.left = xlwt.Borders.DASHED
- borders.right = xlwt.Borders.DASHED
- borders.top = xlwt.Borders.DASHED
- borders.bottom = xlwt.Borders.DASHED
- # 設置樣色
- borders.left_colour = 0x40
- borders.right_colour = 0x40
- borders.top_colour = 0x40
- borders.bottom_colour = 0x40
- #
- style2 = xlwt.XFStyle()
- style2.borders = borders
- # 使用樣式
- sheet.write(5, 8, "新內容2", style2)
8.2 xlwings 調整樣式
簡單介紹 xlwings 對顏色的調整：
- # 獲取顏色
- print(sheet.range('C1').color)
- # 設置顏色
- sheet.range('C1').color = (255, 0, 120)
- # 清除顏色
- sheet.range('C1').color = None
8.3 XlsxWriter 調整樣式
XlsxWriter 包含大量功能，可以創建工作表后對工作表進行高定自定義的樣式修改：
- new_format = xlsx.add_format({
- 'bold': True, # 字體加粗
- 'border': 1, # 單元格邊框寬度
- 'align': 'left', # 水平對齊方式
- 'valign': 'vcenter', # 垂直對齊方式
- 'fg_color': '#F4B084', # 單元格背景顏色
- 'text_wrap': True # 是否自動換行
- })
- sheet.write(row, col, data, new_format)
8.4 openpyxl 調整樣式
openpyxl 樣式主要包括字體、邊框、段落對齊樣式等
- # 字體樣式
- from openpyxl.styles import Font
- cell = sheet['A1']
- font = Font(name='Arial', size=12, bold=True, italic=True, color='FF0000')
- cell.font = font
- # 段落對齊
- from openpyxl.styles import Alignment
- cell = sheet['B2']
- alignment = Alignment(horizontal='center', vertical='center', text_rotation=45, wrap_text=True)
- cell.alignment = alignment
- # 邊框樣式
- from openpyxl.styles import Side, Border
- cell = sheet['B2']
- side1 = Side(style='thin', color='FF0000')
- side2 = Side(style='dashed')
- border = Border(left=side1, right=side1, top=side2, bottom=side2)
- cell.border = border
九、插入圖片
簡單總結對 Excel 文件插入圖片的情況：
- xlrd、xlutils 不能調整樣式 (也可以說 xlutils 可以，只不過是借用了 xlwt 的方法)
- xlwt 可以插入 .bmp 圖片
- xlwings可以插入 圖片
- XlsxWriter 可以插入圖片
- openpyxl 可以插入圖片
- pandas 不能插入圖片
9.1 xlwt 插入圖片
xlwt 插入圖片要求圖片格式必須是 .bmp 格式才能插入成功
- sheet.insert_bitmap("test.bmp", 2, 3, 2, 2, 0.5, 0.5)
insert_bitmap(img, x, y, x1, y1, scale_x, scale_y)img 表示要插入的圖像地址，x 表示行，y 表示列x1 y1 表示相對原來位置向下向右偏移的像素scale_x scale_y表示相對原圖寬高的比例，圖片可放大縮小
9.2 xlwings 插入圖片
下面是用xlwings 插入圖片的代碼，可以指定位置
- sheet.pictures.add(r'C:\\xxx.jpg')
- # 也可以給定位置插入
- sheet.pictures.add(r'C:\\xxx.jpg', left=sheet.range('A2').left, top=sheet.range('A2').top, width=100, height=100)
9.3 XlsxWriter 插入圖片
第一個參數是插入的起始單元格，第二個參數是圖片文件的絕對路徑
- sheet.insert_image('A1', r'C:\\xxx.jpg')
9.4 openpyxl 插入圖片
openpyxl也可以給Excel中插入指定圖片并修改大小
- from openpyxl.drawing.image import Image
- img = Image('test.jpg')
- newsize = (180, 360)
- img.width, img.height = newsize # 設置圖片的寬和高
- sheet.add_image(img, 'A2') # 往A2單元格插入圖片
小結
以上就是根據不同 Python 模塊，對常見的 Excel 操作進行對比的全部內容，最終結果匯總如下表所示
請注意，本文目的并不是要評出一個最好的庫，僅是從不同角度對不同庫進行對比，希望能夠讓大家了解各個庫所擅長的工作。比如pandas雖然處理方便，但是不能添加圖片修改樣式，openpyxl雖然各種操作都支持，但是速度又相對慢一點等。
只有充分了解不同工具的特點，才能夠在不同的場景下靈活運用不同的方法來高效解決問題!
