Python VS VBA 读取Excel中的文本框组件

50 篇文章 16 订阅
3 篇文章 2 订阅

作者:小小明

Python读取Excel的文本框

基本需求

今天看到了一个很奇怪的问题,要读取Excel文件的文本框中的文本,例如这种:

本以为openxlpy可以读取,但查看openxlpy官方文档并没有找到相应的API,咨询了几个大佬,他们也没有处理过类似的问题。

无赖之下,我就准备发挥我较强的数据解析能力,自己写个方法来读取这些东西。

处理代码

xlsx文件的本质是xml格式的压缩包,解压文件做xml解析提取出相应的数据即可。

本来准备用lxml作xpath解析xml,但实际测试发现,这些xml文件存在大量的命名空间,解析起来异常复杂,试了好几个普通的xml解析的库,可以顺利解析,但我觉得还不如正则方便,所以我最终选择了使用正则表达式作xml解析。

最终处理代码如下:

import re
import os
import shutil
from zipfile import ZipFile


def read_xlsx_textbox_text(xlsx_file):
    tempdir = tempfile.gettempdir()
    basename = os.path.basename(xlsx_file)
    xml_names = []
    with ZipFile(xlsx_file) as zip_file:
        for name in zip_file.namelist():
            if name.startswith("xl/drawings/drawing"):
                zip_file.extract(name, tempdir)
                destname = f"{tempdir}/{name}"
                xml_names.append(destname)
    result = []
    for xml_name in xml_names:
        with open(xml_name, encoding="utf-8") as f:
            text = f.read()
        lines = re.findall("<a:p>(.*?)</a:p>", text)
        for line in lines:
            runs = re.findall("<a:t>(.*?)</a:t>", line)
            result.append("".join(runs).replace('&lt;', '<').replace(
                '&gt;', '>').replace('&amp;', '&'))
    return "\n".join(result)

测试一下:

result = read_xlsx_textbox_text("test.xlsx")
print(result)

结果:

什么是JSON?

就是一种数据格式;比如说,我们现在规定,有一个txt文本文件,用来存放一个班级的成绩;然后呢,我们规定,这个文本文件里的学生成绩的格式,是第一行,就是一行列头(姓名 班级 年级 科目 成绩),接下来,每一行就是一个学生的成绩。那么,这个文本文件内的这种信息存放的格式,其实就是一种数据格式。

学生 班级 年级 科目 成绩
张三 一班 大一 高数 90
李四 二班 大一 高数 80

ok,对应到JSON,它其实也是代表了一种数据格式,所谓数据格式,就是数据组织的形式。比如说,刚才所说的学生成绩,用JSON格式来表示的话,如下:

[{"学生":"张三", "班级":"一班", "年级":"大一", "科目":"高数", "成绩":90}, {"学生":"李四", "班级":"二班", "年级":"大一", "科目":"高数", "成绩":80}]

其实,JSON,很简单,一点都不复杂,就是对同样一批数据的,不同的一种数据表示的形式。

JSON的数据语法,其实很简单:如果是包含多个数据实体的话,比如说多个学生成绩,那么需要使用数组的表现形式,就是[]。对于单个数据实体,比如一个学生的成绩,那么使用一个{}来封装数据,对于数据实体中的每个字段以及对应的值,使用key:value的方式来表示,多个key-value对之间用逗号分隔;多个{}代表的数据实体之间,用逗号分隔。
...

这样我们就顺利实现了,从一个Excel文件中,读取全部的文本框的文本。

注意:如果你有啥特殊的其他需求,可以根据实际情况修改代码,也可以联系本文作者(小小明)进行相应的定制。

读取xls文件的文本框内容

上面的方法,仅支持xlsx格式文件的读取,如果要读取xls格式,我们需要先进行格式转换。

完整代码:

import win32com.client as win32


def read_xls_textbox_text(xls_file):
    excel_app = win32.gencache.EnsureDispatch('Excel.Application')
#     excel_app.DisplayAlerts = False
    try:
        wb = excel_app.Workbooks.Open(xls_file)
        xlsx_file = xls_file+"x"
        wb.SaveAs(xlsx_file, FileFormat=51)
    finally:
        excel_app.Quit()
    return read_xlsx_textbox_text(xlsx_file)

如果你希望存在同名的xlsx文件时不提示,关闭注释即可

测试读取:

print(read_xls_textbox_text(r"E:\tmp\test2.xls"))

结果:

我们的数据从哪里来?
互联网行业:网站、app、系统(交易系统。。)
传统行业:电信,人们的上网、打电话、发短信等等数据

