Python中用OpenPyXL處理Excel表格

海風(fēng)中的草帽 2020-06-05

展開全文

前言

用 Python 處理Excel表格的幾個常規(guī)庫：

xlrd 用來讀
xlwt 用來寫
xlutils 用于做復(fù)制,、篩選等針對文檔文件的操作

這些庫都不如 OpenPyXL 強(qiáng)大,，OpenPyXL 即可以讀也可以寫 Excel 2010+ 的 xlsx xlsmxltx xltm 文件。不過,， OpenPyXL 庫也是比較吃內(nèi)存的,，大約是原始文件的50倍左右。例如,，一個50M大小的Excel文件,，需要2.5G大小的內(nèi)存運(yùn)行,。關(guān)于以上幾個庫的性能比對，請移步 OpenPyXL性能測試（點(diǎn)擊文末“閱讀全文”獲取跳轉(zhuǎn)外鏈）,。

安裝

在終端中輸入命令,，如下：

pip install openpyxl

openpyxl 在保存Excel表格時候會丟失原文件的圖片和圖表。如果要操作圖片,，則需要 pillow庫,，安裝如下：

pip install pillow

由于最近天朝在召開兩會，因此必須翻-墻才能安裝,。

使用方法

新建Excel表格

新建Excel表格,，默認(rèn)有一個名為 Sheet 的表格，如下：

from openpyxl import Workbook

wb = Workbook() #創(chuàng)建文件對象ws = wb.active #獲取默認(rèn)sheet

wb.save('sample.xlsx')

打開已有的Excel表格

對已有的Excel表格進(jìn)行操作,，如下：

from openpyxl import Workbook, load_workbook
wb = load_workbook('sample.xlsx')
wb.save('sample.xlsx')

新建/獲取Sheet表格

使用 Workbook.create_sheet() 方法新建Sheet表格,。第一個參數(shù)是sheet名稱，若不填,，則默認(rèn)以 Sheet1 Sheet2 Sheet3 …方式命名,；第二個參數(shù)是插入Sheet表格的位置，以 0 為第一個位置,，若不填,，則置于最后。如下：

ws1 = wb.create_sheet('Mysheet') #默認(rèn)在最后插入

ws2 = wb.create_sheet('Mysheet', 0) #在第一個位置插入

wb.remove(ws1) #刪除sheet

也可以后期隨時修改sheet的名字,，如下：

ws.title = 'New Title'

修改sheet標(biāo)簽顏色,，如下：

ws.sheet_properties.tabColor = '1072BA'

若知道sheet的名字，可以用如下方式獲取sheet ：

ws = wb.get_sheet_by_name('New Title')# orws = wb['New Title']

也可獲取全部sheet的名字,，遍歷sheet名字,，如下：

sheets = wb.sheetnames for sheet in sheets: print(sheets)

for sheet in wb: print sheet.title ['Sheet1', 'New Title', 'Sheet2']

也可以定位到相應(yīng)sheet頁,[0]為sheet頁索引，如下：

sheet_names = wb.sheetnames  # 獲取所有sheet頁名字ws = wb[sheet_names[0]]

復(fù)制Sheet表格

僅能復(fù)制 單元格的值 樣式 超鏈接 注釋塊 等,，而 圖片 和 表格 等是無法復(fù)制的,，如下：

source = wb.activetarget = wb.copy_worksheet(source)

操作單元格

由 worksheet 獲取單元格，或直接給單元格賦值,，如下：

cell = ws['A4'] #獲取第4行第A列的單元格
ws['A4'] = 4 #給第4行第A列的單元格賦值為4
ws.cell(row=4, column=2, value=10) #給第4行第2列的單元格賦值為10ws.cell(4, 2, 10) #同上

獲取區(qū)域內(nèi)的單元格,，如下：

cell_range = ws['A1':'C2'] #獲取A1-C2內(nèi)的區(qū)域

