模塊的介紹和安裝

Python專欄中有對(duì)所有可以操作Excel的模塊進(jìn)行了對(duì)比分析，感興趣的同學(xué)可以前去圍觀。

推薦閱讀：Python殺死Excel？眾多模塊哪家強(qiáng)

openpyxl模塊是一個(gè)可以讀取和寫入Excel文件的模塊，可以處理Excel數(shù)據(jù)、公式、樣式，在表格里面插入圖表等；需要單獨(dú)安裝不包含在python標(biāo)準(zhǔn)庫(kù)里；

安裝：pip install openpyxl -i https://pypi.tuna.tsinghua.edu.cn/simple

在windows系統(tǒng)下使用此條指令進(jìn)行第三方庫(kù)的安裝省時(shí)間，Mac端直接使用pip3 install 模塊名

具體openpyxl的官方文檔可以參照https://openpyxl.readthedocs.io/en/stable/

Excel表格術(shù)語(yǔ)

列：column，以字母表示，在表格的上方

行：row，以數(shù)字表示，但是注意這里是從1開始，在表格的左側(cè)

表單：sheet，在表格的下部

讀數(shù)據(jù)

打開Excel表格并獲取表格名稱

下面讀取表格的整體思路就是：

1. 讀取一個(gè)表格
2. 獲取里面的某個(gè)sheet
3. 獲取sheet中單元格、某行、某列的數(shù)據(jù)
4. 獲取指定范圍的行列數(shù)據(jù)
5. 案例：查找有空值的單元格

load_workbook(filename = 表格文件路徑)；workbook.sheetnames 獲取表格文件內(nèi)的sheet名稱

注意：只能打開存在的表格，不能用該方法創(chuàng)建一個(gè)新表格文件

我們通過(guò)讀取絲芙蘭的售賣商品來(lái)學(xué)習(xí)Excel操作，內(nèi)容如下：

from openpyxl import load_workbookworkbook = load_workbook(filename= 'cosmetics.xlsx')print(workbook.sheetnames)

可以看到有：

 ['cosmetics', 'Sheet1', 'Sheet2']

那么我們就可以通過(guò)sheetname獲取表格，但是如果Excel中只有一個(gè)sheet，那么可以直接使用.active

sheet = workbook['cosmetics']  # 返回的是workbook對(duì)象：<Worksheet "cosmetics">

但是我們的workbook是有多個(gè)sheet的，active的使用如下：

sheet = workbook.active

獲得Excel里面的sheet之后就可以對(duì)表格的數(shù)據(jù)進(jìn)行操作。比如：獲取表格的尺寸大??；第一行第一列的數(shù)據(jù)，代碼如下：

print(sheet.dimensions)  # 獲取的表格的范圍：A1:K1473cell = sheet['B1']print(cell.value) # 獲取B1表格的數(shù)據(jù)，結(jié)果：Brand

獲取某一行或者某一列的內(nèi)容

col_content = sheet['B']  # 表示獲取B這一列row_content = sheet[3]  #獲取第3行數(shù)據(jù)print(row_content)

注意返回的Cell單元格對(duì)象

(<Cell 'cosmetics'.A3>, <Cell 'cosmetics'.B3>, <Cell 'cosmetics'.C3>, <Cell 'cosmetics'.D3>, <Cell 'cosmetics'.E3>, <Cell 'cosmetics'.F3>, <Cell 'cosmetics'.G3>, <Cell 'cosmetics'.H3>, <Cell 'cosmetics'.I3>, <Cell 'cosmetics'.J3>, <Cell 'cosmetics'.K3>)

如果想獲取每個(gè)里面的值，可以結(jié)合value屬性

# 接上代碼for row in row_content:    print(row.value)

獲取多行或者多列內(nèi)容

.iter_rows(min_row = 最低行數(shù),max_row = 最高行數(shù),min_col = 最低列數(shù),max_col = 最高列數(shù)) 按行獲取

.iter_cols(min_row = 最低行數(shù),max_row = 最高行數(shù),min_col = 最低列數(shù),max_col = 最高列數(shù)) 按列獲取

