Python操作Excel神器openpyxl使用教程(超詳細!)

更新時間：2023年01月06日 15:54:38 作者：信息技術(shù)王鳳龍

openpyxl庫是一個很好處理xlsx的python庫,下面這篇文章主要給大家介紹了關(guān)于Python辦公自動化openpyxl使用的相關(guān)資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下

前言

openpyxl是Python下的Excel庫，它能夠很容易的對Excel數(shù)據(jù)進行讀取、寫入以及樣式的設(shè)置，能夠幫助我們實現(xiàn)大量的、重復(fù)的Excel操作，提高我們的辦公效率，實現(xiàn)Excel辦公自動化。

安裝方法：pip install openpyxl
中文文檔：https://www.osgeo.cn/openpyxl/index.html#usage-examples
工作簿、工作表、單元格之間的關(guān)系：
- 一個工作簿(workbook)由多個工作表(worksheet)組成；
- 一個工作表有多個單元格(cell)組成；
- 通過行(row)和列(column)可以定位到單元格。

新建并寫入文件

Workbook()：新建excel文件，新建文件時默認有一個名為Sheet工作表

# coding=utf-8
from openpyxl import Workbook
wb = Workbook() # 新建工作簿
ws = wb.active # 獲取工作表
ws.append(['姓名', '學號', '年齡']) # 追加一行數(shù)據(jù)
ws.append(['張三', "1101", 17]) # 追加一行數(shù)據(jù)
ws.append(['李四', "1102", 18]) # 追加一行數(shù)據(jù)
wb.save(r'測試1.xlsx') # 保存到指定路徑，保存的文件必須不能處于打開狀態(tài)，因為文件打開后文件只讀

打開并讀取文件

load_workbook(path)：加載指定路徑的excel文件

# coding=utf-8
from openpyxl import load_workbook
wb = load_workbook(r'測試1.xlsx') # 獲取已存在的工作簿
ws = wb.active # 獲取工作表
for row in ws.values: # 輸出所有數(shù)據(jù)
    print(row)

工作簿對象

wb.active ：獲取第一張工作表對象
wb[sheet_name] ：獲取指定名稱的工作表對象
wb.sheetnames ：獲取所有工作表名稱
wb.worksheets：獲取所有工作表對象，wb.worksheets[0]可以根據(jù)索引獲取工作表，0代表第一個
wb.create_sheet(sheet_name,index=“end”)：創(chuàng)建并返回一個工作表對象，默認位置最后，0代表第一個
wb.copy_worksheet(sheet)：在當前工作簿復(fù)制指定的工作表并返回復(fù)制后的工作表對象
wb.remove(sheet)：刪除指定的工作表
ws.save(path)：保存到指定路徑path的Excel文件中，若文件不存在會新建，若文件存在會覆蓋

# coding=utf-8
from openpyxl import load_workbook
wb = load_workbook(r"測試1.xlsx")
"""獲取工作表"""
active_sheet = wb.active    # 獲取第一個工作表
print(active_sheet)         # 輸出工作表：<Worksheet "Sheet">
by_name_sheet = wb["Sheet"]    		  # 根據(jù)工作表名稱獲取工作表
by_index_sheet = wb.worksheets[0]     # 根據(jù)工作表索引獲取工作表
"""獲取所有工作表"""
print("獲取所有",wb.sheetnames)
"""新建工作表"""
New_Sheet = wb.create_sheet("New")  # 在最后新建工作表
First_Sheet = wb.create_sheet("First",index=0)  # 在開頭新建工作表
print("新建后",wb.sheetnames)
"""復(fù)制工作表"""
Copy_Sheet = wb.copy_worksheet(active_sheet)    # 復(fù)制第一個工作表
Copy_Sheet.title = "Copy"
print("復(fù)制后",wb.sheetnames)
"""刪除工作表"""
wb.remove(First_Sheet)      # 根據(jù)指定的工作表對象刪除工作表
wb.remove(New_Sheet)
print("刪除后",wb.sheetnames)
wb.save(r"測試2.xlsx")