数据源:网站、app

都要往我们的后台去发送请求,获取数据,执行业务逻辑;app获取要展现的商品数据;发送请求到后台进行交易和结账

后台服务器,比如Tomcat、Jetty;但是,其实在面向大量用户,高并发(每秒访问量过万)的情况下,通常都不会直接是用Tomcat来接收请求。这种时候,通常,都是用Nginx来接收请求,并且后端接入Tomcat集群/Jetty集群,来进行高并发访问下的负载均衡。

比如说,Nginx,或者是Tomcat,你进行适当配置之后,所有请求的数据都会作为log存储起来;接收请求的后台系统(J2EE、PHP、Ruby On Rails),也可以按照你的规范,每接收一个请求,或者每执行一个业务逻辑,就往日志文件里面打一条log。
网站/app会发送请求到后台服务器,通常会由Nginx接收请求,并进行转发
...

xls格式批量转xlsx

假如我们有一批xls文件,希望批量转换为xlsx:

我的实现方式是整个文件夹都转换完毕再关闭应用,这样相对来说处理更快一些,但可能更耗内存,代码如下:

import win32com.client as win32  # 导入模块
from pathlib import Path
import os

def format_conversion(xls_path, output_path):
    if not os.path.exists(output_path):
        os.makedirs(output_path)
    excel_app = win32.gencache.EnsureDispatch('Excel.Application')
    try:
        for filename in Path(xls_path).glob("[!~]*.xls"):
            dest_name = f"{output_path}/{filename.name}x"
            wb = excel_app.Workbooks.Open(filename)
            wb.SaveAs(dest_name, FileFormat=51)
            print(dest_name, "保存完成")
    finally:
        excel_app.Quit()

测试一下:

excel_path = r"F:\excel文档"
output_path = r"E:\tmp\excel"
format_conversion(excel_path, output_path)

结果:

E:\tmp\excel/008.离线日志采集流程.xlsx 保存完成
E:\tmp\excel/009.实时数据采集流程.xlsx 保存完成
E:\tmp\excel/011.用户访问session分析-模块介绍.xlsx 保存完成
E:\tmp\excel/012.用户访问session分析-基础数据结构以及大数据平台架构介绍.xlsx 保存完成
E:\tmp\excel/013.用户访问session分析-需求分析.xlsx 保存完成
E:\tmp\excel/014.用户访问session分析-技术方案设计.xlsx 保存完成
E:\tmp\excel/015.用户访问session分析-数据表设计.xlsx 保存完成
E:\tmp\excel/018.用户访问session分析-JDBC原理介绍以及增删改查示范.xlsx 保存完成
E:\tmp\excel/019.数据库连接池原理.xlsx 保存完成
...

批量提取xlsx文件的文本框文本

上面我们已经获得了一个xlsx文件的文件夹,下面我们的需求是,提取这个文件夹下每个xlsx文件的文本框内容将其保存为对应的txt格式。

处理代码:

from pathlib import Path

xlsx_path = r"E:\tmp\excel"
for filename in Path(xlsx_path).glob("[!~]*.xlsx"):
    filename = str(filename)
    destname = filename.replace(".xlsx", ".txt")
    print(filename, destname)
    txt = read_xlsx_textbox_text(filename)
    with open(destname, "w") as f:
        f.write(txt)

执行后,已经顺利得到相应的txt文件:

需求升级

上面的读取方法是将整个excel文件所有的文本框内容都合并在一起,但有时我们的excel文件的多个sheet都存在文本框,我们希望能够对不同的sheet进行区分:

下面我们改进我们的读取方法,使其返回每个sheet名对应的文本框文本,先测试一下。

首先解压所需的文件:

from zipfile import ZipFile
from pathlib import Path
import shutil
import os
import tempfile
import re

xlsx_file = "test3.xlsx"

tempdir = tempfile.gettempdir()
basename = os.path.basename(xlsx_file)
xml_names = []
sheets_names = None
ids = []
with ZipFile(xlsx_file) as zip_file:
    for name in zip_file.namelist():
        if name.startswith("xl/drawings/drawing"):
            zip_file.extract(name, tempdir)
            destname = f"{tempdir}/{name}"
            xml_names.append(destname)
        elif name == "xl/workbook.xml":
            zip_file.extract(name, tempdir)
            sheets_names = f"{tempdir}/{name}"
        elif name.startswith("xl/worksheets/_rels/sheet"):
            tmp = name.lstrip("xl/worksheets/_rels/sheet")
            ids.append(int(tmp[:tmp.find(".")])-1)
