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

一前言

知识追寻者又要放大招了，学完这篇openpyxl第三方库，读者将会懂得如何灵活的读取excel数据,如何创建excel工作表；更新工作表，删除工作表；是不是感觉很强大，留下赞赞吧！！

二 openpyxl常用属性函数

常用函数或者属性	说明
openpyxl.load_workbook()	加载excel工作本
Workbook.active	获得默认sheet
Workbook.create_sheet()	创建sheet
Workbook.get_sheet_names()	已过时, 获得所有sheet名称
workbook.sheetnames	获得所有sheet名称
workbook.get_sheet_by_name(name)	已过时获得指定的sheet对象
workbook[sheetname]	获得指定的sheet对象
workbook.copy_worksheet(soure)	复制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

读取整体流程如下

加载工作本
获取标签页
获取指定区域的单元格
获取单个单元格对象
通过单元格对象获取值

3.1 准备工作

创建一个名为zszxz.xlsx excel文件；其中标签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 获取所有标签页名称

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

path = r\'C:\\mydata\\generator\\py\\zszxz.xlsx\'
# 加载工作本
workbook = openpyxl.load_workbook(path)
# 获得所有标签页
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)
# 获得所有标签页名称
print(workbook.sheetnames)

3.4 获取指定标签页对象

不建议通过方法获得标签页对象，建议通过属性方式获取；

# -*- 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\”>

当然读者也可以通过循环工作本方式获取sheet，不赘述；

3.5 复制sheet

在已有的sheet上可以进行复制一个副本；

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

path = r\'C:\\mydata\\generator\\py\\zszxz.xlsx\'
# 加载工作本
workbook = openpyxl.load_workbook(path)
# 获取sheet对象
sheet = workbook[\'zszxz\']
# 复制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\'
# 创建一个工作本
workbook = openpyxl.Workbook()
# 创建一个 sheet
sheet = workbook.create_sheet(\'zszxz\')
# 写入文本形式
sheet[\'A1\'] = \'zszxz666\'
# 读取
print(sheet[\'A1\'].value)
# 保存
workbook.save(path)

输出

zszxz666

4.2 写入数字

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

path = r\'C:\\mydata\\generator\\py\\zszxz_write.xlsx\'
# 创建一个工作本
workbook = openpyxl.Workbook()
# 创建一个 sheet
sheet = workbook.create_sheet(\'zszxz\')
# 写入值数字形式
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\'
# 创建一个工作本
workbook = openpyxl.Workbook()
# 创建一个 sheet
sheet = workbook.create_sheet(\'zszxz\')
# 写入公式
sheet[\'A3\'] = \'=SUM(1, 1)\'
print(sheet[\'A3\'].value)
# 保存
workbook.save(path)

输出

=SUM(1, 1)

实际中A3单元格值为2

4.4 合并单元格

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

path = r\'C:\\mydata\\generator\\py\\zszxz_write.xlsx\'
# 创建一个工作本
workbook = openpyxl.Workbook()
# 创建一个 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\'
# 创建一个工作本
workbook = openpyxl.Workbook()
# 创建一个 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\'
# 创建一个工作本
workbook = openpyxl.Workbook()
# 创建一个 sheet
sheet = workbook.create_sheet(\'zszxz\')
# 设置图像
img = Image(r\'C:\\mydata\\generator\\py\\main.jpg\')
# 设置图像单元格说明
sheet[\'A1\'] = \'you are my angel\'
# 插入图片
sheet.add_image(img, \'A1\')
# 保存
workbook.save(path)

结果如下：

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

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 设置行高列高

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

path = r\'C:\\mydata\\generator\\py\\zszxz_write.xlsx\'
# 创建一个工作本
workbook = openpyxl.Workbook()
# 创建一个 sheet
sheet = workbook.create_sheet(\'zszxz\')
# 写入文本形式
sheet[\'A1\'] = \'zszxz666\'
# 设置行高
sheet.row_dimensions[1].height = 50
# 设置列高
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)

输出