目錄

一、一圖概覽

二、表格操作

三、內容操作

四、單元格操作

五、Pandas實現表格操作

六、常見場景示例

---

一、一圖概覽

??????????本文主要對openpyxl庫的常用表格操作進行了梳理，熟練的運用后可極大地提升工作效率。

二、表格操作

#創建一個表格sheet.xlsx
#使用openpyxl創建工作簿
wb=workbook.workbook.Workbook()  #創建一個工作簿對象
wb.save("test.xlsx")     #在py文件同級目錄下創建一個空表
wb.save(r"C:\Users\ZYP_PC\Desktop\test_python\file\test2.xlsx")  #在指定目錄下創建表格sheet2.xlsx#在表格中創建子表
wb.create_sheet(title="sheet_a")  #創建子表sheet_a
wb.save("test.xlsx")             #將子表保存到工作簿test.xlsx中#從工作簿test.xlsx中刪除子表sheet_b
wb=load_workbook("test.xlsx")
delete_sheet=wb["sheet_b"]   #子表不存在會報文件不存在錯誤
wb.remove(delete_sheet)
wb.save("test.xlsx")#修改test.xlsx中子表sheet_b的名稱為sheet_a
wb=load_workbook("test.xlsx")   #加載工作簿test.xlsx
sheet_names=wb.sheetnames   #獲取所有子表名稱
old="sheet_b"
new="sheet_a"
ws=wb[old]     #獲取old的子表
ws.title=new   #修改title屬性值
wb.save("test.xlsx")

三、內容操作

#單元格內容遍歷
wb=load_workbook("20240417.xlsx")
ws=wb.active
for row in ws.iter_rows(values_only=True): ?#按行優先遍歷,沒有內容的顯示為Nonefor r in row:print(r)
for col in ws.iter_cols(values_only=True): ?#按列優先遍歷，沒有內容的顯示為Nonefor c in col:print(c)#讀取指定位置A1的值
A1=ws["A1"].value#讀取指定行如第3行的內容
row=[]
for col in ws.iter_cols(values_only=True):row.append(col[2]) ? #索引從0開始，因為第3行為索引值為2
print("row",row)#讀取指定列如第2列的內容
col=[]
for row in ws.iter_rows(values_only=True):col.append(row[1]) ? #索引從0開始，因為第二列為1
print("col",col)

內容操作之內容寫入

#在工作簿test.xlsx的工作表的sheet_b中的單元格C3寫入你好
#方法1(以字母加數字的方式表示坐標)
wb=load_workbook("test.xlsx")
ws=wb["sheet_b"]
ws["A3"]="你好"
wb.save("test.xlsx") ?#保存修改，修改內容時表格不能打開，否則會提示無權限
#方法2(以行列數字序號表示)
wb=load_workbook("test.xlsx")
ws=wb["sheet_b"]
r=3
c=3
ws.cell(row=r,column=c,value="你好1")
wb.save("test.xlsx") ?#保存修改，修改內容時表格不能打開，否則會提示無權限##逐行寫入
wb=workbook.Workbook() ? ? ? #創建工作簿
ws=wb.active
ws.title="運行時間與內存" ?#設置默認表格名稱sheet為指定的名稱
ws=wb.create_sheet(title="運行時間與內存") #也可以另外創建子表
excel=r"C:\Users\ZYP_PC\Desktop\test_python\test2.xlsx"
start_row=3 #從指定行開始寫入
ws.cell(row=start_row,column=1,value="工程名")
ws.cell(row=start_row,column=2,value="布局時間")
ws.cell(row=start_row,column=3,value="布線時間")
ws.cell(row=start_row,column=4,value="report_timing")
ws.cell(row=start_row,column=5,value="PR內存")
ws.append(("工程名1","布局時間","布線時間","report_timing時間","PR內存")) #各列的名稱
data=[["prj1","1","22","23","34"],["prj2","11","22","23","34"],["prj3","111","22","23","34"]]
for row in data:print("row ",row)ws.append(row)
wb.save(excel)

四、單元格操作