print(xml_names, sheets_names, ids)

结果:

['C:\\Users\\Think\\AppData\\Local\\Temp/xl/drawings/drawing1.xml', 'C:\\Users\\Think\\AppData\\Local\\Temp/xl/drawings/drawing2.xml', 'C:\\Users\\Think\\AppData\\Local\\Temp/xl/drawings/drawing3.xml', 'C:\\Users\\Think\\AppData\\Local\\Temp/xl/drawings/drawing4.xml', 'C:\\Users\\Think\\AppData\\Local\\Temp/xl/drawings/drawing5.xml'] C:\Users\Think\AppData\Local\Temp/xl/workbook.xml [0, 1, 2, 4, 5]

读取sheet名称:

with open(sheets_names, encoding="utf-8") as f:
    text = f.read()
sheet_names = re.findall(
    '<sheet .*?name="([^"]+)" .*?/>', text)
tmp = []
for inx in ids:
    tmp.append(sheet_names[inx])
sheet_names = tmp
sheet_names

结果:

['JSON', '数据库连接池', '实时数据采集', '工厂设计模式', '页面转化率']

解析:

result = {}
for sheet_name, xml_name in zip(sheet_names, xml_names):
    with open(xml_name, encoding="utf-8") as f:
        xml = f.read()
    lines = re.findall("<a:p>(.*?)</a:p>", xml)
    tmp = []
    for line in lines:
        runs = re.findall("<a:t>(.*?)</a:t>", line)
        tmp.append("".join(runs).replace('&lt;', '<').replace(
            '&gt;', '>').replace('&amp;', '&'))
    result[sheet_name] = "\n".join(tmp)
result

结果(省略了大部分文字):

{'JSON': '什么是JSON?....',
 '数据库连接池': 'java程序\n数据库连接\n数据库连接\n数据库连接\nMySQL...',
 '实时数据采集': '...实时数据,通常都是从分布式消息队列集群中读取的,比如Kafka....',
 '工厂设计模式': '如果没有工厂模式,可能会出现的问题:....',
 '页面转化率': '用户行为分析大数据平台\n\n页面单跳转化率,....'}

可以看到已经顺利的读取到每个sheet对应的文本框内容,而且一一对应。

分别读取每个sheet对应文本框文本

我们整合并封装一下上面的过程为一个方法:

import re
import os
from zipfile import ZipFile
import tempfile


def read_xlsx_textbox_text(xlsx_file, combine=False):
    tempdir = tempfile.gettempdir()
    basename = os.path.basename(xlsx_file)
    xml_names = []
    sheets_names = None
    ids = []
    with ZipFile(xlsx_file) as zip_file:
        for name in zip_file.namelist():
            if name.startswith("xl/drawings/drawing"):
                zip_file.extract(name, tempdir)
                destname = f"{tempdir}/{name}"
                xml_names.append(destname)
            elif name == "xl/workbook.xml":
                zip_file.extract(name, tempdir)
                sheets_names = f"{tempdir}/{name}"
            elif name.startswith("xl/worksheets/_rels/sheet"):
                tmp = name.lstrip("xl/worksheets/_rels/sheet")
                ids.append(int(tmp[:tmp.find(".")])-1)
    with open(sheets_names, encoding="utf-8") as f:
        text = f.read()
    sheet_names = re.findall(
        '<sheet .*?name="([^"]+)" .*?/>', text)
    tmp = []
    for inx in ids:
        tmp.append(sheet_names[inx])
    sheet_names = tmp
    result = {}
    for sheet_name, xml_name in zip(sheet_names, xml_names):
        with open(xml_name, encoding="utf-8") as f:
            xml = f.read()
        lines = re.findall("<a:p>(.*?)</a:p>", xml)
        tmp = []
        for line in lines:
            runs = re.findall("<a:t>(.*?)</a:t>", line)
            tmp.append("".join(runs).replace('&lt;', '<').replace(
                '&gt;', '>').replace('&amp;', '&'))
        result[sheet_name] = "\n".join(tmp)
    if combine:
        return "\n".join(result.values())
    return result

调用方式:

result = read_xlsx_textbox_text("test3.xlsx")
print(result)

可以传入combine=True,将sheet的结果合并到一个文本,但这样不如直接调用之前编写的方法。

批量提取文本框文本分sheet单独保存

下面,我们的需求是对每个xlsx文件创建一个同名文件夹,每个文件夹下根据sheet名称单独保存文本框的文本。

