将长表格图片转Excel表格

大家好，我是小小明。

最近很多朋友和同事问我如何将图片转Excel表格，老实说这方面现成的工具基本都不好使，不过百度AI有支持进行表格图片识别的接口，我们只要按照百度AI的要求传入相应的数据进行识别即可。

需求与技术点

需求，有两张超长的表格图片：

现在希望将其识别后转成Excel表格。

经查询两张图片的分辨率分别为791×7616和791×7531，可见其长度非常宽。

本文涉及的技术点：

1. 图片分段切片
2. 图片数据转base64编码
3. keyring密钥环的使用
4. 百度AI表格识别接口的使用
5. 分段表格的合并

主要难点：图片切分如何保证切分处包含完整的单元格

百度AI接口

首先我们进入百度AI，查看开放能力-》文字识别-》表格文字识别：

​​https://ai.baidu.com/tech/ocr_others/table​​

查看技术文档：

​​https://ai.baidu.com/ai-doc/OCR/Ik3h7y238​​

关于Access Token主要需要注册百度AI账号，创建应用后在应用列表里获取ak和sk：

然后就可以通过指定接口读取。

下面演示一下如何通过python代码获取access_token。

我之前已经通过keyring存储过ak和sk：

import keyring

keyring.set_password("baidu_ai", "ak", ak)
keyring.set_password("baidu_ai", "sk", sk)

关于keyring的安装和使用：https://pypi.org/project/keyring/

所以我可以这样获取ak和sk：

import requests
import pandas as pd

ak = keyring.get_password("baidu_ai", "ak")
sk = keyring.get_password("baidu_ai", "sk")
host = f'https://aip.baidubce.com/oauth/2.0/token?grant_type=client_credentials&client_id={ak}&client_secret={sk}'
access_token = requests.get(host).json()['access_token']

接口对图片数据的要求是：base64编码，编码后大小不超过4M，最短边至少15px，最长边最大4096px，支持jpg/jpeg/png/bmp格式。

个人经过实际测试发现，虽然接口支持最大4096像素的识别，但真传入长度超过1000多像素的时候，识别准确率极度下降，所以我们需要对图片进行一定的切分，并转换为base64编码。

下面我们开始测试图片的切片和转换：

图片切分

首先读取一张图片进行测试：

然后现在我们写一个切片算法进行切分：

import math

w, h = img.size

height = 860
r = math.ceil(h/height)
img_splits = []
for i in range(r):
    start = height * i
    end = height * (i + 1)
    if end > h:
        end = h
    if i != 0:
        start -= 25
    box = (0, start, w, end)
    img_split = img.crop(box)
    print(i)
    display(img_split)
    img_splits.append(img_split)

上述切分算法，以每张图860像素为基准进行切分，不是开头的图片上面补25个像素，保证每个单元格都完整出现在每张图片里。切割效果演示：

注意：前面说了图片切分处如何保证切分处包含完整的单元格是个难点，我最终如何解决这个问题呢？我个人是通过将每张切分图片向前补一些像素来完成这个效果。到底补多少个像素最合适呢，经测试补充单元格内部高度的像素是能保证包含每个单元格的。

对于上图最佳像素补充值是30，可以用截图软件量一下。

当然，如果有条件的话可以实现精准的找到黑线位置进行切分，但这样实现编码起来比较复杂。在补像素已经能够得到效果时，无需进行更复杂的编码。

图片转base64编码

下面我们再准备一个将图片转换为base64编码字符串的方法：

from io import BytesIO
import base64


def image_to_base64(img):
    output_buffer = BytesIO()
    img.save(output_buffer, format='JPEG')
    byte_data = output_buffer.getvalue()
    base64_str = base64.b64encode(byte_data)
    return

表格图片识别接口

前面查看百度AI的文档看到，API提供了同步和异步两种识别方式，经过实测发现异步不仅编码复杂反而因为网络传输原因比同步接口更慢，所以接下来我们只使用同步接口进行演示。

在用前面的方面获取access_token后，我们测试一下识别第一张图片：

img_splits[0]

使用同步接口进行识别（指定了​​is_sync='true'​​）：

import pandas as pd
request_url = f"https://aip.baidubce.com/rest/2.0/solution/v1/form_ocr/request?access_token={access_token}"
headers = {'content-type': 'application/x-www-form-urlencoded'}

base64_str = image_to_base64(img_splits[0])
params = {"image": base64_str, "is_sync": "true", "request_type": "excel"}
json_data = requests.post(request_url, data=params, headers=headers).json()
df = pd.read_excel(excel_url, header=None)

可以看到识别结果中，对于边框不完整的图片数据不会参与识别（最后一条小米集团没有被识别）。

这正是我们想要的效果，说明在下一张切分图片补齐缺少部分的方法可行。

封装一下，再识别第二张图片：

