作者：星安果?

來源：AirPython

上一篇python辦公自動化之Excel（上）文章中，我們聊到使用?xlrd、xlwt、xlutils 這一組合操作Excel 的方法。本篇文章將繼續(xù)聊另外一種方式，即：openpyxl。

不得不說，openpyxl 更強大！它支持?xlsx?格式的表格文件，并且支持 Numpy、Pandas 等包，可用于繪制圖表。

準(zhǔn)備：首先，我們需要安裝依賴包。

#?安裝依賴包
pip3?install?openpyxl

讀取數(shù)據(jù)：使用 openpyxl 中的?load_workbook(filepath)?加載本地一個 Excel 文件，返回結(jié)果是一個工作簿對象。

import?openpyxl

#?加載本地的Excel文件
wb?=?openpyxl.load_workbook(file_path)

利用工作簿對象，可以獲取所有的 Sheet 名稱及 Sheet 列表。

def?get_all_sheet_names(wb):
????"""
????獲取所有sheet的名稱
????:param?wb:
????:return:
????"""
????#?sheet名稱列表
????sheet_names?=?wb.sheetnames
????return?sheet_names


def?get_all_sheet(wb):
????"""
????獲取所有的sheet
????:param?wb:
????:return:
????"""
????#?sheet名稱列表
????sheet_names?=?get_all_sheet_names(wb)

????#?所有sheet
????sheets?=?[]
????for?sheet_name?in?sheet_names:
????????sheet?=?wb[sheet_name]
????????sheets.append(sheet)

????return?sheets

工作簿對象提供了?active?屬性，用于快速獲取當(dāng)前選擇的 Sheet。

def?get_current_sheet(wb):
????"""
????獲取當(dāng)前選擇的sheet，默認(rèn)是最后一個sheet
????:param?wb:
????:return:
????"""
????#?當(dāng)前選中的sheet
????current_sheet?=?wb.active

????return?current_sheet

另外，也可以通過?Sheet 名稱去獲取某一個特定的 Sheet 對象。

def?get_sheet_by_name(wb,?sheet_name):
????"""
????通過sheetname去查找某一個sheet
????:param?wb:
????:param?sheet_name:
????:return:
????"""
????sheet_names?=?get_all_sheet_names(wb)
????if?sheet_name?in?sheet_names:
????????result?=?wb[sheet_name]
????else:
????????result?=?None
????return?result

使用?sheet.max_row?和?sheet.max_column?可以獲取當(dāng)前 Sheet 中的數(shù)據(jù)行數(shù)和列數(shù)。

def?get_row_and_column_num(sheet):
????"""
????獲取sheet的行數(shù)和列數(shù)
????:param?sheet:
????:return:
????"""
????#?行數(shù)
????row_count?=?sheet.max_row
????#?列數(shù)
????column_count?=?sheet.max_column

????return?row_count,?column_count

#?行數(shù)和列數(shù)
row_count,?column_count?=?get_row_and_column_num(sheet)
print('行數(shù)和列數(shù)分別為：',?row_count,?column_count)

openpyxl 提供 2 種方式來定位一個單元格，分別是：

• 數(shù)字索引，從 1 開始數(shù)字索引：行數(shù)字索引、列數(shù)字索引比如：row_index=1,column_index=1
• 行和列組成的字符串索引字符串索引：列由字母組成 + 行索引比如：A1 對應(yīng)第一行、第一列的單元格。

并且，openpyxl.utils?提供了方法，便于?列索引?在兩者之間進(jìn)行轉(zhuǎn)換。

from?openpyxl.utils?import?get_column_letter,?column_index_from_string

def?column_num_to_str(num):
????"""
????Excel索引列從數(shù)字轉(zhuǎn)為字母
????:param?num:
????:return:
????"""
????return?get_column_letter(num)

def?column_str_to_num(str):
????"""
????Excel索引列，從字母轉(zhuǎn)為數(shù)字
????:param?str:
????:return:
????"""
????return?column_index_from_string(str)

單元格的獲取，同樣可以通過上面 2 種索引方式來獲取。

def?get_cell(sheet,?row_index,?column_index):
????"""
????獲取單元格
????:param?sheet:
????:param?row_index:
????:param?column_index:
????:return:
????"""
????#?openpyxl索引都是從1開始計數(shù)，這與xlrd有所不同
????#?獲取某一個單元格（二選一）
????#?比如：獲取A1單元格的數(shù)據(jù)，即第一個行、第一列的數(shù)據(jù)
????#?cell_one?=?sheet['A1']
????cell_one?=?sheet.cell(row=row_index,?column=column_index)
????return?cell_one