from openpyxl import workbook
from openpyxl import load_workbook
from openpyxl.styles import Font,Alignment,Color,PatternFill
#打開指定表格進行單元格操作
excel=r"C:\Users\ZYP_PC\Desktop\test_python\test2.xlsx"
wb=load_workbook(excel)
ws=wb.active
ws=wb["測試子表"]  #進行操作的子表#插入2行
ws.insert_rows(4,2)   #從第4行的位置插入2行#插入3列
ws.insert_cols(3,1)  #從第3列開始插入1列#刪除指定行
ws.delete_rows(10)  #刪除第10行#刪除指定列
ws.delete_cols(3)  #刪除第3列#合并單元格
ws.merge_cells("A4:B4")  #將A4和B4進行合并單元格操作#取消合并單元格
ws.unmerge_cells("A4:B4") #取消將A4和B4進行合并單元格操作#設置單元格格式
font=Font(name="Arial",size=12,bold=True,italic=False,color="FF0000",) #對單元格內容進行字體格式，大小，加粗，顏色當進行設置
ws["B3"].font=font
alignment=Alignment(horizontal="center",vertical="center")  #對單元格內容進行對齊設置,例如此處為居中對齊
ws["A5"].alignment=alignment
fill=PatternFill(start_color="FFFF00",end_color="FFFF00",fill_type="solid")  #對單元格設置背景顏色,此處為黃色
ws["A5"].fill=fill
wb.save(excel)

對于單元格格式的設置中，顏色與代碼的映射關系可在下面網站進行查詢

https://www.bchrt.com/tools/rgbcolor/

內容的對齊方式可選值有horizontal（水平對齊方式，包括?"general",?"left",?"center",?"right",?"fill",?"justify",?"centerContinuous","distributed"）

vertical（垂直對齊方式，包括"top",?"center",?"bottom",?"justify",?"distributed"）

textRotation（文字旋轉），wrapText?（自動換行），shrinkToFit（自動縮放到最佳位置），indent（縮進），relativelndent（相對縮進），justifyLastLine（最后一行對齊），reading?Order（讀順序對齊）

五、Pandas實現表格操作

下面針對pandas庫的一些常用表格查詢操作進行了梳理

##使用pandas庫處理表格，pandas對于數據的處理功能更強大,更適合對數據進行各種計算
import pandas as pd
path_excel=r"C:\Users\ZYP_PC\Desktop\test_python\pandas.xlsx"  #工作簿路徑
#方法1,以列表形式寫入
# data = [('張三', 20, '男'),
#         ('李四', 25, '女'),
#         ('王五', 30, '男')]
#df = pd.DataFrame(data, columns=['姓名', '年齡', '性別'])
#方法2，以字典形式寫入
data = {'姓名': ['張三', '李四', '王五'],'年齡': [20, 25, 30],'性別': ['男', '女', '男']}
df=pd.DataFrame(data)    #pandas不支持直接創建工作簿,data數據可以是列表，字典
df.to_excel(path_excel,index=False)   #創建表格pandas.xlsx，并將data數據寫入#數據查詢
path_excel=r"C:\Users\ZYP_PC\Desktop\test_python\pandas1.xlsx"
data=pd.read_excel(path_excel)  #讀取表格
size=data.size    #獲取表格的數據個數
shape=data.shape   #獲取數據的行列數，不包含標題欄
#查看指定行和指定列的內容
print(data.loc[1:2,"年齡"])   #查找1到2行中列名為年齡的內容
# 查看前4行內容
print(data.head(4))
#查看后5行內容
print(data.tail(5))
#根據列名選擇列
single_col=data["年齡"]   #選中單列
multi_col=data[["年齡","性別"]]   #選中多列
#選擇行
single_row=data.iloc[0:1]   #除去標題行后的內容進行篩選
print("row",single_row)# print(data.size,data.shape)
for index,row in data.iterrows():  #按行逐條遍歷print("row:",row)

六、常見場景示例

示例1 遍歷整個工作簿查找包含指定內動的單元格

from openpyxl import workbook
from openpyxl import load_workbook
excel=r"C:\Users\ZYP_PC\Desktop\test_python\測試表格.xlsx"
wb=load_workbook(excel)
ws=wb.active
search_value="搜索的內容"
site=[]     #保存查找結果的單元格地址
sheet_list=[]   #保存查找結果所在的子表名稱
for sheet_name in wb.sheetnames:sheet=wb[sheet_name]for row in sheet.iter_rows():for cell in row:if cell.value==search_value:site.append(cell.coordinate)cell.value=""sheet_list.append(sheet_name)

#示例2 在指定子表的指定范圍內進行指定內容的查找，范圍可指定行范圍，列范圍或區域范圍

from openpyxl import workbook
from openpyxl import load_workbook
excel=r"C:\Users\ZYP_PC\Desktop\test_python\測試表格.xlsx"
wb=load_workbook(excel)
ws=wb.active
ws=wb["VCC"]     #查找的子表名稱為"VCC"
search_value="INPUT"
cells=ws["A3:C6"]  #指定一個區域
# cells=ws["B:F"]  #指定B到F列
# cells=ws[2:6]   #指定第2到第6行
site=[]
for row in cells:for cell in row:if search_value in str(cell.value):site.append(cell.coordinate)
print(site)