colC = ws['C'] #獲取第C列col_range = ws['C:D'] #獲取第C-D列row10 = ws[10] #獲取第10列row_range = ws[5:10] #獲取第5-10列

如果得到單元格，可以賦值,，如下：

cell.value = 'hello, world'或cell = ws.cell(row=i, column=j, value='金額')

獲取單元格的值,，如下：

cellValue = ws.cell(row=i, column=j).value

獲取行列數(shù)，如下：

row = ws.max_row #最大行數(shù)column = ws.max_column #最大列數(shù)

一行行的獲取數(shù)據(jù),，如下：

>>> for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):... for cell in row:... print(cell)

<Cell Sheet1.A1><Cell Sheet1.B1><Cell Sheet1.C1><Cell Sheet1.A2><Cell Sheet1.B2><Cell Sheet1.C2>

一列列的獲取數(shù)據(jù),，如下：

>>> for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):...     for cell in col:...         print(cell)<Cell Sheet1.A1><Cell Sheet1.A2><Cell Sheet1.B1><Cell Sheet1.B2><Cell Sheet1.C1><Cell Sheet1.C2>

因為性能的原因， Worksheet.iter_cols() 方法不能在只讀模式下使用,。

獲取所有的列或行,，如下：

rows = ws.rows

columns = ws.columns

因為性能的原因,， Worksheet.columns 方法不能在只讀模式下使用。

如果只想從worksheet中獲取值,，可以使用 Worksheet.values 屬性,，如下：

for row in ws.values:   for value in row:     print(value)

Worksheet.iter_rows() 和 Worksheet.iter_cols() 方法都可以添加 values_only 參數(shù)來達(dá)到僅獲取值的目的，如下：

for row in ws.iter_rows(min_row=1, max_col=3, max_row=2, values_only=True): print(row)

保存文件

使用 Workbook.save() 方法保存workbook,，這個方法會不加提示的覆蓋原文件,，如下：

wb = Workbook()wb.save('balances.xlsx')

獲取單元格類型

from openpyxl import Workbook, load_workbookimport datetime

wb = load_workbook('sample.xlsx')

ws=wb.activewb.guess_types = True #開啟獲取單元格類型

ws['A1']=datetime.datetime(2010, 7, 21)print ws['A1'].number_format

ws['A2']='12%'print ws['A2'].number_format

ws['A3']= 1.1print ws['A4'].number_format

ws['A4']= '中國'print ws['A5'].number_format

wb.save('sample.xlsx')

# 執(zhí)行結(jié)果：# yyyy-mm-dd h:mm:ss# 0%# General# General# 如果是常規(guī)，顯示general,如果是數(shù)字,，顯示'0.00_ ',，如果是百分?jǐn)?shù)顯示0%

使用公式

from openpyxl import Workbook, load_workbook
wb = load_workbook('sample.xlsx')ws1=wb.active
ws1['A1']=1ws1['A2']=2ws1['A3']=3
ws1['A4'] = '=SUM(1, 1)'ws1['A5'] = '=SUM(A1:A3)'
print ws1['A4'].value  #打印的是公式內(nèi)容，不是公式計算后的值,程序無法取到計算后的值print ws1['A5'].value  #打印的是公式內(nèi)容,，不是公式計算后的值,程序無法取到計算后的值
wb.save('sample.xlsx')

合并單元格

from openpyxl import Workbook, load_workbook

wb = load_workbook('sample.xlsx')ws1=wb.active

ws.merge_cells('A2:D2')

ws.unmerge_cells('A2:D2')  #合并后的單元格,，腳本單獨(dú)執(zhí)行拆分操作會報錯，需要重新執(zhí)行合并操作再拆分

# or equivalentlyws.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)

wb.save('sample.xlsx')

插入一個圖片

需要 pillow 庫,，安裝如下：

pip install pillow

from openpyxl import load_workbookfrom openpyxl.drawing.image import Image

wb = load_workbook('sample.xlsx')ws1=wb.active

img = Image('1.png')ws1.add_image(img, 'A1')