在日常處理 Excel 數(shù)據(jù)過程中，可能需要判斷單元格數(shù)據(jù)類型，而 openpyxl 并沒有提供現(xiàn)成的方法。這里，我們可以通過單元格對象的?value?屬性拿到值，接著使用?isinstance?方法判斷數(shù)據(jù)類型。

def?get_cell_value_and_type(cell):
????"""
????獲取某一個cell的內(nèi)容及數(shù)據(jù)類型
????:param?cell:
????:return:
????"""
????#?單元格的值
????cell_value?=?cell.value
????#?單元格的類型
????cell_type?=?get_cell_value_type(cell_value)

????return?cell_value,?cell_type

def?get_cell_value_type(cell_value):
????"""
????獲取數(shù)據(jù)類型
????:param?cell_value:
????:return:
????"""
????#?其中
????#?0：空
????# 1：數(shù)字
????# 2：字符串
????# 3：日期
????# 4：其他
????if?not?cell_value:
????????cell_type?=?0
????elif?isinstance(cell_value,?int)?or?isinstance(cell_value,?float):
????????cell_type?=?1
????elif?isinstance(cell_value,?str):
????????cell_type?=?2
????elif?isinstance(cell_value,?datetime.datetime):
????????cell_type?=?3
????else:
????????cell_type?=?4
????return?cell_type=

單獨獲取某一行[列]的數(shù)據(jù)，可以使用下面的方式：

def?get_row_cells_by_index(sheet,?row_index):
????"""
????通過行索引，獲取某一行的單元格
????:param?row_index:
????:return:
????"""
????#?注意：第一列從1開始
????row_cells?=?sheet[row_index]
????return?row_cells


def?get_column_cells_by_index(sheet,?column_index):
????"""
????通過列索引，獲取某一列的單元格
????"""
????#?數(shù)字轉(zhuǎn)為字母
????column_index_str?=?column_num_to_str(column_index)
????#?獲取某一列的數(shù)據(jù)
????column_cells?=?sheet[column_index_str]
????return?column_cells

需要注意的是，獲取某一行的數(shù)據(jù)需要傳入數(shù)字索引；而對于列數(shù)據(jù)的獲取，必須傳入字符串索引。和 Python 列表范圍取值類似，openpyxl 同樣支持使用 : 符號拿到某個范圍內(nèi)的數(shù)據(jù)行[列]

def?get_rows_by_range(sheet,?row_index_start,?row_index_end):
????"""
????通過范圍去選擇行范圍
????比如：選擇第2行到第4行的所有數(shù)據(jù)，返回值為元組
????:param?sheet:
????:param?row_index_start:
????:param?row_index_end:
????:return:
????"""
????rows_range?=?sheet[row_index_start:row_index_end]
????return?rows_range


def?get_columns_by_range(sheet,?column_index_start,?column_index_end):
????"""
????通過范圍去選擇列范圍
????比如：選擇第2列到第4列的所有數(shù)據(jù)，返回值為元組
????:param?sheet:
????:param?column_index_start:
????:param?column_index_end:
????:return:
????"""
????columns_range?=?sheet[column_num_to_str(column_index_start):column_num_to_str(column_index_end)]
????return?columns_range

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

要寫入數(shù)據(jù)到 Excel 表格。首先，使用?openpyxl.Workbook()?創(chuàng)建一個 Excel 工作簿對象。接著，使用工作簿對象的?create_sheet()?新建一個 Sheet。

#?創(chuàng)建一個Excel工作簿
#?注意：每次新建一個Excel文件，都會默認(rèn)生成一個名稱為【Sheet】的工作表Sheet
wb?=?openpyxl.Workbook()

#?創(chuàng)建一個新的sheet，默認(rèn)被插到尾部
#?new_sheet?=?wb.create_sheet('新的Sheet')
#?也可以通過第二個參數(shù)：index來指定插入的位置
#?比如：插入到開頭
new_sheet?=?wb.create_sheet('新的Sheet',?0)

默認(rèn)創(chuàng)建的 Sheet 被插入到最后一個位置，第 2 個參數(shù)可以指定 Sheet 插入的位置。

Sheet 標(biāo)簽的背景色同樣支持修改，使用?sheet_properties.tabColor?指定?RGB 顏色值。

比如，要設(shè)置某一個 Sheet 的背景色為紅色，只需要先查詢到對應(yīng)的?Sheet，然后指定顏色值為 FF0000 即可。

