詳解Python操作Excel文件

點擊上方「後端技術精選」，選擇「置頂公眾號」

技術文章第一時間送達！

前言

本篇文章主要總結了一下利用python操作Excel文件的第三方庫和方法。

常見庫簡介1.xlrd

xlrd是一個從Excel文件讀取數據和格式化信息的庫，支持.xls以及.xlsx文件。

地址：http://xlrd.readthedocs.io/en/latest/

2.xlwt

xlwt是一個用於將數據和格式化信息寫入舊Excel文件的庫（如.xls）。

地址：https://xlwt.readthedocs.io/en/latest/

3.xlutils

xlutils是一個處理Excel文件的庫，依賴於xlrd和xlwt。

地址：http://xlutils.readthedocs.io/en/latest/

xlutils支持.xls文件。

支持Excel操作。

4.xlwings

xlwings是一個可以實現從Excel調用Python，也可在python中調用Excel的庫。

地址：http://docs.xlwings.org/en/stable/index.html

4、強大的轉換器可以處理大部分數據類型，包括在兩個方向上的numpy array和pandas DataFrame。

5.openpyxl

openpyxl是一個用於讀取和編寫Excel 2010 xlsx/xlsm/xltx/xltm文件的庫。

地址：https://openpyxl.readthedocs.io/en/stable/

6.xlsxwriter

xlsxwriter是一個用於創建Excel .xlsx文件的庫。

地址：https://xlsxwriter.readthedocs.io/

xlswriter支持.xlsx文件的寫。

支持VBA。

寫入大.xlsx文件時使用內存優化模式。

7.win32com

win32com庫存在於pywin32中，是一個讀寫和處理Excel文件的庫。

地址：http://pythonexcels.com/python-excel-mini-cookbook/

8.DataNitro

DataNitro是一個內嵌在Excel中的插件。

地址：https://datanitro.com/docs/

9.pandas

pandas通過對Excel文件的讀寫實現數據輸入輸出

地址：http://pandas.pydata.org/

pandas支持.xls，.xlsx文件的讀寫。

支持只加載每個表的單一工作頁。

提醒及注意：

xlutils 僅支持 xls 文件，即2003以下版本；

win32com 與 DataNitro 僅支持 windows 系統；

xlwings 安裝成功後，如果運行提示報錯「ImportError: no module named win32api」，請再安裝 pypiwin32 或者 pywin32 包；

win32com 不是獨立的擴展庫，而是集成在其他庫中，安裝 pypiwin32 或者 pywin32 包即可使用；

DataNitro 是 Excel 的插件，安裝需到官網下載。

基本功能：

由於設計目的不同，每個模塊通常著重於某一方面功能，各有所長。

1.xlwings

可結合 VBA 實現對 Excel 編程，強大的數據輸入分析能力，同時擁有豐富的接口，結合 pandas/numpy/matplotlib 輕鬆應對 Excel 數據處理工作。

2.openpyxl

簡單易用，功能廣泛，單元格格式/圖片/表格/公式/篩選/批註/文件保護等等功能應有盡有，圖表功能是其一大亮點，缺點是對 VBA 支持的不夠好。

3.pandas

數據處理是 pandas 的立身之本，Excel 作為 pandas 輸入/輸出數據的容器。

4.win32com

從命名上就可以看出，這是一個處理 windows 應用的擴展，Excel 只是該庫能實現的一小部分功能。該庫還支持 office 的眾多操作。需要注意的是，該庫不單獨存在，可通過安裝 pypiwin32 或者 pywin32 獲取。

5.xlsxwriter

擁有豐富的特性，支持圖片/表格/圖表/篩選/格式/公式等，功能與openpyxl相似，優點是相比 openpyxl 還支持 VBA 文件導入，迷你圖等功能，缺點是不能打開/修改已有文件，意味著使用 xlsxwriter 需要從零開始。

6.DataNitro

作為插件內嵌到 Excel 中，可完全替代 VBA，在 Excel 中使用 python 腳本。既然被稱為 Excel 中的 python，協同其他 python 庫亦是小事一樁。然而，這是付費插件…

7.xlutils

基於 xlrd/xlwt，老牌 python 包，算是該領域的先驅，功能特點中規中矩，比較大的缺點是僅支持 xls 文件。

讀寫測試測試用例

用例1. 讀.xls文件的整個表（表有5個分頁，每個分頁有2000行1200列的整數）。

用例2. 讀.xlsx文件的整個表（表有5個分頁，每個分頁有2000行1200列的整數）。