工作表對象

ws.title：獲取或設(shè)置工作表名
ws.max_row：工作表最大行數(shù)
ws.max_column：工作表最大列數(shù)
ws.append(list)：表格末尾追加數(shù)據(jù)
ws.merge_cells(‘A2:D2’)：合并單元格
ws.unmerge_cells(‘A2:D2’)：解除合并單元格。

# coding=utf-8
from openpyxl import load_workbook
wb = load_workbook(r'測試1.xlsx') # 獲取已存在的工作簿
ws = wb.active
print("工作表名",ws.title)
ws.title = "學生信息表"
print("修改后工作表名",ws.title)
print("最大行數(shù)",ws.max_row)
print("最大列數(shù)",ws.max_column)
ws.append(["王五","1103",17])
print("最大行數(shù)",ws.max_row)
wb.save(r"測試3.xlsx")

單元格讀取

ws[‘A1’]：根據(jù)坐標獲取單個單元格對象
ws.cell(row, column, value=None)：根據(jù)行列獲取單個單元格對象
ws[1]：獲取第一行所有單元格對象，ws[“1”]也可
ws[“A”]：獲取第A列所有單元格對象
ws[“A”:“B”]：獲取A到B列所有單元格對象，ws[“A:B”]也可
ws[1:2]：獲取1到2行所有單元格對象，ws[“1:2”]也可
ws[“A1”:“B2”]：獲取A1到B2范圍所有單元格對象，ws[“A1:B2”]也可。

# coding=utf-8
from openpyxl import load_workbook
wb = load_workbook(r'測試1.xlsx')
ws = wb.active
A1 = ws["A1"]     # 根據(jù)坐標獲取單個單元格
print("第一行第一列",ws.cell(1,1))    # 根據(jù)行列獲取單個單元格
print("第一行",ws[1])
print("第A列",ws["A"])
print("A到B列",ws["A":"B"])
print("1到2行",ws["1":"2"])
print("A1到B2范圍",ws["A1":"B2"])

ws.values：獲取所有單元格數(shù)據(jù)的可迭代對象，可以通過for循環(huán)迭代或通過list(ws.values)轉(zhuǎn)換為數(shù)據(jù)列表

# coding=utf-8
from openpyxl import load_workbook
wb = load_workbook(r'測試1.xlsx')     # 獲取已存在的工作簿
ws = wb.active  # 獲取工作表
for row in ws.values:       # for循環(huán)迭代
    print(row)
print(list(ws.values))	    # 轉(zhuǎn)換為數(shù)據(jù)列表

ws.rows：獲取所有數(shù)據(jù)以行的格式組成的可迭代對象

ws.columns：獲取所有數(shù)據(jù)以列的格式組成的可迭代對象

# coding=utf-8
from openpyxl import load_workbook
wb = load_workbook(r'測試1.xlsx')
ws = wb.active
for row in ws.rows:  # 以行的形式迭代
    print(row)
print("-"*55)
for col in ws.columns:  # 以列的形式迭代
    print(col)

ws.iter_rows(min_row=None, max_row=None, min_col=None, max_col=None)：獲取指定邊界范圍并以行的格式組成的可迭代對象，默認所有行

ws.iter_cols(min_col=None, max_col=None, min_row=None, max_row=None)：獲取指定邊界范圍并以列的格式組成的可迭代對象，默認所有列

# coding=utf-8
from openpyxl import load_workbook
wb = load_workbook(r'測試1.xlsx')
ws = wb.active
for row in ws.iter_rows(max_row=2,max_col=2):  # 指定邊界范圍并以行的形式可迭代
    print(row)
print("-"*35)
for column in ws.iter_cols(max_row=2,max_col=2):  # 指定邊界范圍并以行的形式可迭代
    print(column)

單元格對象