def?set_sheet_bg_color(sheet,?rgb_value):
????"""
????設(shè)置Sheet標(biāo)簽的顏色
????:param?rgb_value:
????:return:
????"""
????#?設(shè)置Sheet底部按鈕的顏色（RRGGBB）
????sheet.sheet_properties.tabColor?=?rgb_value

?#?設(shè)置Sheet的背景色(紅色)
set_sheet_bg_color(new_sheet,?'FF0000')

openpyxl?支持行列數(shù)字索引、字符串索引以這 2 種方式寫入數(shù)據(jù)到單元格中。

def?write_value_to_cell_with_num(sheet,?row_index,?column_index,?value):
????"""
????按行索引、列索引寫入數(shù)據(jù)
????:param?shell:
????:param?row_index:?行索引
????:param?column_index:?列索引
????:param?value:
????:return:
????"""
????#?二選一
????sheet.cell(row=row_index,?column=column_index,?value=value)
????#?shell.cell(row=row_index,?column=column_index).value?=?value


def?write_value_to_cell_with_index_str(sheet,?index_str,?value):
????"""
????按字母位置，寫入數(shù)據(jù)到對應(yīng)單元格
????:param?shell:
????:param?index_str:?字母對應(yīng)的單元格位置
????:param?value:
????:return:
????"""
????sheet[index_str]?=?value

在單元格中插入圖片也很簡單，openpyxl 提供的?add_image()?方法。參數(shù)有 2 個，分別是：圖片對象、單元格字符串索引。為了便于使用，我們可以將列索引進(jìn)行轉(zhuǎn)換，然后封裝成兩個插入圖片的方法。

from?openpyxl.drawing.image?import?Image

def?insert_img_to_cell_with_num(sheet,?image_path,?row_index,?column_index):
????"""
????往單元格中插入圖片
????:param?sheet:
????:param?image_path:
????:param?row_index:
????:param?column_index:
????:return:
????"""
????#?通過行索引、列索引，獲取到字母索引
????index_str?=?column_num_to_str(column_index)?+?str(row_index)
????insert_img_to_cell_with_str(sheet,?image_path,?index_str)

def?insert_img_to_cell_with_str(sheet,?image_path,?index_str):
????"""
????往單元格中插入圖片
????:param?sheet:
????:param?image_path:
????:param?index_str:
????:return:
????"""
????sheet.add_image((image_path),?index_str)

最后，調(diào)用工作簿對象的?save()?方法，將數(shù)據(jù)真實寫入到 Excel 文件中。

#?注意：必須要寫入，才能真實的保存到文件中
wb.template?=?False
wb.save('new.xlsx')

修改數(shù)據(jù)

修改數(shù)據(jù)包含：單元格數(shù)據(jù)的修改、單元格樣式的修改。對于單元格數(shù)據(jù)的修改，只需要先讀取工作簿對象，查詢到要操作的 Sheet 對象，然后調(diào)用上面的方法修改單元格數(shù)據(jù)，最后調(diào)用 save() 函數(shù)保存覆蓋即可。

def?modify_excel(self,?file_path):
????"""
????修改本地Excel文件中數(shù)據(jù)
????:param?file_path:
????:return:
????"""
????#?讀取本地Excel文件
????wb?=?openpyxl.load_workbook(file_path)

????#?讀取某一個sheet
????sheet?=?wb['第一個Sheet']
????print(sheet)

????#?直接修改某一個單元格的數(shù)據(jù)
????write_value_to_cell_with_num(sheet,?1,?1,?'姓名1')

????#?保存并覆蓋
????wb.save(file_path)

單元格樣式包含：字體樣式、單元格背景樣式、邊框樣式、對齊方式等。以常見的字體樣式、對齊方式為例。

首先，使用?openpyxl 中的?Font?類創(chuàng)建一個對象，指定字體名稱、字體大小、是否加粗、是否斜體、顏色、下劃線等。

from?openpyxl.styles?import?Font

#?字體格式
#?指定字體類型、大小、是否加粗、顏色等
font0?=?Font(name='Calibri',
?????????????size=20,
?????????????bold=False,
?????????????italic=False,
?????????????vertAlign=None,??
?????????????underline='none',?
?????????????strike=False,
?????????????color='FF00FF00')

接著，構(gòu)建一個?Alignment?對象，指定單元格的對齊方式。

from?openpyxl.styles?import?Font,Alignment

#?單元格對齊方式
alignment0?=?Alignment(horizontal='center',
???????????????????????vertical='bottom',
???????????????????????text_rotation=0,
???????????????????????wrap_text=False,
???????????????????????shrink_to_fit=False,
???????????????????????indent=0)

