373 lines
10 KiB
Markdown
373 lines
10 KiB
Markdown
# 财务Excel数据处理系统 - 需求文档
|
||
|
||
## 项目概述
|
||
|
||
本系统用于自动化处理财务Excel数据,提取收款信息并生成标准会计分录表,支持数据验证和异常标记功能。
|
||
|
||
---
|
||
|
||
## 功能模块
|
||
|
||
### 模块一: Excel数据提取 (`process_excel.py`)
|
||
|
||
#### 1.1 数据源
|
||
- **文件路径**: `data/data.xlsx`
|
||
- **工作表**: Sheet1
|
||
- **数据起始行**: 第2行(第1行为表头)
|
||
|
||
#### 1.2 数据提取规则
|
||
|
||
##### 1.2.1 主记录字段
|
||
- **ReceivedAmount** (实收金额)
|
||
- 来源: F列
|
||
- 说明: 支持合并单元格(一行或多行)
|
||
- 处理: 非合并单元格按单行处理
|
||
|
||
- **HandlingFee** (手续费)
|
||
- 来源: G列
|
||
- 处理: 空值自动记为0
|
||
|
||
##### 1.2.2 订单明细字段
|
||
- **Order** (订单列表)
|
||
- 范围: F列合并单元格包含的所有行
|
||
- 包含字段:
|
||
- **OrderNum** (订单号): H列 - 可以为空(null)
|
||
- **Amount** (金额): I列 - 必需(空值的订单会被跳过)
|
||
- **AccountName** (账户名称): O列(支持公式,读取计算结果)
|
||
|
||
##### 1.2.3 数据验证
|
||
- **checkRes** (验证结果): Boolean
|
||
- 计算规则: `ReceivedAmount + HandlingFee ≈ Sum(Order[].Amount)`
|
||
- 容差: 0.01
|
||
- true: 金额匹配
|
||
- false: 金额不匹配,需要人工核对
|
||
|
||
#### 1.3 输出格式
|
||
|
||
**文件**: `res.json`
|
||
|
||
**结构**:
|
||
```json
|
||
[
|
||
{
|
||
"ReceivedAmount": 12125,
|
||
"HandlingFee": 25,
|
||
"Order": [
|
||
{
|
||
"OrderNum": "XLRQD300T25",
|
||
"Amount": 550,
|
||
"AccountName": "24台湾长荣航运"
|
||
},
|
||
{
|
||
"OrderNum": "XLRQD044T25",
|
||
"Amount": 11600,
|
||
"AccountName": "24台湾长荣航运"
|
||
}
|
||
],
|
||
"checkRes": true
|
||
}
|
||
]
|
||
```
|
||
|
||
#### 1.4 特殊处理
|
||
- 允许订单号(OrderNum)为空,但金额(Amount)必需
|
||
- 跳过金额为空的订单,不生成分录
|
||
- 处理F列合并和非合并单元格混合的情况
|
||
- 读取O列公式计算后的值(data_only=True)
|
||
|
||
---
|
||
|
||
### 模块二: 会计分录生成 (`generate_accounting_entries.py`)
|
||
|
||
#### 2.1 输入输出
|
||
- **输入**: `res.json`
|
||
- **输出**: `AccountingEntries.xlsx`
|
||
|
||
#### 2.2 会计分录规则
|
||
|
||
##### 2.2.1 基本分录
|
||
|
||
每笔 ReceivedAmount 记录生成以下分录:
|
||
|
||
**1) 到账金额 - 借方记录** (每笔记录1条)
|
||
- 科目代码: `1002.02`
|
||
- 科目名称: `银行存款 - 中行USD`
|
||
- 摘要: `美金收款-{OrderNum}` (如果OrderNum为空,则为 `美金收款`)
|
||
- 核算项目: Order[0].AccountName
|
||
- 币别: 美元
|
||
- 汇率: 7.1072 (可配置)
|
||
- 原币金额: ReceivedAmount
|
||
- 金额: ReceivedAmount × 汇率
|
||
|
||
**2) 手续费 - 借方记录** (仅当 HandlingFee > 0)
|
||
- 科目代码: `5603.03`
|
||
- 科目名称: `财务费用-手续费`
|
||
- 摘要: `美金收款-{OrderNum}` (如果OrderNum为空,则为 `美金收款`)
|
||
- 核算项目: 空
|
||
- 币别: 人民币
|
||
- 汇率: 空
|
||
- 原币金额: 空
|
||
- 金额: HandlingFee × 汇率
|
||
|
||
**3) 订单明细 - 贷方记录** (每个Order记录1条)
|
||
- 科目代码: `1122`
|
||
- 科目名称: `应收账款`
|
||
- 应收账款: Order.Amount (显示在"应收账款"列)
|
||
- 摘要: `美金收款-{OrderNum}` (如果OrderNum为空,则为 `美金收款`)
|
||
- 核算项目: Order.AccountName
|
||
- 币别: 美元
|
||
- 汇率: 7.1072 (可配置)
|
||
- 原币金额: Order.Amount
|
||
- 金额: Order.Amount × 汇率
|
||
|
||
##### 2.2.2 特殊规则
|
||
|
||
**摘要生成**
|
||
- 当OrderNum有值时: 摘要为 `美金收款-{OrderNum}`
|
||
- 当OrderNum为空时: 摘要固定为 `美金收款`
|
||
|
||
**空值处理**
|
||
- Order.Amount为null的订单跳过,不生成分录
|
||
- OrderNum可以为空(null),允许记录
|
||
|
||
**金额验证**
|
||
- checkRes为false的记录,所有相关分录行标记为粉红色背景(#FAD1D4)
|
||
|
||
#### 2.3 Excel格式设置
|
||
|
||
##### 2.3.1 表头
|
||
- 字段: 到账金额, 手续费, 订单号, 应收账款, 金蝶名称, 摘要, 借/贷, 科目代码(*), 科目名称(*), 核算项目, 币别, 汇率, 原币金额, 金额
|
||
- 样式: 粗体, 蓝色背景(#CCE5FF), 居中对齐
|
||
|
||
##### 2.3.2 单元格合并
|
||
- **到账金额列** (A列): 同一ReceivedAmount的所有分录行合并
|
||
- **手续费列** (B列): 同一ReceivedAmount的所有分录行合并
|
||
- 对齐方式: 垂直居中,水平居中
|
||
|
||
##### 2.3.3 异常标记
|
||
- 条件: 原始记录的 checkRes = false
|
||
- 处理: 该记录产生的所有分录行设置背景色
|
||
- 颜色: #FAD1D4 (粉红色)
|
||
- 目的: 突出显示金额不匹配的记录,便于人工核对
|
||
|
||
##### 2.3.4 列宽设置
|
||
```
|
||
A(到账金额): 12, B(手续费): 10, C(订单号): 18,
|
||
D(应收账款): 12, E(金蝶名称): 25, F(摘要): 25,
|
||
G(借/贷): 8, H(科目代码): 15, I(科目名称): 25,
|
||
J(核算项目): 25, K(币别): 10, L(汇率): 10,
|
||
M(原币金额): 12, N(金额): 15
|
||
```
|
||
|
||
---
|
||
|
||
## 配置参数
|
||
|
||
### 汇率配置
|
||
- **变量名**: `EXCHANGE_RATE`
|
||
- **位置**: `generate_accounting_entries.py` 第12行
|
||
- **默认值**: 7.1072
|
||
- **修改方式**: 直接编辑变量值
|
||
|
||
```python
|
||
# 固定汇率
|
||
EXCHANGE_RATE = 7.1072 # 修改此值
|
||
```
|
||
|
||
---
|
||
|
||
## 数据流程
|
||
|
||
```
|
||
data/data.xlsx (原始数据)
|
||
↓
|
||
[process_excel.py 提取]
|
||
↓
|
||
res.json (中间数据)
|
||
↓
|
||
[generate_accounting_entries.py 生成]
|
||
↓
|
||
AccountingEntries.xlsx (会计分录表)
|
||
```
|
||
|
||
---
|
||
|
||
## 技术要求
|
||
|
||
### 开发语言
|
||
- Python 3.x
|
||
|
||
### 依赖库
|
||
- openpyxl: Excel文件读写
|
||
|
||
### 安装命令
|
||
```bash
|
||
pip install openpyxl --break-system-packages
|
||
```
|
||
|
||
---
|
||
|
||
## 数据示例
|
||
|
||
### 输入示例 (data.xlsx)
|
||
|
||
| 到账金额 | 手续费 | 订单号 | 解款金额 | 金蝶名称 |
|
||
|---------|--------|---------|---------|---------|
|
||
| 12125 | 25 | XLRQD300T25 | 550 | 24台湾长荣航运 |
|
||
| (合并) | (合并) | XLRQD044T25 | 11600 | 24台湾长荣航运 |
|
||
|
||
### 中间数据 (res.json)
|
||
```json
|
||
{
|
||
"ReceivedAmount": 12125,
|
||
"HandlingFee": 25,
|
||
"Order": [
|
||
{"OrderNum": "XLRQD300T25", "Amount": 550, "AccountName": "24台湾长荣航运"},
|
||
{"OrderNum": "XLRQD044T25", "Amount": 11600, "AccountName": "24台湾长荣航运"}
|
||
],
|
||
"checkRes": true
|
||
}
|
||
```
|
||
|
||
### 输出示例 (AccountingEntries.xlsx)
|
||
|
||
| 到账金额 | 手续费 | 订单号 | 应收账款 | 金蝶名称 | 借/贷 | 科目代码 | 科目名称 | 币别 | 原币金额 | 金额 |
|
||
|---------|--------|--------|---------|---------|-------|----------|----------|------|----------|------|
|
||
| 12125 | 25 | XLRQD300T25 | | 24台湾长荣航运 | 借 | 1002.02 | 银行存款 - 中行USD | 美元 | 12125 | 86,174.80 |
|
||
| (合并) | (合并) | XLRQD300T25 | | | 借 | 5603.03 | 财务费用-手续费 | 人民币 | | 177.68 |
|
||
| (合并) | (合并) | XLRQD300T25 | 550 | 24台湾长荣航运 | 贷 | 1122 | 应收账款 | 美元 | 550 | 3,908.96 |
|
||
| (合并) | (合并) | XLRQD044T25 | 11600 | 24台湾长荣航运 | 贷 | 1122 | 应收账款 | 美元 | 11600 | 82,443.52 |
|
||
|
||
---
|
||
|
||
## 验证规则
|
||
|
||
### checkRes计算
|
||
```python
|
||
received_plus_fee = ReceivedAmount + HandlingFee
|
||
order_amount_sum = Sum(Order[].Amount where Amount is not null)
|
||
checkRes = abs(received_plus_fee - order_amount_sum) < 0.01
|
||
```
|
||
|
||
### 异常情况
|
||
- **checkRes = false**: 粉红色背景标记
|
||
- **Order.Amount = null**: 跳过该订单,不生成分录
|
||
- **HandlingFee = null**: 自动记为0
|
||
|
||
---
|
||
|
||
## 测试用例
|
||
|
||
### 测试用例1: 正常单订单
|
||
- 输入: ReceivedAmount=695, HandlingFee=0, Order[0].Amount=695
|
||
- 预期: 2行分录(到账借+订单贷), checkRes=true
|
||
- 应收账款列: 贷方记录显示695
|
||
|
||
### 测试用例2: 正常多订单
|
||
- 输入: ReceivedAmount=12125, HandlingFee=25, Order=[550, 11600]
|
||
- 预期: 4行分录(到账借+手续费借+2个订单贷), checkRes=true
|
||
- 应收账款列: 两条贷方记录分别显示550和11600
|
||
|
||
### 测试用例3: 金额不匹配
|
||
- 输入: ReceivedAmount=17270, HandlingFee=0, Order=[5676, 11450]
|
||
- 预期: checkRes=false, 所有分录行粉红色背景(#FAD1D4)
|
||
|
||
### 测试用例4: 订单金额为空
|
||
- 输入: ReceivedAmount=240, HandlingFee=25, Order[0].Amount=null
|
||
- 预期: 跳过订单明细分录,不报错,只生成到账借方和手续费借方
|
||
|
||
---
|
||
|
||
## 文件清单
|
||
|
||
| 文件名 | 类型 | 说明 |
|
||
|--------|------|------|
|
||
| data/data.xlsx | 输入 | 原始财务数据 |
|
||
| process_excel.py | 程序 | 数据提取脚本 |
|
||
| res.json | 中间 | 提取的JSON数据 |
|
||
| generate_accounting_entries.py | 程序 | 会计分录生成脚本 |
|
||
| AccountingEntries.xlsx | 输出 | 会计分录表 |
|
||
| analyze_excel.py | 工具 | Excel结构分析工具 |
|
||
| task.md | 文档 | 需求文档(本文件) |
|
||
| User.md | 文档 | 用户使用说明 |
|
||
|
||
---
|
||
|
||
## 性能要求
|
||
|
||
- 支持处理300+行Excel数据
|
||
- 生成500+行会计分录
|
||
- 处理时间 < 10秒
|
||
- 实测: 174条记录生成566条会计分录
|
||
|
||
---
|
||
|
||
## 错误处理
|
||
|
||
### Excel读取错误
|
||
- 文件不存在: 提示并退出
|
||
- Sheet1不存在: 提示并退出
|
||
- 列映射错误: 记录日志,跳过该行
|
||
|
||
### 数据验证错误
|
||
- 空值: 自动处理(HandlingFee=0, 跳过Amount=null)
|
||
- 格式错误: 记录日志,继续处理
|
||
- checkRes=false: 标记但继续处理
|
||
|
||
---
|
||
|
||
## 扩展需求
|
||
|
||
### 未来可能增加的功能
|
||
- [ ] 支持多工作表批量处理
|
||
- [ ] 汇率从配置文件读取
|
||
- [ ] 生成汇总统计报表
|
||
- [ ] 支持导出金蝶格式
|
||
- [ ] 添加数据修正功能
|
||
|
||
---
|
||
|
||
## 版本信息
|
||
|
||
- **版本**: v1.3
|
||
- **最后更新**: 2025-10-17
|
||
- **作者**: Claude Code
|
||
- **状态**: 已完成并测试
|
||
|
||
### 版本历史
|
||
|
||
**v1.3** (2025-10-17)
|
||
- 支持OrderNum为空的订单记录
|
||
- 修改数据提取逻辑: 允许OrderNum为空,只跳过Amount为空的订单
|
||
- 优化摘要生成: OrderNum为空时摘要显示为 `美金收款`
|
||
- 确保所有数据行都被正确记录和处理
|
||
|
||
**v1.2** (2025-10-17)
|
||
- 新增汇率文件支持: 可通过 `exchange_rate.txt` 设置汇率
|
||
- 智能汇率验证: 自动检测异常汇率并回退到默认值
|
||
- 改进错误处理: 汇率文件异常时提供详细提示信息
|
||
|
||
**v1.1** (2025-01-17)
|
||
- 优化会计分录规则: 移除"到账金额-贷方"记录
|
||
- 每个Order记录都生成对应的贷方分录(无例外)
|
||
- Order生成的贷方记录在"应收账款"列显示Amount金额
|
||
- 简化单订单处理逻辑,移除单订单优化规则
|
||
|
||
**v1.0** (2025-01-17)
|
||
- 初始版本
|
||
- Excel数据提取功能
|
||
- 会计分录生成功能
|
||
- 合并单元格支持
|
||
- 金额验证功能
|
||
- 异常背景色标记
|
||
|
||
---
|
||
|
||
## 备注
|
||
|
||
1. 所有金额计算保留2位小数
|
||
2. 汇率统一使用固定值,不从Excel读取
|
||
3. 合并单元格的值读取左上角单元格
|
||
4. O列公式使用data_only=True读取计算结果
|
||
5. 背景色仅用于标记,不影响数据内容
|