使用openpyxl进行excel操作

最新推荐文章于 2024-09-18 09:10:46 发布
最新推荐文章于 2024-09-18 09:10:46 发布
阅读量1.3w 收藏 76
点赞数 11
分类专栏： python 经验 自动化测试 python 开发入门 文章标签： python 自动化测试
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Daivon_Up/article/details/79310816
版权

openpyxl模块用来读写Excel文件。openpyxl工作时，在内存中创建Excel工作簿和工作表，然后在工作表中的单元格中进行各种数据编辑和样式编辑操作，或在工作表中绘制图形，最后再保存文件写入到Excel中。

官方文档： http://openpyxl.readthedocs.io/en/default/

1. 基本操作

1.1. 引入openpyxl库

import openpyxl

1.2. 打开工作簿（xlsx）文件

1.2.1. 全新创建工作簿

wb = openpyxl.Workbook()

得到一个wb对象，它就是我们要操作的工作簿文件的对象。

>>> wb
<openpyxl.workbook.workbook.Workbook object at 0x0000000003543358>

1.2.2. 打开现有xlsx文件：

wb = openpyxl.load_workbook(“test.xlsx”)

1.3. 保存wb对象到xlsx文件

wb.save("test.xlsx")

文件名支持路径。

以上语句获得的一个空的工作簿，默认含有一个名为“Sheet”的工作表页面。

1.4. 工作表(sheet)操作

wb对象既是整个xlsx文件在内存中的存在形式，它一般由一个或多个sheet页面组成，我们对xlsx文件的操作，主要就是在sheet页面上操作；我们在Excel软件中，能对sheet页面进行什么操作，基本使用openpyxl也都能完成。

1.4.1. 工作表的创建和选定

wb对象创建后，默认含有一个默认的名为 Sheet 的 页面，可以使用active来得到它

>>> ws1 = wb.active
>>> ws1
<Worksheet "Sheet">

或使用名称来得到它：

ws1 = wb.get_sheet_by_name('Sheet')

或

ws1 = wb["Sheet"]

名称有中文要使用unicode

也可以使用工作表序号获得该页面： ws1 = wb.worksheets[0]

注：序号从0开始

可以使用 wb.create_sheet() 来创建新的sheet页面。 创建时可以同时关联一个变量。

ws2 = wb.create_sheet()

如：

import openpyxl
wb = openpyxl.Workbook()
# ws1 = wb.active
ws1 = wb.get_sheet_by_name('Sheet')
ws2 = wb.create_sheet()
wb.create_sheet()
wb.create_sheet()
wb.create_sheet()
ws1['A1'] = "This is a test!"
wb.save("test.xlsx")

对于已有的工作表，我们也可以通过遍历名称、序号等方式拿到相应的对象地址。

wb.sheetnames 是所有工作表sheet页面的名称列表。

1.4.2. 工作表命名

工作表sheet可以在创建时候直接指定名字，中文要使用unicode，也可以通过修改对象的title值，来修改名称。

import openpyxl
wb = openpyxl.Workbook()
# ws1 = wb.active
ws1 = wb.get_sheet_by_name('Sheet')
ws1.title = "Test"
ws2 = wb.create_sheet("ABC")
wb.create_sheet(u"中文表名")
ws1['A1'] = "哇嘎嘎"
wb.save("test.xlsx")

>>> print wb.sheetnames
[u'Test', u'ABC', u'\u4e2d\u6587\u8868\u540d']

1.4.3. 工作表的标签颜色修改

修改工作表的标签的颜色，等价于工作表标签右键菜单中“工作表标签颜色”功能：

ws1.sheet_properties.tabColor="0000FF"

颜色的值为颜色的RRGGBB格式。

import openpyxl
wb = openpyxl.Workbook()
#ws1 = wb.active
ws1 = wb.get_sheet_by_name('Sheet')
ws1.title = "Test"
ws2 = wb.create_sheet("ABC")
wb.create_sheet(u"中文表名")
ws1.sheet_properties.tabColor="0000FF"
ws2.sheet_properties.tabColor="FF00FF"
ws1['A1'] = "哇嘎嘎"
ws1['B2'] = "嗯~ o(*￣▽￣*)o"
wb.save("test.xlsx")

