资讯专栏INFORMATION COLUMN

用python库openpyxl操作excel,从源excel表中提取信息复制到目标excel表中

gaosboy / 4088人阅读

摘要:特别注意当用模式载入时,跟都是对象。可以使用载入已经存在的表。我们的目的是从源表中提取信息并批量复制到目标表中,所以我们首先定义一些变量。最后保存目标就可以了。

现代生活中,我们很难不与excel表打交道,excel表有着易学易用的优点,只是当表中数据量很大,我们又需要从其他表册中复制粘贴一些数据(比如身份证号)的时候,我们会越来越倦怠,毕竟我们不是机器,没法长时间做某种重复性的枯燥操作。想象这样一个场景,我们有个几千行的表要填,需要根据姓名输入其对应的身份证号,但之前我们已经做过一个类似的表,同样的一些人的姓名跟身份证号是完整的,那么我们就需要通过一个个查找姓名,然后把身份证号码复制到我们当前要做的表里去。

当我日复一日重复着这些操作的时候,我都很想有一个自动化工具来完成这种操作,把做为人的我从这种非人的折磨里解脱出来,最后还是想到了python,因为这样我能很少的关注语言内部的一些细节,从而专注于解决这个问题。

python有很多专门处理excel的第三方库,我选择了openpyxl,因为支持微软最新excel的格式xlsx,其官网地址为https://openpyxl.readthedocs.io/en/latest/index.html,官网上的教程很全面,有其他需求的可以好好研究一番。

其安装命令为 pip install openpyxl(在线安装)或者 easy_install openpyxl

openpyxl的操作可以分四步,第一步载入现有workbook或者创建workbook到内存,分别使用

from openpyxl import load_workbook
from openpyxl import Workbook
#载入现有workbook中
wb1=load_workbook("lalala.xlsx")
"""
在源表数据量很大的时候,这里我们可以使用openpyxl的read_only模式
载入源表,这样做的好处是不用把整个表都载入内存
"""
wb1=load_workbook(filename="lalala.xlsx",read_only=True)
#创建workbook
wb2 = Workbook()

第二步就是操作excel表中的sheet了,通过Workbook()创建的workbook默认活动的sheet名称为Sheet,可以通过python交互命令行进行验证。

#获取活动的sheet
ws = wb.active
#设置sheet的标题
ws.title = "range names"
#创建以Pi为标题的sheet
ws = wb.create_sheet(title="Pi")
#获取标题为Sheet1的sheet
ws=wb["Sheet1"]

第三步就是操作sheet中的cell了。需要注意的是,一个cell的位置由它所在的列跟行共同决定,比如一个cell,它在A列,并在第三行,就可以通过ws["A3"] 来访问。cell还具有row跟column属性,cell.row跟cell.column的数据类型如下图所示。

特别注意当用read_only模式载入workbook时,cell.row跟cell.column都是int对象。cell.column记录的是cell所在列离第一列的偏移数,并非workbook中真正代表列数的大写字母,比如“A”。

#获取第一行,数据类型为tuple
row=ws[1]
#获取A列,数据类型为tuple
column=ws["A"]
#设置F5的值
ws["F5"]="sfs"
#设置cell的值
ws["F5"].value="hello"
#获得cell的行数
m=ws["F5"].row
#获得cell的列数
n=ws["F5"].column
#获得特定区域的值,比如从F5到F30,数据类型为tuple
k=ws["F5":"F30"]
#获得特定区域的值,比如从F5到G30,数据类型为tuple
j=ws["F5":"G30"]
#获取sheet的最大行数
row_count=ws.max_row
#获取sheet的最大列数
column_count=ws.max_column

最后一步把更改保存,这里要注意,当要保存的表在别的软件(microsoft office或者wps)中打开时,保存操作会报错。

wb1.save("empty_book.xlsx")
wb2.save(filename="other_book.xlsx")
实现需求

新建一个get_info_from_excel.py文件,用你习惯的编辑器来编辑,首先需要引入openpyxl库中的load_workbook模块。可以使用load_workbook载入已经存在的excel表。

from openpyxl import load_workbook

我们的目的是从源excel表中提取信息并批量复制到目标excel表中,所以我们首先定义一些变量。

#源表名称
source_file_name="lalala.xlsx"
#目标表名称
target_file_name="lelele.xlsx"
#源表中要提取信息的sheet
source_sheet_name="Sheet2"
#目标表中要批量复制信息的sheet
target_sheet_name="Sheet2"
#源表中的标题行在哪一行
source_header_row=3
#目标表中的标题行在哪一行
target_header_row=2
#源表中要根据哪一列数据提取信息,根据源表标题行
source_cell_condition="姓名"
#目标表中要根据哪一列数据复制信息,根据目标表标题行
target_cell_condition="姓名"
#源表中要提取信息的列
source_cell_filled="身份证号"
#目标表中要复制信息的列
target_cell_filling="身份证号"

将源表跟目标表载入内存,方便下一步操作这两个表。

#在源表数据量很大的时候,这里我们可以使用openpyxl的read_only模式载入源表,这样做的好处是不用把整个表都载入内存
#wb_w=load_workbook(source_file_name)
wb_r=load_workbook(filename=source_file_name,read_only=True)
wb_w=load_workbook(target_file_name)

从前面已经定义的sheet名称跟标题行数获取源表跟目标表的标题行:

ws_r=wb_r[source_sheet_name]
ws_w=wb_w[target_sheet_name]

header_row_r=ws_r[source_header_row]
header_row_w=ws_w[target_header_row]

