python使用openpyxl庫處理Excel文件詳細(xì)教程

更新時間：2023年11月30日 08:55:56 作者：西涼的悲傷

這篇文章主要給大家介紹了關(guān)于python使用openpyxl庫處理Excel文件詳細(xì)教程的相關(guān)資料,openpyxl屬于第三方模塊,在python中用來處理excel文件,可以對excel進(jìn)行的操作有讀寫、修改、調(diào)整樣式及插入圖片等,需要的朋友可以參考下

前言

python 操作excel 的庫有很多，有的庫只能讀取 xsl 格式，比如 xlrd 庫；

有的庫只能寫 xsl 格式，比如 xlwt 庫;

有的只能讀寫 xslx 格式，比如 openpyxl 庫。

綜合各庫及 xslx 格式比較常見，所以本文主要講解 openpyxl 庫對 xslx 格式的 excel 操作。

一、安裝openpyxl庫

pip install openpyxl

二、新建excel及寫入單元格

1.創(chuàng)建一個xlsx格式的excel文件并保存

from openpyxl import Workbook

# 創(chuàng)建一個工作表
wb = Workbook()
# 保存為本地excel文件
wb.save("F:\pythonTest\sample.xlsx")

2.保存成流(stream)

例如當(dāng)使用 Pyramid, Flask 或 Django 等 web 應(yīng)用程序時，如果想把文件保存成流，可以使用 NamedTemporaryFile( )方法

from tempfile import NamedTemporaryFile
from openpyxl import Workbook

wb = Workbook()
with NamedTemporaryFile() as tmp:
     wb.save(tmp.name)
     tmp.seek(0)
     stream = tmp.read()

3.寫入單元格

from openpyxl import Workbook

wb = Workbook()
# 創(chuàng)建第一個sheet
sheet = wb.active

# 方式一 在第1行第1列的單元格寫入111
sheet['A1'] = 111
# 方式二 在第1行第1列的單元格寫入111
# sheet.cell(row=1, column=1, value=111)
# 方式三 先獲取到單元格，再將數(shù)據(jù)寫入value屬性
# cell = sheet['A1']
# cell.value = 111

wb.save("F:\pythonTest\sample.xlsx")

下面的簡單示例將新建一個 xlsx 文件并在里面新建 3 個 sheet 工作表，每個 sheet 工作表都寫上一點數(shù)據(jù)：

from openpyxl import Workbook

# 初始化
wb = Workbook()

# 創(chuàng)建第一個sheet
sheet = wb.active
# 在第1行第1列的單元格寫入111
sheet['A1'] = 111

# 創(chuàng)建一個新的sheet
sheet1 = wb.create_sheet()
# 在這個新的sheet中第2行第2列的單元格寫入222
sheet1['B2'] = 222

# 創(chuàng)建一個叫"第3個工作表"的新sheet
sheet3 = wb.create_sheet("第3個工作表")
# 在這個新的sheet中第3行第3列的單元格寫入333
sheet3['C3'] = 333

# 保存本地excel文件中
wb.save("F:\pythonTest\sample.xlsx")

三、創(chuàng)建sheet工作表及操作

1.創(chuàng)建新的工作表

一個工作表至少有一個工作表，你可以通過 Workbook.active 來創(chuàng)建第一個sheet

sheet = wb.active

創(chuàng)建新的帶有名字的工作表

# 在結(jié)尾插入一個叫 "我的sheet1" 的sheet
sheet1 = wb.create_sheet("我的sheet1") 
# 或者在最開始插入一個叫 "我的sheet12" 的sheet
sheet2 = wb.create_sheet("我的sheet12", 0)
# 或者在倒數(shù)第二的位置插入一個叫 "我的sheet13" 的sheet
sheet3 = wb.create_sheet("我的sheet13", -1)

2.刪除指定工作表

# 刪除名字是 "我的sheet13" 的工作表
wb.remove(wb['3號sheet'])

