模塊的介紹和安裝

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

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

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

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

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

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

Excel表格術語

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

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

表單：sheet，在表格的下部

讀數據

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

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

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

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

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

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

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之后就可以對表格的數據進行操作。比如：獲取表格的尺寸大小；第一行第一列的數據，代碼如下：

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

獲取某一行或者某一列的內容

col_content = sheet['B']  # 表示獲取B這一列row_content = sheet[3]  #獲取第3行數據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>)

如果想獲取每個里面的值，可以結合value屬性

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

獲取多行或者多列內容

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

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

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

每次獲取一行數據

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

結果：

這個獲取的內容有點類似使用pandas的loc獲取第幾行幾列的情況。

同樣列的獲取也是一樣道理，就是每次獲取一列數據

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)

結果：

如果有需要獲取表格的行或者列可以使用，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)  # 打印結果是：A，1，K，1473   即最小列是A，最大列是K，最小行是1，最大行是1473# 5. 聲明一個空的列表存放有空值的單元格坐標none_list = []# 6. 遍歷行和列# 遍歷列，但是需要注意的是列是字母，所以要使用ord將字母轉成數字才可以使用range范圍for col in range(ord(col_min),ord(col_max)+1):      # 7. 遍歷行,將字符串的行轉成整型    for row in range(int(row_min), int(row_max)+1):      # 8. 通過chr(col)+str(row)獲取單元格坐標，再通過chr將數字轉成字母比如65就是A，所以chr(col)+str(row)的結果類似是：A3        if sheet[chr(col)+str(row)].value == None:          # 9. 如果某個單元格沒有值則將單元格坐標保存到列表：none_list中            none_list.append(chr(col)+str(row))# 10. 打印查看none_list里面的內容for i in none_list:    print(i)

綜合對比openpyxl在讀取獲取數據上沒有pandas有優勢，但是pandas不能修改表格，但是openpyxl是可以的。

寫數據

向某個格子寫入數據并保存

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

用Python列表插入行數據

sheet.append(Python列表) 插入的數據會接在表格內已有數據后面

復制一個sheet

workbook.copy_worksheet(sheet實例)

案例：

from openpyxl import Workbookfrom openpyxl.utils import get_column_letterwb = Workbook()dest_filename = 'workbook.xlsx'# 激活默認工作薄ws1 = wb.active# 設置工作簿ws1.title = "numbers"# 向指定單元格寫入內容ws1['C5'] = 3.14ws1['A2'] = 1.5# 創建第二個工作薄，名字為ws2 = wb.create_sheet(title="score")data = [    ['張三',100],    ['李四',98],    ['王五',83],    ['趙六',99],]for row in data: ws2.append(row)# 復制一個sheetwb.copy_worksheet(ws2)# 最后將內容保存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足夠熟悉也可以使用過復雜的公式：

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

結果：

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

from openpyxl.utils import FORMULAE print(FORMULAE)

比如說有SUM

添加篩選

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

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

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

凍結窗格

sheet.freeze_panes = ‘G2’

凍結的結果是，在這個窗格的左上都是不動的，當移動滑塊時，變化的只有窗格的右下方數據。也就是當運行此程序代碼后，Excel表格里面會在G2窗格（左上方）處出現’十字’坐標線，在第二象限的數據沒有辦法移動，改變的只有其它象限的數據

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

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

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

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

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

也可以添加空行

插入多行空行

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

或者多個空列

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

案例

編寫一個Python程序，要求：

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

（2）找到Price這一列

（3）找到Price中大于100的數據

（4）將這些數據所在行復制到一個新的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('輸出滿足條件的數據所在行數的列表：\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('正在寫入第{}行數據'.format(j),end = ' ')    j += 1workbook_1.save('cosmetics_other.xlsx')

樣式

修改字體樣式

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

獲取表格中字體的樣式

cell.font.屬性

設置對齊樣式

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

設置邊框樣式

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

填充

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

設置行高和列寬

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

合并單元格

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

取消合并單元格

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

案例綜合應用

編寫一個python程序，要求

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

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

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

（4）將price數據背景標為紅色，字體標為白色

（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):  '''函數功能：根據輸入的content，  篩選出窗格中含有這個content的所有的col(列)和row(行)  返回兩個的列表，第一個是所在列的數據，第二個是所在行的數據  '''  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的數值為{}，對應的價格是{}'.format(data_col_by_Rank[i].value,data_col_Price[i].value))  data_finial_row.append(data_col_by_Rank[i].row)print('\n篩選后滿足要求的數據行列表輸出為：{}\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('正在寫入第{}行數據'.format(j),end = ' ') j += 1print('\n\n數據已全部導入新Excel文件！下面給數據做標記......\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('數據標記完成')workbook_1.save(filename='篩選數據后的表格.xlsx')print('\ncompleted!') 

結果：

繪制圖形

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全棧+人工智能、網絡安全等熱門學科，正在火熱報名中，想從事互聯網IT行業，想高薪就業的同學，歡迎咨詢免費體驗~，線上部分學科學費最高減免3000元。

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

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