wb.save('sample.xlsx')

隱藏單元格

from openpyxl import load_workbook
wb = load_workbook('sample.xlsx')ws = wb.active
ws.column_dimensions.group('A', 'D', hidden=True)    # 隱藏A到D列
ws.row_dimensions.group(1, 10, hidden=True)    # 隱藏1到10行
ws.row_dimensions[2].hidden # 獲取第二行是否隱藏了
wb.save('sample.xlsx')

優(yōu)化模式

在處理非常大的 xlsx 文件時,，openPyXL 的常規(guī)模式無法處理這種負(fù)載。幸運(yùn)的是,，有兩種模式可以在(幾乎)恒定內(nèi)存消耗的情況下讀寫無限量的數(shù)據(jù),。

只讀模式

from openpyxl import load_workbookwb = load_workbook(filename='large_file.xlsx', read_only=True)ws = wb['big_data']

for row in ws.rows: for cell in row: print(cell.value)

只寫模式

from openpyxl import Workbookwb = Workbook(write_only=True)ws = wb.create_sheet()
# now we'll fill it with 100 rows x 200 columns
for irow in range(100):...  ws.append(['%d' % i for i in range(200)])# save the filewb.save('new_big_file.xlsx')

與普通工作簿不同，新創(chuàng)建的只寫工作簿不包含任何工作表,；
必須使用 create_sheet() 方法專門創(chuàng)建工作表,。
在只寫的工作簿中，只能使用 append() 添加行,。
使用 cell() 或 iter_rows() 在任意位置寫(或讀)單元格是不可能的,。
它能夠?qū)С鰺o限數(shù)量的數(shù)據(jù)(甚至比Excel實際能夠處理的更多)，同時將內(nèi)存使用量保持在10Mb以下,。

插入/刪除行/列,，移動區(qū)域單元格

插入行/列

在第7行之上插入一行,，如下：

ws.insert_rows(7)

在第7列的左邊插入一列,，如下：

ws.insert_cols(7)

刪除行/列

從第6列開始，刪除3列,，即刪除6,、7、8列,，如下：

ws.delete_cols(6, 3)

移動區(qū)域單元格

將 D4:F10 區(qū)域向上移動一行向右移動2列,，如下：

ws.move_range('D4:F10', rows=-1, cols=2)

如果區(qū)域內(nèi)包含 公式 ，則如下方法可以連同公式一起挪動：

ws.move_range('G4:H10', rows=1, cols=1, translate=True)

注釋

openpyxl 可讀/寫注釋,，但格式信息會被丟失,。在 只讀模式 下不支持操作注釋,。注釋必須包括 內(nèi)容 和 作者 。

讀注釋,，如下：

comment = ws['A1'].commentcomment.text # 注釋內(nèi)容comment.author # 注釋作者

寫注釋,，如下：

comment = Comment('Text', 'Author')comment.width = 300 # 設(shè)置寬度comment.height = 50 # 設(shè)置高度ws['A1'].comment = commentws['B2'].comment = comment

表格樣式

字體樣式

字體名稱、字體大小,、字體顏色,、加粗、斜體,、縱向?qū)R方式（有三種：baseline,，superscript， subscript）,、下劃線,、刪除線，如下：

from openpyxl.styles import Font
font = Font(name='Calibri',            size=11,            color='FF000000',            bold=False,            italic=False,            vertAlign=None,            underline='none',            strike=False)
ws['A1'].font = font
cell2.font = Font(name=cell1.font.name, sz=cell1.font.sz, b=cell1.font.b, i=cell1.font.i)

字體顏色可以用 RGB 或 aRGB ,，如下：

font = Font(color='FFBB00')

font = Font(color='FFFFBB00')

繼承并重寫樣式,，如下：

ft1 = Font(name='Arial', size=14)ft2 = copy(ft1)ft2.name = 'Tahoma'

填充樣式

詳情請移步填充樣式

from openpyxl.styles import PatternFill