注意：這兩個(gè)演示任一個(gè)都可以，兩個(gè)案例的區(qū)別就是第一個(gè)是每次獲取一行內(nèi)容，第二個(gè)是按照列獲取內(nèi)容。

每次獲取一行數(shù)據(jù)

for row in sheet.iter_rows(min_row=2, max_row=5, min_col= 1,max_col=4):  # 涵蓋范圍的所有單元格都會(huì)顯示    # print(row) # 每行的內(nèi)容是一個(gè)元組的內(nèi)容，如果想看到數(shù)據(jù)還需要繼續(xù)遍歷    for cell in row:        print(cell.value)    print('-'*50)

結(jié)果：

這個(gè)獲取的內(nèi)容有點(diǎn)類似使用pandas的loc獲取第幾行幾列的情況。

同樣列的獲取也是一樣道理，就是每次獲取一列數(shù)據(jù)

for col in sheet.iter_cols(min_row=2, max_row=5, min_col= 1,max_col=4):    print(col)    for cell in row:     print(cell.value)    print('-'*50)

結(jié)果：

如果有需要獲取表格的行或者列可以使用，sheet.rows或者sheet.columns

案例：

編寫一個(gè)python程序，要求：

（1）打開文件絲芙蘭化妝品表格cosmetics.xlsx

（2）找到其中空的單元格

（3）輸出這些空單元格的坐標(biāo)（如A1，B5，C6）

from openpyxl import load_workbook# 1. 加載cosmetics.xlsx表格workbook = load_workbook(filename= 'cosmetics.xlsx')# 2. 得到cosmetics工作簿sheet = workbook['cosmetics']# 3. 獲取工作簿的范圍并切割 范圍：'A1:K1473' ---->使用字符串的split分隔得到：['A1','K1473']size_ls=sheet.dimensions.split(':')# 4. 從而可以得到行和列的最大和最小值col_min,row_min,col_max,row_max = size_ls[0][0],size_ls[0][1],size_ls[1][0],size_ls[1][1:]# print(col_min,row_min,col_max,row_max)  # 打印結(jié)果是：A，1，K，1473   即最小列是A，最大列是K，最小行是1，最大行是1473# 5. 聲明一個(gè)空的列表存放有空值的單元格坐標(biāo)none_list = []# 6. 遍歷行和列# 遍歷列，但是需要注意的是列是字母，所以要使用ord將字母轉(zhuǎn)成數(shù)字才可以使用range范圍for col in range(ord(col_min),ord(col_max)+1):      # 7. 遍歷行,將字符串的行轉(zhuǎn)成整型    for row in range(int(row_min), int(row_max)+1):      # 8. 通過(guò)chr(col)+str(row)獲取單元格坐標(biāo)，再通過(guò)chr將數(shù)字轉(zhuǎn)成字母比如65就是A，所以chr(col)+str(row)的結(jié)果類似是：A3        if sheet[chr(col)+str(row)].value == None:          # 9. 如果某個(gè)單元格沒(méi)有值則將單元格坐標(biāo)保存到列表：none_list中            none_list.append(chr(col)+str(row))# 10. 打印查看none_list里面的內(nèi)容for i in none_list:    print(i)

綜合對(duì)比openpyxl在讀取獲取數(shù)據(jù)上沒(méi)有pandas有優(yōu)勢(shì)，但是pandas不能修改表格，但是openpyxl是可以的。

寫數(shù)據(jù)

向某個(gè)格子寫入數(shù)據(jù)并保存

sheet[‘A1’] = ‘你好啊’

用Python列表插入行數(shù)據(jù)

sheet.append(Python列表) 插入的數(shù)據(jù)會(huì)接在表格內(nèi)已有數(shù)據(jù)后面

復(fù)制一個(gè)sheet

workbook.copy_worksheet(sheet實(shí)例)

案例：

