作者：小小明

Python讀取Excel的文本框

基本需求

今天看到了一個(gè)很奇怪的問(wèn)題，要讀取Excel文件的文本框中的文本，例如這種：

本以為openxlpy可以讀取，但查看openxlpy官方文檔并沒(méi)有找到相應(yīng)的API，咨詢(xún)了幾個(gè)大佬，他們也沒(méi)有處理過(guò)類(lèi)似的問(wèn)題。

無(wú)賴(lài)之下，我就準(zhǔn)備發(fā)揮我較強(qiáng)的數(shù)據(jù)解析能力，自己寫(xiě)個(gè)方法來(lái)讀取這些東西。

處理代碼

xlsx文件的本質(zhì)是xml格式的壓縮包，解壓文件做xml解析提取出相應(yīng)的數(shù)據(jù)即可。

本來(lái)準(zhǔn)備用lxml作xpath解析xml，但實(shí)際測(cè)試發(fā)現(xiàn)，這些xml文件存在大量的命名空間，解析起來(lái)異常復(fù)雜，試了好幾個(gè)普通的xml解析的庫(kù)，可以順利解析，但我覺(jué)得還不如正則方便，所以我最終選擇了使用正則表達(dá)式作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)

測(cè)試一下：

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

結(jié)果：

什么是JSON？

就是一種數(shù)據(jù)格式；比如說(shuō)，我們現(xiàn)在規(guī)定，有一個(gè)txt文本文件，用來(lái)存放一個(gè)班級(jí)的成績(jī)；然后呢，我們規(guī)定，這個(gè)文本文件里的學(xué)生成績(jī)的格式，是第一行，就是一行列頭（姓名 班級(jí) 年級(jí) 科目 成績(jī)），接下來(lái)，每一行就是一個(gè)學(xué)生的成績(jī)。那么，這個(gè)文本文件內(nèi)的這種信息存放的格式，其實(shí)就是一種數(shù)據(jù)格式。

學(xué)生 班級(jí) 年級(jí) 科目 成績(jī)
張三 一班 大一 高數(shù) 90
李四 二班 大一 高數(shù) 80

ok，對(duì)應(yīng)到JSON，它其實(shí)也是代表了一種數(shù)據(jù)格式，所謂數(shù)據(jù)格式，就是數(shù)據(jù)組織的形式。比如說(shuō)，剛才所說(shuō)的學(xué)生成績(jī)，用JSON格式來(lái)表示的話(huà)，如下：

[{"學(xué)生":"張三", "班級(jí)":"一班", "年級(jí)":"大一", "科目":"高數(shù)", "成績(jī)":90}, {"學(xué)生":"李四", "班級(jí)":"二班", "年級(jí)":"大一", "科目":"高數(shù)", "成績(jī)":80}]

其實(shí)，JSON，很簡(jiǎn)單，一點(diǎn)都不復(fù)雜，就是對(duì)同樣一批數(shù)據(jù)的，不同的一種數(shù)據(jù)表示的形式。

JSON的數(shù)據(jù)語(yǔ)法，其實(shí)很簡(jiǎn)單：如果是包含多個(gè)數(shù)據(jù)實(shí)體的話(huà)，比如說(shuō)多個(gè)學(xué)生成績(jī)，那么需要使用數(shù)組的表現(xiàn)形式，就是[]。對(duì)于單個(gè)數(shù)據(jù)實(shí)體，比如一個(gè)學(xué)生的成績(jī)，那么使用一個(gè){}來(lái)封裝數(shù)據(jù)，對(duì)于數(shù)據(jù)實(shí)體中的每個(gè)字段以及對(duì)應(yīng)的值，使用key:value的方式來(lái)表示，多個(gè)key-value對(duì)之間用逗號(hào)分隔；多個(gè){}代表的數(shù)據(jù)實(shí)體之間，用逗號(hào)分隔。
...

這樣我們就順利實(shí)現(xiàn)了，從一個(gè)Excel文件中，讀取全部的文本框的文本。

注意：如果你有啥特殊的其他需求，可以根據(jù)實(shí)際情況修改代碼，也可以聯(lián)系本文作者（小小明）進(jìn)行相應(yīng)的定制。

讀取xls文件的文本框內(nèi)容

上面的方法，僅支持xlsx格式文件的讀取，如果要讀取xls格式，我們需要先進(jìn)行格式轉(zhuǎn)換。

完整代碼：

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文件時(shí)不提示，關(guān)閉注釋即可

測(cè)試讀?。?/p>

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

結(jié)果：

我們的數(shù)據(jù)從哪里來(lái)？
互聯(lián)網(wǎng)行業(yè)：網(wǎng)站、app、系統(tǒng)（交易系統(tǒng)。。）
傳統(tǒng)行業(yè)：電信，人們的上網(wǎng)、打電話(huà)、發(fā)短信等等數(shù)據(jù)

