前言

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

常見庫簡介

1.xlrd

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

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

• xlrd支持.xls，.xlsx文件的讀

• 通過設(shè)置on_demand變量使open_workbook()函數(shù)只加載那些需要的sheet，從而節(jié)省時間和內(nèi)存（該方法對.xlsx文件無效）。

• xlrd.Book對象有一個unload_sheet方法，它將從內(nèi)存中卸載工作表，由工作表索引或工作表名稱指定（該方法對.xlsx文件無效）

2.xlwt

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

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

• xlwt支持.xls文件寫。

3.xlutils

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

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

• xlutils支持.xls文件。

• 支持Excel操作。

4.xlwings

xlwings是一個可以實現(xiàn)從Excel調(diào)用Python，也可在python中調(diào)用Excel的庫。

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

• xlwings支持.xls讀，支持.xlsx文件讀寫。

• 支持Excel操作。

• 支持VBA。

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

5.openpyxl

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

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

• openpyxl支持.xlsx文件的讀寫。

• 支持Excel操作。

• 加載大.xlsx文件可以使用read_only模式。

• 寫入大.xlsx文件可以使用write_only模式。

6.xlsxwriter

xlsxwriter是一個用于創(chuàng)建Excel .xlsx文件的庫。

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

• xlswriter支持.xlsx文件的寫。

• 支持VBA。

• 寫入大.xlsx文件時使用內(nèi)存優(yōu)化模式。

7.win32com

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

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

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

• 支持Excel操作。

8.DataNitro

DataNitro是一個內(nèi)嵌在Excel中的插件。

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

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

• 支持Excel操作。

• 支持VBA。

• 收費

9.pandas

pandas通過對Excel文件的讀寫實現(xiàn)數(shù)據(jù)輸入輸出

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

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

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

?

?

提醒及注意：

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

• win32com 與 DataNitro 僅支持 windows 系統(tǒng)；

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

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

• DataNitro 是 Excel 的插件，安裝需到官網(wǎng)下載。

基本功能：

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

1.xlwings

可結(jié)合 VBA 實現(xiàn)對 Excel 編程，強大的數(shù)據(jù)輸入分析能力，同時擁有豐富的接口，結(jié)合 pandas/numpy/matplotlib 輕松應(yīng)對 Excel 數(shù)據(jù)處理工作。

2.openpyxl

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

3.pandas

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

4.win32com

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

5.xlsxwriter

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

6.DataNitro

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

7.xlutils

基于 xlrd/xlwt，老牌 python 包，算是該領(lǐng)域的先驅(qū)，功能特點中規(guī)中矩，比較大的缺點是僅支持 xls 文件。

讀寫測試

測試用例

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

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

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

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

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

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

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

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

測試結(jié)果

注：

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

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

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

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

性能比較

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

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

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

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

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

便捷性比較

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

代碼示例

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

6.1 xlwings基本代碼

import?xlwings?as?xw

#連接到excel

workbook?=?xw.Book(r'path/myexcel.xlsx')#連接excel文件

#連接到指定單元格

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

#寫入數(shù)據(jù)

data_range.value?=?[1,2,3]

#保存

workbook.save()

6.2 xlsxwriter基本代碼

import?xlsxwriter?as?xw

#新建excel

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

#新建工作薄

worksheet?=?workbook.add_worksheet()

#寫入數(shù)據(jù)

worksheet.write('A1',1)

#關(guān)閉保存

workbook.close()

6.3 xlutils基本代碼import xlrd #讀取數(shù)據(jù)

import?xlwt?#寫入數(shù)據(jù)

import?xlutils?#操作excel

#----xlrd庫

#打開excel文件

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

#獲取表單

worksheet?=?workbook.sheet_by_index(0)

#讀取數(shù)據(jù)

data?=?worksheet.cell_value(0,0)

#----xlwt庫

#新建excel

wb?=?xlwt.Workbook()

#添加工作薄

sh?=?wb.add_sheet('Sheet1')

#寫入數(shù)據(jù)

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

#保存文件

wb.save('myexcel.xls')

#----xlutils庫

#打開excel文件

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

#復(fù)制一份

new_book?=?xlutils.copy(book)

#拿到工作薄

worksheet?=?new_book.getsheet(0)

#寫入數(shù)據(jù)

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

#保存