from openpyxl import Workbookfrom openpyxl.utils import get_column_letterwb = Workbook()dest_filename = 'workbook.xlsx'# 激活默認(rèn)工作薄ws1 = wb.active# 設(shè)置工作簿ws1.title = "numbers"# 向指定單元格寫入內(nèi)容ws1['C5'] = 3.14ws1['A2'] = 1.5# 創(chuàng)建第二個(gè)工作薄，名字為ws2 = wb.create_sheet(title="score")data = [    ['張三',100],    ['李四',98],    ['王五',83],    ['趙六',99],]for row in data: ws2.append(row)# 復(fù)制一個(gè)sheetwb.copy_worksheet(ws2)# 最后將內(nèi)容保存wb.save(filename = dest_filename)

使用Excel公式

workbook = load_workbook(filename= 'workbook.xlsx')sheet = workbook['score']sheet['B5'] = '=AVERAGE(B1:B4)'sheet['B6'] = '=SUM(B1:B4)'workbook.save(filename='workbook.xlsx')

如果你對(duì)Excel足夠熟悉也可以使用過(guò)復(fù)雜的公式：

workbook = load_workbook(filename= 'workbook.xlsx')sheet = workbook['Sheet1']sheet['D1'] = '標(biāo)準(zhǔn)身高'# Excel中的公式：  =IF(RIGHt(C2,2)="cm",C2,SUBSTITUTE(C2,"m","")*100&"cm")for i in range(2,9):    sheet['D{}'.format(i)] = f'=IF(RIGHt(C{i},2)="cm",C{i},SUBSTITUTE(C{i},"m","")*100&"cm")'workbook.save(filename='workbook.xlsx')

結(jié)果：

可以使用的公式是（可以通過(guò)下列代碼獲?。?/span>

from openpyxl.utils import FORMULAE print(FORMULAE)

比如說(shuō)有SUM

添加篩選

sheet.auto_filter.ref：給表格添加“篩選器”

.auto_filter.ref = sheet.dimension 給所有字段添加篩選器；

.auto_filter.ref = "A1" 給A1這個(gè)格子添加“篩選器”，就是給第一列添加“篩選器”；

凍結(jié)窗格

sheet.freeze_panes = ‘G2’

凍結(jié)的結(jié)果是，在這個(gè)窗格的左上都是不動(dòng)的，當(dāng)移動(dòng)滑塊時(shí)，變化的只有窗格的右下方數(shù)據(jù)。也就是當(dāng)運(yùn)行此程序代碼后，Excel表格里面會(huì)在G2窗格（左上方）處出現(xiàn)’十字’坐標(biāo)線，在第二象限的數(shù)據(jù)沒(méi)有辦法移動(dòng)，改變的只有其它象限的數(shù)據(jù)

from openpyxl import load_workbook# 1. 加載cosmetics.xlsx表格workbook = load_workbook(filename= 'cosmetics.xlsx')# 2. 得到cosmetics工作簿sheet = workbook['cosmetics']# 3. 設(shè)置篩選器sheet.auto_filter.ref = 'A1'# 4. 凍結(jié)C100窗口sheet.freeze_panes ='C100'# 5. 保存設(shè)置workbook.save(filename = 'cosmetics.xlsx')

當(dāng)然如果不需要也可以刪除行、列、表，分別使用：

.remove("sheet名")：刪除某個(gè)sheet表；

.delete_rows(idx = 數(shù)字編號(hào),amount = 要插入的列數(shù)) 刪除行， 從idx這一列開始，包括idx這一列

.delete_cols(idx = 數(shù)字編號(hào),amount = 要插入的列數(shù)) 刪除列， 從idx這一行開始，包括idx這一行

也可以添加空行

插入多行空行

.insert_rows(idx = 數(shù)字編號(hào),amount = 要插入的列數(shù)) 在idx數(shù)字編號(hào)的行上邊插入幾行

或者多個(gè)空列

.insert_cols(idx = 數(shù)字編號(hào),amount = 要插入的列數(shù)) 在idx數(shù)字編號(hào)的列左邊插入幾列

案例

編寫一個(gè)Python程序，要求：

（1）打開文件絲芙蘭化妝品表格cosmetics.xlsx

（2）找到Price這一列

（3）找到Price中大于100的數(shù)據(jù)

（4）將這些數(shù)據(jù)所在行復(fù)制到一個(gè)新的Excel文件中