數(shù)據(jù)源：網(wǎng)站、app

都要往我們的后臺(tái)去發(fā)送請(qǐng)求，獲取數(shù)據(jù)，執(zhí)行業(yè)務(wù)邏輯；app獲取要展現(xiàn)的商品數(shù)據(jù)；發(fā)送請(qǐng)求到后臺(tái)進(jìn)行交易和結(jié)賬

后臺(tái)服務(wù)器，比如Tomcat、Jetty；但是，其實(shí)在面向大量用戶(hù)，高并發(fā)（每秒訪問(wèn)量過(guò)萬(wàn)）的情況下，通常都不會(huì)直接是用Tomcat來(lái)接收請(qǐng)求。這種時(shí)候，通常，都是用Nginx來(lái)接收請(qǐng)求，并且后端接入Tomcat集群/Jetty集群，來(lái)進(jìn)行高并發(fā)訪問(wèn)下的負(fù)載均衡。

比如說(shuō)，Nginx，或者是Tomcat，你進(jìn)行適當(dāng)配置之后，所有請(qǐng)求的數(shù)據(jù)都會(huì)作為log存儲(chǔ)起來(lái)；接收請(qǐng)求的后臺(tái)系統(tǒng)（J2EE、PHP、Ruby On Rails），也可以按照你的規(guī)范，每接收一個(gè)請(qǐng)求，或者每執(zhí)行一個(gè)業(yè)務(wù)邏輯，就往日志文件里面打一條log。
網(wǎng)站/app會(huì)發(fā)送請(qǐng)求到后臺(tái)服務(wù)器，通常會(huì)由Nginx接收請(qǐng)求，并進(jìn)行轉(zhuǎn)發(fā)
...

xls格式批量轉(zhuǎn)xlsx

假如我們有一批xls文件，希望批量轉(zhuǎn)換為xlsx：

我的實(shí)現(xiàn)方式是整個(gè)文件夾都轉(zhuǎn)換完畢再關(guān)閉應(yīng)用，這樣相對(duì)來(lái)說(shuō)處理更快一些，但可能更耗內(nèi)存，代碼如下：

import win32com.client as win32  # 導(dǎo)入模塊
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()

測(cè)試一下：

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

結(jié)果：

E:\tmp\excel/008.離線日志采集流程.xlsx 保存完成
E:\tmp\excel/009.實(shí)時(shí)數(shù)據(jù)采集流程.xlsx 保存完成
E:\tmp\excel/011.用戶(hù)訪問(wèn)session分析-模塊介紹.xlsx 保存完成
E:\tmp\excel/012.用戶(hù)訪問(wèn)session分析-基礎(chǔ)數(shù)據(jù)結(jié)構(gòu)以及大數(shù)據(jù)平臺(tái)架構(gòu)介紹.xlsx 保存完成
E:\tmp\excel/013.用戶(hù)訪問(wèn)session分析-需求分析.xlsx 保存完成
E:\tmp\excel/014.用戶(hù)訪問(wèn)session分析-技術(shù)方案設(shè)計(jì).xlsx 保存完成
E:\tmp\excel/015.用戶(hù)訪問(wèn)session分析-數(shù)據(jù)表設(shè)計(jì).xlsx 保存完成
E:\tmp\excel/018.用戶(hù)訪問(wèn)session分析-JDBC原理介紹以及增刪改查示范.xlsx 保存完成
E:\tmp\excel/019.數(shù)據(jù)庫(kù)連接池原理.xlsx 保存完成
...

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

上面我們已經(jīng)獲得了一個(gè)xlsx文件的文件夾，下面我們的需求是，提取這個(gè)文件夾下每個(gè)xlsx文件的文本框內(nèi)容將其保存為對(duì)應(yīng)的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)

執(zhí)行后，已經(jīng)順利得到相應(yīng)的txt文件：

需求升級(jí)

上面的讀取方法是將整個(gè)excel文件所有的文本框內(nèi)容都合并在一起，但有時(shí)我們的excel文件的多個(gè)sheet都存在文本框，我們希望能夠?qū)Σ煌膕heet進(jìn)行區(qū)分：

下面我們改進(jìn)我們的讀取方法，使其返回每個(gè)sheet名對(duì)應(yīng)的文本框文本，先測(cè)試一下。

首先解壓所需的文件：

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)

結(jié)果：

