328 lines
12 KiB
Python
328 lines
12 KiB
Python
#!/usr/bin/env python3
|
||
"""KOCR v4 — Excel 模板填充与合并"""
|
||
|
||
import os
|
||
from datetime import datetime
|
||
from collections import defaultdict
|
||
|
||
|
||
def fill_template(voucher_data, template_path, output_path, verbose=False):
|
||
"""填充金蝶 K3 导入模板
|
||
|
||
Args:
|
||
voucher_data: K3VoucherParser.parse() 的输出
|
||
template_path: 模板 .xls 文件路径
|
||
output_path: 输出 .xls 文件路径
|
||
verbose: 是否输出详细信息
|
||
"""
|
||
import xlrd
|
||
import xlwt
|
||
|
||
wb = xlrd.open_workbook(template_path, formatting_info=True)
|
||
schema_sheet = wb.sheet_by_index(0)
|
||
page_sheet = wb.sheet_by_index(1)
|
||
|
||
out_wb = xlwt.Workbook(encoding='utf-8')
|
||
|
||
# Sheet 1: t_Schema
|
||
out_ws = out_wb.add_sheet('t_Schema', cell_overwrite_ok=True)
|
||
for r in range(schema_sheet.nrows):
|
||
for c in range(schema_sheet.ncols):
|
||
val = schema_sheet.cell_value(r, c)
|
||
if isinstance(val, float) and val == int(val):
|
||
val = int(val)
|
||
out_ws.write(r, c, val)
|
||
|
||
# Sheet 2: Page1
|
||
out_page = out_wb.add_sheet('Page1', cell_overwrite_ok=True)
|
||
|
||
headers = []
|
||
for c in range(page_sheet.ncols):
|
||
val = page_sheet.cell_value(0, c)
|
||
out_page.write(0, c, val)
|
||
headers.append(val)
|
||
|
||
def col_idx(predicate, default=999):
|
||
for i, h in enumerate(headers):
|
||
if predicate in str(h):
|
||
return i
|
||
return default
|
||
|
||
# 列索引
|
||
col_date = col_idx('凭证日期')
|
||
col_year = col_idx('会计年度')
|
||
col_period = col_idx('会计期间')
|
||
col_vword = col_idx('凭证字')
|
||
col_vno = col_idx('凭证号')
|
||
col_code = col_idx('科目代码')
|
||
col_name = col_idx('科目名称')
|
||
col_currency = col_idx('币别')
|
||
col_cur_name = col_idx('币别名称')
|
||
col_debit = col_idx('借方')
|
||
col_credit = col_idx('贷方')
|
||
col_desc = col_idx('摘要')
|
||
col_preparer = col_idx('制单')
|
||
col_checker = col_idx('审核')
|
||
col_approve = col_idx('核准')
|
||
col_cashier = col_idx('出纳')
|
||
col_handler = col_idx('经办')
|
||
col_settle_type = col_idx('结算方式')
|
||
col_attachments = col_idx('附件数')
|
||
col_entry_id = col_idx('分录序号')
|
||
col_amount_for = col_idx('原币金额')
|
||
col_biz_date = col_idx('业务日期')
|
||
col_measure_unit = col_idx('数量单位')
|
||
col_unit_price = col_idx('单价')
|
||
col_exchange_rate = col_idx('汇率')
|
||
|
||
date = voucher_data['date'] or datetime.now()
|
||
|
||
row = 1
|
||
entry_id = 0
|
||
|
||
for ei, entry in enumerate(voucher_data['entries']):
|
||
debit = entry.get('debit', 0) or 0
|
||
credit = entry.get('credit', 0) or 0
|
||
amount = max(debit, credit)
|
||
|
||
out_page.write(row, col_date, f'{date.year}-{date.month}-{date.day}')
|
||
out_page.write(row, col_year, float(voucher_data['year'] or date.year))
|
||
out_page.write(row, col_period, float(voucher_data['period'] or date.month))
|
||
out_page.write(row, col_vword, voucher_data['voucher_word'] or '记')
|
||
out_page.write(row, col_vno, float(voucher_data['voucher_no'] or 0))
|
||
out_page.write(row, col_biz_date, f'{date.year}-{date.month}-{date.day}')
|
||
|
||
out_page.write(row, col_code, entry['account_code'])
|
||
out_page.write(row, col_name, entry['account_name'])
|
||
out_page.write(row, col_currency, 'RMB')
|
||
out_page.write(row, col_cur_name, '人民币')
|
||
|
||
out_page.write(row, col_amount_for, float(amount))
|
||
out_page.write(row, col_debit, float(debit))
|
||
out_page.write(row, col_credit, float(credit))
|
||
|
||
out_page.write(row, col_desc, entry.get('description', ''))
|
||
out_page.write(row, col_preparer, voucher_data['preparer'] or 'Manager')
|
||
out_page.write(row, col_checker, 'NONE')
|
||
out_page.write(row, col_approve, 'NONE')
|
||
out_page.write(row, col_cashier, 'NONE')
|
||
out_page.write(row, col_handler, voucher_data['handler'] or '')
|
||
out_page.write(row, col_settle_type, '*')
|
||
out_page.write(row, col_attachments, float(0))
|
||
out_page.write(row, col_measure_unit, '*')
|
||
out_page.write(row, col_unit_price, float(0))
|
||
out_page.write(row, col_entry_id, float(entry_id))
|
||
out_page.write(row, col_exchange_rate, float(1))
|
||
|
||
row += 1
|
||
entry_id += 1
|
||
|
||
os.makedirs(os.path.dirname(output_path), exist_ok=True)
|
||
out_wb.save(output_path)
|
||
|
||
if verbose:
|
||
total_entries = len(voucher_data['entries'])
|
||
print(f"\n✅ Excel 输出: {output_path}")
|
||
print(f" 凭证: {voucher_data['voucher_word']}-{voucher_data['voucher_no']}")
|
||
print(f" 日期: {date.strftime('%Y-%m-%d')}")
|
||
print(f" 分录: {total_entries} 行")
|
||
print(f" 合计: 借 {sum(e.get('debit',0) or 0 for e in voucher_data['entries']):.2f} 贷 {sum(e.get('credit',0) or 0 for e in voucher_data['entries']):.2f}")
|
||
|
||
|
||
def merge_output(results, template_path, output_dir, review=False):
|
||
"""合并多张凭证为一个排序好的 Excel
|
||
|
||
Args:
|
||
results: [(fname, voucher_data, output_path, quality), ...]
|
||
template_path: 模板路径
|
||
output_dir: 输出目录
|
||
review: 是否生成校验报告
|
||
|
||
Returns:
|
||
merge_path: 合并后的 Excel 路径
|
||
"""
|
||
import xlrd
|
||
import xlwt
|
||
|
||
# 按 (日期, 凭证号) 分组
|
||
groups = defaultdict(list)
|
||
for item in results:
|
||
v = item[1]
|
||
key = (
|
||
v.get('date').strftime('%Y-%m-%d') if v.get('date') else '0000',
|
||
v.get('voucher_no') or 0
|
||
)
|
||
groups[key].append(item)
|
||
|
||
sorted_keys = sorted(groups.keys())
|
||
src_wb = xlrd.open_workbook(template_path, formatting_info=True)
|
||
schema_sheet = src_wb.sheet_by_index(0)
|
||
|
||
all_entries = []
|
||
merged_report = []
|
||
|
||
for key in sorted_keys:
|
||
group = groups[key]
|
||
|
||
if len(group) == 1:
|
||
# 单页
|
||
fname, voucher, out_path, quality = group[0]
|
||
wb = xlrd.open_workbook(out_path)
|
||
ws = wb.sheet_by_name('Page1')
|
||
for r in range(1, ws.nrows):
|
||
row_data = [ws.cell_value(r, c) for c in range(ws.ncols)]
|
||
all_entries.append(row_data)
|
||
merged_report.append(([fname], fname, voucher, None))
|
||
else:
|
||
# 多页合并
|
||
page_names = [g[0] for g in group]
|
||
voucher = group[0][1]
|
||
|
||
total_dr = 0
|
||
total_cr = 0
|
||
pages_entries = []
|
||
|
||
for fname, v, out_path, quality in group:
|
||
wb = xlrd.open_workbook(out_path)
|
||
ws = wb.sheet_by_name('Page1')
|
||
page_rows = []
|
||
for r in range(1, ws.nrows):
|
||
row_data = [ws.cell_value(r, c) for c in range(ws.ncols)]
|
||
page_rows.append(row_data)
|
||
dr_val = float(row_data[10] or 0) if isinstance(row_data[10], (int, float)) else 0.0
|
||
cr_val = float(row_data[11] or 0) if isinstance(row_data[11], (int, float)) else 0.0
|
||
total_dr += dr_val
|
||
total_cr += cr_val
|
||
pages_entries.append((fname, page_rows))
|
||
|
||
merged_ok = abs(total_dr - total_cr) < 0.01
|
||
|
||
for fname, page_rows in pages_entries:
|
||
for row_data in page_rows:
|
||
all_entries.append(row_data)
|
||
|
||
pages_str = ' + '.join(page_names)
|
||
merged_report.append((page_names, pages_str, voucher, merged_ok))
|
||
|
||
# 写入合并文件
|
||
|
||
# ---- 科目名称归一化：同一科目代码取最高频名称 ---- #
|
||
from collections import Counter
|
||
name_counter = Counter()
|
||
for row_data in all_entries:
|
||
code = str(row_data[5]) if len(row_data) > 5 else ''
|
||
name = str(row_data[6]) if len(row_data) > 6 else ''
|
||
if name and len(name) >= 2:
|
||
name_counter[(code, name)] += 1
|
||
code_best = {}
|
||
for (code, name), cnt in name_counter.items():
|
||
if code not in code_best or cnt > code_best[code][1]:
|
||
code_best[code] = (name, cnt)
|
||
fixed_count = 0
|
||
for row_data in all_entries:
|
||
code = str(row_data[5]) if len(row_data) > 5 else ''
|
||
if code in code_best:
|
||
best_name = code_best[code][0]
|
||
if len(row_data) > 6 and str(row_data[6]) != best_name:
|
||
row_data[6] = best_name
|
||
fixed_count += 1
|
||
if fixed_count > 0:
|
||
print(f' 🔄 科目名称归一化：修正 {fixed_count} 处不一致')
|
||
|
||
# ---- 标准映射覆盖：用户提供的正确对照表 ---- #
|
||
STANDARD_NAMES = {
|
||
'1002.01.01': '淇县支行1710020809200182975',
|
||
'1002.02.01': '漓江支行4105016228400000750',
|
||
'1012.01': 'POS刷卡未到账',
|
||
'2203.01': '物业管理费',
|
||
'2241.01.01': '装修押金',
|
||
'6051.02': '办证工本收入',
|
||
'6051.08': '转售水电费收入',
|
||
'6401.02.02': '办公费',
|
||
'6401.51.09': '日常维修费',
|
||
}
|
||
override_count = 0
|
||
for row_data in all_entries:
|
||
code = str(row_data[5]) if len(row_data) > 5 else ''
|
||
if code in STANDARD_NAMES and len(row_data) > 6:
|
||
correct_name = STANDARD_NAMES[code]
|
||
if str(row_data[6]) != correct_name:
|
||
row_data[6] = correct_name
|
||
override_count += 1
|
||
if override_count > 0:
|
||
print(f' 📋 标准映射覆盖：修正 {override_count} 处')
|
||
|
||
out_wb = xlwt.Workbook(encoding='utf-8')
|
||
out_ws = out_wb.add_sheet('t_Schema', cell_overwrite_ok=True)
|
||
for r in range(schema_sheet.nrows):
|
||
for c in range(schema_sheet.ncols):
|
||
val = schema_sheet.cell_value(r, c)
|
||
if isinstance(val, float) and val == int(val):
|
||
val = int(val)
|
||
out_ws.write(r, c, val)
|
||
|
||
out_page = out_wb.add_sheet('Page1', cell_overwrite_ok=True)
|
||
for c in range(len(all_entries[0]) if all_entries else 0):
|
||
out_page.write(0, c, src_wb.sheet_by_index(1).cell_value(0, c))
|
||
for r, row_data in enumerate(all_entries):
|
||
for c, val in enumerate(row_data):
|
||
out_page.write(r + 1, c, val)
|
||
|
||
merge_path = os.path.join(output_dir, '合并凭证_按顺序.xls')
|
||
out_wb.save(merge_path)
|
||
|
||
return merge_path
|
||
|
||
|
||
def merge_voucher_data(results):
|
||
"""按(日期,凭证号)合并凭证数据，返回合并后的凭证列表
|
||
|
||
多页凭证（同一凭证号的多张照片）合并 entries，单页不变。
|
||
合并后的数据用于统一的借贷平衡 + 大小写金额校验。
|
||
|
||
Args:
|
||
results: [(fname, voucher_data, out_path, quality), ...]
|
||
|
||
Returns:
|
||
[voucher_dict, ...] 每个 voucher_dict 包含合并后的 entries
|
||
"""
|
||
from collections import defaultdict
|
||
|
||
groups = defaultdict(list)
|
||
for item in results:
|
||
v = item[1]
|
||
key = (
|
||
v.get('date').strftime('%Y-%m-%d') if v.get('date') else '0000',
|
||
v.get('voucher_no') or 0
|
||
)
|
||
groups[key].append(item)
|
||
|
||
merged_list = []
|
||
for key in sorted(groups.keys()):
|
||
group = groups[key]
|
||
page_names = [g[0] for g in group]
|
||
base = group[0][1] # 第一页的元数据（日期、凭证号等）
|
||
|
||
# 合并所有页的 entries
|
||
all_entries = []
|
||
for fname, v, out_path, quality in group:
|
||
all_entries.extend(v.get('entries', []))
|
||
|
||
# 重新编号
|
||
for i, e in enumerate(all_entries):
|
||
e['id'] = i + 1
|
||
|
||
merged_list.append({
|
||
'date': base.get('date'),
|
||
'voucher_word': base.get('voucher_word', '记'),
|
||
'voucher_no': base.get('voucher_no'),
|
||
'entries': all_entries,
|
||
'total_str': base.get('total_str', ''),
|
||
'total_amount': base.get('total_amount', 0),
|
||
'pages': page_names,
|
||
'is_multi_page': len(group) > 1,
|
||
'page_count': len(group),
|
||
})
|
||
|
||
return merged_list
|