from openpyxl import Workbookfrom openpyxl import load_workbookworkbook = load_workbook(filename = 'cosmetics.xlsx')sheet = workbook.activeworkbook_1 = Workbook()sheet_1 = workbook_1.activecells = sheet['D']data_list = []for cell in cells:    if isinstance(cell.value,int) and cell.value >100:        data_list.append(cell.row)print('輸出滿足條件的數(shù)據(jù)所在行數(shù)的列表：\n{}\n'.format(data_list))j = 1for row in data_list:    for col in range(ord('A'),ord('G')+1):        sheet_1[chr(col)+str(j)] = sheet[chr(col)+str(row)].value    print('正在寫入第{}行數(shù)據(jù)'.format(j),end = ' ')    j += 1workbook_1.save('cosmetics_other.xlsx')

樣式

修改字體樣式

Font(name=字體名稱，size=字體大小, bold=是否加粗,italic=是否斜體,color=字體顏色)

獲取表格中字體的樣式

cell.font.屬性

設(shè)置對(duì)齊樣式

Alignment(horizontal=水平對(duì)齊模式,vertical=垂直對(duì)齊模式,text_rotation=旋轉(zhuǎn)角度,wrap_text=是否自動(dòng)換行)

設(shè)置邊框樣式

Side(style=邊線樣式,color=邊線顏色) Border(left=左邊線樣式,right=右邊線樣式,top=上邊線樣式,bottom=下邊線樣式)

填充

PatternFill(fill_type=填充樣式, fgColor=填充顏色) GradientFill(stop=(漸變顏色1,漸變顏色2,…))

設(shè)置行高和列寬

.row_dimensions[行編號(hào)].height = 行高 .column_dimensions[列編號(hào)].width = 列寬

合并單元格

.merge_cells(待合并的格子編號(hào)) .merge_cells(start_row=起始行號(hào),start_column=起始列號(hào),end_row=結(jié)束行號(hào),end_column=結(jié)束列號(hào))

取消合并單元格

.unmerge_cells(待合并的格子編號(hào)) .unmerge_cells(start_row=起始行號(hào),start_column=起始列號(hào),end_row=結(jié)束行號(hào),end_column=結(jié)束列號(hào))

案例綜合應(yīng)用

編寫一個(gè)python程序，要求

（1）打開文件絲芙蘭化妝品表格cosmetics.xlsx

（2）找到Rank在4.5年以上的，Price價(jià)格大于100的數(shù)據(jù)

（3）將其他數(shù)據(jù)刪除，最后不要在中間留空行

（4）將price數(shù)據(jù)背景標(biāo)為紅色，字體標(biāo)為白色

（5）保存該Excel文件