3.修改sheet工作表的名稱

# 在結(jié)尾插入一個叫 "我的sheet1" 的工作表
sheet1 = wb.create_sheet("我的sheet1") 
# 將"我的sheet1" 的工作表名字改為"first"
sheet1.title = "first"

4.獲取所有工作表的名稱

print(wb.sheetnames) #['Sheet', '我的sheet1', 'first', 'second']

5.切換工作表

current = wb["first"]
# 切換到 "second" 工作表
current = wb["second"]

6.遍歷所有sheet工作表

for s in wb:
     print(s.title)

7.復(fù)制sheet工作表

sheet = wb.active
sheetNew = wb.copy_worksheet(sheet)

四、讀取excel和單元格

1.讀取 excel 文件

讀取 excel 文件并打印所有的 sheet 工作表名稱

from openpyxl import load_workbook

wb = load_workbook("F:\pythonTest\sample.xlsx")
print(wb.sheetnames) # ['Sheet', 'Sheet1', 'New Title']

2.讀取單元格

from openpyxl import load_workbook

wb = load_workbook("F:\pythonTest\sample.xlsx")
# 獲取"New Title" 這個sheet工作表
sheet = wb["第3個工作表"]

# 方式一 獲取 "C3"這個單元格
cell = sheet["C3"]
# 方式二 獲取 第3行3列 這個單元格
# cell = sheet.cell(row=3, column=3)

# 打印單元格的值
print(cell.value)

3.獲取某一行某一列的數(shù)據(jù)

from openpyxl import load_workbook

wb = load_workbook("F:\pythonTest\sample1.xlsx")
sheet = wb["Sheet"]

# 獲取第2行的單元格數(shù)據(jù)并打印
row = sheet['2']
row_data = []
for cell in row:
    row_data.append(cell.value)
print(row_data)

# 獲取第3列的單元格數(shù)據(jù)并打印
col = sheet['C']
col_data = []
for cell in col:
    col_data.append(cell.value)
print(col_data)

4.遍歷所有單元格

from openpyxl import load_workbook

wb = load_workbook("F:\pythonTest\sample.xlsx")
# 遍歷所有的sheet工作表
for sheet in wb:
    # 獲取當(dāng)前sheet工作表的行數(shù)據(jù)
    rows = tuple(sheet.rows)
    # 遍歷每一行
    for row in rows:
        # 遍歷每一個單元格
        for cell in row:
            print(cell.value)

上面使用了sheet.rows 獲取行數(shù)據(jù)，如果要獲取列數(shù)據(jù)可以使用 sheet.columns

5.遍歷指定行列范圍的單元格

方式一：使用切片方式

from openpyxl import load_workbook

wb = load_workbook("F:\pythonTest\sample.xlsx")
# 獲取叫"Sheet"的工作表
sheet = wb["Sheet"]
# 獲取叫'A2'到'C4'范圍的所有單元格
rows = sheet['A2':'C4']
for cells in rows:
    for cell in cells:
        print(cell.value, end=" ")
    print()

方式二：使用iter_rows方法

from openpyxl import load_workbook

wb = load_workbook("F:\pythonTest\sample.xlsx")
# 獲取叫"Sheet"的工作表
sheet = wb["Sheet"]
# 獲取 2到4行，1到3列 范圍的所有單元格
for row in sheet.iter_rows(min_row=2, min_col=1, max_col=3, max_row=4):
    for cell in row:
        print(cell.value, end=" ")
    print()

五、合并、拆分單元格和插入刪除行列

1.合并單元格

合并單元格時，除了左上角的單元格內(nèi)容，其他都選中范圍單元格將從工作表中刪除

from openpyxl import load_workbook

wb = load_workbook("F:\pythonTest\sample.xlsx")
# 獲取叫"Sheet"的工作表
sheet = wb["Sheet"]

