Python操作Excel神器openpyxl使用教程(超詳細!)
更新時間：2023年01月06日 15:54:38 作者：信息技術王鳳龍
openpyxl庫是一個很好處理xlsx的python庫,下面這篇文章主要給大家介紹了關于Python辦公自動化openpyxl使用的相關資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下
前言
openpyxl是Python下的Excel庫，它能夠很容易的對Excel數據進行讀取、寫入以及樣式的設置，能夠幫助我們實現大量的、重復的Excel操作，提高我們的辦公效率，實現Excel辦公自動化。
- 安裝方法：pip install openpyxl
- 中文文檔：https://www.osgeo.cn/openpyxl/index.html#usage-examples
- 工作簿、工作表、單元格之間的關系：
- 一個工作簿(workbook)由多個工作表(worksheet)組成；
- 一個工作表有多個單元格(cell)組成；
- 通過行(row)和列(column)可以定位到單元格。
新建并寫入文件
Workbook()：新建excel文件，新建文件時默認有一個名為Sheet工作表
# coding=utf-8 from openpyxl import Workbook wb = Workbook() # 新建工作簿 ws = wb.active # 獲取工作表 ws.append(['姓名', '學號', '年齡']) # 追加一行數據 ws.append(['張三', "1101", 17]) # 追加一行數據 ws.append(['李四', "1102", 18]) # 追加一行數據 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: # 輸出所有數據
print(row)
工作簿對象
- wb.active ：獲取第一張工作表對象
- wb[sheet_name] ：獲取指定名稱的工作表對象
- wb.sheetnames ：獲取所有工作表名稱
- wb.worksheets：獲取所有工作表對象，wb.worksheets[0]可以根據索引獲取工作表，0代表第一個
- wb.create_sheet(sheet_name,index=“end”)：創(chuàng)建并返回一個工作表對象，默認位置最后，0代表第一個
- wb.copy_worksheet(sheet)：在當前工作簿復制指定的工作表并返回復制后的工作表對象
- 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"] # 根據工作表名稱獲取工作表
by_index_sheet = wb.worksheets[0] # 根據工作表索引獲取工作表
"""獲取所有工作表"""
print("獲取所有",wb.sheetnames)
"""新建工作表"""
New_Sheet = wb.create_sheet("New") # 在最后新建工作表
First_Sheet = wb.create_sheet("First",index=0) # 在開頭新建工作表
print("新建后",wb.sheetnames)
"""復制工作表"""
Copy_Sheet = wb.copy_worksheet(active_sheet) # 復制第一個工作表
Copy_Sheet.title = "Copy"
print("復制后",wb.sheetnames)
"""刪除工作表"""
wb.remove(First_Sheet) # 根據指定的工作表對象刪除工作表
wb.remove(New_Sheet)
print("刪除后",wb.sheetnames)
wb.save(r"測試2.xlsx")
工作表對象
- ws.title：獲取或設置工作表名
- ws.max_row：工作表最大行數
- ws.max_column：工作表最大列數
- ws.append(list)：表格末尾追加數據
- 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("最大行數",ws.max_row)
print("最大列數",ws.max_column)
ws.append(["王五","1103",17])
print("最大行數",ws.max_row)
wb.save(r"測試3.xlsx")
單元格讀取
- ws[‘A1’]：根據坐標獲取單個單元格對象
- ws.cell(row, column, value=None)：根據行列獲取單個單元格對象
- 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"] # 根據坐標獲取單個單元格
print("第一行第一列",ws.cell(1,1)) # 根據行列獲取單個單元格
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：獲取所有單元格數據的可迭代對象，可以通過for循環(huán)迭代或通過list(ws.values)轉換為數據列表
# 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)) # 轉換為數據列表
ws.rows：獲取所有數據以行的格式組成的可迭代對象
ws.columns：獲取所有數據以列的格式組成的可迭代對象
# 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 ：獲取或設置值
- cell.column ： 數字列標
- cell.column_letter ： 字母列標
- cell.row ： 行號
- cell.coordinate ： 坐標，例如’A1’
- cell.data_type ： 數據類型， ’s‘ = string字符串,‘n’ = number數值，會根據單元格值自動判斷
- cell.number_format ：單元格格式，默認”General“常規(guī)，詳見excel自定義數據類型
# coding=utf-8
from openpyxl import Workbook
wb = Workbook() # 新建工作簿
ws = wb.active
"""獲取與設置單元格值的兩種方式"""
cell1 = ws.cell(1,1) # 先獲取第一行第一列的單元格對象
cell1.value = 18 # 再設置單元格對象的值
print("值",cell1.value)
print("數字列標",cell1.column)
print("字母列標",cell1.column_letter)
print("行號",cell1.row)
print("坐標",cell1.coordinate)
cell2 = ws.cell(2,1,17) # 直接在獲取單元格的時候設置值
"""使用公式和不適用公式"""
cell3 = ws.cell(3,1,"=A1+A2") # 直接輸入公式具有計算功能
cell4 = ws.cell(4,1,"=A1+A2")
cell4.data_type = 's' # 指定單元格數據類型為文本可以避免公式被計算
"""設置格式和不設置格式"""
cell5 = ws.cell(5,1,3.1415) # 默認常規(guī)格式
cell6 = ws.cell(6,1,3.1415)
cell6.number_format = "0.00" # 設置格式為保留兩位小數
wb.save(r'測試4.xlsx') # 保存到指定路徑
單元格樣式
- cell.font ：獲取或設置單元格Font對象 （字體名稱，字體大小，是否加粗，字體顏色等）
- cell.border ： 獲取或設置單元格邊框
- cell.alignment ： 獲取或設置單元格水平/垂直對齊方式
- cell.fill：獲取或設置單元格填充顏色
from openpyxl import Workbook
from openpyxl.styles import Font, Border, Side, Alignment,PatternFill
from copy import copy
wb = Workbook()
ws = wb.active
"""獲取單元格并設置單元格值為 姓名 """
cell = ws.cell(1,1,"姓名")
"""設置單元格文字樣式"""
cell.font = Font(bold=True, # 加粗
italic=True, # 傾斜
name="楷體", # 字體
size=13, # 文字大小
color="FF0000" # 字體顏色為紅色
)
"""復制單元格樣式"""
cell2 = ws.cell(1,2,"學號")
cell2.font = copy(cell.font)
"""設置單元格邊框為黑色邊框"""
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'))
"""設置單元格對齊方式為水平垂直居中"""
cell.alignment = Alignment(horizontal='center',vertical='center')
"""設置單元格底紋顏色為黃色"""
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[行號]：獲取行對象（非行數據，包括行的相關屬性、行高等）
- ws.column_dimensions[字母列標]：獲取列對象（非行數據，包括行的相關屬性、列寬等）
- get_column_letter(index)：根據列的索引返回字母
- column_index_from_string(string)：根據字母返回列的索引
- row.height：獲取或設置行高
- column.width：獲取或設置列寬
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 # 設置行高
print("行高",row.height)
"""列"""
column = ws.column_dimensions["A"] # 根據字母列標獲取第一列列對象
column = ws.column_dimensions[get_column_letter(1)] # 根據數字列標獲取第一列列對象
print("字母列標",column.index)
print("數字列標",column_index_from_string(column.index))
column.width = 15 # 設置列寬
print("列寬",column.width)
wb.save(r'測試6.xlsx')
如何根據輸入內容計算其在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)] # 根據數字列標獲取第一列列對象
value = "我愛中國ILoveChain" # 4*2+10*1+1=19
column.width = len(str(value).encode("GBK"))+1 # 根據內容設置列寬,+1既可以補充誤差又可以讓兩邊留有一定的空白，美觀
print("列寬",column.width) # 輸出：19
ws.cell(1,1,value)
wb.save(r'測試6.xlsx')
插入和刪除行和列
插入和刪除行、列均使用數字指定
- ws.insert_rows(row_index,amount=1)：在第row_index行上方插入amount列，默認插入1列
- ws.insert_cols(col_index,amount=1)：在第col_index列左側插入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兩列
"""為避免刪除多列時前面列對后面列產生影響，采取從后面列往前面列刪的策略,行同理"""
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
"""設置全局樣式"""
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 = ['姓名', '學號', '分數']
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
"""寫入結果"""
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 = "拆分結果" # 拆分文件后保存的文件夾
group_item = "班級" # 拆分的依據字段
"""打開拆分的excel文件并讀取標題"""
wb = load_workbook(file_path)
ws = wb.active
title = []
for cell in ws[1]:
title.append(cell.value)
"""開始分組，分組結果保存到字典，鍵為班級名，值為班級學生列表"""
group_result = {} # 存儲分組結果
group_index = title.index(group_item) # 獲取拆分依據字段的索引
for row in list(ws.values)[1:]:
class_name = row[group_index] # 獲取分組依據數據，即班級名
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) # 進入拆分文件夾
"""打印并輸出分組后的數據"""
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) # 講分組數組追加到新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行開始的數據
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("已完成人數：{}，已完成名單：{}".format(len(yes),yes))
print("未完成人數：{}，未完成名單：{}".format(len(no),no))
總結
到此這篇關于Python操作Excel神器openpyxl使用的文章就介紹到這了,更多相關Python openpyxl使用內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
在本篇內容里我們給大家分享的是關于python安裝pil庫方法及代碼內容，有此需要的朋友們可以參考下。2019-06-06
python使用reportlab實現圖片轉換成pdf的方法
這篇文章主要介紹了python使用reportlab實現圖片轉換成pdf的方法,涉及Python使用reportlab模塊操作圖片轉換的相關技巧,需要的朋友可以參考下2015-05-05
Python?是一種多用途、功能強大的編程語言,具有廣泛的特性和能力,在本文中,我們將探討?15?個高級?Python?技巧,它們有助于改善您的開發(fā)工作流程,并使您的代碼更加高效,更加?Pythonic2023-12-12
這篇文章主要介紹了40個你可能不知道的Python的特點和技巧,需要的朋友可以參考下2020-01-01
這篇文章主要為大家介紹了Python人工智能構建簡單聊天機器人示例詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪2023-03-03
今天小編就為大家分享一篇Django獲取該數據的上一條和下一條方法，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2019-08-08
這篇文章主要介紹了Python編程之求數字平方的實例，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-03-03
這篇文章主要介紹了Django框架表單操作,結合實例形式分析了Django框架表單數據發(fā)送、請求相關操作技巧與注意事項,需要的朋友可以參考下2019-11-11
這篇文章主要介紹了在Python 中的復數問題，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-05-05
下面小編就為大家?guī)硪黄猀ueue 實現生產者消費者模型(實例講解)。小編覺得挺不錯的，現在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-11-11