cell.value ：獲取或設(shè)置值
cell.column ：數(shù)字列標
cell.column_letter ：字母列標
cell.row ：行號
cell.coordinate ：坐標，例如’A1’
cell.data_type ：數(shù)據(jù)類型， ’s‘ = string字符串,‘n’ = number數(shù)值，會根據(jù)單元格值自動判斷
cell.number_format ：單元格格式，默認”General“常規(guī)，詳見excel自定義數(shù)據(jù)類型

# coding=utf-8
from openpyxl import Workbook
wb = Workbook() # 新建工作簿
ws = wb.active
"""獲取與設(shè)置單元格值的兩種方式"""
cell1 = ws.cell(1,1)    # 先獲取第一行第一列的單元格對象
cell1.value = 18		# 再設(shè)置單元格對象的值
print("值",cell1.value)
print("數(shù)字列標",cell1.column)
print("字母列標",cell1.column_letter)
print("行號",cell1.row)
print("坐標",cell1.coordinate)
cell2 = ws.cell(2,1,17) # 直接在獲取單元格的時候設(shè)置值
"""使用公式和不適用公式"""
cell3 = ws.cell(3,1,"=A1+A2") # 直接輸入公式具有計算功能
cell4 = ws.cell(4,1,"=A1+A2")
cell4.data_type = 's' # 指定單元格數(shù)據(jù)類型為文本可以避免公式被計算
"""設(shè)置格式和不設(shè)置格式"""
cell5 = ws.cell(5,1,3.1415) # 默認常規(guī)格式
cell6 = ws.cell(6,1,3.1415)
cell6.number_format = "0.00" # 設(shè)置格式為保留兩位小數(shù)
wb.save(r'測試4.xlsx') # 保存到指定路徑

單元格樣式

cell.font ：獲取或設(shè)置單元格Font對象（字體名稱，字體大小，是否加粗，字體顏色等）
cell.border ：獲取或設(shè)置單元格邊框
cell.alignment ：獲取或設(shè)置單元格水平/垂直對齊方式
cell.fill：獲取或設(shè)置單元格填充顏色

from openpyxl import Workbook
from openpyxl.styles import Font, Border, Side, Alignment,PatternFill
from copy import copy
wb = Workbook()
ws = wb.active
"""獲取單元格并設(shè)置單元格值為 姓名 """
cell = ws.cell(1,1,"姓名")
"""設(shè)置單元格文字樣式"""
cell.font = Font(bold=True, # 加粗
                         italic=True, # 傾斜
                         name="楷體", # 字體
                         size=13, # 文字大小
                         color="FF0000" # 字體顏色為紅色
                         )
"""復(fù)制單元格樣式"""
cell2 = ws.cell(1,2,"學號")
cell2.font = copy(cell.font)
"""設(shè)置單元格邊框為黑色邊框"""
cell.border = Border(bottom=Side(style='thin', color='000000'),
                             right=Side(style='thin', color='000000'),
                             left=Side(style='thin', color='000000'),
                             top=Side(style='thin', color='000000'))
"""設(shè)置單元格對齊方式為水平垂直居中"""
cell.alignment = Alignment(horizontal='center',vertical='center')
"""設(shè)置單元格底紋顏色為黃色"""
cell.fill = PatternFill(fill_type='solid', start_color='FFFF00')
"""
	白色：FFFFFF，黑色：000000，紅色：FF0000，黃色：FFFF00
	綠色：00FF00，藍色：0000FF，橙色：FF9900，灰色：C0C0C0
	常見顏色代碼表：https://www.osgeo.cn/openpyxl/styles.html#indexed-colours
"""
wb.save(r"測試5.xlsx")

列寬與行高

ws.row_dimensions[行號]：獲取行對象（非行數(shù)據(jù)，包括行的相關(guān)屬性、行高等）
ws.column_dimensions[字母列標]：獲取列對象（非行數(shù)據(jù)，包括行的相關(guān)屬性、列寬等）
get_column_letter(index)：根據(jù)列的索引返回字母
column_index_from_string(string)：根據(jù)字母返回列的索引
row.height：獲取或設(shè)置行高
column.width：獲取或設(shè)置列寬