import osfrom openpyxl import Workbookfrom openpyxl import load_workbookfrom openpyxl.styles import Fontfrom openpyxl.styles import PatternFillworkbook = load_workbook(filename = 'cosmetics.xlsx')sheet = workbook.activeworkbook_1 = Workbook()sheet_1 = workbook_1.activedef return_col_or_row(content):  '''函數(shù)功能：根據(jù)輸入的content，  篩選出窗格中含有這個(gè)content的所有的col(列)和row(行)  返回兩個(gè)的列表，第一個(gè)是所在列的數(shù)據(jù)，第二個(gè)是所在行的數(shù)據(jù)  '''  data_size = sheet.dimensions  size_ls = data_size.split(":")  col_min,row_min,col_max,row_max = size_ls[0][0],size_ls[0][1],size_ls[1][0],size_ls[1][1:]  row_ls = []  col_ls = []  for col in range(ord(col_min),ord(col_max)+1):      for row in range(int(row_min), int(row_max)+1):          if sheet[chr(col)+str(row)].value == content:              col_content = chr(col)              row_content = str(row)              col_ls.append(col_content)              row_ls.append(row_content)  return(col_ls,row_ls)col_by_Rank = return_col_or_row('Rank')[0][0]col_Price = return_col_or_row('Price')[0][0]data_col_by_Rank = sheet[col_by_Rank]data_col_Price = sheet[col_Price]data_finial_row = []for i in range(len(data_col_by_Rank)): if data_col_by_Rank[i].value=='Rank':  continue if data_col_Price[i].value=='Price':  continue if isinstance(float(data_col_by_Rank[i].value), float) and float(data_col_by_Rank[i].value) >4.5 and isinstance(float(data_col_Price[i].value), float) and float(data_col_Price[i].value)>100:  print('Rank的數(shù)值為{}，對(duì)應(yīng)的價(jià)格是{}'.format(data_col_by_Rank[i].value,data_col_Price[i].value))  data_finial_row.append(data_col_by_Rank[i].row)print('\n篩選后滿足要求的數(shù)據(jù)行列表輸出為：{}\n'.format(data_finial_row))data_finial_row.insert(0,1)#這一步的目的是將原來(lái)文件的標(biāo)簽寫到新文件中去j = 1for row in data_finial_row: for col in range(ord('A'),ord('K')+1):    #print(sheet[chr(col)+str(row)].value)  sheet_1[chr(col)+str(j)] = sheet[chr(col)+str(row)].value print('正在寫入第{}行數(shù)據(jù)'.format(j),end = ' ') j += 1print('\n\n數(shù)據(jù)已全部導(dǎo)入新Excel文件！下面給數(shù)據(jù)做標(biāo)記......\n')data_after_Price = sheet_1[return_col_or_row('Price')[0][0]]for cell in data_after_Price:    cell.fill = PatternFill(fill_type='solid',fgColor='FF0000')    cell.font = Font(color='FFFFFF')print('數(shù)據(jù)標(biāo)記完成')workbook_1.save(filename='篩選數(shù)據(jù)后的表格.xlsx')print('\ncompleted!') 

結(jié)果：

繪制圖形

from openpyxl import Workbookfrom openpyxl.chart import BarChart, Series, Reference,BarChart3D    wb = Workbook()ws = wb.active    rows = [        (None, 2020, 2021),        ("Apples", 6, 9),        ("Oranges", 5, 2),        ("Pears", 8, 3)]    for row in rows:    ws.append(row)    data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=4)titles = Reference(ws, min_col=1, min_row=2, max_row=4)chart = BarChart3D()chart.title = "3D Bar Chart"chart.add_data(data=data, titles_from_data=True)chart.set_categories(titles)ws.add_chart(chart, "E5")wb.save("bar3d.xlsx")

或者

from openpyxl import Workbookfrom openpyxl.chart import Series, Reference, BubbleChartwb = Workbook()ws = wb.activerows = [    ("Number of Products", "Sales in USD", "Market share"),    (14, 12200, 15),    (20, 60000, 33),    (18, 24400, 10),    (22, 32000, 42),    (),    (12, 8200, 18),    (15, 50000, 30),    (19, 22400, 15),    (25, 25000, 50),]for row in rows:    ws.append(row)chart = BubbleChart()chart.style = 18 # use a preset style# add the first series of dataxvalues = Reference(ws, min_col=1, min_row=2, max_row=5)yvalues = Reference(ws, min_col=2, min_row=2, max_row=5)size = Reference(ws, min_col=3, min_row=2, max_row=5)series = Series(values=yvalues, xvalues=xvalues, zvalues=size, title="2013")chart.series.append(series)# add the secondxvalues = Reference(ws, min_col=1, min_row=7, max_row=10)yvalues = Reference(ws, min_col=2, min_row=7, max_row=10)size = Reference(ws, min_col=3, min_row=7, max_row=10)series = Series(values=yvalues, xvalues=xvalues, zvalues=size, title="2014")chart.series.append(series)# place the chart starting in cell E1ws.add_chart(chart, "E1")wb.save("bubble.xlsx")

Python全棧+人工智能、網(wǎng)絡(luò)安全等熱門學(xué)科，正在火熱報(bào)名中，想從事互聯(lián)網(wǎng)IT行業(yè)，想高薪就業(yè)的同學(xué)，歡迎咨詢免費(fèi)體驗(yàn)~，線上部分學(xué)科學(xué)費(fèi)最高減免3000元。

以下文章來(lái)源于Python專欄 ，作者宋宋

來(lái)源：https://mp.weixin.qq.com/s/K_rHejrgOXKIXg9hJ30PhA