目錄

• 前言
• 一、安裝openpyxl庫
• 二、新建excel及寫入單元格
• • 1.創(chuàng)建一個(gè)xlsx格式的excel文件并保存
  • 2.保存成流(stream)
  • 3.寫入單元格
• 三、創(chuàng)建sheet工作表及操作
• 四、讀取excel和單元格
• • 1.讀取 excel 文件
  • 2.讀取單元格
  • 3.獲取某一行某一列的數(shù)據(jù)
  • 4.遍歷所有單元格
  • 5.遍歷指定行列范圍的單元格
• 五、合并、拆分單元格和插入刪除行列
• • 1.合并單元格
  • 2.拆分合并的單元格
  • 3.插入行和列
  • • (1)插入單行單列
    • (2)插入多行多列
  • 4.刪除行和列
  • • (1)刪除單行單列
    • (2)刪除多行多列
• 六、單元格對齊
• • 1. 對齊方式與換行
  • 2.單元格對齊
  • 3.合并后的單元格設(shè)置對齊方式
• 七、單元格邊框設(shè)置
• • 1. 邊框線條粗細(xì)、顏色設(shè)置
  • 2.設(shè)置單個(gè)單元格邊框和顏色
  • 3.設(shè)置多個(gè)單元格整體邊框和顏色
• 八、設(shè)置單元格背景顏色和文字字體
• • 1.設(shè)置單元格背景顏色
  • 2.設(shè)置字體、字體的粗細(xì)、大小、顏色
• 九、設(shè)置行高和列寬

前言

python 操作excel 的庫有很多 ，有的庫只能讀取 xsl 格式，比如 xlrd 庫；
有的庫只能寫 xsl 格式，比如 xlwt 庫;
有的只能讀寫 xslx 格式，比如 openpyxl 庫 。

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

一、安裝openpyxl庫

pip install openpyxl

二、新建excel及寫入單元格

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

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

2.保存成流(stream)

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

from tempfile import NamedTemporaryFile from openpyxl import Workbookwb = Workbook() with NamedTemporaryFile() as tmp:wb.save(tmp.name)tmp.seek(0)stream = tmp.read()

3.寫入單元格

from openpyxl import Workbookwb = Workbook() # 創(chuàng)建第一個(gè)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 = 111wb.save("F:\pythonTest\sample.xlsx")

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

from openpyxl import Workbook# 初始化 wb = Workbook()# 創(chuàng)建第一個(gè)sheet sheet = wb.active # 在第1行第1列的單元格寫入111 sheet['A1'] = 111# 創(chuàng)建一個(gè)新的sheet sheet1 = wb.create_sheet() # 在這個(gè)新的sheet中第2行第2列的單元格寫入222 sheet1['B2'] = 222# 創(chuàng)建一個(gè)叫"第3個(gè)工作表"的新sheet sheet3 = wb.create_sheet("第3個(gè)工作表") # 在這個(gè)新的sheet中第3行第3列的單元格寫入333 sheet3['C3'] = 333# 保存本地excel文件中 wb.save("F:\pythonTest\sample.xlsx")

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

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

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

sheet = wb.active

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

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

2.刪除指定工作表

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

3.修改sheet工作表的名稱

# 在結(jié)尾插入一個(gè)叫 "我的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_workbookwb = load_workbook("F:\pythonTest\sample.xlsx") print(wb.sheetnames) # ['Sheet', 'Sheet1', 'New Title']

2.讀取單元格

from openpyxl import load_workbookwb = load_workbook("F:\pythonTest\sample.xlsx") # 獲取"New Title" 這個(gè)sheet工作表 sheet = wb["第3個(gè)工作表"]# 方式一 獲取 "C3"這個(gè)單元格 cell = sheet["C3"] # 方式二 獲取 第3行3列 這個(gè)單元格 # cell = sheet.cell(row=3, column=3)# 打印單元格的值 print(cell.value)

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

from openpyxl import load_workbookwb = 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_workbookwb = load_workbook("F:\pythonTest\sample.xlsx") # 遍歷所有的sheet工作表 for sheet in wb:# 獲取當(dāng)前sheet工作表的行數(shù)據(jù)rows = tuple(sheet.rows)# 遍歷每一行for row in rows:# 遍歷每一個(gè)單元格for cell in row:print(cell.value)

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

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

方式一：使用切片方式

from openpyxl import load_workbookwb = 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_workbookwb = 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.合并單元格

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

from openpyxl import load_workbookwb = 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_workbookwb = 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_workbookwb = 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_workbookwb = 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_workbookwb = 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_workbookwb = 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)單元格填滿了是不換行的，如果要自動(dòng)換行可使用 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 Alignmentwb = Workbook() sheet = wb.active# 在第1行第1列的那個(gè)單元格寫入123546 cell = sheet['A1'] cell.value = 123546 # 將該單元格對齊方式設(shè)置為水平和垂直都居中 cell.alignment = Alignment(horizontal="center", vertical="center")wb.save("F:\pythonTest\sample.xlsx")

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

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

from openpyxl.styles import Alignment from openpyxl import load_workbookwb = 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è)置虛線和實(shí)線

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

其中thin是細(xì)實(shí)線，thick是粗實(shí)線，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è)置單個(gè)單元格邊框和顏色

from openpyxl import Workbook from openpyxl.styles import Border, Sidewb = Workbook() sheet = wb.active# 在第1行第1列的那個(gè)單元格寫入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è)置多個(gè)單元格整體邊框和顏色

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

（1） 先合并單元格再對左上角的單元格進(jìn)行操作，但往往這些單元格都有不同的數(shù)據(jù)，難以進(jìn)行合并。
（2） 遍歷所有的單元格，分別賦予屬性。這種方法比較適用于為所有的單元格賦予相同的屬性。

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

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

from openpyxl import Workbook from openpyxl.styles import PatternFillwb = Workbook() sheet = wb.active# 在第1行第1列的那個(gè)單元格寫入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 Fontwb = Workbook() sheet = wb.active# 在第1行第1列的那個(gè)單元格寫入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 Workbookwb = Workbook() sheet = wb.active# 設(shè)置第1行高度為60 sheet.row_dimensions[1].height = 60 # 設(shè)置B列寬度為30 sheet.column_dimensions["B"].width = 30wb.save("F:\pythonTest\sample1.xlsx")

---

參考：
Tutorial

總結(jié)

以上是生活随笔為你收集整理的python 使用 openpyxl 处理 Excel 教程的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。