模塊的介紹和安裝

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

推薦閱讀：Python殺死Excel？眾多模塊哪家強

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

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

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

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

Excel表格術(shù)語

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

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

表單：sheet，在表格的下部

讀數(shù)據(jù)

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

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

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

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

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

我們通過讀取絲芙蘭的售賣商品來學習Excel操作，內(nèi)容如下：

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

可以看到有：

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

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

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

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

sheet = workbook.active

獲得Excel里面的sheet之后就可以對表格的數(shù)據(jù)進行操作。比如：獲取表格的尺寸大小；第一行第一列的數(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單元格對象

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

如果想獲取每個里面的值，可以結(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ù)) 按列獲取

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

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

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

結(jié)果：

這個獲取的內(nèi)容有點類似使用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

案例：

編寫一個python程序，要求：

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

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

（3）輸出這些空單元格的坐標（如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. 聲明一個空的列表存放有空值的單元格坐標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. 通過chr(col)+str(row)獲取單元格坐標，再通過chr將數(shù)字轉(zhuǎn)成字母比如65就是A，所以chr(col)+str(row)的結(jié)果類似是：A3        if sheet[chr(col)+str(row)].value == None:          # 9. 如果某個單元格沒有值則將單元格坐標保存到列表：none_list中            none_list.append(chr(col)+str(row))# 10. 打印查看none_list里面的內(nèi)容for i in none_list:    print(i)

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

寫數(shù)據(jù)

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

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

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

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

復(fù)制一個sheet

workbook.copy_worksheet(sheet實例)

案例：

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

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

workbook = load_workbook(filename= 'workbook.xlsx')sheet = workbook['Sheet1']sheet['D1'] = '標準身高'# 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é)果：

可以使用的公式是（可以通過下列代碼獲取）：

from openpyxl.utils import FORMULAE print(FORMULAE)

比如說有SUM

添加篩選

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

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

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

凍結(jié)窗格

sheet.freeze_panes = ‘G2’

凍結(jié)的結(jié)果是，在這個窗格的左上都是不動的，當移動滑塊時，變化的只有窗格的右下方數(shù)據(jù)。也就是當運行此程序代碼后，Excel表格里面會在G2窗格（左上方）處出現(xiàn)’十字’坐標線，在第二象限的數(shù)據(jù)沒有辦法移動，改變的只有其它象限的數(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')

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

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

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

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

也可以添加空行

插入多行空行

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

或者多個空列

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

案例

編寫一個Python程序，要求：

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

（2）找到Price這一列

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

（4）將這些數(shù)據(jù)所在行復(fù)制到一個新的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è)置對齊樣式

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

設(shè)置邊框樣式

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

填充

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

設(shè)置行高和列寬

.row_dimensions[行編號].height = 行高 .column_dimensions[列編號].width = 列寬

合并單元格

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

取消合并單元格

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

案例綜合應(yīng)用

編寫一個python程序，要求

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

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

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

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

（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，  篩選出窗格中含有這個content的所有的col(列)和row(行)  返回兩個的列表，第一個是所在列的數(shù)據(jù)，第二個是所在行的數(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ù)值為{}，對應(yīng)的價格是{}'.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)#這一步的目的是將原來文件的標簽寫到新文件中去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ù)做標記......\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ù)標記完成')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ò)安全等熱門學科，正在火熱報名中，想從事互聯(lián)網(wǎng)IT行業(yè)，想高薪就業(yè)的同學，歡迎咨詢免費體驗~，線上部分學科學費最高減免3000元。

以下文章來源于Python專欄 ，作者宋宋

來源：https://mp.weixin.qq.com/s/K_rHejrgOXKIXg9hJ30PhA