1.4.4. 删除工作表

使用wb.remove_sheet()方法删除工作表

wb.remove_sheet(wb.get_sheet_by_name(“中文表名”)) wb.remove_sheet(ws2)

1.5. 单元格操作

获得一个工作表对象后，就可以工作表中的单元格进行操作。

1.5.1. 向单个单元格中写入值

  • 按照Excel单元格定位的传统方法，在指定单元格写入数据，之前例子中已有体现： 
     ws1['A1'] = "哇嘎嘎"
 ws1['B2'] = "嗯~ o(*￣▽￣*)o"
  • 在指定行、列的单元格中写入数据： ws1.cell(row=3, column=2).value = "AAAAA"

1.5.2. 遍历方式向多个单元格中写入值：

有多种的方式可对多个单元格进行选定：

  • 采用iter_cols方法，指定行列范围，对范围内的列进行遍历操作，然后再对列中的单元格操作；
  • 对应有iter_rows方法，与inter_cols区别，是遍历出所有行先； 
     for col in ws1.iter_cols(min_row=2, min_col=2, max_row=7, max_col=6):
     for cell in col:
  • 采用传统的Excel多单元格选择的标记方法，区域选择单元格，遍历与iter_rows相同，返回每行，然后再遍历行中单元格； 
    for row in ws2["B2:F7"]:
    for cell in row:

完整示例：

import openpyxl
wb = openpyxl.Workbook()
ws1 = wb.get_sheet_by_name('Sheet')
ws1.title = "Test"
i = 0
# 遍历方式1：
ws1['A1'] = "遍历方式1"
for col in ws1.iter_cols(min_row=2, min_col=2, max_row=7, max_col=6):
    for cell in col:
        cell.value = i
        i = i + 1
ws2 = wb.create_sheet("Test2")
i = 0
# 遍历方式2：
ws2['A1'] = "遍历方式2"
for row in ws2["B2:F7"]:
    for cell in row:
        cell.value = i
        i = i + 1
wb.save("test.xlsx")

可以看出这两种遍历方式遍历的行列顺序是完全不一样的。

import openpyxl
wb = openpyxl.Workbook()
ws1 = wb.get_sheet_by_name('Sheet')
ws1.title = "Test"
i = 0
# 遍历方式1：
ws1['A1'] = "遍历方式1"
print("遍历方式1")
j = 1
for col in ws1.iter_cols(min_row=2, min_col=2, max_row=7, max_col=6):
    print("Cols No." + str(j) + ":  ", end="")
    print(col)
    j = j + 1
    for cell in col:
        cell.value = i
        i = i + 1
ws2 = wb.create_sheet("Test2")
i = 0
# 遍历方式2：
ws2['A1'] = "遍历方式2"
print("遍历方式2")
j = 1
for row in ws2["B2:F7"]:
    print("Row No." + str(j) + ":  ", end="")
    print(row)
    j = j + 1
    for cell in row:
        cell.value = i
        i = i + 1
wb.save("test.xlsx")

执行后控制台输出：