def table_orc_by_baiduAI(img, access_token):
    request_url = f"https://aip.baidubce.com/rest/2.0/solution/v1/form_ocr/request?access_token={access_token}"
    headers = {'content-type': 'application/x-www-form-urlencoded'}

    base64_str = image_to_base64(img)
    params = {"image": base64_str, "is_sync": "true", "request_type": "excel"}
    json_data = requests.post(request_url, data=params, headers=headers).json()
    excel_url = json_data["result"]["result_data"]
    df = pd.read_excel(excel_url, header=None)
    return df


table_orc_by_baiduAI(img_splits[1], access_token)

非常好，正好衔接了。

下面可以批量识别该图片的所有图片片段：

可以看到，识别耗时为30秒。

从252条数据以及结尾编号250来看，每行数据都被识别到了，现在将结果保存下来：

result.to_excel("a.xlsx", index=False, header=False)

批量表格图片识别代码

上面我们经过测试，已经成功识别单张长表格图片，下面我们封装一下上面的过程：

from PIL import Image
import keyring
import requests
from io import BytesIO
import base64
import math
import pandas as pd


def img_split(img, height=860, cell_height=30):
    w, h = img.size
    r = math.ceil(h/height)
    img_splits = []
    for i in range(r):
        start = height * i
        end = height * (i + 1)
        if end > h:
            end = h
        if i != 0:
            start -= cell_height
        img_split = img.crop((0, start, w, end))
        img_splits.append(img_split)
    return img_splits


def image_to_base64(img):
    output_buffer = BytesIO()
    img.save(output_buffer, format='JPEG')
    byte_data = output_buffer.getvalue()
    base64_str = base64.b64encode(byte_data)
    return base64_str


def get_baidu_access_token(ak, sk):
    host = f'https://aip.baidubce.com/oauth/2.0/token?grant_type=client_credentials&client_id={ak}&client_secret={sk}'
    access_token = requests.get(host).json()['access_token']
    return access_token


def table_orc_by_baiduAI(img, access_token):
    request_url = f"https://aip.baidubce.com/rest/2.0/solution/v1/form_ocr/request?access_token={access_token}"
    headers = {'content-type': 'application/x-www-form-urlencoded'}

    base64_str = image_to_base64(img)
    params = {"image": base64_str, "is_sync": "true", "request_type": "excel"}
    json_data = requests.post(request_url, data=params, headers=headers).json()
    if not "result" in json_data:
        print("识别失败：", json_data)
    excel_url = json_data["result"]["result_data"]
    df = pd.read_excel(excel_url, header=None)
    return df


def batch_orc_table_img(img):
    ak = keyring.get_password("baidu_ai", "ak")
    sk = keyring.get_password("baidu_ai", "sk")
    access_token = get_baidu_access_token(ak, sk)
    result = []
    for img in img_split(img):
        df = table_orc_by_baiduAI(img, access_token)
        try:
            if result:
                last = int(result[-1].iloc[-1, 0])
                first = int(df.iloc[0, 0])
                diff = first-last
                if diff > 1:
                    print("缺少数据排名", list(range(last+1, first)))
                elif diff == 0:
                    print("多出数据排名", list(range(first, last+1)))
        except Exception as e:
            print(e)
        result.append(df)
    result = pd.concat(result)
    return

最后识别一下第二张图片并保存：

img = Image.open("table/b.png")
df = batch_orc_table_img(img)
df.to_excel("b.xlsx", index=False, header=False)

可以看到250条数据全部都识别出来了。

最终结果合并

下面我们再把两张图片的结果合并一下：

files = ["a.xlsx", "b.xlsx"]
df = pd.concat([pd.read_excel(file, header=None) for file in files])
df.to_excel("result.xlsx", index=False, header=False)

含标题共502条数据，数据条数都正确。

更多Excel合并操作示例：

各类Excel表格批量合并问题的实现思路与案例

至此我们就完成了表格图片到Excel表格转换。

注意：百度AI的表格文字识别每日只有50次免费使用机会，需要珍惜识别资源。

图形化界面开发

考虑以后使用方便，我们可以考虑开发一个简单的界面。

输入参数分别为百度AI的ak和sk，以及图片位置和Excel表保存位置，共四个参数，一个确定按钮。

最终设计界面如下：

界面程序代码：

"""
小小明的代码

"""
__author__ = '小小明'

import os

import PySimpleGUI as sg
import keyring

from table_orc import table_img_orc