from openpyxl import Workbook
from openpyxl.utils import get_column_letter,column_index_from_string
wb = Workbook()
ws = wb.active
"""行"""
row = ws.row_dimensions[1]  # 獲取第一行行對象
print("行號",row.index)
row.height = 20     # 設(shè)置行高
print("行高",row.height)
"""列"""
column = ws.column_dimensions["A"]     # 根據(jù)字母列標獲取第一列列對象
column = ws.column_dimensions[get_column_letter(1)]    # 根據(jù)數(shù)字列標獲取第一列列對象
print("字母列標",column.index)
print("數(shù)字列標",column_index_from_string(column.index))
column.width = 15  # 設(shè)置列寬
print("列寬",column.width)
wb.save(r'測試6.xlsx')

如何根據(jù)輸入內(nèi)容計算其在excel的列寬是多少？

利用GBK編碼方式，非漢字字符占1個長度，漢字字符占2個長度

from openpyxl import Workbook
from openpyxl.utils import get_column_letter,column_index_from_string
wb = Workbook()
ws = wb.active
column = ws.column_dimensions[get_column_letter(1)]    # 根據(jù)數(shù)字列標獲取第一列列對象
value = "我愛中國ILoveChain"	# 4*2+10*1+1=19
column.width = len(str(value).encode("GBK"))+1  # 根據(jù)內(nèi)容設(shè)置列寬,+1既可以補充誤差又可以讓兩邊留有一定的空白，美觀
print("列寬",column.width)	# 輸出：19
ws.cell(1,1,value)
wb.save(r'測試6.xlsx')

插入和刪除行和列

插入和刪除行、列均使用數(shù)字指定

ws.insert_rows(row_index,amount=1)：在第row_index行上方插入amount列，默認插入1列
ws.insert_cols(col_index,amount=1)：在第col_index列左側(cè)插入amount列，默認插入1列
ws.delete_rows(row_index,amount=1)：從row_index行開始向下刪除amount行，默認刪除1行
ws.delete_cols(col_index,amount=1)：從col_index列開始向右刪除amount行，默認刪除1列

from openpyxl import Workbook,load_workbook
wb = load_workbook("測試1.xlsx")
ws = wb.active
ws.insert_rows(1,2)     # 在第一行前插入兩行
delete_col_index = [1,3]    # 刪除1、3兩列
"""為避免刪除多列時前面列對后面列產(chǎn)生影響，采取從后面列往前面列刪的策略,行同理"""
delete_col_index.sort(reverse=True)     # 從大到小排序
for col_index in delete_col_index:
    ws.delete_cols(col_index)
wb.save(r'測試7.xlsx')

綜合寫入實踐

寫入后的效果如下：

# coding=utf-8
from openpyxl import Workbook
from openpyxl.styles import Font, Border, Side, Alignment, PatternFill
wb = Workbook()
ws = wb.active
"""設(shè)置全局樣式"""
border = Border(bottom=Side(style='thin', color='000000'),
                right=Side(style='thin', color='000000'),
                left=Side(style='thin', color='000000'),
                top=Side(style='thin', color='000000'))
alignment = Alignment(horizontal='center', vertical='center')
row_index = 1 # 寫入的行索引，每寫入一行后+1
"""寫入標題"""
title = ['姓名', '學號', '分數(shù)']
for index,item in enumerate(title):
    cell = ws.cell(row_index,index+1,item)
    cell.border = border
    cell.alignment = alignment
    cell.font = Font(bold=True)
row_index += 1
data = [['張三', "1101", 17],['李四', "3412", 18],['王五', "1103", 16]]
"""寫入正文"""
for row in data:
    for index,item in enumerate(row):
        cell = ws.cell(row_index, index + 1, item)
        cell.border = border
        cell.alignment = alignment
    row_index += 1