用例3. 讀.xls文件的整個表（表有1個分頁，頁有2000行1200列的整數）。

用例4. 讀.xlsx文件的整個表（表有1個分頁，頁有2000行1200列的整數）。

用例5. 寫.xls文件的整個表（表有5個分頁，每個分頁有2000行1200列的整數）。

用例6. 寫.xlsx文件的整個表（表有5個分頁，每個分頁有2000行1200列的整數）。

用例7. 寫.xls文件的整個表（表有1個分頁，頁有2000行1200列的整數）。

用例8. 寫.xlsx文件的整個表（表有1個分頁，頁有2000行1200列的整數）。

測試結果

註：

xlwt和pandas每個工作頁最多寫入256列，因此測試用例修改為每頁有2000行256列的整數.

xlutils讀寫依賴於xlrd和xlwt，不單獨測試。

openpyxl測試兩種模式，一是普通加載寫入，二是read_only/write_only模式下的加載寫入。

DataNitro要收費，且需依託Excel使用，本次不測試。

性能比較

單從讀寫的性能上考慮，win32com的性能是最好的，xlwings其次。

openpyxl雖然操作Excel的功能強大，但讀寫性能過於糟糕，尤其是寫大表時，會佔用大量內存（把我的4G內存用完了），開啟read_only和write_only模式後對其性能有大幅提升，尤其是對讀的性能提升很大，使其幾乎不耗時（0.01秒有點誇張，不過確實是加載上了）。

pandas把Excel當作數據讀寫的容器，為其強大的數據分析服務，因此讀寫性能表現中規中矩，但其對Excel文件兼容性是最好的，支持讀寫.xls，.xlsx文件，且支持只讀表中單一工作頁。同樣支持此功能的庫還有xlrd，但xlrd只支持讀，並不支持寫，且性能不突出，需要配合xlutils進行Excel操作，並使用xlwt保存數據，而xlwt只能寫入.xls文件（另一個可以寫入.xls文件的庫是pandas，且這兩個寫入的Excel文件最多只能有256列，其餘庫就我目前的了解均只能寫入.xlsx文件），性能一般。

xlsxwriter功能單一，一般用來創建.xlsx文件，寫入性能中庸。win32com擁有最棒的讀寫性能，但該庫存在於pywin32的庫中，自身沒有完善的文檔，使用略吃力。xlwings擁有和win32com不相伯仲的讀寫性能，強大的轉換器可以處理大部分數據類型，包括二維的numpy array和pandas DataFrame，可以輕鬆搞定數據分析的工作。

綜合考慮，xlwings的表現最佳，正如其名，xlwings——Make Excel Fly！

便捷性比較

本測試目前只是針對Excel文件的讀寫，並未涉及Excel操作，單從讀寫的便捷性來講，各庫的表現難分上下，但是win32com和xlwings這兩個庫可以在程序運行時實時在打開的Excel文件中進行操作，實現過程的可視化，其次xlwings的數據結構轉換器使其可以快速的為Excel文件添加二維數據結構而不需要在Excel文件中重定位數據的行和列，因此從讀寫的便捷性來比較，仍是xlwings勝出。

代碼示例

最後，附上一些演示代碼，大家可自行體會下不同模塊的使用。

6.1 xlwings基本代碼

import xlwings as xw

workbook = xw.Book(r'path/myexcel.xlsx')

data_range = workbook.sheets('Sheet1').range('A1')

data_range.value = [1,2,3]

workbook.save()

6.2 xlsxwriter基本代碼

import xlsxwriter as xw

workbook  = xw.Workbook('myexcel.xlsx')

worksheet = workbook.add_worksheet()

worksheet.write('A1',1)

workbook.close()

6.3 xlutils基本代碼import xlrd #讀取數據

import xlwt 
import xlutils 


workbook = xlrd.open_workbook('myexcel.xls')

worksheet = workbook.sheet_by_index(0)

data = worksheet.cell_value(0,0)


wb = xlwt.Workbook()

sh = wb.add_sheet('Sheet1')

sh.write(0,0,'data')

wb.save('myexcel.xls')


book = xlrd.open_workbook('myexcel.xls')

new_book = xlutils.copy(book)

worksheet = new_book.getsheet(0)

worksheet.write(0,0,'new data')

new_book.save()

6.4 win32com基本代碼

import win32com.client as wc

excel_app = wc.Dispatch('Excel.Application')

workbook = excel_app.Workbooks.Open(r'e:/myexcel.xlsx' )

workbook.Worksheets('Sheet1').Cells(1,1).Value = 'data'