sg.change_look_and_feel("LightBlue")
layout = [
    [sg.Text("ak和sk通过百度AI接口获取，不填取本机上次填写的值。", text_color='red')],
    [sg.Text("ak："), sg.In(key="ak"), ],
    [sg.Text("sk："), sg.In(key="sk"), ],
    [sg.Text("切割长度："), sg.In(size=(4, 1), key="height", default_text="860"),
     sg.Text("单元格长度："), sg.In(size=(4, 1), key="cell_height", default_text="30"), ],
    [sg.Text("表格图片位置：")],
    [sg.In(key="img_file"),
     sg.FileBrowse('游览', target='img_file', file_types=(("常用图片文件", "*.jpg;*.jpeg;*.bmp;*.png;*.gif"),))],
    [sg.Text("Excel保存位置（不填默认与图片相同文件名）：")],
    [sg.In(key="save_path"),
     sg.FileBrowse('游览', target='save_path', file_types=(("常用图片文件", "*.jpg;*.jpeg;*.bmp;*.png;*.gif"),))],
    [sg.Button('开始识别', enable_events=True, key="start", pad=(120, 0), font=('楷体', 20))],
    [sg.Text("进度：", key="-TOUT-"), sg.ProgressBar(1000, orientation='h', size=(38, 20), key='progressbar')],
]

window = sg.Window('表格图片识别工具By小小明', layout)
while True:
    event, values = window.read()
    # print(event, values)
    if event in (None,):
        break  # 相当于关闭界面
    elif event == "start":
        ak, sk, img_file, save_path = values["ak"], values["sk"], values["img_file"], values["save_path"]
        if not values["height"].isdigit() or not values["cell_height"].isdigit():
            sg.popup("长度填写的不是数字！请检查后重新填写", title="提示")
            continue
        if not img_file:
            sg.popup("请输入需要被处理的表格图片的路径！", title="提示")
            continue
        if not os.path.exists(img_file):
            sg.popup("填入的表格图片文件不存在，请重新选择！", title="提示")
            continue
        if not ak:
            ak = keyring.get_password("baidu_ai", "ak")
            window["ak"].update(value=ak)
        else:
            keyring.set_password("baidu_ai", "ak", ak)
        if not sk:
            sk = keyring.get_password("baidu_ai", "sk")
            window["sk"].update(value=sk)
        else:
            keyring.set_password("baidu_ai", "sk", sk)
        height, cell_height = int(values["height"]), int(values["cell_height"])
        if save_path == "":
            start_p = max(img_file.rfind("/"), img_file.rfind("\\"))
            end_p = img_file.rfind('.')
            save_path = img_file[:start_p]
            filename = img_file[start_p + 1:end_p]
            save_path = f"{save_path}/{filename}.xlsx"
            window["save_path"].update(value=save_path)

        for i in table_img_orc(ak, sk, height, cell_height, img_file, save_path):
            if isinstance(i, str):
                sg.popup(f"识别失败：{i}", title="提示")
                break
            window['progressbar'].UpdateBar(i)
        else:
            sg.popup("识别完成，结果已存储到目标目录中！", title="提示")

window.close()

​​table_orc.py​​的内容：

"""
小小明的代码

"""
__author__ = '小小明'
__time__ = '2021/7/7 16:59'

import base64
import math
from io import BytesIO

import pandas as pd
import requests
from PIL import Image


def img_split(img, height=860, cell_height=30):
    w, h = img.size
    r = math.ceil(h / height)
    img_splits = []
    for i in range(r):
        start = height * i
        end = height * (i + 1)
        if end > h:
            end = h
        if i != 0:
            start -= cell_height
        img_split = img.crop((0, start, w, end))
        img_splits.append(img_split)
    return img_splits


def image_to_base64(img):
    output_buffer = BytesIO()
    img.save(output_buffer, format='JPEG')
    byte_data = output_buffer.getvalue()
    base64_str = base64.b64encode(byte_data)
    return base64_str


def get_baidu_access_token(ak, sk):
    host = f'https://aip.baidubce.com/oauth/2.0/token?grant_type=client_credentials&client_id={ak}&client_secret={sk}'
    access_token = requests.get(host).json()['access_token']
    return access_token


def table_orc_by_baiduAI(img, access_token):
    request_url = f"https://aip.baidubce.com/rest/2.0/solution/v1/form_ocr/request?access_token={access_token}"
    headers = {'content-type': 'application/x-www-form-urlencoded'}

    base64_str = image_to_base64(img)
    params = {"image": base64_str, "is_sync": "true", "request_type": "excel"}
    json_data = requests.post(request_url, data=params, headers=headers).json()
    if not "result" in json_data:
        return json_data
    excel_url = json_data["result"]["result_data"]
    df = pd.read_excel(excel_url, header=None)
    return df


def table_img_orc(ak, sk, height, cell_height, img_file, save_path):
    access_token = get_baidu_access_token(ak, sk)
    result = []
    img_splits = img_split(Image.open(img_file), height, cell_height)
    for i, img in enumerate(img_splits, 1):
        df = table_orc_by_baiduAI(img, access_token)
        if isinstance(df, dict):
            yield str(df)
            break
        result.append(df)
        yield i * 1000 // len(img_splits)
    result = pd.concat(result)
    result.to_excel(save_path, index=False, header=False)

识别失败时会有如下提示：