🏦 每月几百张回单手工对账？用Python一键搞定！

场景痛点：建行银企联云接口返回的电子回单金额全是 0，财务同事每月要手动打开几百个 PDF 附件逐一核对金额，再手工录入对账，效率极低，还容易出错。

一、问题背景

在 NCC（用友）财务系统中，银行电子回单通过银企联云接口同步数据，但建行返回的回单报文中，amount（金额）字段值为 0.0。

真实金额藏在 PDF 附件里，每次对账都需要：

1. 手动下载 PDF 附件

2. 打开查看金额

3. 手工填写对账数据

每月涉及数百条数据，这是一项重复性极高、价值极低的体力劳动。

💡 本文介绍一套完整的自动化解决方案，通过 SQL + Shell + Python 三步走，实现附件金额的自动识别与数据库批量更新。

二、整体方案架构

┌─────────────────────────────────────────────────┐
│              自动化处理流程                                                                                │
│                                                                                                                     │
│  Step 1             Step 2        Step 3                                                               │
│  SQL查询   →  批量下载   →  Python解析                                                     │
│  回单主键       PDF附件       金额+生成SQL                                                    │
│  及附件路径                                                                                                  │
│                                     ↓                                                                              │
│             Step 4: 执行SQL更新数据库                                                              │
└─────────────────────────────────────────────────┘

三、Step 1：SQL 查询回单主键及附件路径

首先从数据库中查询出所有金额为0、未匹配的建行回单，同时关联出对应的附件存储路径。

-- 电子回单及附件目录查询
SELECT
-- 回单业务信息
    a.billnoAS"单据号",
    a.pk_bankreceiptAS"银行电子回单主键",
    a.vbillnoAS"回单编号",
    a.amountAS"金额",
    a.transdateAS"交易日期",
CASE a.direction
WHEN'0'THEN'收款'
WHEN'1'THEN'付款'
ELSENULL
ENDAS"收付方向",
    a.curraccnoAS"本方账号",
    a.curraccnameAS"本方账户名",
    b.nameAS"银行类别名称",
CASE a.matchstatus
WHEN'0'THEN'Unmatch'
WHEN'1'THEN'ToBeConfirmed'
WHEN'-1'THEN'Nomatch'
WHEN'2'THEN'Matched'
ELSENULL
ENDAS"匹配状态",

-- 文件元数据信息
    f.pk_docAS"文件主键",
    f.filepathAS"附件真实路径",
    f.filedescAS"文件描述/名称",
    f.filelengthAS"文件大小",
    f.filetypeAS"文件类型",

-- 底层文件存储信息
    h.guidAS"底层文件头GUID",
    h.pathAS"底层文件关联PATH",
    bd.*

FROM sp_bankreceipt a
INNERJOIN bd_banktype b
ON a.pk_banktype= b.pk_banktype
INNERJOIN sm_pub_filesystem f
ON(f.filepath= a.pk_bankreceipt
OR f.filepathLIKE a.pk_bankreceipt||'/%')
LEFTJOIN bap_fs_header h
ON h.path= f.pk_doc
LEFTJOIN bap_fs_body bd
ON bd.headid= h.guid
WHERE
    a.transdate>='2026-01-01'
AND b.code='04'-- 建行
AND a.matchstatus='0'-- 未匹配
AND a.amount=0-- 金额为0
ORDERBY
    a.transdateDESC,
    f.tsDESC;

关键关联逻辑说明：

四、Step 2：批量下载 PDF 附件到本地

将 SQL 查询出来的 filepath（STOREPATH）保存为 found_paths.txt，然后使用 Shell 脚本批量打包下载。

方案一：使用 --transform 参数（推荐）

# 将所有PDF打包，去除深层目录，所有文件平铺在压缩包最外层
tar -czvf target_pdfs.tar.gz \
--transform='s#.*/##' \
-T found_paths.txt

--transform='s#.*/##' 的作用：删除最后一个 / 前的所有路径，只保留文件名，避免解压后产生深层目录结构。

方案二：兼容旧版 tar（临时文件夹法）