"""寫入結(jié)果"""
result = ["", "合計", 17+18+16]
for index,item in enumerate(result):
    cell = ws.cell(row_index,index+1,item)
    cell.border = border
    cell.alignment = alignment
    cell.fill = PatternFill(fill_type='solid', start_color="FFFF00")
wb.save(r"學生信息表.xlsx")

合并表格

# coding=utf-8
from openpyxl import Workbook,load_workbook
import os
dir_path = "學生名單"  # 要合并文件的文件夾地址
"""讀取文件夾下的所有excel文件"""
files = []
for file in os.listdir(dir_path): # 獲取當前目錄下的所有文件
    files.append(os.path.join(dir_path,file)) # 獲取文件夾+文件名的完整路徑
"""以第一個文件為基本表"""
merge_excel = load_workbook(files[0])
merge_sheet = merge_excel.active
"""遍歷剩余文件，追加到基本表"""
for file in files[1:]:
    wb = load_workbook(file)
    ws = wb.active
    for row in list(ws.values)[1:]:  # 從第二行開始讀取每一行并追加到基本表
        merge_sheet.append(row)
merge_excel.save("高一學生匯總.xlsx")

拆分表格

# coding=utf-8
from openpyxl import Workbook,load_workbook
import os
file_path = "高一學生匯總.xlsx" # 要拆分的文件地址
split_dir = "拆分結(jié)果"  # 拆分文件后保存的文件夾
group_item = "班級"	 # 拆分的依據(jù)字段
"""打開拆分的excel文件并讀取標題"""
wb = load_workbook(file_path)
ws = wb.active
title = []
for cell in ws[1]:
    title.append(cell.value)
"""開始分組，分組結(jié)果保存到字典，鍵為班級名，值為班級學生列表"""
group_result = {}  # 存儲分組結(jié)果
group_index = title.index(group_item) 	# 獲取拆分依據(jù)字段的索引
for row in list(ws.values)[1:]:
    class_name = row[group_index] # 獲取分組依據(jù)數(shù)據(jù)，即班級名
    if class_name in group_result:    # 如果分組存在就追加，不存在就新建
        group_result[class_name].append(row)
    else:
        group_result[class_name] = [row]
"""創(chuàng)建輸出文件夾"""
if not os.path.exists(split_dir):   # 如果不存在文件夾就新建
    os.mkdir(split_dir)
os.chdir(split_dir)     # 進入拆分文件夾
"""打印并輸出分組后的數(shù)據(jù)"""
for class_name,students in group_result.items():
    new_wb = Workbook()     # 新建excel
    new_ws = new_wb.active
    new_ws.append(title)    # 追加標題
    for student in students:
        new_ws.append(student)  # 講分組數(shù)組追加到新excel中
    new_wb.save("{}.xlsx".format(class_name))

作業(yè)提交情況檢測

# encoding: utf-8
import os
from openpyxl import Workbook, load_workbook
excel_path = r"學生名單/高一1班.xlsx"    # excel文件路徑
job_path = r"作業(yè)"     # 作業(yè)文件夾路徑
"""獲取姓名列表"""
wb = load_workbook(excel_path)
ws = wb.active
names = []
for cell in ws["C"][1:]:	# 獲取第C列第2行開始的數(shù)據(jù)
    names.append(cell.value)
"""獲取作業(yè)列表"""
os.chdir(job_path)  # 切換到作業(yè)目錄
files = []    # 獲取文件列表
for file in os.listdir():
    files.append(os.path.splitext(file)[0])
"""作業(yè)檢測"""
yes,no = [],[]
for name in names:     # 逐個姓名判斷
    if name in files:   # 判斷姓名是否在文件列表中
        yes.append(name)    # 如果在，添加到已完成名單
    else:
        no.append(name)     # 否則，添加到未完成名單
print("已完成人數(shù)：{}，已完成名單：{}".format(len(yes),yes))
print("未完成人數(shù)：{}，未完成名單：{}".format(len(no),no))