处理代码:

from pathlib import Path
import os

xlsx_path = r"E:\tmp\excel"
for filename in Path(xlsx_path).glob("[!~]*.xlsx"):
    dest = filename.with_suffix("")
    if not os.path.exists(dest):
        os.mkdir(dest)
    filename = str(filename)
    print(filename, dest)
    result = read_xlsx_textbox_text(filename)
    for txtname, txt in result.items():
        with open(f"{dest}/{txtname}", "w") as f:
            f.write(txt)
        print(f"\t{dest}/{txtname}")

经测试顺利的为每个excel文件创建了一个目录,每个目录下根据哪些sheet存在文本框就有相应的sheet名文件。

使用Python调用VBA解决需求

VBA官方文档地址:https://docs.microsoft.com/zh-cn/office/vba/api/overview/excel

整体而言,上面自行解析xml的方法还是挺麻烦的,在写完上面的方法后我灵机一动,VBA不就有现成的读取文本框的方法吗?而Python又可以全兼容的写VBA代码,那问题就简单了。通过VBA,不仅代码简单,而且不用考虑格式转换的问题,直接可以解决问题,读取代码如下:

import win32com.client as win32


def read_excel_textbox_text(excel_file, app=None, combine=False):
    if app is None:
        excel_app = win32.gencache.EnsureDispatch('Excel.Application')
    else:
        excel_app = app
    wb = excel_app.Workbooks.Open(excel_file)
    result = {}
    for sht in wb.Sheets:
        if sht.Shapes.Count == 0:
            continue
        lines = []
        for shp in sht.Shapes:
            try:
                text = shp.TextFrame2.TextRange.Text
                lines.append(text)
            except Exception as e:
                pass
        result[sht.Name] = "\n".join(lines)
    if app is None:
        excel_app.Quit()
    if combine:
        return "\n".join(result.values())
    return result

测试读取:

result = read_excel_textbox_text(r'F:\jupyter\test\提取word图片\test3.xlsx')
print(result)

顺利读出结果。

批量处理:

from pathlib import Path
import os

xlsx_path = r"E:\tmp\excel"
app = win32.gencache.EnsureDispatch('Excel.Application')
try:
    for filename in Path(xlsx_path).glob("[!~]*.xls"):
        dest = filename.with_suffix("")
        if not os.path.exists(dest):
            os.mkdir(dest)
        filename = str(filename)
        print(filename, dest)
        result = read_excel_textbox_text(filename, app)
        for txtname, txt in result.items():
            with open(f"{dest}/{txtname}", "w") as f:
                f.write(txt)
            print(f"\t{dest}/{txtname}")
finally:
    app.Quit()

经测试,VBA处理的缺点也很明显,63个文件耗时达到25秒,而直接解析xml耗时仅259毫秒,性能差别不在一个数量级。

使用xlwings解决需求

苹果电脑是不支持VBA的,上面调用VBA的代码对于苹果电脑来说无效,但所幸的是xlwings在0.21.4版本中新增了访问文本框文本的属性text。

作为Windows上Pywin32和Mac上appscript的智能包装的xlwings,已经通过appscript实现了在Mac系统上对文本框文本的访问:

import xlwings as xw

app = xw.App(visible=True, add_book=False)
wb = app.books.open(r'test3.xlsx')
for sht in wb.sheets:
    print("-------------", sht.name)
    for shp in sht.shapes:
        if hasattr(shp, 'text') and shp.text:
            print(shp.text)
wb.close()
app.quit()

注意:如果你的xlwings没有这个属性,请注意升级:

pip install xlwings -U

总结

读取excel中的数据,基本没有VBA干不了的事,python调用VBA也很简单,直接使用pywin32即可。当然2007的xlsx本质上是xml格式的压缩包,解析xml文本也没有读不了的数据,只是代码编写起来异常费劲,当然也得你对xlsx的存储原理较为了解。

这样VBA与直接解析xml的优劣势就非常明显了:

  • VBA是excel应用直接支持的API,代码编写起来相对很简单,但执行效率低下。苹果电脑无法使用VBA,可以使用xlwings已经封装好的方法实现。
  • 直接解析xml文件,需要对excel的存储格式较为了解,编码起来很费劲,但是执行效率极高。

作为读者的你有何看法呢?欢迎你在下方留言区发表你的看法。

相关推荐

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:鲸 设计师:meimeiellie 返回首页

打赏作者

小小明-代码实体

喜欢,就关注;爱,就打赏

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值