最全總結(jié) | 聊聊 Python 辦公自動化之 Excel（中）

第一時間關(guān)注 Python 技術(shù)干貨！

1. 前言

上一篇文章中，我們聊到使用?xlrd、xlwt、xlutils 這一組合操作 Excel 的方法

最全總結(jié) | 聊聊 Python 辦公自動化之 Excel（上）

本篇文章將繼續(xù)聊另外一種方式，即：openpyxl

不得不說，openpyxl 更強(qiáng)大！

它支持 xlsx 格式的表格文件，并且支持 Numpy、Pandas 等包，可用于繪制圖表

2. 準(zhǔn)備

首先，我們需要安裝依賴包

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

3. 讀取數(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

#?創(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)

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

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

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)

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

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)

from?openpyxl.styles?import?Font

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

from?openpyxl.styles?import?Font,Alignment

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

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

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

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、

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

7. 最后