python使用openpyxl操作excel的方法步驟

更新時間：2020年05月28日 10:42:41 作者：知識追尋者

這篇文章主要介紹了python使用openpyxl操作excel的方法步驟，詳細(xì)的介紹了openpyxl的常用屬性以及操作excel的方法，感興趣的可以了解一下

一前言

知識追尋者又要放大招了，學(xué)完這篇openpyxl第三方庫，讀者將會懂得如何靈活的讀取excel數(shù)據(jù),如何創(chuàng)建excel工作表；更新工作表，刪除工作表；是不是感覺很強(qiáng)大，留下贊贊吧??！

二 openpyxl常用屬性函數(shù)

常用函數(shù)或者屬性	說明
openpyxl.load_workbook()	加載excel工作本
Workbook.active	獲得默認(rèn)sheet
Workbook.create_sheet()	創(chuàng)建sheet
Workbook.get_sheet_names()	已過時, 獲得所有sheet名稱
workbook.sheetnames	獲得所有sheet名稱
workbook.get_sheet_by_name(name)	已過時獲得指定的sheet對象
workbook[sheetname]	獲得指定的sheet對象
workbook.copy_worksheet(soure)	復(fù)制sheet
sheet[cell]	獲取單個單元格
sheet.cell(self, row, column, value=None)	獲取單個單元格
sheet[cell,cell]	訪問多個單元格
sheet.iter_rows(min_row, max_col, max_row)	返回多行，用于訪問多個單元格
sheet.iter_cols(min_row, max_col, max_row)	返回多列，用于訪問多個單元格
sheet.rows	獲取所有行
sheet.columns	獲取所有列
cell.value	獲取屬性值
sheet.merge_cells()	合并單元格
sheet.unmerge_cells()	取消合并單元格

三讀取excel

讀取整體流程如下

加載工作本
獲取標(biāo)簽頁
獲取指定區(qū)域的單元格
獲取單個單元格對象
通過單元格對象獲取值

3.1 準(zhǔn)備工作

創(chuàng)建一個名為zszxz.xlsx excel文件；其中標(biāo)簽sheet名為zszxz; 列A B C 如下；

id num name
1 100 zszxz
2 101 smile
3 102 kitty
4 103 wolf
5 104 cloud
6 105 water

3.2 安裝

支持操作excel和圖像

pip install openpyxl
pip install pillow

3.3 獲取所有標(biāo)簽頁名稱

# -*- coding: utf-8 -*-
import openpyxl

path = r'C:\mydata\generator\py\zszxz.xlsx'
# 加載工作本
workbook = openpyxl.load_workbook(path)
# 獲得所有標(biāo)簽頁
sheet_names = workbook.get_sheet_names()
print(sheet_names)

輸出

['zszxz']

正確的獲取方式

# -*- coding: utf-8 -*-
import openpyxl

path = r'C:\mydata\generator\py\zszxz.xlsx'
# 加載工作本
workbook = openpyxl.load_workbook(path)
# 獲得所有標(biāo)簽頁名稱
print(workbook.sheetnames)

3.4 獲取指定標(biāo)簽頁對象

不建議通過方法獲得標(biāo)簽頁對象，建議通過屬性方式獲?。?/p>

# -*- coding: utf-8 -*-
import openpyxl

path = r'C:\mydata\generator\py\zszxz.xlsx'
# 加載工作本
workbook = openpyxl.load_workbook(path)
# 獲得指定的sheet
sheet = workbook.get_sheet_by_name('zszxz')
print(sheet)

輸出

<Worksheet "zszxz">

正確的獲取方式

# -*- coding: utf-8 -*-
import openpyxl

path = r'C:\mydata\generator\py\zszxz.xlsx'
# 加載工作本
workbook = openpyxl.load_workbook(path)
# 獲取sheet對象
sheet = workbook['zszxz']
print(sheet)

輸出

<Worksheet "zszxz">

當(dāng)然讀者也可以通過循環(huán)工作本方式獲取sheet，不贅述；

3.5 復(fù)制sheet

在已有的sheet上可以進(jìn)行復(fù)制一個副本；

# -*- coding: utf-8 -*-
import openpyxl

path = r'C:\mydata\generator\py\zszxz.xlsx'
# 加載工作本
workbook = openpyxl.load_workbook(path)
# 獲取sheet對象
sheet = workbook['zszxz']
# 復(fù)制sheet
cp_sheet = workbook.copy_worksheet(sheet)
print(cp_sheet)