# 1. 创建临时文件夹
mkdir my_temp_pdfs

# 2. 将文件平铺复制到临时文件夹
cat found_paths.txt | xargs -I {} cp {} my_temp_pdfs/

# 3. 进入文件夹并打包
cd my_temp_pdfs
tar -czvf ../target_pdfs.tar.gz *

# 4. 清理临时文件夹
cd ..
rm-rf my_temp_pdfs

五、Step 3：Python 批量解析 PDF 金额并生成更新 SQL

5.1 安装依赖库

pip install pdfplumber pandas openpyxl cffi cryptography

为什么需要 cffi 和 cryptography？pdfplumber 底层依赖 cryptography 处理 PDF 的安全限制，而 cryptography 又依赖 C 语言接口模块 cffi，缺少任一组件都会导致解析失败。

⚠️ 扫描件 PDF 兜底方案：如果遇到图片型扫描 PDF，pdfplumber 无法提取文字（返回"未找到金额"），可追加 OCR 兜底处理：

pip install pdf2image pytesseract
# 还需安装系统依赖：apt install tesseract-ocr tesseract-ocr-chi-sim poppler-utils

frompdf2imageimportconvert_from_path
importpytesseract

defocr_fallback(pdf_path: str) ->str:
"""对pdfplumber无法解析的扫描件PDF进行OCR识别"""
images = convert_from_path(pdf_path, dpi=200)
forimginimages:
text = pytesseract.image_to_string(img, lang='chi_sim+eng')
forpatterninPATTERNS:
match = pattern.search(text)
ifmatch:
returnmatch.group(1)
return"OCR未找到金额"

在 get_amount_from_pdf 末尾返回 "未找到金额" 前，调用 ocr_fallback(pdf_path) 即可实现两级识别，覆盖率可从约 80% 提升至 95%+。

5.2 完整 Python 脚本

importos
importre
importpdfplumber
importpandasaspd

# ================= 配置区域（按实际情况修改）=================
EXCEL_PATH  = r"D:\log\银行电子回单查询20260417.xlsx"# SQL导出的Excel
PDF_FOLDER  = r"D:\log\target_pdfs"# 解压后的PDF目录
OUTPUT_PATH = r"D:\log\银行电子回单查询_已更新.xlsx"# 输出文件路径

# Excel中的列名（需与导出文件完全一致）
COL_PK      = "银行电子回单主键"
COL_PATH    = "STOREPATH"
COL_AMOUNT  = "金额"
COL_SQL     = "数据库更新"
# =============================================================


defget_amount_from_pdf(pdf_name: str) ->str:
"""从 PDF 中提取金额汇总文本（多模式匹配，提升覆盖率）"""
pdf_path = os.path.join(PDF_FOLDER, pdf_name)
ifnotos.path.exists(pdf_path):
return"文件不存在"
# 多模式降级匹配：建行不同版本PDF关键词可能不同
PATTERNS = [
re.compile(r'金额汇总\s*[:：]\s*([\d\.,]+)'),
re.compile(r'合计金额\s*[:：]?\s*([\d\.,]+)'),
re.compile(r'实际到账金额\s*[:：]?\s*([\d\.,]+)'),
re.compile(r'¥\s*([\d\.,]+)'),
    ]
try:
withpdfplumber.open(pdf_path) aspdf:
forpageinpdf.pages:
text = page.extract_text()
iftext:
forpatterninPATTERNS:
match = pattern.search(text)
ifmatch:
returnmatch.group(1)
exceptException:
return"读取出错"
return"未找到金额"


defclean_amount_for_sql(amount_str: str):
"""将金额字符串转为纯数字格式，去掉千位分隔符"""
ifnotamount_str:
returnNone
# 包含空格或字母（如"文件不存在"）则跳过
if" "inamount_strorany(c.isalpha() forcinamount_str):
returnNone
returnamount_str.replace(',', '')


defmain():
ifnotos.path.exists(EXCEL_PATH):
print(f"❌ 错误：找不到文件 {EXCEL_PATH}")
return

print("📖 正在读取 Excel（文本模式）...")
df = pd.read_excel(EXCEL_PATH, dtype=str)

