【办公自动化】用Python在Excel中查找并替换数据

简介: 【办公自动化】用Python在Excel中查找并替换数据

一、Python处理Excel


  • Python处理Excel的好处


1.批量操作:当要处理众多Excel文件时,例如出现重复性的手工劳动,那么使用Python就可以实现批量扫描文件、自动化进行处理,利用代码代替手工重复劳动,实现自动化,是Python第一个比Excel强大的地方


2.大型文件,当Excel文件超过几十兆、甚至上百兆时,打开文件很慢、处理文件更加慢,这时候若使用Python,会发现处理几十兆、几百兆甚至几GB都是没有问题的


3.当使用Excel进行复杂的计算时,会使用VBA,但是VBA本身是过时并且复杂的语言,Python是当前最简单且容易实现的一门语言,用Python能够处理比VBA难度更高的业务逻辑


4.Python是通用语言,不仅可以处理Excel,使用Python就可以得到很多额外的功能,例如:爬虫、发布网页的Web服务、与数据库进行连接、同时结合word和PPT进行处理、加入定时任务处理、人工智能分析等,各种额外的功能,这是Excel和VBA所不具备的


  • Python处理Excel主要有三大类库


1.pandas:是Python领域非常重要的,用于数据分析和可视化的类库,在处理Excel中,90%可以利用pandas类库就可以搞掂,利用pandas就可以读取Excel、处理Excel和输出Excel,但是pandas也有缺点,就是无法做到格式类,例如Excel中合并单元、大量复杂的样式(看起来很精美)的时候,用pandas无法搞掂,此时,依然是使用pandas结合openyxl、xlwings来搞掂需求


2.openpyxl:若电脑上未安装office时,也可以使用openpyxl,这个类型可以运行在linux上,并且也可以实现操作大部分Excel格式和样式的功能,使用它配合pandas,也可以完成大部分场景的需求


3.xlwings:比openyxl更加强大,只能运行在Windows或者Mac系统,并且该系统中必须安装了office才能运行,xlwings的原理,就是基于当前系统已经安装好的office软件,来进行功能的拓展来操作Excel


  • 使用pandas的时候,经常会结合其他类库,来完成更加复杂的功能


requests, bs4:可以完成爬虫的功能


flask:可以做网页,把表格展示在网页上


Matplotlib:读取表格后,进行可视化


sklearn:进行复杂的数据分析时,也可以结合机器学习Sklearn把读取的Excel数据,进行数据分析和机器学习


Python-docx:也可以结合Python-docx类库,实现Excel和word的互通


smtplib:也可以使用smtplib,讲Excel数据发送邮件出去


  • 开发环境


操作系统:使用windows, mac都可以


Python版本:系统中需要安装Python3.6以上的版本,Python2已经过期不建议使用,Python3.6以前的版本功能相对弱,最好就是采用Python3.6以上的版本


开发工具:有两个可以选择,jupyter notebook,是个网页编辑器,可以运行Python,常常用于交互性、探索性的开发;pycharm,用于成熟脚本,或者web服务的一些开发;这两个工具可以随意选择。


二、用Python在Excel中查找并替换数据


技术工具:


Python版本:3.9


代码编辑器:jupyter notebook


 随着项目的进展,需要经常在Excel业务表格中查找及替换数据,已保证数据与实际项目进度一致。手动一个一个查找,然后替换,效率太低,还容易遗漏。现在我们来试试用Python自动完成查找及替换吧。具体要求如下。


首先,我们先将左边表格中的数据提取,并存入字典data,其键为“查找内容”中的数据,值为“替换内容”中的数据。

from openpyxl import load_workbook #用于读取Excel中的信息
#获取Excel表格中的数据
wb = load_workbook('查找替换.xlsx')#读取工作簿
ws = wb.active #读取活动工作表
data={} #新建字典,用于储存数据
for row in range(2,ws.max_row+1):
    chazhao = str(ws['A' + str(row)].value)  #转换成字符串,以免后续比对时出现数据类型冲突
    tihuan = str(ws['B' + str(row)].value) #转换成字符串,以免后续比对时出现数据类型冲突
    data[chazhao]=tihuan #键值对应存入字典
data


        然后,读取目标表格,将D列中的所有数据提取出来,以便后续比对及替换。通过`for`循环遍历“原表”,将D列每个单元格的值提取并存入`ID_list`。然后通过切片`ID_list[:10]`查看前10个数据是否OK。结果显示相当正常。

