478 lines
14 KiB
Markdown
478 lines
14 KiB
Markdown
# 物流链接自动填充 - 完整逻辑说明
|
||
|
||
## 📋 功能概述
|
||
|
||
自动读取腾讯文档表格中的订单信息，根据**第三方单号**（thirdPartyOrderNo）在数据库中查询对应的物流链接，并自动填充到表格的"物流单号"列。
|
||
|
||
---
|
||
|
||
## 🔄 完整执行流程
|
||
|
||
### 第一步：读取表头（识别列位置）
|
||
|
||
```
|
||
输入参数：
|
||
- fileId: 文件ID
|
||
- sheetId: 工作表ID
|
||
- headerRow: 表头所在行号（默认：2）
|
||
|
||
执行逻辑：
|
||
1. 构建 range：A{headerRow}:Z{headerRow}（例如：A2:Z2）
|
||
2. 调用 API 读取表头数据
|
||
3. 解析表头，识别关键列的位置：
|
||
- "单号" 列（orderNoColumn）→ 这是第三方单号列
|
||
- "物流单号" 列（logisticsLinkColumn）→ 要写入物流信息的列
|
||
```
|
||
|
||
**示例日志**：
|
||
```
|
||
读取表头 - 行号: 2, range: A2:Z2
|
||
解析后的数据行数: 1
|
||
第 1 行（26列）: ["日期","公司","单号","型号","数量",...,"物流单号","","标记",...]
|
||
识别到关键列：
|
||
- "单号" 列位置: 第 3 列（索引2）
|
||
- "物流单号" 列位置: 第 13 列（索引12）
|
||
```
|
||
|
||
---
|
||
|
||
### 第二步：读取数据行（批量处理）
|
||
|
||
```
|
||
输入参数：
|
||
- startRow: 起始行号（表头行 + 1，默认：3）
|
||
- endRow: 结束行号（startRow + 49，每次读取50行）
|
||
|
||
执行逻辑：
|
||
1. 构建 range：A{startRow}:Z{endRow}（例如：A3:Z52）
|
||
2. 调用 API 读取数据行
|
||
3. 如果读取失败（range超出实际范围），返回"没有更多数据"
|
||
4. 如果成功，继续第三步
|
||
```
|
||
|
||
**示例日志**：
|
||
```
|
||
开始读取数据行 - 行号: 3 ~ 52, range: A3:Z52
|
||
解析后的数据行数: 50
|
||
```
|
||
|
||
**⚠️ 重要限制**：
|
||
- A1 表示法的 range **不能超出表格实际数据区域**
|
||
- 如果表格只有 100 行，`A3:Z200` 会报错：`invalid param error: 'range' invalid`
|
||
- 因此每次只读取 **50 行**，避免超出范围
|
||
|
||
---
|
||
|
||
### 第三步：逐行处理（匹配+填充）
|
||
|
||
```
|
||
对于每一行数据：
|
||
|
||
1. 提取第三方单号
|
||
- 从 orderNoColumn（"单号"列）取值
|
||
- 例如：row[2] = "JY202506181808"
|
||
|
||
2. 数据库查询
|
||
- 调用：jdOrderService.selectJDOrderByThirdPartyOrderNo(orderNo)
|
||
- 查询条件：third_party_order_no = "JY202506181808"
|
||
- 返回：JDOrder 对象（包含 logisticsLink 字段）
|
||
|
||
3. 检查物流链接
|
||
- 如果 order == null：跳过，errorCount++
|
||
- 如果 logisticsLink 为空：跳过，errorCount++
|
||
- 如果 logisticsLink 不为空：继续第4步
|
||
|
||
4. 写入物流链接
|
||
- 目标单元格：第 {当前行号} 行，第 {logisticsLinkColumn} 列
|
||
- 例如：第 3 行，第 13 列（M3）
|
||
- 调用 batchUpdate API 写入物流链接
|
||
- 成功：filledCount++
|
||
- 失败：errorCount++
|
||
```
|
||
|
||
**示例日志**：
|
||
```
|
||
正在处理订单 - 单号: JY202506181808, 行号: 3, 列: 3
|
||
查询到订单物流链接: https://m.kuaidi100.com/result.jsp?nu=6649902864
|
||
准备写入 - 目标单元格: 第 3 行, 第 13 列（索引12）
|
||
写入成功 - 单号: JY202506181808
|
||
```
|
||
|
||
---
|
||
|
||
### 第四步：更新进度（Redis缓存）
|
||
|
||
```
|
||
执行逻辑：
|
||
1. 记录本次处理的最大行号：lastMaxRow = endRow
|
||
2. 保存到 Redis：key = "tencent:doc:lastMaxRow:{fileId}:{sheetId}"
|
||
3. 下次执行时，从 lastMaxRow 开始继续处理
|
||
```
|
||
|
||
**进度管理**：
|
||
- 首次执行：startRow = 3, endRow = 52
|
||
- 第二次执行：startRow = 52, endRow = 101
|
||
- 第三次执行：startRow = 101, endRow = 150
|
||
- ...以此类推，直到所有数据处理完毕
|
||
|
||
---
|
||
|
||
## 📊 关键数据结构
|
||
|
||
### 1. 表格结构
|
||
|
||
| 列号 | 列名 | 数据示例 | 用途 |
|
||
|------|------|----------|------|
|
||
| A (0) | 日期 | 3月10日 | - |
|
||
| B (1) | 公司 | XX公司 | - |
|
||
| **C (2)** | **单号** | **JY202506181808** | **用于查询数据库** |
|
||
| D (3) | 型号 | iPhone 14 | - |
|
||
| E (4) | 数量 | 1 | - |
|
||
| ... | ... | ... | - |
|
||
| **M (12)** | **物流单号** | **6649902864** | **要填充的目标列** |
|
||
|
||
### 2. 数据库表结构（jd_order）
|
||
|
||
| 字段 | 类型 | 说明 | 示例 |
|
||
|------|------|------|------|
|
||
| **third_party_order_no** | varchar | **第三方单号（唯一索引）** | **JY202506181808** |
|
||
| **logistics_link** | varchar | **物流链接** | **https://m.kuaidi100.com/...** |
|
||
| order_no | varchar | 京东单号 | JD123456789 |
|
||
| order_status | int | 订单状态 | 1 |
|
||
| ... | ... | ... | ... |
|
||
|
||
### 3. API 请求示例
|
||
|
||
**读取表头**：
|
||
```http
|
||
GET https://docs.qq.com/openapi/spreadsheet/v3/files/DUW50RUprWXh2TGJK/BB08J2/A2:Z2
|
||
Headers:
|
||
Access-Token: {ACCESS_TOKEN}
|
||
Client-Id: {CLIENT_ID}
|
||
Open-Id: {OPEN_ID}
|
||
```
|
||
|
||
**读取数据行**：
|
||
```http
|
||
GET https://docs.qq.com/openapi/spreadsheet/v3/files/DUW50RUprWXh2TGJK/BB08J2/A3:Z52
|
||
Headers:
|
||
Access-Token: {ACCESS_TOKEN}
|
||
Client-Id: {CLIENT_ID}
|
||
Open-Id: {OPEN_ID}
|
||
```
|
||
|
||
**写入物流链接**（单个单元格）：
|
||
```http
|
||
POST https://docs.qq.com/openapi/spreadsheet/v3/files/DUW50RUprWXh2TGJK/batchUpdate
|
||
Headers:
|
||
Access-Token: {ACCESS_TOKEN}
|
||
Client-Id: {CLIENT_ID}
|
||
Open-Id: {OPEN_ID}
|
||
Content-Type: application/json
|
||
|
||
Body:
|
||
{
|
||
"requests": [
|
||
{
|
||
"updateCells": {
|
||
"range": {
|
||
"sheetId": "BB08J2",
|
||
"startRowIndex": 2, // 第3行（索引2）
|
||
"endRowIndex": 3, // 不包含
|
||
"startColumnIndex": 12, // 第13列（索引12）
|
||
"endColumnIndex": 13 // 不包含
|
||
},
|
||
"rows": [
|
||
{
|
||
"values": [
|
||
{
|
||
"cellValue": {
|
||
"text": "6649902864"
|
||
}
|
||
}
|
||
]
|
||
}
|
||
]
|
||
}
|
||
}
|
||
]
|
||
}
|
||
```
|
||
|
||
---
|
||
|
||
## 🎯 核心逻辑图
|
||
|
||
```
|
||
┌─────────────────────────────────────────────────────────┐
|
||
│ 1. 读取表头（A2:Z2） │
|
||
│ → 识别"单号"列位置（orderNoColumn） │
|
||
│ → 识别"物流单号"列位置（logisticsLinkColumn） │
|
||
└─────────────────────────────────────────────────────────┘
|
||
↓
|
||
┌─────────────────────────────────────────────────────────┐
|
||
│ 2. 读取数据行（A3:Z52，每次50行） │
|
||
│ → 解析为二维数组：[[row1], [row2], ...] │
|
||
└─────────────────────────────────────────────────────────┘
|
||
↓
|
||
┌─────────────────────────────────────────────────────────┐
|
||
│ 3. 逐行处理 │
|
||
│ FOR EACH row IN dataRows: │
|
||
│ a. 提取单号：orderNo = row[orderNoColumn] │
|
||
│ b. 查询数据库： │
|
||
│ order = DB.query(third_party_order_no=orderNo) │
|
||
│ c. 检查物流链接： │
|
||
│ IF order != null AND logistics_link != null: │
|
||
│ → 写入表格： │
|
||
│ cell[rowIndex][logisticsLinkColumn] │
|
||
│ = order.logistics_link │
|
||
│ → filledCount++ │
|
||
│ ELSE: │
|
||
│ → errorCount++ │
|
||
└─────────────────────────────────────────────────────────┘
|
||
↓
|
||
┌─────────────────────────────────────────────────────────┐
|
||
│ 4. 更新进度 │
|
||
│ → Redis.set("lastMaxRow", endRow) │
|
||
│ → 返回统计信息：filledCount, errorCount, skippedCount │
|
||
└─────────────────────────────────────────────────────────┘
|
||
```
|
||
|
||
---
|
||
|
||
## ⚙️ 配置参数
|
||
|
||
### 接口请求参数
|
||
|
||
```json
|
||
{
|
||
"accessToken": "用户访问令牌（必填）",
|
||
"fileId": "文件ID（必填）",
|
||
"sheetId": "工作表ID（必填）",
|
||
"headerRow": 2, // 表头行号（可选，默认2）
|
||
"orderNoColumn": 2, // 单号列位置（可选，自动识别）
|
||
"logisticsLinkColumn": 12 // 物流列位置（可选，自动识别）
|
||
}
|
||
```
|
||
|
||
### 自动识别列位置
|
||
|
||
如果不传 `orderNoColumn` 和 `logisticsLinkColumn`，系统会自动识别：
|
||
|
||
```java
|
||
// 在表头行中查找匹配的列名
|
||
for (int i = 0; i < headerRow.size(); i++) {
|
||
String cellValue = headerRow.getString(i);
|
||
if ("单号".equals(cellValue)) {
|
||
orderNoColumn = i; // 找到"单号"列
|
||
}
|
||
if ("物流单号".equals(cellValue)) {
|
||
logisticsLinkColumn = i; // 找到"物流单号"列
|
||
}
|
||
}
|
||
```
|
||
|
||
---
|
||
|
||
## 📈 返回结果示例
|
||
|
||
### 成功响应
|
||
|
||
```json
|
||
{
|
||
"msg": "物流链接填充成功",
|
||
"code": 200,
|
||
"data": {
|
||
"startRow": 3,
|
||
"endRow": 52,
|
||
"lastMaxRow": 52,
|
||
"filledCount": 45, // 成功填充45个
|
||
"skippedCount": 3, // 跳过3个（已有物流信息）
|
||
"errorCount": 2, // 失败2个（未找到订单）
|
||
"message": "成功填充45个物流链接，跳过3个，失败2个"
|
||
}
|
||
}
|
||
```
|
||
|
||
### 没有更多数据
|
||
|
||
```json
|
||
{
|
||
"msg": "没有需要处理的数据",
|
||
"code": 200,
|
||
"data": {
|
||
"startRow": 103,
|
||
"endRow": 152,
|
||
"lastMaxRow": 100, // 上次处理到第100行
|
||
"filledCount": 0,
|
||
"skippedCount": 0,
|
||
"errorCount": 0,
|
||
"message": "指定范围内没有数据"
|
||
}
|
||
}
|
||
```
|
||
|
||
### 错误响应
|
||
|
||
```json
|
||
{
|
||
"msg": "读取数据行失败: invalid param error: 'range' invalid",
|
||
"code": 500
|
||
}
|
||
```
|
||
|
||
---
|
||
|
||
## 🔧 关键代码片段
|
||
|
||
### 1. 根据第三方单号查询订单
|
||
|
||
```java
|
||
// Controller层
|
||
String orderNo = row.getString(orderNoColumn); // 从"单号"列取值
|
||
JDOrder order = jdOrderService.selectJDOrderByThirdPartyOrderNo(orderNo);
|
||
|
||
// Service层
|
||
@Override
|
||
public JDOrder selectJDOrderByThirdPartyOrderNo(String thirdPartyOrderNo) {
|
||
return jdOrderMapper.selectJDOrderByThirdPartyOrderNo(thirdPartyOrderNo);
|
||
}
|
||
|
||
// Mapper.xml
|
||
<select id="selectJDOrderByThirdPartyOrderNo" parameterType="string" resultMap="JDOrderResult">
|
||
SELECT * FROM jd_order
|
||
WHERE third_party_order_no = #{thirdPartyOrderNo}
|
||
LIMIT 1
|
||
</select>
|
||
```
|
||
|
||
### 2. 写入物流链接到指定单元格
|
||
|
||
```java
|
||
// 构建更新请求
|
||
JSONObject updateRequest = new JSONObject();
|
||
JSONObject updateCells = new JSONObject();
|
||
|
||
// 指定目标单元格范围（第excelRow行，第logisticsLinkColumn列）
|
||
JSONObject range = new JSONObject();
|
||
range.put("sheetId", sheetId);
|
||
range.put("startRowIndex", excelRow - 1); // Excel行号转索引
|
||
range.put("endRowIndex", excelRow); // 不包含
|
||
range.put("startColumnIndex", logisticsLinkColumn); // 列索引
|
||
range.put("endColumnIndex", logisticsLinkColumn + 1); // 不包含
|
||
|
||
// 设置单元格值
|
||
JSONArray rows = new JSONArray();
|
||
JSONObject rowData = new JSONObject();
|
||
JSONArray values = new JSONArray();
|
||
JSONObject cellData = new JSONObject();
|
||
JSONObject cellValue = new JSONObject();
|
||
cellValue.put("text", logisticsLink); // 物流链接文本
|
||
cellData.put("cellValue", cellValue);
|
||
values.add(cellData);
|
||
rowData.put("values", values);
|
||
rows.add(rowData);
|
||
|
||
updateCells.put("range", range);
|
||
updateCells.put("rows", rows);
|
||
updateRequest.put("updateCells", updateCells);
|
||
|
||
// 调用 batchUpdate API
|
||
tencentDocService.batchUpdate(accessToken, fileId, updateRequest);
|
||
```
|
||
|
||
---
|
||
|
||
## ⚠️ 注意事项
|
||
|
||
### 1. Range 格式限制
|
||
|
||
✅ **正确格式**（A1 表示法）：
|
||
- `A2:Z2` - 表头行
|
||
- `A3:Z52` - 数据行（50行）
|
||
- `M3` - 单个单元格
|
||
|
||
❌ **错误格式**：
|
||
- `1,0,1,25` - 索引格式（已废弃）
|
||
- `A3:Z203` - 超出实际数据范围（会报错）
|
||
|
||
### 2. API 限制
|
||
|
||
根据官方文档：
|
||
- 查询范围行数 ≤ 1000
|
||
- 查询范围列数 ≤ 200
|
||
- 范围内总单元格数 ≤ 10000
|
||
|
||
### 3. 批处理策略
|
||
|
||
- **每次读取 50 行**：避免超出表格实际范围
|
||
- **分批处理**：通过 Redis 记录进度，支持多次执行
|
||
- **错误重试**：如果某一行写入失败，记录错误但继续处理下一行
|
||
|
||
### 4. 数据库字段映射
|
||
|
||
⚠️ **关键对应关系**：
|
||
- 表格中的"单号" → 数据库中的 `third_party_order_no`
|
||
- **不是** `order_no`（京东单号）
|
||
- **不是** `remark`（备注）
|
||
|
||
### 5. 空值处理
|
||
|
||
- 如果单元格为空，`row.getString(index)` 返回空字符串 `""`
|
||
- 如果行不存在，`values.size()` 会小于预期列数
|
||
- 需要做好空值判断和边界检查
|
||
|
||
---
|
||
|
||
## 🧪 测试验证
|
||
|
||
### 测试请求
|
||
|
||
```bash
|
||
curl -X POST 'http://localhost:30313/jarvis/tencentDoc/fillLogisticsByOrderNo' \
|
||
-H 'Content-Type: application/json' \
|
||
-d '{
|
||
"accessToken": "YOUR_ACCESS_TOKEN",
|
||
"fileId": "DUW50RUprWXh2TGJK",
|
||
"sheetId": "BB08J2",
|
||
"headerRow": 2
|
||
}'
|
||
```
|
||
|
||
### 预期日志
|
||
|
||
```
|
||
读取表头 - 行号: 2, range: A2:Z2
|
||
解析后的数据行数: 1
|
||
第 1 行（26列）: ["日期","公司","单号",...,"物流单号","","标记",...]
|
||
|
||
开始读取数据行 - 行号: 3 ~ 52, range: A3:Z52
|
||
解析后的数据行数: 50
|
||
|
||
正在处理订单 - 单号: JY202506181808, 行号: 3
|
||
查询到订单物流链接: https://m.kuaidi100.com/result.jsp?nu=6649902864
|
||
写入成功 - 单号: JY202506181808
|
||
|
||
正在处理订单 - 单号: JY202506181809, 行号: 4
|
||
未找到订单 - 单号: JY202506181809
|
||
|
||
...
|
||
|
||
填充完成 - 成功: 45, 跳过: 3, 失败: 2
|
||
```
|
||
|
||
---
|
||
|
||
## 📚 相关文档
|
||
|
||
- [腾讯文档官方 API 文档](https://docs.qq.com/open/document/app/openapi/v3/sheet/get/get_range.html)
|
||
- [A1 表示法说明](https://docs.qq.com/open/document/app/openapi/v3/sheet/model/a1_notation.html)
|
||
- [批量更新接口](https://docs.qq.com/open/document/app/openapi/v3/sheet/batchupdate/update.html)
|
||
|
||
---
|
||
|
||
**文档版本**：1.0
|
||
**创建时间**：2025-11-05
|
||
**状态**：✅ 已完成
|
||
|