# 方式一 合并 A2到C4 范圍的單元格
sheet.merge_cells('A2:C4')
# 方式二 合并 2到4行，1到3列 范圍的所有單元格
# sheet.merge_cells(start_row=2, start_column=1, end_row=4, end_column=3)

wb.save("F:\pythonTest\sample.xlsx")

2.拆分合并的單元格

from openpyxl import load_workbook

wb = load_workbook("F:\pythonTest\sample.xlsx")
# 獲取叫"Sheet"的工作表
sheet = wb["Sheet"]

# 方式一 拆分 A2到C4 范圍的單元格
sheet.unmerge_cells('A2:C4')
# 方式二 拆分 2到4行，1到3列 范圍的所有單元格
ws.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4)

wb.save("F:\pythonTest\sample.xlsx")

3.插入行和列

(1)插入單行單列

from openpyxl import load_workbook

wb = load_workbook("F:\pythonTest\sample.xlsx")
sheet = wb["Sheet"]
# 在第3行前插入一行
sheet.insert_rows(3)
# 在第2列前插入一列
sheet.insert_cols(2)
wb.save("F:\pythonTest\sample.xlsx")

(2)插入多行多列

from openpyxl import load_workbook

wb = load_workbook("F:\pythonTest\sample.xlsx")
sheet = wb["Sheet"]
# 在第3行前插入四行
sheet.insert_rows(3, 4)
# 在第2列前插入五列
sheet.insert_cols(2, 5)
wb.save("F:\pythonTest\sample.xlsx")

4.刪除行和列

(1)刪除單行單列

from openpyxl import load_workbook

wb = load_workbook("F:\pythonTest\sample.xlsx")
sheet = wb["Sheet"]
# 刪除第3行
sheet.delete_rows(3)
# 刪除第2列
sheet.delete_cols(2)
wb.save("F:\pythonTest\sample.xlsx")

(2)刪除多行多列

from openpyxl import load_workbook

wb = load_workbook("F:\pythonTest\sample.xlsx")
sheet = wb["Sheet"]
# 從第3行前開始刪除四行
sheet.delete_rows(3, 4)
# 從第2列開始刪除五列
sheet.delete_cols(2, 5)
wb.save("F:\pythonTest\sample.xlsx")

六、單元格對齊

1. 對齊方式與換行

# horizontal是水平方向，vertical 是垂直方向
Alignment(horizontal="center", vertical="center")
# 默認(rèn)單元格填滿了是不換行的，如果要自動換行可使用 wrap_text=True
Alignment(horizontal="center", vertical="center", wrap_text=True)

horizontal 水平對齊可選值如下

參數(shù)值	對齊方式
left	左對齊
center	左右居中
right	右對齊
fill	填滿對齊
distributed	分散對齊
centerContinously	連續(xù)居中
justify	兩端對齊
general	一般對齊

vertical 垂直對齊可選值如下

參數(shù)值	對齊方式
top	上對齊
center	左右居中
bottom	下對齊
distributed	分散對齊
justify	兩端對齊

2.單元格對齊

from openpyxl import Workbook
from openpyxl.styles import Alignment

wb = Workbook()
sheet = wb.active

# 在第1行第1列的那個單元格寫入123546
cell = sheet['A1']
cell.value = 123546
# 將該單元格對齊方式設(shè)置為水平和垂直都居中
cell.alignment = Alignment(horizontal="center", vertical="center")

wb.save("F:\pythonTest\sample.xlsx")

3.合并后的單元格設(shè)置對齊方式

可以改變左上單元格的對齊方式、邊框等屬性來改變整個合并單元格的對齊方式、邊框等屬性。

from openpyxl.styles import Alignment
from openpyxl import load_workbook

wb = load_workbook("F:\pythonTest\sample.xlsx")
# 獲取叫"Sheet"的工作表
sheet = wb["Sheet"]