# 初始化列
df[COL_AMOUNT] = ""
df[COL_SQL]    = ""

total_rows = len(df)
print(f"🚀 开始处理，共 {total_rows} 条数据...")
print("-"*60)

forindex, rowindf.iterrows():
# 1. 提取 PDF 金额
store_path  = str(row[COL_PATH])
file_name   = store_path.split('/')[-1] if'/'instore_pathelse"无效"
amount_raw  = get_amount_from_pdf(file_name)
df.at[index, COL_AMOUNT] = amount_raw

# 2. 生成 SQL 语句
pk_value = row[COL_PK]
sql_val  = clean_amount_for_sql(amount_raw)

ifsql_valandpk_value:
sql_str = (
f"update sp_bankreceipt "
f"set amount = {sql_val} "
f"where pk_bankreceipt = '{pk_value}';"
            )
df.at[index, COL_SQL] = sql_str
else:
df.at[index, COL_SQL] = "-- 无法生成(无金额或主键)"

# 实时打印进度
print(f"[{index + 1}/{total_rows}] 主键: {pk_value} | 金额: {amount_raw}")

print("-"*60)
print("💾 正在保存文件...")
try:
df.to_excel(OUTPUT_PATH, index=False)
print(f"✅ 执行成功！\n📁 输出路径: {OUTPUT_PATH}")
exceptExceptionase:
print(f"❌ 保存失败: {e}")


if__name__ == "__main__":
main()

脚本执行效果示例：

[964/969] 主键: 1770a691Z2Db6_x13CE36BABB9326 → 识别金额: 34,769.76
[965/969] 主键: 1770a69121V97_15A8ECF049MA4SS → 识别金额: 34,769.76
[966/969] 主键: 1770a69121V97_BCO8SD1Y7D826F5 → 识别金额: 4,788.00
[967/969] 主键: 1770a6903ZG413_DIRAG6C24AZCBC4 → 识别金额: 3,320.00
[968/969] 主键: 1770a690S36Z26_70087A7BF6AFCV08 → 识别金额: 2,633.00
[969/969] 主键: 1770a6934B17_BF26D4DA8A1C7F9A5 → 识别金额: 27.08

六、Step 4：执行 SQL 批量更新数据库

将 Excel 中生成的 数据库更新 列 SQL 语句复制出来，在数据库中执行。强烈建议用事务包裹，发生异常可随时回滚，避免部分更新导致数据不一致：

-- ⚠️ 生产执行前务必先在测试环境验证，并备份相关记录
BEGIN;
update sp_bankreceipt set amount =15560576.66where pk_bankreceipt ='1001K310000000A9K6RQ';
update sp_bankreceipt set amount =15560576.66where pk_bankreceipt ='1001K310000000A9K6RP';
update sp_bankreceipt set amount =3799190.31where pk_bankreceipt ='1001K310000000A9K6RN';
update sp_bankreceipt set amount =3799190.31where pk_bankreceipt ='1001K310000000A9K6RO';
update sp_bankreceipt set amount =6318253.25where pk_bankreceipt ='1001K310000000A9K6RE';
update sp_bankreceipt set amount =2930565.48where pk_bankreceipt ='1001K310000000A9K6RD';
update sp_bankreceipt set amount =2635840.22where pk_bankreceipt ='1001K310000000A9K6R6';
-- ... 更多语句
COMMIT;
-- 若发现异常，执行 ROLLBACK; 全部回滚

更新完成后，系统中的回单金额即可正确显示。

七、效果对比

八、总结

本方案通过 SQL + Shell + Python 三步协作，将原本需要财务人员每月耗费数小时的手工对账工作，压缩至自动化运行的几分钟内完成。

核心思路很简单：银行接口的锅，我们用代码来补。只要数据在附件里，就有办法把它读出来。

如果你的系统也有类似的"接口返回0、数据在附件里"的问题，欢迎参考这套方案，举一反三。

本文涉及的系统环境：用友 NCC、建行银企联云、Linux 服务器、Python 3.x

如有问题或改进建议，欢迎留言交流 👇