輸出

<Worksheet "zszxz Copy">

3.5 獲取指定一個單元格對象

指定獲取A1單元格對象

# -*- coding: utf-8 -*-
import openpyxl

path = r'C:\mydata\generator\py\zszxz.xlsx'
# 加載工作本
workbook = openpyxl.load_workbook(path)
# 獲取sheet對象
sheet = workbook['zszxz']
# 獲取指定單元格
cell = sheet['A1']
print(cell)

輸出

<Cell 'zszxz'.A1>

方式二

# -*- coding: utf-8 -*-
import openpyxl

path = r'C:\mydata\generator\py\zszxz.xlsx'
# 加載工作本
workbook = openpyxl.load_workbook(path)
# 獲取sheet對象
sheet = workbook['zszxz']
# 獲取指定單元格
cell = sheet.cell(row=1, column=1)
print(cell)

輸出

<Cell 'zszxz'.A1>

3.6 訪問多個單元格

訪問單元格獲取單元格對象；

# -*- coding: utf-8 -*-
import openpyxl

path = r'C:\mydata\generator\py\zszxz.xlsx'
# 加載工作本
workbook = openpyxl.load_workbook(path)
# 獲取sheet對象
sheet = workbook['zszxz']
cells_range = sheet['A1':'C1']
for cells in cells_range:
  for cell in cells:
    print(cell)

輸出

<Cell 'zszxz'.A1>
<Cell 'zszxz'.B1>
<Cell 'zszxz'.C1>

方式二

# -*- coding: utf-8 -*-
import openpyxl

path = r'C:\mydata\generator\py\zszxz.xlsx'
# 加載工作本
workbook = openpyxl.load_workbook(path)
# 獲取sheet對象
sheet = workbook['zszxz']
# 獲取指定范圍的行
row = sheet.iter_rows(min_row=1, max_col=3, max_row=1)
for cell in row:
  print(cell)

輸出

(<Cell 'zszxz'.A1>, <Cell 'zszxz'.B1>, <Cell 'zszxz'.C1>)

sheet.iter_cols(min_row, max_col, max_row)通用的道理，不贅述

3.7 獲取全部行

# -*- coding: utf-8 -*-
import openpyxl

path = r'C:\mydata\generator\py\zszxz.xlsx'
# 加載工作本
workbook = openpyxl.load_workbook(path)
# 獲取sheet對象
sheet = workbook['zszxz']
# 獲取所有行
for row in sheet.rows:
  print(row)

輸出

(<Cell 'zszxz'.A1>, <Cell 'zszxz'.B1>, <Cell 'zszxz'.C1>)
(<Cell 'zszxz'.A2>, <Cell 'zszxz'.B2>, <Cell 'zszxz'.C2>)
(<Cell 'zszxz'.A3>, <Cell 'zszxz'.B3>, <Cell 'zszxz'.C3>)
(<Cell 'zszxz'.A4>, <Cell 'zszxz'.B4>, <Cell 'zszxz'.C4>)
(<Cell 'zszxz'.A5>, <Cell 'zszxz'.B5>, <Cell 'zszxz'.C5>)
(<Cell 'zszxz'.A6>, <Cell 'zszxz'.B6>, <Cell 'zszxz'.C6>)
(<Cell 'zszxz'.A7>, <Cell 'zszxz'.B7>, <Cell 'zszxz'.C7>)

同理 sheet.columns獲取所有列不再贅述

3.8 獲取值

cell.value獲取屬性值

# -*- coding: utf-8 -*-
import openpyxl

path = r'C:\mydata\generator\py\zszxz.xlsx'
# 加載工作本
workbook = openpyxl.load_workbook(path)
# 獲取sheet對象
sheet = workbook['zszxz']
cells_range = sheet['A1':'C1']
for cells in cells_range:
  for cell in cells:
   # 獲取屬性值
    print(cell.value)

輸出

id
num
name

四寫入 excel

4.1 寫入文本

# -*- coding: utf-8 -*-
import openpyxl

path = r'C:\mydata\generator\py\zszxz_write.xlsx'
# 創(chuàng)建一個工作本
workbook = openpyxl.Workbook()
# 創(chuàng)建一個 sheet
sheet = workbook.create_sheet('zszxz')
# 寫入文本形式
sheet['A1'] = 'zszxz666'
# 讀取
print(sheet['A1'].value)
# 保存
workbook.save(path)

輸出

zszxz666

4.2 寫入數(shù)字