#  合并 A2到C4 范圍的單元格
sheet.merge_cells('A2:C4')
# 改變左上單元格的對齊方式來改變合并單元格的對齊方式
cell = sheet['A2']
cell.alignment = Alignment(horizontal="center", vertical="center")

wb.save("F:\pythonTest\sample.xlsx")

七、單元格邊框設(shè)置

1. 邊框線條粗細(xì)、顏色設(shè)置

（1）你可以對單元格上下左右的邊框進(jìn)行設(shè)置虛線和實線

Border(
    left=Side(style='thick'),
    bottom=Side(style='mediumDashed'),
    right=Side(style='thin'),
    top=Side(style='dashed'))

其中thin是細(xì)實線，thick是粗實線，dashed是細(xì)虛線，mediumDashed是粗虛線.
全部邊框線條可選的有如下：

'dashDot','dashDotDot', 'dashed','dotted',
'double','hair', 'medium', 'mediumDashDot', 
'mediumDashDotDot','mediumDashed', 'slantDashDot',
'thick', 'thin'

（2）你還可以使用 color 參數(shù)調(diào)整單元格上下左右邊框的顏色

Border(
    left=Side(style='thick', color='00000000'),
    bottom=Side(style='mediumDashed', color='00000000'),
    right=Side(style='thin', color='00000000'),
    top=Side(style='dashed', color='00000000'))

可取的顏色參考如下：

2.設(shè)置單個單元格邊框和顏色

from openpyxl import Workbook
from openpyxl.styles import Border, Side

wb = Workbook()
sheet = wb.active

# 在第1行第1列的那個單元格寫入123546
cell = sheet['B2']
cell.value = 123546
# 設(shè)置該單元格邊框和顏色
cell.border = Border(
    left=Side(style='thick', color='00000000'),
    bottom=Side(style='mediumDashed', color='00000000'),
    right=Side(style='thin', color='00000000'),
    top=Side(style='dashed', color='00000000'))

wb.save("F:\pythonTest\sample1.xlsx")

3.設(shè)置多個單元格整體邊框和顏色

想要整體添加邊框，有兩種方式：

（1）先合并單元格再對左上角的單元格進(jìn)行操作，但往往這些單元格都有不同的數(shù)據(jù)，難以進(jìn)行合并。

（2）遍歷所有的單元格，分別賦予屬性。這種方法比較適用于為所有的單元格賦予相同的屬性。

八、設(shè)置單元格背景顏色和文字字體

1.設(shè)置單元格背景顏色

from openpyxl import Workbook
from openpyxl.styles import PatternFill

wb = Workbook()
sheet = wb.active

# 在第1行第1列的那個單元格寫入123546
cell = sheet['B2']
cell.value = 123546
# 使用fgColor屬性16進(jìn)制顏色填充
cell.fill = PatternFill('solid', fgColor="FF00FF")

wb.save("F:\pythonTest\sample1.xlsx")

2.設(shè)置字體、字體的粗細(xì)、大小、顏色

from openpyxl import Workbook
from openpyxl.styles import Font

wb = Workbook()
sheet = wb.active

# 在第1行第1列的那個單元格寫入123546
cell = sheet['B2']
cell.value = 123546
# 設(shè)置字體樣式：  字體大小為30， bold加粗,字體顏色 00FFFF 16進(jìn)制顏色
cell.font = Font(u'微軟雅黑', size=30, bold=True, color="00FFFF")

wb.save("F:\pythonTest\sample1.xlsx")

九、設(shè)置行高和列寬

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

# 設(shè)置第1行高度為60
sheet.row_dimensions[1].height = 60
# 設(shè)置B列寬度為30
sheet.column_dimensions["B"].width = 30

wb.save("F:\pythonTest\sample1.xlsx")

總結(jié)

到此這篇關(guān)于python使用openpyxl庫處理Excel文件的文章就介紹到這了,更多相關(guān)python openpyxl處理Excel內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片