new_book.save()

6.4 win32com基本代碼

import?win32com.client?as?wc

#啟動Excel應(yīng)用

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

#連接excel

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

#寫入數(shù)據(jù)

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

#關(guān)閉并保存

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'

#單元區(qū)域賦值

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

openpyxl具體使用

1、 創(chuàng)建一個excel 文件，并寫入不同類的內(nèi)容

#?-*-?coding:?utf-8?-*-

from?openpyxl?import?Workbook

import?datetime

import?time

#?創(chuàng)建文件對象

wb?=?Workbook()

#?獲取第一個sheet

ws?=?wb.active

#?在第1行第A列，寫入數(shù)字23333

ws['A1']?=?23333

#?寫入中文（unicode中文也可以）

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

#?在下一行，寫入多個單元格

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

#?寫入一個當(dāng)前時間

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

#?寫入一個自定義的時間格式

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

#?保存文件，注意文件覆蓋

wb.save("test.xlsx")

#?關(guān)閉流

wb.close()

2、創(chuàng)建sheet

from?openpyxl?import?Workbook

wb?=?Workbook()

#?創(chuàng)建一個sheet

ws1?=?wb.create_sheet("sheet1")

#?設(shè)定一個sheet的名字

ws1.title?=?"sheet1?Title"

#?設(shè)定sheet的插入位置?默認插在后面

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

ws2.title?=?"Mysheet"

#?設(shè)定sheet的標(biāo)簽的背景顏色

ws1.sheet_properties.tabColor?=?"1072BA"

#?獲取某個sheet對象

print(wb["sheet1?Title"])

print(wb["Mysheet"])

#?獲取全部sheet的名字，遍歷sheet名字

print(wb.sheetnames)

for?i?in?wb.sheetnames:

print(i)

print("*"*50)

for?sheet?in?wb:

print(sheet.title)

#?將sheet1中的A1單元格賦值為zeke

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

#?復(fù)制一個sheet

source?=?wb["sheet1?Title"]

target?=?wb.copy_worksheet(source)

wb.save("test2.xlsx")

wb.close()

3、操作單元格

#?-*-?coding:?utf-8?-*-

from?openpyxl?import?Workbook

wb?=?Workbook()

#?創(chuàng)建一個sheet

ws1?=?wb.create_sheet("Sheet1")

#?將A1單元格賦值為123.11

ws1["A1"]?=?123.11

#?將B2單元格賦值為你好

ws1["B2"]?=?"你好"

#?將第4行第2列的單元賦值為10

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、操作已存在的文件

#?-*-?coding:?utf-8?-*-

from?openpyxl?import?Workbook

from?openpyxl?import?load_workbook

#?打開test5.xlsx文件

wb?=?load_workbook('test5.xlsx')

#?猜測格式類型

wb.guess_types?=?True

ws?=?wb.active

ws["A1"]?=?"12%"

print(ws["A1"].value)

#?注意如果原文件有一些圖片或者圖標(biāo)，則保存的時候可能會導(dǎo)致圖片丟失

wb.save("test5.xlsx")

wb.close()

5、操作批量的單元格

#?-*-?coding:?utf-8?-*-

from?openpyxl?import?Workbook

wb?=?Workbook()

#?創(chuàng)建一個sheet

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)

#?從A列到C列,獲取每一個值

print(ws1["A:C"])

for?column?in?ws1["A:C"]:

for?cell?in?column:

print(cell.value)

#?從第1行到第3行，獲取每一個值

row_range?=?ws1[1:3]

print(row_range)

for?row?in?row_range:

for?cell?in?row:

print(cell.value)

print("*"*50)

#?從第1行到第3行，從第1列到第3列

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、獲取所有的行(列)對象：

#?coding=utf-8

from?openpyxl?import?Workbook

from?openpyxl?import?load_workbook

#?打開test5.xlsx文件

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[-1]

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()

?

?

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

?

1.?SpringBoot內(nèi)容聚合

2.?面試題內(nèi)容聚合

3.?設(shè)計模式內(nèi)容聚合

4.?Mybatis內(nèi)容聚合

5.?多線程內(nèi)容聚合

原文鏈接：https://blog.csdn.net/weixin_38405253/article/details/100512061

總結(jié)

以上是生活随笔為你收集整理的详解Python操作Excel文件的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。