操作源表标题行,获取我们想要的信息:

"""
openpyxl用read_only模式载入workbook时,获取到的cell不是一般的cell,
经过测试cell.column变成偏移了几列的整数,所以这里我们定义一个函数来处理,
把整数转换成excel真正的列数,比如“A”、“BB”等。
"""
def readOnly_offsetColunmNumber_toRealColumn(number):
    column=""
    if number<=26:
       column=chr(number+ord("A")-1)
    else:
       number1=number//26
       column1=chr(number1+ord("A")-1)
       number2=number%26
       column2=chr(number2+ord("A")-1)
       column=column1+column2
    return column

#初始化两个变量,分别是源表的条件列,要复制的列
source_condition_column=""
source_filled_column=""
"""
循环源表的标题列,得到条件列的位置以及要复制列的位置,
再通过内嵌的循环得到条件列的最大行数
"""
for cell in header_row_r:
    if cell.value==source_cell_condition:
       source_condition_column=readOnly_offsetColunmNumber_toRealColumn(cell.column)            
    elif cell.value==source_cell_filled:
         source_filled_column=readOnly_offsetColunmNumber_toRealColumn(cell.column)

操作目标表标题行,获取我们想要的信息:

#初始化两个变量,分别是目标表的条件列,要粘贴的列
target_condition_column=""
target_filling_column=""
"""
循环目标表的标题列,得到条件列的位置以及要粘贴列的位置,
再通过内嵌的循环得到条件列的最大行数
"""
for cell_j in header_row_w:
    if cell_j.value==target_cell_condition:
       target_condition_column=cell_j.column           
    elif cell_j.value==target_cell_filling:
         target_filling_column=cell_j.column

现在我们已经得到所有需要的信息,该到实际粘贴数据的时候了。

"""
循环目标表的条件列,内部嵌套循环源表的条件列,一旦目标表条件列的某个cell
与源表条件列某个cell的值相同,我们就把源表要复制列的同一行的cell的值
赋予目标表要粘贴列的同一行的cell。
""" 
for cell_m in ws_w[target_condition_column+str(target_header_row+1):target_condition_column+str(ws_w.max_row)]:
    for cell_n in ws_r[source_condition_column+str(source_header_row+1):source_condition_column+str(ws_r.max_row)]:
        if cell_m[0].value==cell_n[0].value:
           ws_w[target_filling_column+str(cell_m[0].row)].value=ws_r[source_filled_column+str(cell_n[0].row)].value

最后保存目标workbook就可以了。

wb_w.save(target_file_name)

注意:如果excel中标题行有合并居中的话,脚本会报错,一种办法就是去掉合并居中;另一种就是在原标题行下再插入一行新的行再把原标题行的内容粘贴进去,用插入的这一行作为标题行。

[欢迎浏览我的个人博客,https://diwugebingren.github.io
](https://diwugebingren.github.io)

文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。

转载请注明本文地址:https://www.ucloud.cn/yun/43549.html

相关文章

  • python3与Excel的完美结合

    摘要:对象也有和属性提供该单元格的位置信息。读取对象的属性一个完整的案例代码如下读取文档返回一个对象,有点类似于文件对象在工作薄中取得工作表返回一个列表,存储表中所有的工作表返回一个对象,返回当前的活动表获取工作表中,单元格的信息的属性 Excel 是 Windows 环境下流行的、强大的电子表格应用。openpyxl 模块让 Python 程序能读取和修改 Excel 电子表格文件。例如,...

    enrecul101 评论0 收藏0
  • python实现——处理Excel表格(超详细)

    摘要:目录和基本操作用模块打开文档,查看所有表通过名称获取表格获取活动表获取表格的尺寸获取单元格中的数据获取单元格的行列坐标获取区间内的数据获取指定区间的数据获取指定行列的数据按行列获取值获取活动表的行列数操作创建新的修改单 ...

    Richard_Gao 评论0 收藏0
  • 第一篇(从django后台解析excel数据批量导入数据)

    摘要:总结整个过程的难点在于获取文件对象,从数据中取值然后在按取出,这样我们就可以从后台上传文件,然后进行批量导入数据库,其他数据格式只需要改和中的数据字段就可以 第一篇(从django后台解析excel数据批量导入数据库) 文章会在github中持续更新 作者: knthony github 联系我 1.django 如何从后台上传excel中批量解析数据 要从django后台导入...

    2i18ns 评论0 收藏0
  • 爬虫小demo

    摘要:爬取的数据存入表格分析要爬取的内容的网页结构是库写入表所用读取表所用通过解析文档为用户提供需要抓取的数据改变标准输出的默认编码我们开始利用来获取网页并利用解析网页返回的是状态码,加上以字节形式二进制返回数据。 爬取的数据存入Excel表格 分析要爬取的内容的网页结构: showImg(https://segmentfault.com/img/bVbsFt6?w=1644&h=1012)...

    pf_miles 评论0 收藏0
  • python大佬养成计划----excel操作

    摘要:新型数据类型中存储系列数据,比较常见的数据类型有,除此之外,还有数据类型元组的只能通过访问,模块的子类不仅可以使用的访问,还可以通过的进行访问。可以将理解为中的结构,其首先将各个命名,然后对每个赋予数据。 namedtuple新型数据类型 Python中存储系列数据,比较常见的数据类型有list,除此之外,还有tuple数据类型.tuple元组的item只能通过index访问,coll...

    cpupro 评论0 收藏0

发表评论

0条评论

gaosboy

|高级讲师

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<