wb = load_workbook('原表.xlsx') #读取目标工作簿
ws = wb.active
ID_list = [] #新建一个列表,用于储存原表D列的信息
for row in range(2,ws.max_row+1):
    ID = ws['D' + str(row)].value #遍历整个工作表,将D列的数据逐个存入ID变量
    ID_list.append(ID) #将读取到的结果存入列表
ID_list[:10] #查看列表中前10个数据

type("")


为了比对数据,我们需要将`'说明码:77601'`中的“说明码:”字符拿掉,只保留“77601”。于是调用`split`函数来进行分割,并将分割好的数字部分存入新建的列表`code`。不好,居然报错了,说`ID_list`列表中有"None"(空)类型的数据,而"None"类型的数据是不能使用`split`函数的。目测了一下,`ID_list`列表中除了`'说明码:77601'`和`''`这样的空字符串,没看到None啊。再回来“原表”侦察一下,发现最下面还有一些单元格很有嫌疑。原来是表尾有一些“供应商”和仓位信息,这些信息所在位置对应的D列都是空单元格,其值为"None"。用`ID_list[-10:]`查看最后10个元素,果然都是"None"。  

code = [i.split(":")[-1] for i in ID_list]
code

ID_list[-10:]


这样,我们就知道`ID_list`中有三种数据,即含内容的字符串(比如'说明码:77601'),空字符串(比如'')和空值None。因此,需要修改一下字符串分割代码如下。加入了`if`判断语句,如果`ID_list`中的值是None,则放入None占位,以保持列表的值的顺序与原表一致;值不是None,则按":"符号分割,并放分割后的最后一个值`[-1]`进入新列表code。空字符串在这里也要经过`split`分割,但其中没有“:”符号,所以就分割不了,只得直接跳过,最后放入新列表的还是空字符串。  

code = []
for i in ID_list:
    if i == None:# 如果是None,则放入None占位,以保持列表的值的顺序与原表一致
        code.append(None)
    else:
        code.append(i.split(":")[-1]) #不是None,则按":"符号分割,并放分割后的最后一个值进入新列表code
code[:10]


处理完数据后,即可开始查找并替换目标数据了。用`for`循环遍历列表`code`,即原表D列中的数字部分。如果其中的值也存在于data的键中,即语句`if code[i] in data`,则将原表中D列(`column=4`)对应的行中的数据改写成新的值。新的值由两部分组成,一部分是“说明码:”这样的,即`ID_list[i].split(":")[0]`,另一部分则是要替换的数字,即`data[code[i]]`。这样保证只替换了需要替换的数字部分,而保留中文和冒号部分。最后保存为新的文件,替换完成。  

for i in range(len(code)):
    if code[i] in data:
        ws.cell(row=i+2,column=4).value = ID_list[i].split(":")[0] +":"+ data[code[i]]
wb.save('原表-替换.xlsx') 

 如果以上不能通过观察原表,发现空值问题,还可以用`enumerate`函数给列表里的所有元素加上索引,以便精确定位`ID_list`中的空值。加上索引后,在转换成列表,并存入新的列表`ID_list_idx`中。观察其中前10个数据,可见索引已加好了。然后遍历新列表,判断其中的值是否为空值,若是则打印其对应的索引编号,这样就能精准定位哪些是空值了,再回到原Excel表,就容易弄清楚发生了什么事啦。其中,新列表中的元素的结构是一个元组,像这样`(2, '说明码:77601')`,`i[0]`是索引`2`,`i[1]`是索引对应的值`说明码:77601`。

ID_list_idx = list(enumerate(ID_list)) #加索引
ID_list_idx[:10]

for i in ID_list_idx: #遍历列表
    if i[1] == None: #判断索引对应的值是否为空值。i的结构是一个元组,像这样(2, '说明码:77601'),i[0]是索引,i[1]是索引对应的值
        print(i[0]) #打印索引编号


三、往期推荐


Python提取pdf中的表格数据(附实战案例)

使用Python自动发送邮件

Python操作ppt和pdf基础

Python操作word基础

Python操作excel基础

使用Python一键提取PDF中的表格到Excel

使用Python批量生成PPT版荣誉证书

使用Python批量处理Excel文件并转为csv文件