# -*- coding: utf-8 -*-
import openpyxl
import datetime

path = r'C:\mydata\generator\py\zszxz_write.xlsx'
# 創(chuàng)建一個工作本
workbook = openpyxl.Workbook()
# 創(chuàng)建一個 sheet
sheet = workbook.create_sheet('zszxz')
# 寫入值數(shù)字形式
sheet['A2'] = datetime.datetime(2010, 7, 21)
print(sheet['A2'].value)
# 保存
workbook.save(path)

輸出

2010-07-21 00:00:00

4.3 寫入公式

# -*- coding: utf-8 -*-
import openpyxl

path = r'C:\mydata\generator\py\zszxz_write.xlsx'
# 創(chuàng)建一個工作本
workbook = openpyxl.Workbook()
# 創(chuàng)建一個 sheet
sheet = workbook.create_sheet('zszxz')
# 寫入公式
sheet['A3'] = '=SUM(1, 1)'
print(sheet['A3'].value)
# 保存
workbook.save(path)

輸出

=SUM(1, 1)

實(shí)際中A3單元格值為2

4.4 合并單元格

# -*- coding: utf-8 -*-
import openpyxl

path = r'C:\mydata\generator\py\zszxz_write.xlsx'
# 創(chuàng)建一個工作本
workbook = openpyxl.Workbook()
# 創(chuàng)建一個 sheet
sheet = workbook.create_sheet('zszxz')
# 合并單元格
sheet.merge_cells('A2:D2')
# 保存
workbook.save(path)

4.5 取消合并單元格

# -*- coding: utf-8 -*-
import openpyxl

path = r'C:\mydata\generator\py\zszxz_write.xlsx'
# 創(chuàng)建一個工作本
workbook = openpyxl.Workbook()
# 創(chuàng)建一個 sheet
sheet = workbook.create_sheet('zszxz')
# 取消合并單元格
sheet.unmerge_cells('A2:D2')
# 保存
workbook.save(path)

4.5 插入圖片

# -*- coding: utf-8 -*-
import openpyxl
from openpyxl.drawing.image import Image

path = r'C:\mydata\generator\py\zszxz_write.xlsx'
# 創(chuàng)建一個工作本
workbook = openpyxl.Workbook()
# 創(chuàng)建一個 sheet
sheet = workbook.create_sheet('zszxz')
# 設(shè)置圖像
img = Image(r'C:\mydata\generator\py\main.jpg')
# 設(shè)置圖像單元格說明
sheet['A1'] = 'you are my angel'
# 插入圖片
sheet.add_image(img, 'A1')
# 保存
workbook.save(path)

結(jié)果如下：

4.6 隱藏輪廓

# -*- coding: utf-8 -*-
import openpyxl

path = r'C:\mydata\generator\py\zszxz_write.xlsx'
wb = openpyxl.Workbook()
ws = wb.create_sheet()
# 隱藏行 A-B
ws.column_dimensions.group('A', 'B', hidden=True)
# 隱藏 列 1 -5 
ws.row_dimensions.group(1, 5, hidden=True)
wb.save(path)

4.7 設(shè)置行高列高

# -*- coding: utf-8 -*-
import openpyxl

path = r'C:\mydata\generator\py\zszxz_write.xlsx'
# 創(chuàng)建一個工作本
workbook = openpyxl.Workbook()
# 創(chuàng)建一個 sheet
sheet = workbook.create_sheet('zszxz')
# 寫入文本形式
sheet['A1'] = 'zszxz666'
# 設(shè)置行高
sheet.row_dimensions[1].height = 50
# 設(shè)置列高
sheet.column_dimensions['A'].width = 30
workbook.save(path)

五刪除sheet

# -*- coding: utf-8 -*-
import openpyxl

path = r'C:\mydata\generator\py\zszxz_write.xlsx'
# 加載工作本
workbook = openpyxl.load_workbook(path)
# 獲得sheet
sheet = workbook['Sheet']
# 移除sheet
workbook.remove(sheet)
# 保存
workbook.save(path)

六更新工作表

# -*- coding: utf-8 -*-
import openpyxl

path = r'C:\mydata\generator\py\zszxz_write.xlsx'
# 加載工作本
workbook = openpyxl.load_workbook(path)
# 獲得sheet
sheet = workbook['zszxz']
# 獲得值
val = sheet['A1'].value
print(val)
# 重新賦值
new_val = sheet['A1'].value = 'zszxz'
print(new_val)
# 保存
workbook.save(path)

輸出