python處理Excel文件
對 excel 文檔操作有多簡單?看看python如何處理。
1 前言
最近需要頻繁讀寫 excel 文件,想通過程序對 excel 文件進行自動化處理,發現使用 python 的 openpyxl 庫進行 excel 文件讀寫實在太方便了,結構清晰,操作簡單。本文對 openpyxl 的使用進行總結,主要包含以下內容:
-
openpyxl 的介紹及 excel 文件結構說明
-
工作表的讀寫處理
-
行列的讀寫處理
-
單元格的讀寫處理
2 openpyxl 及 excel 文件結構
openpyxl 是一個對 xlsx/xlsm/xltx/xltm
格式的 2010 excel 文檔進行讀寫的 python 庫。它 官網 [1] 有詳細的文檔介紹。在進行使用前,需先安裝並引入
# 安裝 pip install openpyxl # 引入openpyxl 模塊 import openpyxl
在進行 excel 操作之前,先對 excel 的文件結構做一個簡單瞭解,以便於熟悉後續的操作。如下圖:
一個 excel 文件,其內容按層次分爲 工作簿(文件) -> 工作表(sheet) -> 行列 -> 單元格
,對應上圖,整個 excel 文件即是一個工作簿;工作簿下可以有多個工作表(如圖中的 Sheet1/test1 等等);工作表中就是對應的表格數據,分爲行和列,行是用序號表示,列用大寫字母表示(也可用序號);行與列的交點就是每一個存儲數據的單元格。因此,我們對 excel 表格進行讀寫,基本按這個層次思路來操作:讀入文件,找到工作表,遍歷行列,定位單元格,對單元格進行讀寫。因此,會涉及到工作表、行列、單元格的讀寫操作。這些操作之前,需要先把文件加載進來,一個 excel 文件就是一個工作簿 (workbook),加載操作如下(示例中的 excel 文件爲 text.xlsx):
# 加載excel文件 file_path = "E:/pythontest/test.xlsx" workbook = openpyxl.load_workbook(file_path)
3 工作表處理
3.1 工作表讀取
工作表( sheet )會有多個,可以讀取全部的工作表,讀取單個時,可以按 sheet 名稱讀取,也可以按下標(下標從0開始)。
-
全部工作表對象:
workbook.worksheets
-
全部工作表名稱:
workbook.sheetnames
-
按名稱(sheet_name)獲取工作表:
workbook[sheet_name]
-
按下標(i從0開始)獲取工作表:
workbook.worksheets[i]
-
獲取正在使用的工作表:
workbook.active
-
sheet.title sheet.max_row sheet.max_column
如下:
# 全部sheet對象 >>> workbook.worksheets [<Worksheet "Sheet1">, <Worksheet "test1">, <Worksheet "test2">] # 全部sheet名稱 >>> workbook.sheetnames ['Sheet1', 'test1', 'test2'] # 按名稱讀取sheet >>> workbook["Sheet1"] <Worksheet "Sheet1"> # 按下標讀取 >>> workbook.worksheets[0] <Worksheet "Sheet1"> # 獲取當前正在使用的sheet >>> workbook.active <Worksheet "Sheet1"> # 獲取sheet的屬性 >>> sheet_active.title Sheet1 >>> sheet_active.max_row 6 >>> sheet_active.max_column 3
3.2 工作表添加
若需要新增工作表,按操作流程,先添加工作表,再保存文件。創建通過 create_sheet
完成,創建後保存(save)文件,添加才能生效。
-
創建工作表,若名稱相同,則自動進行重命名:
workbook.create_sheet("test3")
-
在指定的下標創建工作表:
workbook.create_sheet("test4",1)
-
保存文件,若文件路徑與打開的文件路徑相同,則覆蓋;不同,則會複製原文件並保存(相當於另存爲):
workbook.save(file_path)
3.3 工作表修改
要修改工作表名稱,直接通過設置工作表的 title 即可,修改後同樣需要保存文件。
# 修改工作表名稱 >>> sheet1 = workbook['test1'] >>> sheet1.title = 'test11' # 保存文件 >>> workbook.save(file_path)
3.4 工作表刪除
刪除工作表,需要先獲取 sheet 對象,然後刪除。刪除有兩種方式,一是使用 workbook
提供的 remove
方法,也可以直接使用 python 的 del
進行刪除。刪除操作後,同樣需要保存文件:
# remove刪除工作表 sheet = workbook["test-1"] workbook.remove(sheet) # del操作刪除 del workbook["test2"] # 保存文件 workbook.save(file_path)
4 行列處理
獲取 sheet 對象後,後續即可進行行列操作,包括行列讀取,添加,刪除等。
4.1 讀行列
-
獲取全部行和列,然後可以進行遍歷:
sheet.rows
,sheet.columns
-
sheet[1] sheet[2:3] sheet['A'] sheet['B:C']
# 遍歷全部行 >>> for row in sheet.rows: ... print(row) ... (<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>) (<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>) .... # 讀取部分行列 >>> sheet[1] (<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>) >>> sheet["A:B"] ((<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.A2>, <Cell 'Sheet1'.A3>, <Cell 'Sheet1'.A4>, <Cell 'Sheet1'.A5>, <Cell 'Sheet1'.A6>), (<Cell 'Sheet1'.B1>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.B4>, <Cell 'Sheet1'.B5>, <Cell 'Sheet1'.B6>))
4.2 添加行列
添加行列,可以指定位置添加單個行列或多個行列。
-
直接在工作表中追加行數據:
sheet.append(rowdata)
-
在指定 index(從1開始計算) 位置添加行列:
sheet.insert_rows
,sheet.insert_cols
# 在第4行插入1行空行 >>> sheet.insert_rows(4) # 在第2行插入2行空行 >>> sheet.insert_rows(idx=2,amount=2) # 添加一行數據到表 >>> row_data = ["tom", 15, "[email protected]"] >>> sheet.append(row_data) # 保存修改內容 >>> workbook.save(file_path)
4.3 刪除行列
刪除操作與插入行列操作方式一致,使用 delete_rows
及 delete_cols
方法。
# 刪除行 >>> sheet.delete_rows(2,2) >>> workbook.save(file_path)
5 單元格處理
我們的數據最終是保存在每一個單元格(Cell)中,因此,最終我們操作數據其實就是單元格中的數據,單元格中,openpyxl 使用是 Cell 對象。前面在遍歷行列數據時,可以看到輸出 <Cell 'Sheet1'.A1>
的內容,這對應的單元格對象。下面對單元格的操作進行說明。
5.1 獲取單元格數據值及屬性值
定位獲取單元格有兩種方式:
-
直接指定行列名:
sheet[A1]
-
使用
cell
函數(行列下標從1開始):sheet.cell(row=2,column=1)
# 指定行列座標獲取單元格 >>> sheet["A1"] <Cell 'Sheet1'.A1> # cell函數獲取單元格 >>> sheet.cell(row=1, column=1) <Cell 'Sheet1'.A1>
獲取單元格對象後,可以獲取數據值及其屬性,包括它所在的行列數,座標,值等。
>>> cell = sheet["A2"] >>> cell.value '張三' >>> cell.coordinate 'A2' >>> cell.column 1 >>> cell.row 2
5.2 移動單元格
通過對單元格區域,可以向上、下、左、右進行移動,使用的是 move_range(range,rows,cols)
,其中 rows 和 cols 爲整數,正整數表示向下或向右,負整數爲向上或向左。
# 移動數據區域(向上移動2行,向右移動3列),正整數爲向下或向右,負整數爲向上或向左 sheet.move_range("A3:C3", rows=-2, cols=3) wb.save(file_path)
5.3 合併拆分單元格
對於跨行和跨列,需要對單元格進行合併,使用的是 merge_cells(range_string, start_row, start_column, end_row, end_column)
。如果要合併的單元格都有數據,只會保留左上角的數據,其他則丟棄。合併及拆分都可以通過行列座標(如A1)或者行列下標(如1,2)進行。
# 單元格合併,使用範圍座標 sheet.merge_cells("A2:B3") # 單元格合併,指定行列下標(下標從1開始) sheet.merge_cells(start_row=5, start_column=3, end_row=7, end_column=4) wb.save(file_path) # 拆分單元格 sheet.unmerge_cells("A2:B3") sheet.unmerge_cells(start_row=5, start_column=3, end_row=7, end_column=4) # 保存文件 wb.save(file_path)
5.4 寫入單元格
對單元格值進行修改和寫入,直接對 cell.value
進行賦值即可。這裏需要注意的是,可以寫入 excel 公式,具體公式與 excel 中用到公式一致,另外,若是寫入公式,讀取時獲取到的 value 值也是公式,而非公式值。
# 寫入值 cell.value = "張三" # 寫入公式(求平均值) cell.value = "=AVERAGE(B2:B6)"
5.5 設置單元格格式
單元格的格式包括行高,列寬,字體、邊框、對齊方式、填充顏色等。這些都在 openpyxl 的 styles 模塊中。
-
行高/列寬:
row_dimensions[row_num].height = xx
,sheet.column_dimensions[col_name].width = xx
-
字體( Font 對象):包括字段名稱,大小、加粗、斜體、顏色等,
Font(name="微軟雅黑", size=20, bold=True, italic=True, color="000000")
-
邊框( Border 對象和 Side 對象):邊框每一條邊的格式大小/顏色
Side(style="thin", color="000000")
,通過邊構建邊框對象:Border(left=side, right=side, top=side, bottom=side)
-
對齊( Alignment 對象):垂直和水平對齊方向,是否自動換行。
Alignment(horizontal="center", vertical="center", wrap_text=True)
-
填充顏色,分爲普通顏色填充和漸變顏色填充:
PatternFill(fill_type="solid", fgColor="FF0000")
和GradientFill(stop=("FF0000", "FD1111", "000000"))
# 設置行高和列寬 sheet.row_dimensions[1].height = 50 sheet.column_dimensions["A"].width = 20 # 設置單元格字體 cell = sheet["A1"] current_font = cell.font font = Font(name="微軟雅黑", size=20, bold=True, italic=True, color="000000") cell.font = font # 設置邊框(細邊,黑色) side_style = Side(style="thin", color="000000") border = Border(left=side_style, right=side_style, top=side_style, bottom=side_style) cell.border = border # 居中對齊,自動換行 cell_alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) cell.alignment = cell_alignment # 填充顏色(紅色填充,和紅色到黑色漸變填充) p_fill = PatternFill(fill_type="solid", fgColor="FF0000") g_fill = GradientFill(stop=("FF0000", "FD1111", "000000")) cell.fill = p_fill sheet["B1"].fill = g_fill
最後注意的是,這些修改操作最後都需要通過保存操作( wb.save(file_path)
)才能生效。
6 總結
通過上面的講解,瞭解如何使用 python 的 openpyxl 庫對 excel 文檔的處理操作,可以發現它的操作邏輯相當是清晰簡單的,符合的我們使用 excel 的習慣。處理流程基本是加載文件、定位需要處理的工作表、行、列及單元格。對它們進行讀、寫、修改格式等操作。因此,如果有自動化處理 excel 文件的需求,用 openpyxl 吧,但它限制只能處理 2010 格式的 excel 文檔,對於舊格式( xls )的建議都統一換爲新的格式再操作,或者也可以使用 xlrd 和 xlwt 模塊操作。
參考資料
-
openpyxl官方文檔 :
https://openpyxl.readthedocs.io/