作者：星安果?

來源：AirPython

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

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

準備：首先，我們需要安裝依賴包。

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

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

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?屬性，用于快速獲取當前選擇的 Sheet。

def?get_current_sheet(wb):
????"""
????獲取當前選擇的sheet，默認是最后一個sheet
????:param?wb:
????:return:
????"""
????#?當前選中的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?可以獲取當前 Sheet 中的數據行數和列數。

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

????return?row_count,?column_count

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

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

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

并且，openpyxl.utils?提供了方法，便于?列索引?在兩者之間進行轉換。

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

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

def?column_str_to_num(str):
????"""
????Excel索引列，從字母轉為數字
????: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開始計數，這與xlrd有所不同
????#?獲取某一個單元格（二選一）
????#?比如：獲取A1單元格的數據，即第一個行、第一列的數據
????#?cell_one?=?sheet['A1']
????cell_one?=?sheet.cell(row=row_index,?column=column_index)
????return?cell_one

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

def?get_cell_value_and_type(cell):
????"""
????獲取某一個cell的內容及數據類型
????: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):
????"""
????獲取數據類型
????:param?cell_value:
????:return:
????"""
????#?其中
????#?0：空
????# 1：數字
????# 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=

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

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):
????"""
????通過列索引，獲取某一列的單元格
????"""
????#?數字轉為字母
????column_index_str?=?column_num_to_str(column_index)
????#?獲取某一列的數據
????column_cells?=?sheet[column_index_str]
????return?column_cells

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

def?get_rows_by_range(sheet,?row_index_start,?row_index_end):
????"""
????通過范圍去選擇行范圍
????比如：選擇第2行到第4行的所有數據，返回值為元組
????: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列的所有數據，返回值為元組
????: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

寫入數據

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

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

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

默認創建的 Sheet 被插入到最后一個位置，第 2 個參數可以指定 Sheet 插入的位置。

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

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

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

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

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

def?write_value_to_cell_with_num(sheet,?row_index,?column_index,?value):
????"""
????按行索引、列索引寫入數據
????: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):
????"""
????按字母位置，寫入數據到對應單元格
????:param?shell:
????:param?index_str:?字母對應的單元格位置
????:param?value:
????:return:
????"""
????sheet[index_str]?=?value

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

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)

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

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

修改數據

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

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

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

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

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

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

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

from?openpyxl.styles?import?Font

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

接著，構建一個?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 屬性，將字體樣式和對齊方式設置進去即可。

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

6. 進階用法

接下來，聊聊幾個常用的進階用法：

1、獲取可見及隱藏的 Sheet

通過判斷 Sheet 對象的?sheet_state?屬性值，可以判斷當前 Sheet 是顯示還是隱藏。當值為?visible?時，代表 Sheet 是顯示的。當值是?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?值，判斷當前行是否隱藏或顯示。

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:
????????#?顏色不存在，可能單元格沒有數據
????????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

?最后

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

 

  ——熱門課程推薦： 
 

 

  想從事業務型數據分析師，您可以點擊>>>“數據分析師”了解課程詳情； 
 

 

  想從事大數據分析師，您可以點擊>>>“大數據就業”了解課程詳情； 
 

 

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

 

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

 

  想咨詢互聯網運營，你可以點擊>>>“互聯網運營就業班”了解課程詳情； 
 

 

  想了解更多優質課程，請點擊>>>