Excel与库表结构不匹配时导入汇率关联数据的实现方案

现有实体定义 #

@Data
@Entity
@Table(name = "rates")
public class ExchangeRate {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @OneToOne
    @JsonIgnore
    private LocalCurrency localCurrency;

    @Column(name = "rate")
    private BigDecimal rate;

    @Column(name = "date")
    private Date date;
}

原有逻辑问题说明 #

原有解析代码采用固定列索引映射字段的逻辑，和实际Excel结构完全不匹配，核心错误点如下：

• Excel为宽表结构：首列为日期，后续每列表头为币种名称，单元格值为对应日期+币种的汇率，即一行存储N个币种的汇率；但库表为窄表结构，要求一行仅存储「单日期+单币种+单汇率」一条记录，结构不匹配直接导致映射错位
• 错误将首列的Excel日期序列值赋值给自增主键id：Excel内部将日期存储为距1900年的天数序列值，原有逻辑直接取首列数值转Long给id，才会出现1961、1962这类异常id
• 固定取索引1的单元格作为日期的逻辑完全错位，实际取到的是汇率数值，POI将普通数值按日期解析时就会返回1900-01-01的错误时间
• 完全没有处理表头的币种映射逻辑，无法关联LocalCurrency实体

修复实现 #

实现思路 #

1. 优先解析第一行表头，从第2列开始建立「列索引 -> 对应LocalCurrency实体」的映射关系，提前全量查询币种表数据做内存匹配，避免循环查库
2. 解析数据行时，首列固定获取当前行的日期值
3. 遍历当前行所有币种列，每个单元格单独生成一条ExchangeRate记录：复用当前行日期，关联当前列对应的币种实体，取单元格值作为汇率，主键id无需手动赋值（由数据库自增生成）
4. 增加空单元格、格式异常的容错处理

修复后代码 #

import org.apache.poi.ss.usermodel.*;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.*;

public static List<ExchangeRate> excelToExchangeRate(InputStream is, Map<String, LocalCurrency> currencyNameMap) {
    List<ExchangeRate> rates = new ArrayList<>();
    // 自动识别xlsx/xls格式，自动关闭资源
    try (Workbook workbook = WorkbookFactory.create(is)) {
        Sheet sheet = workbook.getSheet("FX Rates");
        Iterator<Row> rowIterator = sheet.iterator();
        if (!rowIterator.hasNext()) {
            return rates;
        }

        // 第一步：解析表头，建立列索引 -> LocalCurrency的映射
        Row headerRow = rowIterator.next();
        Map<Integer, LocalCurrency> columnCurrencyMap = new HashMap<>();
        // 从第2列（索引1）开始遍历表头，第1列（索引0）是日期列
        for (int cellIdx = 1; cellIdx < headerRow.getLastCellNum(); cellIdx++) {
            Cell currencyCell = headerRow.getCell(cellIdx);
            if (currencyCell == null) continue;
            String currencyName = getCellStringValue(currencyCell).trim();
            if (currencyName.isEmpty() || !currencyNameMap.containsKey(currencyName)) {
                // 可按需添加日志，记录未匹配到的币种
                continue;
            }
            columnCurrencyMap.put(cellIdx, currencyNameMap.get(currencyName));
        }

        // 第二步：逐行解析数据
        while (rowIterator.hasNext()) {
            Row currentRow = rowIterator.next();
            Cell dateCell = currentRow.getCell(0);
            if (dateCell == null || dateCell.getCellType() != CellType.NUMERIC || !DateUtil.isCellDateFormatted(dateCell)) {
                continue; // 空行/日期格式错误直接跳过
            }
            Date currentDate = dateCell.getDateCellValue();

            // 遍历所有币种列，每个单元格生成一条汇率记录
            for (Map.Entry<Integer, LocalCurrency> entry : columnCurrencyMap.entrySet()) {
                Integer cellIdx = entry.getKey();
                LocalCurrency currency = entry.getValue();
                Cell rateCell = currentRow.getCell(cellIdx);
                if (rateCell == null || rateCell.getCellType() != CellType.NUMERIC) {
                    continue; // 空汇率/格式错误跳过
                }

                ExchangeRate rate = new ExchangeRate();
                // 禁止手动setId，主键自增由数据库生成
                rate.setDate(currentDate);
                rate.setLocalCurrency(currency);
                rate.setRate(BigDecimal.valueOf(rateCell.getNumericCellValue()));
                rates.add(rate);
            }
        }
    } catch (IOException e) {
        throw new RuntimeException("解析Excel文件失败: " + e.getMessage(), e);
    }
    return rates;
}

// 通用单元格字符串取值工具，避免类型转换异常
private static String getCellStringValue(Cell cell) {
    CellType cellType = cell.getCellType();
    if (cellType == CellType.STRING) {
        return cell.getStringCellValue();
    } else if (cellType == CellType.NUMERIC) {
        return String.valueOf(cell.getNumericCellValue());
    } else if (cellType == CellType.BLANK) {
        return "";
    }
    return cell.toString();
}

调用注意事项 #

• 调用方法前，先从数据库查询所有LocalCurrency数据，构建Map<币种名称, LocalCurrency实体>传入方法，不要在循环内查询数据库
• 实体类id字段配置了GenerationType.IDENTITY自增策略，解析时不要手动赋值，否则会干扰主键生成
• 使用WorkbookFactory.create(is)可以自动兼容xlsx和老版本xls格式，无需硬编码指定XSSFWorkbook
• 可根据业务需要增加自定义校验逻辑，比如汇率不能为负、日期不能超过当前时间等

内容的提问来源于stack exchange，提问作者Alexander