# fill_type 的樣式為 None 或 solidcell2.fill = PatternFill(fill_type=cell1.fill.fill_type, fgColor=cell1.fill.fgColor)

邊框樣式

詳情請移步邊框樣式

from openpyxl.styles import Border, Side
border = Border(left=Side(border_style=None, color='FF000000'),                right=Side(border_style=None, color='FF000000'),                top=Side(border_style=None, color='FF000000'),                bottom=Side(border_style=None, color='FF000000'),                diagonal=Side(border_style=None, color='FF000000'),                diagonal_direction=0,                outline=Side(border_style=None, color='FF000000'),                vertical=Side(border_style=None, color='FF000000'),                horizontal=Side(border_style=None, color='FF000000'))

對齊樣式

horizontal 的值有：distributed, justify, center, left, fill, centerContinuous, right, general
vertical 的值有：bottom, distributed, justify, center, top

from openpyxl.styles import Alignment

alignment=Alignment(horizontal='general', vertical='bottom', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0)

保護(hù)樣式

鎖定、隱藏

from openpyxl.styles import Protection
protection = Protection(locked=True, hidden=False)

整行或整列應(yīng)用樣式

col = ws.column_dimensions['A']col.font = Font(bold=True)row = ws.row_dimensions[1]row.font = Font(underline='single')

更改合并的單元格樣式

合并的單元格可以想想成為左上角的那個單元格來操作,。

篩選和排序

from openpyxl import Workbook
wb = Workbook()ws = wb.active
data = [    ['Fruit', 'Quantity'],    ['Kiwi', 3],    ['Grape', 15],    ['Apple', 3],    ['Peach', 3],    ['Pomegranate', 3],    ['Pear', 3],    ['Tangerine', 3],    ['Blueberry', 3],    ['Mango', 3],    ['Watermelon', 3],    ['Blackberry', 3],    ['Orange', 3],    ['Raspberry', 3],    ['Banana', 3]]
for r in data:    ws.append(r)
ws.auto_filter.ref = 'A1:B15'ws.auto_filter.add_filter_column(0, ['Kiwi', 'Apple', 'Mango'])ws.auto_filter.add_sort_condition('B2:B15')
wb.save('filtered.xlsx')

生成的Excel表格,，有篩選排序的操作，但是沒有實際表現(xiàn)出效果,，如下圖：

需要手動點(diǎn)擊 重寫應(yīng)用 才能顯示出效果,，如下圖：

密碼保護(hù)

該功能僅能提供一個很基礎(chǔ)的密碼保護(hù)，沒有進(jìn)行加密處理,，網(wǎng)上普通的破解軟件都可以破解密碼,。不過，日常使用還是可以的,。

該功能僅可用于新建excel表格,，不能用于已存在的excel表格。

workbook工作薄保護(hù)

防止查看隱藏sheet,，避免增加,、移動、刪除,、隱藏或重命名sheet等操作,，可以保護(hù)workbook的結(jié)構(gòu)，如下：

wb.security.workbookPassword = '...'wb.security.lockStructure = True

worksheet保護(hù)

worksheet保護(hù)不需要密碼,，如下：

ws = wb.activewb.protection.sheet = True

使用 Pandas 和 NumPy

詳情請移步 Working with Pandas and NumPy

圖表

圖表由至少一個系列的一個或多個單元格區(qū)域數(shù)據(jù)點(diǎn)組成,。更多內(nèi)容請移步圖表介紹。

（點(diǎn)擊文末“閱讀全文”獲取跳轉(zhuǎn)外鏈）

本站是提供個人知識管理的網(wǎng)絡(luò)存儲空間，所有內(nèi)容均由用戶發(fā)布,，不代表本站觀點(diǎn),。請注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息,，謹(jǐn)防詐騙,。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點(diǎn)擊一鍵舉報,。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自：海風(fēng)中的草帽 > 《信息技術(shù)》

舉報/認(rèn)領(lǐng)