遍历方式1
Cols No.1:  (<Cell 'Test'.B2>, <Cell 'Test'.B3>, <Cell 'Test'.B4>, <Cell 'Test'.B5>, <Cell 'Test'.B6>, <Cell 'Test'.B7>)
Cols No.2:  (<Cell 'Test'.C2>, <Cell 'Test'.C3>, <Cell 'Test'.C4>, <Cell 'Test'.C5>, <Cell 'Test'.C6>, <Cell 'Test'.C7>)
Cols No.3:  (<Cell 'Test'.D2>, <Cell 'Test'.D3>, <Cell 'Test'.D4>, <Cell 'Test'.D5>, <Cell 'Test'.D6>, <Cell 'Test'.D7>)
Cols No.4:  (<Cell 'Test'.E2>, <Cell 'Test'.E3>, <Cell 'Test'.E4>, <Cell 'Test'.E5>, <Cell 'Test'.E6>, <Cell 'Test'.E7>)
Cols No.5:  (<Cell 'Test'.F2>, <Cell 'Test'.F3>, <Cell 'Test'.F4>, <Cell 'Test'.F5>, <Cell 'Test'.F6>, <Cell 'Test'.F7>)
遍历方式2
Row No.1:  (<Cell 'Test2'.B2>, <Cell 'Test2'.C2>, <Cell 'Test2'.D2>, <Cell 'Test2'.E2>, <Cell 'Test2'.F2>)
Row No.2:  (<Cell 'Test2'.B3>, <Cell 'Test2'.C3>, <Cell 'Test2'.D3>, <Cell 'Test2'.E3>, <Cell 'Test2'.F3>)
Row No.3:  (<Cell 'Test2'.B4>, <Cell 'Test2'.C4>, <Cell 'Test2'.D4>, <Cell 'Test2'.E4>, <Cell 'Test2'.F4>)
Row No.4:  (<Cell 'Test2'.B5>, <Cell 'Test2'.C5>, <Cell 'Test2'.D5>, <Cell 'Test2'.E5>, <Cell 'Test2'.F5>)
Row No.5:  (<Cell 'Test2'.B6>, <Cell 'Test2'.C6>, <Cell 'Test2'.D6>, <Cell 'Test2'.E6>, <Cell 'Test2'.F6>)
Row No.6:  (<Cell 'Test2'.B7>, <Cell 'Test2'.C7>, <Cell 'Test2'.D7>, <Cell 'Test2'.E7>, <Cell 'Test2'.F7>)

1.5.3. 按行追加数据

采用工作表的append()方法，可以向工作表中按行追加数据：

import openpyxl
wb = openpyxl.Workbook()
ws1 = wb.get_sheet_by_name('Sheet')
ws1.title = "Test"
DATA = [
    ['第一天', 123, 12, 123, 900, 231, 7],
    ['第二天', 13, 56, 3, 900, 231, 90],
    ['第三天', 216, 38, 37, 543, 55, 376],
    ['第四天', 89, 99, 88, 453, 87, 527]
]
for row in DATA:
    ws1.append(row)
wb.save("test.xlsx")

1.5.4. 加载已有文件，读取数据

已有文件：test2.xlsx 

import openpyxl
wb = openpyxl.load_workbook("test2.xlsx")
ws = wb.get_sheet_by_name('Sheet')
print("数据表最大行数：" + str(ws.max_row))
print("数据表最大列数：" + str(ws.max_column))
DATA = []
# 遍历读取
for row in ws.iter_rows(min_col=1, min_row=1, max_row=ws.max_row, max_col=ws.max_column):
    ROW = []
    for cell in row:
        ROW.append(cell.value)
    DATA.append(ROW)
print("表中读取到的数据：", end="")
print(DATA)

读取指定位置

B2 = ws[‘B2’].value
print("B2 = " + B2)

执行结果：

数据表最大行数：2
数据表最大列数：2
表中读取到的数据：[['测试2', None], [None, 'B列2行']]
B2 = B列2行

1.5.5. 使用公式

可以直接在单元格中写入公式：

import openpyxl
wb = openpyxl.Workbook()
ws1 = wb.get_sheet_by_name('Sheet')
ws1.title = "Test"
DATA = [
    ['第一天', 123, 12, 123, 900, 231, 7],
    ['第二天', 13, 56, 3, 900, 231, 90],
    ['第三天', 216, 38, 37, 543, 55, 376],
    ['第四天', 89, 99, 88, 453, 87, 527]
]
ws1['A1'] = '这是一个测试用表格'
for row in DATA:
    ws1.append(row)
ws1.append(['合计', '=sum(B2:B5)', '=sum(C2:C5)', '=sum(D2:D5)', '=sum(E2:E5)', '=sum(F2:F5)', '=sum(G2:G5)'])
wb.save("test.xlsx")

2.单元格样式设置

单元格样式的控制，依赖openpyxl.style包，其中定义有样式需要的对象，引入样式相关：

from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
  • PatternFill 填充
  • Font 字体
  • Aignment 对齐
  • Border 边框
  • Side 边线

以上五个基本可满足需要

基本用法是，将单元格对象的设置的属性赋为新的与默认不同的相应对象。

比如设置一个字体对象：

2.1. 字体设置

在 1.5.5. 的示例中，保存文件前先设置一下标题字体：