['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名稱(chēng)：

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

結(jié)果：

['JSON', '數(shù)據(jù)庫(kù)連接池', '實(shí)時(shí)數(shù)據(jù)采集', '工廠設(shè)計(jì)模式', '頁(yè)面轉(zhuǎn)化率']

解析：

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

結(jié)果（省略了大部分文字）：

{'JSON': '什么是JSON？....',
 '數(shù)據(jù)庫(kù)連接池': 'java程序\n數(shù)據(jù)庫(kù)連接\n數(shù)據(jù)庫(kù)連接\n數(shù)據(jù)庫(kù)連接\nMySQL...',
 '實(shí)時(shí)數(shù)據(jù)采集': '...實(shí)時(shí)數(shù)據(jù)，通常都是從分布式消息隊(duì)列集群中讀取的，比如Kafka....',
 '工廠設(shè)計(jì)模式': '如果沒(méi)有工廠模式，可能會(huì)出現(xiàn)的問(wèn)題：....',
 '頁(yè)面轉(zhuǎn)化率': '用戶(hù)行為分析大數(shù)據(jù)平臺(tái)\n\n頁(yè)面單跳轉(zhuǎn)化率，....'}

可以看到已經(jīng)順利的讀取到每個(gè)sheet對(duì)應(yīng)的文本框內(nèi)容，而且一一對(duì)應(yīng)。

分別讀取每個(gè)sheet對(duì)應(yīng)文本框文本

我們整合并封裝一下上面的過(guò)程為一個(gè)方法：

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

調(diào)用方式：

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

可以傳入combine=True，將sheet的結(jié)果合并到一個(gè)文本，但這樣不如直接調(diào)用之前編寫(xiě)的方法。

批量提取文本框文本分sheet單獨(dú)保存

下面，我們的需求是對(duì)每個(gè)xlsx文件創(chuàng)建一個(gè)同名文件夾，每個(gè)文件夾下根據(jù)sheet名稱(chēng)單獨(dú)保存文本框的文本。

處理代碼：

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}")

經(jīng)測(cè)試順利的為每個(gè)excel文件創(chuàng)建了一個(gè)目錄，每個(gè)目錄下根據(jù)哪些sheet存在文本框就有相應(yīng)的sheet名文件。

使用Python調(diào)用VBA解決需求

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

整體而言，上面自行解析xml的方法還是挺麻煩的，在寫(xiě)完上面的方法后我靈機(jī)一動(dòng)，VBA不就有現(xiàn)成的讀取文本框的方法嗎？而Python又可以全兼容的寫(xiě)VBA代碼，那問(wèn)題就簡(jiǎn)單了。通過(guò)VBA，不僅代碼簡(jiǎn)單，而且不用考慮格式轉(zhuǎn)換的問(wèn)題，直接可以解決問(wèn)題，讀取代碼如下：

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

測(cè)試讀?。?/p>

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

順利讀出結(jié)果。

批量處理：

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()

經(jīng)測(cè)試，VBA處理的缺點(diǎn)也很明顯，63個(gè)文件耗時(shí)達(dá)到25秒，而直接解析xml耗時(shí)僅259毫秒，性能差別不在一個(gè)數(shù)量級(jí)。

使用xlwings解決需求

蘋(píng)果電腦是不支持VBA的，上面調(diào)用VBA的代碼對(duì)于蘋(píng)果電腦來(lái)說(shuō)無(wú)效，但所幸的是xlwings在0.21.4版本中新增了訪問(wèn)文本框文本的屬性text。

作為Windows上Pywin32和Mac上appscript的智能包裝的xlwings，已經(jīng)通過(guò)appscript實(shí)現(xiàn)了在Mac系統(tǒng)上對(duì)文本框文本的訪問(wèn)。

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沒(méi)有這個(gè)屬性，請(qǐng)注意升級(jí)：

pip install xlwings -U

總結(jié)

讀取excel中的數(shù)據(jù)，基本沒(méi)有VBA干不了的事，python調(diào)用VBA也很簡(jiǎn)單，直接使用pywin32即可。當(dāng)然2007的xlsx本質(zhì)上是xml格式的壓縮包，解析xml文本也沒(méi)有讀不了的數(shù)據(jù)，只是代碼編寫(xiě)起來(lái)異常費(fèi)勁，當(dāng)然也得你對(duì)xlsx的存儲(chǔ)原理較為了解。

這樣VBA與直接解析xml的優(yōu)劣勢(shì)就非常明顯了：

• VBA是excel應(yīng)用直接支持的API，代碼編寫(xiě)起來(lái)相對(duì)很簡(jiǎn)單，但執(zhí)行效率低下。蘋(píng)果電腦無(wú)法使用VBA，可以使用xlwings已經(jīng)封裝好的方法實(shí)現(xiàn)。
• 直接解析xml文件，需要對(duì)excel的存儲(chǔ)格式較為了解，編碼起來(lái)很費(fèi)勁，但是執(zhí)行效率極高。

你有何看法呢？歡迎你在下方留言區(qū)發(fā)表你的看法。