workbook.SaveAs('newexcel.xlsx')
excel_app.Application.Quit()

6.5 openpyxl基本代碼

import openpyxl

workbook = openpyxl.Workbook()

sheet = workbook.activesheet['A1']='data'

workbook.save('test.xlsx')

6.6 DataNitro基本代碼

Cell('A1').value = 'data'

CellRange('A1:B2').value = 'data'

openpyxl具體使用1、 創建一個excel 文件，並寫入不同類的內容

from openpyxl import Workbook
import datetime
import time


wb = Workbook()

ws = wb.active


ws['A1'] = 23333

ws['B1'] = "你好！"+"歡迎使用openpyxl"


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


ws['A2'] = datetime.datetime.now()


ws['A3'] = time.strftime('%Y{y}%m{m}%d{d}%H{h}%M{f}%S{s}', time.localtime()).format(y='年', m='月', d='日', h='時', f='分', s='秒')


wb.save("test.xlsx")

wb.close()

2、創建sheet

from openpyxl import Workbook

wb = Workbook()


ws1 = wb.create_sheet("sheet1")

ws1.title = "sheet1 Title"

ws2 = wb.create_sheet("Mysheet", 0)
ws2.title = "Mysheet"

ws1.sheet_properties.tabColor = "1072BA"


print(wb["sheet1 Title"])
print(wb["Mysheet"])


print(wb.sheetnames)
for i in wb.sheetnames:
    print(i)

print("*"*50)

for sheet in wb:
    print(sheet.title)


wb["sheet1 Title" ]["A1"] = "zeke"

source = wb["sheet1 Title"]
target = wb.copy_worksheet(source)

wb.save("test2.xlsx")
wb.close()

3、操作單元格

from openpyxl import Workbook

wb = Workbook()

ws1 = wb.create_sheet("Sheet1")


ws1["A1"] = 123.11

ws1["B2"] = "你好"

temp = ws1.cell(row=4, column=2, value=10)

print(ws1["A1"].value)
print(ws1["B2"].value)
print(temp.value)

wb.save("test3.xlsx")
wb.close()

4、操作已存在的文件

from openpyxl import Workbook
from openpyxl import load_workbook


wb = load_workbook('test5.xlsx')

wb.guess_types = True
ws = wb.active
ws["A1"] = "12%"
print(ws["A1"].value)


wb.save("test5.xlsx")
wb.close()

5、操作批量的單元格

from openpyxl import Workbook

wb = Workbook()

ws1 = wb.create_sheet("Sheet")

ws1["A1"] = 1
ws1["A2"] = 2
ws1["A3"] = 3

ws1["B1"] = 4
ws1["B2"] = 5
ws1["B3"] = 6

ws1["C1"] = 7
ws1["C2"] = 8
ws1["C3"] = 9


print(ws1["A"])
for cell in ws1["A"]:
    print(cell.value)


print(ws1["A:C"])
for column in ws1["A:C"]:
    for cell in column:
        print(cell.value)


row_range = ws1[1:3]
print(row_range)
for row in row_range:
    for cell in row:
        print(cell.value)

print("*"*50)


for row in ws1.iter_rows(min_row=1, min_col=1, max_col=3, max_row=3):
    for cell in row:
        print(cell.value)


print(ws1.rows)
for row in ws1.rows:
    print(row)

print("*"*50)


print(ws1.columns)
for col in ws1.columns:
    print(col)

wb.save("test4.xlsx")
wb.close()

6、獲取所有的行(列)對象：

from openpyxl import Workbook
from openpyxl import load_workbook


wb = load_workbook('test5.xlsx')
ws = wb.active
rows = []
for row in ws.iter_rows():
    rows.append(row)


print(rows)

print(rows[0])

print(rows[0][0])

print(rows[0][0].value)

print(rows[len(rows) - 1])

print(rows[len(rows) - 1][len(rows[0]) - 1])

print(rows[len(rows) - 1][len(rows[0]) - 1].value)

cols = []
for col in ws.iter_cols():
    cols.append(col)

print(cols)

print(cols[0])

print(cols[0][0])

print(cols[0][0].value)
print("*" * 30)

print(cols[len(cols) - 1] )

print(cols[len(cols) - 1][len(cols[0]) - 1])

print(cols[len(cols) - 1][len(cols[0]) - 1].value)

wb.close()

推薦閱讀(點擊即可跳轉閱讀)

1. SpringBoot內容聚合

2. 面試題內容聚合

3. 設計模式內容聚合

4. Mybatis內容聚合

5. 多線程內容聚合