# 样式
font = Font(size=14, bold=True, name='微软雅黑',  color="FF0000")
ws1['A1'].font = font
wb.save("test.xlsx")

2.2. 设置边框

边框Border对象，创建时可指定边框各边的Side对象，根据之前的执行结果，默认都是无边框

# 样式
font = Font(size=14, bold=True, name='微软雅黑', color="FF0000")
thin = Side(border_style="thin", color="0000FF")
border = Border(left=thin, right=thin, top=thin, bottom=thin)
ws1['A1'].font = font
ws1['A1'].border = border
for row in ws1['A2:G6']:
    for cell in row:
        cell.border = border
wb.save("test.xlsx")

2.3. 对齐设置

# 对齐
alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
ws1['A1'].alignment = alignment
  • wrap_text=True 打开自动换行

2.4. 合并单元格

采用 merge_cells 方法合并单元格

# 对齐
alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
ws1['A1'].alignment = alignment
# 合并单元格
ws1.merge_cells('A1:G1')
wb.save("test.xlsx")

2.5.填充单元格

# 填充
fill = PatternFill(patternType="solid", start_color="33CCFF")
ws1['A1'].fill = fill
for row in ws1.iter_rows(min_row=ws1.max_row, max_col=ws1.max_column):
    for cell in row:
        cell.fill = PatternFill(patternType="solid", start_color="0066FF")
        cell.font = Font(bold=True, color="FFFFFF")
        cell.alignment = Alignment(horizontal="center")
wb.save("test.xlsx")

如果觉得我的文章对你有帮助，可关注我的blog https://github.com/AwesomeDevin/blog

文章转载自:http://yaoyan.me/2017/08/python-openpyxl1/