最后，使用單元格對象的?font/alignment 屬性，將字體樣式和對齊方式設(shè)置進(jìn)去即可。

#?設(shè)置屬性樣式（字體、對齊方式）
sheet['A1'].font?=?font0
sheet['A1'].alignment?=?alignment0

6. 進(jìn)階用法

接下來，聊聊幾個常用的進(jìn)階用法：

1、獲取可見及隱藏的 Sheet

通過判斷 Sheet 對象的?sheet_state?屬性值，可以判斷當(dāng)前 Sheet 是顯示還是隱藏。當(dāng)值為?visible?時，代表 Sheet 是顯示的。當(dāng)值是?hidden?時，代表這個 Sheet 被隱藏了。

def?get_all_visiable_sheets(wb):
????"""
????獲取工作簿中所有可見的sheet
????:param?wb:
????:return:
????"""
????return?[sheet?for?sheet?in?get_all_sheet(wb)?if?sheet.sheet_state?==?'visible']


def?get_all_hidden_sheets(wb):
????"""
????獲取工作簿中所有隱藏的sheet
????:param?wb:
????:return:
????"""
????return?[sheet?for?sheet?in?get_all_sheet(wb)?if?sheet.sheet_state?==?'hidden']

2、獲取隱藏/顯示的行索引列表、列索引列表

受限于篇幅，這里以獲取所有顯示/隱藏的行索引列表為例，遍歷 Sheet 對象的?row_dimensions?屬性值，通過判斷行屬性的?hidden?值，判斷當(dāng)前行是否隱藏或顯示。

def?get_all_rows_index(sheet,?hidden_or_visiable):
????"""
????獲取所有隱藏/顯示的行
????:param?hidden_or_visiable:??True:隱藏；False：顯示
????:param?sheet:
????:return:
????"""
????#?遍歷行
????#?隱藏的索引
????hidden_indexs?=?[]

????#?所有隱藏的行索引
????for?row_index,?rowDimension?in?sheet.row_dimensions.items():
????????if?rowDimension.hidden:
????????????hidden_indexs.append(row_index)

????#?所有顯示的行索引
????visiable_indexs?=?[index?+?1?for?index?in?range(get_row_and_column_num(sheet)
    [0])?if?index?+?1?not?in?hidden_indexs]

????#?隱藏或者顯示的行索引列表
????return?hidden_indexs?if?hidden_or_visiable?else?visiable_indexs、

3、獲取單元格字體顏色及單元格背景顏色

單元格對象的?font.color.rgb、fill.fgColor.rgb?屬性值分別代表字體顏色值、單元格背景顏色。

def?get_cell_font_color(sheet,?row_index,?column_index):
????"""
????獲取單元格字體的顏色
????:param?sheet:
????:param?row_index:行索引
????:param?column_index:列索引
????:return:
????"""
????cell_color?=?sheet.cell(row_index,?column_index).font.color
????if?cell_color:
????????return?sheet.cell(row_index,?column_index).font.color.rgb
????else:
????????#?顏色不存在，可能單元格沒有數(shù)據(jù)
????????return?None

def?get_cell_bg_color(sheet,?row_index,?column_index):
????"""
????獲取單元格背景的顏色
????:param?sheet:
????:param?row_index:行索引
????:param?column_index:列索引
????:return:
????"""
????return?sheet.cell(row_index,?column_index).fill.fgColor.rgb

?最后

可以發(fā)現(xiàn)，openpyxl 相比 xlrd/xlwt，提供了大量實用的 API，功能更強大，并且完美支持 xlsx！

 

  ——熱門課程推薦： 
 

 

  想從事業(yè)務(wù)型數(shù)據(jù)分析師，您可以點擊>>>“數(shù)據(jù)分析師”了解課程詳情； 
 

 

  想從事大數(shù)據(jù)分析師，您可以點擊>>>“大數(shù)據(jù)就業(yè)”了解課程詳情； 
 

 

  想成為人工智能工程師，您可以點擊>>>“人工智能就業(yè)”了解課程詳情；  
 

 

  想了解Python數(shù)據(jù)分析，您可以點擊>>>“Python數(shù)據(jù)分析師”了解課程詳情； 
 

 

  想咨詢互聯(lián)網(wǎng)運營，你可以點擊>>>“互聯(lián)網(wǎng)運營就業(yè)班”了解課程詳情； 
 

 

  想了解更多優(yōu)質(zhì)課程，請點擊>>>