目录
相关文章
|
4天前
|
Java 测试技术 持续交付
【入门思路】基于Python+Unittest+Appium+Excel+BeautifulReport的App/移动端UI自动化测试框架搭建思路
本文重点讲解如何搭建App自动化测试框架的思路,而非完整源码。主要内容包括实现目的、框架设计、环境依赖和框架的主要组成部分。适用于初学者,旨在帮助其快速掌握App自动化测试的基本技能。文中详细介绍了从需求分析到技术栈选择,再到具体模块的封装与实现,包括登录、截图、日志、测试报告和邮件服务等。同时提供了运行效果的展示,便于理解和实践。
23 4
【入门思路】基于Python+Unittest+Appium+Excel+BeautifulReport的App/移动端UI自动化测试框架搭建思路
|
1天前
|
运维 监控 Python
自动化运维:使用Python脚本简化日常任务
【10月更文挑战第36天】在数字化时代,运维工作的效率和准确性成为企业竞争力的关键。本文将介绍如何通过编写Python脚本来自动化日常的运维任务,不仅提高工作效率,还能降低人为错误的风险。从基础的文件操作到进阶的网络管理,我们将一步步展示Python在自动化运维中的应用,并分享实用的代码示例,帮助读者快速掌握自动化运维的核心技能。
7 3
|
7天前
|
运维 监控 应用服务中间件
自动化运维:如何利用Python脚本提升工作效率
【10月更文挑战第30天】在快节奏的IT行业中,自动化运维已成为提升工作效率和减少人为错误的关键技术。本文将介绍如何使用Python编写简单的自动化脚本,以实现日常运维任务的自动化。通过实际案例,我们将展示如何用Python脚本简化服务器管理、批量配置更新以及监控系统性能等任务。文章不仅提供代码示例,还将深入探讨自动化运维背后的理念,帮助读者理解并应用这一技术来优化他们的工作流程。
|
8天前
|
数据管理 程序员 数据处理
利用Python自动化办公:从基础到实践####
本文深入探讨了如何运用Python脚本实现办公自动化,通过具体案例展示了从数据处理、文件管理到邮件发送等常见办公任务的自动化流程。旨在为非程序员提供一份简明扼要的实践指南,帮助他们理解并应用Python在提高工作效率方面的潜力。 ####
|
8天前
|
数据采集 Web App开发 JavaScript
爬虫策略规避:Python爬虫的浏览器自动化
爬虫策略规避:Python爬虫的浏览器自动化
|
8天前
|
数据采集 存储 XML
Python实现网络爬虫自动化:从基础到实践
本文将介绍如何使用Python编写网络爬虫,从最基础的请求与解析,到自动化爬取并处理复杂数据。我们将通过实例展示如何抓取网页内容、解析数据、处理图片文件等常用爬虫任务。
|
5天前
|
Web App开发 测试技术 数据安全/隐私保护
自动化测试的魔法:使用Python进行Web应用测试
【10月更文挑战第32天】本文将带你走进自动化测试的世界,通过Python和Selenium库的力量,展示如何轻松对Web应用进行自动化测试。我们将一起探索编写简单而强大的测试脚本的秘诀,并理解如何利用这些脚本来确保我们的软件质量。无论你是测试新手还是希望提升自动化测试技能的开发者,这篇文章都将为你打开一扇门,让你看到自动化测试不仅可行,而且充满乐趣。
|
1月前
|
数据采集 存储 JavaScript
自动化数据处理:使用Selenium与Excel打造的数据爬取管道
本文介绍了一种使用Selenium和Excel结合代理IP技术从WIPO品牌数据库(branddb.wipo.int)自动化爬取专利信息的方法。通过Selenium模拟用户操作,处理JavaScript动态加载页面,利用代理IP避免IP封禁,确保数据爬取稳定性和隐私性。爬取的数据将存储在Excel中,便于后续分析。此外,文章还详细介绍了Selenium的基本设置、代理IP配置及使用技巧,并探讨了未来可能采用的更多防反爬策略,以提升爬虫效率和稳定性。
|
3月前
|
关系型数据库 MySQL Shell
不通过navicat工具怎么把查询数据导出到excel表中
不通过navicat工具怎么把查询数据导出到excel表中
43 0
|
29天前
|
数据处理 Python
Python实用记录(十):获取excel数据并通过列表的形式保存为txt文档、xlsx文档、csv文档
这篇文章介绍了如何使用Python读取Excel文件中的数据,处理后将其保存为txt、xlsx和csv格式的文件。
44 3
Python实用记录(十):获取excel数据并通过列表的形式保存为txt文档、xlsx文档、csv文档