AwesomeDevin
关注
专栏目录
Python使用openpyxl操作excel表格
a21700790yan的博客
12-18 515
一、openpyxl的使用 pip install openpyxl 第一步：打开工作簿（读取Excel文件中所有的数据保存为工作簿对象） workbook = openpyxl.load_workbook('cases.xlsx') 第二步：选中表单对象 sheet = workbook['login'] 第三步：通过表单选中表格读取数据 ①读取内容（第五行第四列） da...
python使用openpyxl操作excel
snow_small的博客
06-16 667
一、类 1、定义：class + 大写字母开头，函数里面必须有self class Student: def __init__(self, name): // 构造函数 self.name = name def say(self): print(self.name) s = Student('lilei') print(s.name) 二、Excel处理 1、模块openpyxl安装：pip install openpyxl 2、术语：行-row
openpyxl操作excel
weixin_45671410的博客
10-06 213
openpyxl是关于python操作excel的第三方库 关于excel的基本术语： row:行 column:列 cell:excel表格中的小格子 sheet:excel中的工作表，默认以Sheet1，Sheet2……排列 两种获取excel文件的方式 #从硬盘中加载一个excel文件到内存中 workbook = load_workbook("路径+文件名") #也可以直接在内存中生成一个excel文件 new_work = Workbook() 将sheet表激活 #该方法默认激活第一个shee
python之openpyxl模块——实现Excel表格的处理（万字教学，全网最全，超详细！）
最新发布
m0_59470317的博客
09-18 3034
本篇文章详细介绍了openpyxl模块的各种使用方法，实现python对Excel表格的数据处理，同时简单介绍了CSV模块的用法，讲解了python如何以CSV的格式写入和读取数据；全文超2万字，超详细！！！，通过各种案例带你轻松学会两种数据处理的方法。工作簿（Workbook）定义：一个Excel文件就是一个工作簿，它可以包含多个工作表。工作表（Worksheet）
Python.openpyxl操作Excel
bingbangx的博客
04-12 9930
Python 操作 Excel 存取数据 1. openpyxl 模块 1.1 安装 openpyxl 模块 1.2 Excel 文件的三个对象 1.2.1 Workbook 对象 1.2.2 Worksheet 对象 1.3.3 Cell 2. Python 操作 Excel 2.1 Python 操作 Excel 之读取 2.1.1 打开本地工作簿，获取所有工作表名称 2.1.2 创建工作表 2.1.3 根据工作表名称获取工作表 2.1.4 获取单元格对象及单元格的值 2.1.5 获取单元格的行、列及值
【openpyxl的使用】操作excel
familyyan的博客
11-29 3794
一、openpyxl 1、安装 pip install openpyxl 2、了解excel的操作步骤 找到目标excel 打开 读取数据、编辑excel单元格 保存 关闭 3、openpyxl操作excel 创建wb对象（找到excel并打开它） 找到Sheet对象（既要找到操作的工作簿） 找到要操作的单元格 读取数据、修改数据 保存、关闭 4、openpyxl支持的excel的格式 xlsx xlsm xltx xltm 5、openpyxl的基本操作类 Workbook：相当
使用 openpyxl 处理 Excel 电子表格
weixin_44785184的博客
01-20 931
`openpyxl`模块让 Python 程序能读取和修改 Excel 电子表格文件。例如，可能有一个无聊的任务，需要从一个电子表格拷贝一些数据，粘贴到另一个电子表格中。或者可能需要从几千行中挑选几行，根据某种条件稍作修改。或者需要查看几百份部门预算电子表格，寻找其中的赤字。正是这种无聊无脑的电子表格任务，可以通过 Python 来完成。
python使用openpyxl操作excel的方法步骤
09-16
下面将详细介绍如何使用openpyxl进行Excel的各种操作。 首先，我们需要安装openpyxl库，可以通过pip命令来安装： ```bash pip install openpyxl ``` 安装完成后，我们可以开始读取和写入Excel文件。以下是一些...
最新Python使用openpyxl操作excel表格
2401_84692456的博客
05-03 80
注意点：不要随便在表格中敲空格二、openpyxl的封装导包import openpyxl封装class ReadExcel(object):“”“操作Excl文件”“”def init(self, fileName, sheetName):“”"初始化方法:param fileName: Excel文件名:param sheetName: 表单名“”"self.fileName = fileNameself.sheetName = sheetNamedef open(self):“”“打开工作簿，选中表单
【openpyxl】：openpyxl操作excel最详细教程
傅里叶也头大
02-18 1944
一、读操作 1、加载excel # -*- coding: utf-8 -*- import openpyxl path = 'demo.xlsx' # 加载工作本 wb = openpyxl.load_workbook(path) 2、获取所有sheet # 获得所有标签页 #sheet=wb.get_sheet_by_name("Sheet1") #已过时,不用 #获取所有工作表名称 sheet = wb.sheetnames #返回一个list 3、获取指定sheet sheet = wb["d
python.excel 利用python openpyxl 实现excel操作全集
AI_LINNGLONG的博客
02-13 955
学习内容： 【PYTHON,EXCEL】利用python进行EXCEL处理1 打开，读取数据的方法https://blog.csdn.net/AI_LINNGLONG/article/details/104281736 【PYTHON,EXCEL】利用python进行EXCEL处理2 写入内容 https://blog.csdn.net/AI_LINNGLONG/article/detail...
python—openpyxl操作excel详解
LHJCSDNYL的博客
10-24 8998
openpyxl属于第三方模块，在python中用来处理excel文件。可以对excel进行的操作有：读写、修改、调整样式及插入图片等。但只能用来处理【 .xlsx】 后缀的excel文件。
如何用openpyxl操作excel文档
worldboy18的博客
08-07 454
openpyxl读写excel
自动化办公-excel篇 openpyxl的使用详解
热门推荐
m0_71559726的博客
04-24 1万+
本章介绍excel自动化办公——openpyxl库的使用，全文13000多字，基本涵盖所有知识点。
【Excel自动化办公】使用openpyxl对Excel进行读写操作
qq_38196449的博客
03-18 3228
openpyxl支持利用工作表中单元格的数据，创建条形图、折线图、散点图和饼图。这个结果居然是读取到了公式字符串，但我们想要的是公式计算的结果，也就是A3的结果，如何解决呢？我们先准备一个名为test.xlsx的表格。指定虚拟环境为python3.9版本...设置行高和列宽：Worksheet对象有。属性，控制行高和列宽。
python专题使用openpyxl操作excel
知识追寻者(Inheriting the spirit of open source, Spreading technology knowledge;)
01-27 4239
l am fine that reason for you
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值