问题背景

在实际物流订单处理场景中，Excel模板常包含合并单元格、内嵌图片等复杂结构。本文针对以下三个核心问题提供解决方案：合并单元格导致同一箱组生成不同BOX编号、箱数读取异常、合并组标记不一致。

一、核心实现代码

// 图片缓存容器（键：图片ID，值：二进制数据）
private static Map<String, byte[]> imageCache = new HashMap<>();

/**
 * 主入口：解析物流Excel文件
 */
public static LogisticsOrderDTO parseLogisticsFile(MultipartFile multipartFile) throws IOException {
    validateFile(multipartFile);
    InputStream stream = multipartFile.getInputStream();
    XSSFWorkbook workbook = null;
    try {
        workbook = new XSSFWorkbook(stream);
        XSSFSheet targetSheet = locateSheet(workbook, "单票下单");
        if (targetSheet == null) {
            throw new IllegalArgumentException("Excel文件中缺少【单票下单】工作表");
        }
        preloadImages(workbook);
        SheetParseResult parseResult = extractSheetContent(targetSheet);
        LogisticsOrderDTO order = buildMainOrder(parseResult.getRawData());
        List<P0ProductInfoDTO> products = buildProductList(parseResult);
        order.setProductList(products);
        return order;
    } finally {
        if (stream != null) stream.close();
        if (workbook != null) workbook.close();
    }
}

/**
 * 预加载所有工作表中的图片
 */
private static void preloadImages(Workbook workbook) {
    imageCache.clear();
    if (!(workbook instanceof XSSFWorkbook)) return;
    XSSFWorkbook xw = (XSSFWorkbook) workbook;
    for (int i = 0; i < xw.getNumberOfSheets(); i++) {
        Sheet sheet = xw.getSheetAt(i);
        if (sheet instanceof XSSFSheet) {
            XSSFDrawing drawing = ((XSSFSheet) sheet).getDrawingPatriarch();
            if (drawing != null) {
                for (XSSFShape shape : drawing.getShapes()) {
                    if (shape instanceof XSSFPicture) {
                        XSSFPicture pic = (XSSFPicture) shape;
                        String imageId = parseImageId(pic.getPictureData().getPackagePart().getPartName().toString());
                        if (imageId != null) {
                            imageCache.put(imageId, pic.getPictureData().getData());
                        }
                    }
                }
            }
        }
    }
}

/**
 * 从路径中提取图片ID
 */
private static String parseImageId(String partName) {
    if (partName.contains("ID_")) {
        return partName.substring(partName.indexOf("ID_"), partName.lastIndexOf("."));
    }
    return partName.substring(partName.lastIndexOf("/") + 1, partName.lastIndexOf("."));
}

/**
 * 从公式 =DISPIMG("ID_XXX") 中提取ID
 */
private static String extractImageId(String formula) {
    Pattern pattern = Pattern.compile("\"(ID_[A-F0-9]+)\"");
    Matcher matcher = pattern.matcher(formula);
    if (matcher.find()) {
        return matcher.group(1);
    }
    return null;
}

/**
 * 安全获取单元格值
 */
private static String getCellValueSafely(Row row, int col) {
    Cell cell = row.getCell(col);
    if (cell == null) return "";
    cell.setCellType(CellType.STRING);
    return cell.getStringCellValue().trim();
}

/**
 * 文件校验
 */
private static void validateFile(MultipartFile file) {
    if (file == null || file.isEmpty()) {
        throw new IllegalArgumentException("上传文件不能为空");
    }
    String filename = file.getOriginalFilename();
    if (filename == null || !filename.toLowerCase().endsWith(".xlsx")) {
        throw new IllegalArgumentException("仅支持.xlsx格式，当前文件：" + filename);
    }
    if (file.getSize() > 10 * 1024 * 1024) {
        throw new IllegalArgumentException("文件大小不能超过10MB");
    }
}

/**
 * 模糊查找目标工作表
 */
private static XSSFSheet locateSheet(XSSFWorkbook workbook, String name) {
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        XSSFSheet sheet = workbook.getSheetAt(i);
        if (name.equalsIgnoreCase(sheet.getSheetName())) {
            return sheet;
        }
    }
    return null;
}

/**
 * 核心提取：按合并范围分组处理箱子信息
 */
private static SheetParseResult extractSheetContent(XSSFSheet sheet) {
    List<List<String>> rawData = new ArrayList<>();
    List<RowGroup> groups = new ArrayList<>();
    int boxSeq = 1;
    final int PRODUCT_START = 18;

    // 收集合并行映射
    Map<Integer, Integer> mergeMap = new HashMap<>();
    for (int rowIdx = PRODUCT_START; rowIdx < sheet.getPhysicalNumberOfRows(); rowIdx++) {
        XSSFRow row = sheet.getRow(rowIdx);
        if (row == null) continue;
        XSSFCell skuCell = row.getCell(0);
        if (skuCell == null || StringUtils.isBlank(getCellString(skuCell))) continue;

        Integer mergeEnd = getMergeEndRow(sheet, rowIdx, 1);
        if (mergeEnd == null) {
            mergeMap.put(rowIdx, rowIdx);
            continue;
        }
        boolean consistent = mergeEnd.equals(getMergeEndRow(sheet, rowIdx, 2))
                && mergeEnd.equals(getMergeEndRow(sheet, rowIdx, 3))
                && mergeEnd.equals(getMergeEndRow(sheet, rowIdx, 4));
        if (consistent) {
            mergeMap.put(rowIdx, mergeEnd);
            for (int i = rowIdx + 1; i <= mergeEnd; i++) {
                mergeMap.put(i, mergeEnd);
            }
        } else {
            mergeMap.put(rowIdx, rowIdx);
        }
    }

    // 生成合并组
    Set<String> rangeSet = new HashSet<>();
    for (Map.Entry<Integer, Integer> entry : mergeMap.entrySet()) {
        int start = entry.getKey();
        int end = entry.getValue();
        if (start != end) rangeSet.add(start + "_" + end);
    }
    for (String range : rangeSet) {
        String[] parts = range.split("_");
        int start = Integer.parseInt(parts[0]);
        int end = Integer.parseInt(parts[1]);
        Integer boxCount = getFirstBoxCount(sheet, start, end);
        groups.add(new RowGroup(start, end, boxSeq++, boxCount));
    }

    // 读取所有行数据
    int dataIdx = 0;
    Map<Integer, Integer> excelToData = new HashMap<>();
    for (int rowIdx = 0; rowIdx < sheet.getPhysicalNumberOfRows(); rowIdx++) {
        XSSFRow row = sheet.getRow(rowIdx);
        if (row == null) continue;
        List<String> rowData = readRow(sheet, row, rowIdx);
        if (rowData.stream().allMatch(StringUtils::isBlank)) continue;
        rawData.add(rowData);
        excelToData.put(rowIdx, dataIdx++);
    }

    // 构建合并区域映射
    Map<Integer, MergeArea> areaMap = new HashMap<>();
    for (RowGroup group : groups) {
        Integer startData = excelToData.get(group.startRow);
        if (startData == null) continue;
        for (int rowIdx = group.startRow; rowIdx <= group.endRow; rowIdx++) {
            Integer dataRow = excelToData.get(rowIdx);
            if (dataRow == null) continue;
            areaMap.put(dataRow, new MergeArea(
                    group.startRow, group.endRow,
                    1, 4,
                    group.boxNo, group.mergeSku,
                    group.boxCount, startData
            ));
        }
    }
    return new SheetParseResult(rawData, areaMap);
}

/**
 * 获取合并范围内第一个有效箱数
 */
private static Integer getFirstBoxCount(XSSFSheet sheet, int start, int end) {
    for (int rowIdx = start; rowIdx <= end; rowIdx++) {
        XSSFRow row = sheet.getRow(rowIdx);
        if (row == null) continue;
        XSSFCell cell = row.getCell(4);
        if (cell == null || cell.getCellType() == CellType.BLANK.getCode()) continue;
        try {
            if (cell.getCellType() == CellType.FORMULA.getCode()) {
                if (cell.getCachedFormulaResultType() == CellType.NUMERIC.getCode()) {
                    return (int) cell.getNumericCellValue();
                } else if (cell.getCachedFormulaResultType() == CellType.STRING.getCode()) {
                    String cleaned = cell.getStringCellValue().trim().replaceAll("[^0-9]", "");
                    return cleaned.isEmpty() ? 0 : Integer.parseInt(cleaned);
                }
            } else if (cell.getCellType() == CellType.NUMERIC.getCode()) {
                return (int) cell.getNumericCellValue();
            } else if (cell.getCellType() == CellType.STRING.getCode()) {
                String cleaned = cell.getStringCellValue().trim().replaceAll("[^0-9]", "");
                return cleaned.isEmpty() ? 0 : Integer.parseInt(cleaned);
            }
        } catch (Exception e) {
            System.err.println("行" + (rowIdx + 1) + "箱数解析失败：" + e.getMessage());
        }
    }
    return 0;
}

/**
 * 获取合并区域的结束行
 */
private static Integer getMergeEndRow(XSSFSheet sheet, int row, int col) {
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress region = sheet.getMergedRegion(i);
        if (region.isInRange(row, col)) return region.getLastRow();
    }
    return null;
}

/**
 * 读取一行数据（合并单元格处理）
 */
private static List<String> readRow(XSSFSheet sheet, XSSFRow row, int rowIdx) {
    List<String> data = new ArrayList<>();
    int lastCol = row.getLastCellNum();
    for (int col = 0; col <= lastCol; col++) {
        XSSFCell cell = row.getCell(col);
        CellRangeAddress mergeRegion = findMergeRegion(sheet, rowIdx, col);
        if (mergeRegion != null) {
            XSSFRow mergeRow = sheet.getRow(mergeRegion.getFirstRow());
            cell = mergeRow.getCell(mergeRegion.getFirstColumn());
        }
        data.add(getCellString(cell));
    }
    return data;
}

/**
 * 查找合并区域
 */
private static CellRangeAddress findMergeRegion(XSSFSheet sheet, int row, int col) {
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress region = sheet.getMergedRegion(i);
        if (region.isInRange(row, col)) return region;
    }
    return null;
}

/**
 * 获取单元格字符串值
 */
private static String getCellString(XSSFCell cell) {
    if (cell == null) return "";
    switch (cell.getCellTypeEnum()) {
        case STRING:
            return StringUtils.trim(cell.getStringCellValue());
        case NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                return DateUtil.getJavaDate(cell.getNumericCellValue()).toString().replace("T", " ");
            } else {
                cell.setCellType(CellType.STRING);
                return StringUtils.trim(cell.getStringCellValue());
            }
        case BOOLEAN:
            return String.valueOf(cell.getBooleanCellValue());
        case FORMULA:
            try {
                String formula = cell.getCellFormula();
                if (formula != null && formula.startsWith("DISPIMG")) {
                    return "=" + formula;
                }
                if (cell.getCachedFormulaResultType() == CellType.STRING.getCode()) {
                    return StringUtils.trim(cell.getStringCellValue());
                } else if (cell.getCachedFormulaResultType() == CellType.NUMERIC.getCode()) {
                    return String.valueOf((int) cell.getNumericCellValue());
                }
                return StringUtils.trim(cell.getStringCellValue());
            } catch (Exception e) {
                return String.valueOf(cell.getNumericCellValue());
            }
        default:
            return "";
    }
}

/**
 * 解析订单主信息
 */
private static LogisticsOrderDTO buildMainOrder(List<List<String>> rawData) {
    LogisticsOrderDTO order = new LogisticsOrderDTO();
    int maxRow = Math.min(16, rawData.size());
    for (int row = 0; row < maxRow; row++) {
        List<String> rowData = rawData.get(row);
        if (rowData.isEmpty()) continue;
        String key = safeGet(rowData, 0);
        if (StringUtils.isBlank(key)) continue;
        switch (key) {
            case "客户单号：":
                order.setCustomerOrderNo(safeGet(rowData, 1));
                order.setLogisticsChannel(safeGet(rowData, 7));
                break;
            case "*仓库代码：":
                order.setWarehouseCode(safeGet(rowData, 1));
                order.setTaxIncluded(safeGet(rowData, 7));
                break;
            case "收件人公司":
                order.setRecipientCompany(safeGet(rowData, 1));
                order.setCustomsDeclaration(safeGet(rowData, 7));
                break;
            case "收件人姓名":
                order.setRecipientName(safeGet(rowData, 1));
                order.setCustomsClearanceMethod(safeGet(rowData, 7));
                break;
            case "*收件人电话":
                order.setRecipientPhone(safeGet(rowData, 1));
                break;
            case "*收件人地址":
                order.setRecipientAddress(safeGet(rowData, 1));
                order.setWithElectricity(safeGet(rowData, 7));
                break;
            case "*收件人城市":
                order.setRecipientCity(safeGet(rowData, 1));
                order.setWithMagnetism(safeGet(rowData, 7));
                break;
            case "*收件人省份":
                order.setRecipientProvince(safeGet(rowData, 1));
                order.setDeclareCurrency(safeGet(rowData, 7));
                break;
            case "*收件人邮编":
                order.setRecipientPostalCode(safeGet(rowData, 1));
                order.setEinCbpNumber(safeGet(rowData, 7));
                break;
            case "*Country国家/区域:":
                order.setCountryRegion(safeGet(rowData, 1));
                order.setCompanyName(safeGet(rowData, 7));
                break;
            case "*FBA Shipment ID":
                order.setFbaShipmentId(safeGet(rowData, 1));
                break;
            case "*Amazon Reference ID":
                order.setAmazonReferenceId(safeGet(rowData, 1));
                break;
            case "*所在货站":
                order.setShipmentStation(safeGet(rowData, 1));
                break;
            default:
                break;
        }
    }
    validateOrderFields(order);
    return order;
}

/**
 * 解析产品列表（合并组统一处理）
 */
private static List<P0ProductInfoDTO> buildProductList(SheetParseResult parseResult) {
    List<List<String>> rawData = parseResult.getRawData();
    Map<Integer, MergeArea> areaMap = parseResult.getAreaMap();
    List<P0ProductInfoDTO> products = new ArrayList<>();
    int start = 18;
    if (rawData.size() <= start) {
        throw new IllegalArgumentException("Excel中缺少产品数据（需从第19行开始）");
    }
    for (int dataIdx = start; dataIdx < rawData.size(); dataIdx++) {
        List<String> row = rawData.get(dataIdx);
        if (row.isEmpty()) continue;
        String sku = safeGet(row, 0);
        if (StringUtils.isBlank(sku)) continue;
        P0ProductInfoDTO product = new P0ProductInfoDTO();
        product.setSku(sku);
        MergeArea area = areaMap.get(dataIdx);
        boolean merged = area != null;
        if (merged) {
            product.setLengthCm(parseBigDecimal(safeGet(row, 1), "长度"));
            product.setWidthCm(parseBigDecimal(safeGet(row, 2), "宽度"));
            product.setHeightCm(parseBigDecimal(safeGet(row, 3), "高度"));
            product.setBoxCount(area.boxCount);
            product.setBoxNo(area.boxNo);
            product.setIsMerge(1);
            product.setMergeSku(area.mergeSku);
        } else {
            product.setLengthCm(parseBigDecimal(safeGet(row, 1), "长度"));
            product.setWidthCm(parseBigDecimal(safeGet(row, 2), "宽度"));
            product.setHeightCm(parseBigDecimal(safeGet(row, 3), "高度"));
            product.setBoxCount(parseInteger(safeGet(row, 4), "箱数"));
            product.setBoxNo(sku);
            product.setIsMerge(0);
            product.setMergeSku(sku);
        }
        product.setSingleGrossWeightKgs(parseBigDecimal(safeGet(row, 5), "单件毛重"));
        product.setDeclareQuantity(parseInteger(safeGet(row, 6), "申报数量"));
        product.setUnitPrice(parseBigDecimal(safeGet(row, 7), "单价"));
        product.setCustomsCode(safeGet(row, 8));
        product.setChineseProductName(safeGet(row, 9));
        product.setEnglishProductName(safeGet(row, 10));
        product.setProductMaterial(safeGet(row, 11));
        product.setProductUsage(safeGet(row, 12));
        product.setBrand(safeGet(row, 13));
        product.setSpecificationModel(safeGet(row, 14));
        String imageVal = safeGet(row, 15);
        if (StringUtils.isNotBlank(imageVal)) {
            if (imageVal.startsWith("http")) {
                product.setProductImage(imageVal);
            } else if (imageVal.startsWith("=DISPIMG")) {
                String imageId = extractImageId(imageVal);
                byte[] imgBytes = imageCache.get(imageId);
                if (imgBytes != null) {
                    try {
                        ShipmentHelper helper = new ShipmentHelper();
                        MultipartFile imgFile = new CustomMultipartFile(imageId + ".png", imageId + ".png", "image/png", imgBytes);
                        Map<String, Object> uploadResult = helper.uploadFile(imgFile);
                        product.setProductImage((String) uploadResult.get("url"));
                    } catch (Exception e) {
                        System.err.println("图片上传失败: " + imageId + ", 错误: " + e.getMessage());
                    }
                } else {
                    System.err.println("未找到图片数据: " + imageId);
                }
            } else {
                try {
                    ShipmentHelper helper = new ShipmentHelper();
                    Map<String, Object> uploadResult = helper.uploadFile(imageVal);
                    product.setProductImage((String) uploadResult.get("url"));
                } catch (Exception e) {
                    System.err.println("图片上传失败: " + imageVal);
                }
            }
        }
        validateProductFields(product, sku);
        products.add(product);
    }
    if (products.isEmpty()) {
        throw new IllegalArgumentException("未能解析到有效产品数据");
    }
    return products;
}

// ========== 辅助方法 ==========
private static String safeGet(List<String> data, int idx) {
    if (data == null || idx < 0 || idx >= data.size()) return "";
    return StringUtils.trim(data.get(idx));
}

private static BigDecimal parseBigDecimal(String val, String field) {
    if (StringUtils.isBlank(val)) throw new IllegalArgumentException(field + "不能为空");
    try { return new BigDecimal(val); } catch (NumberFormatException e) {
        throw new IllegalArgumentException(field + "格式错误：" + val);
    }
}

private static Integer parseInteger(String val, String field) {
    if (StringUtils.isBlank(val)) throw new IllegalArgumentException(field + "不能为空");
    try { return Integer.parseInt(val); } catch (NumberFormatException e) {
        throw new IllegalArgumentException(field + "格式错误：" + val);
    }
}

private static void validateOrderFields(LogisticsOrderDTO order) {
    List<String> missing = new ArrayList<>();
    if (StringUtils.isBlank(order.getLogisticsChannel())) missing.add("物流渠道");
    if (StringUtils.isBlank(order.getWarehouseCode())) missing.add("仓库代码");
    if (ShipmentHelper.isPostalCode(order.getWarehouseCode())) {
        if (StringUtils.isBlank(order.getRecipientCompany())) missing.add("收件人公司");
        if (StringUtils.isBlank(order.getRecipientName())) missing.add("收件人姓名");
        if (StringUtils.isBlank(order.getRecipientAddress())) missing.add("收件人地址");
        if (StringUtils.isBlank(order.getRecipientCity())) missing.add("收件人城市");
        if (StringUtils.isBlank(order.getRecipientProvince())) missing.add("收件人省份");
        if (StringUtils.isBlank(order.getRecipientPostalCode())) missing.add("收件人邮编");
        if (StringUtils.isBlank(order.getCountryRegion())) missing.add("Country国家/区域");
        if (StringUtils.isBlank(order.getRecipientPhone())) missing.add("收件人电话");
    }
    if (!missing.isEmpty()) {
        throw new IllegalArgumentException("订单必填字段缺失：" + String.join("、", missing));
    }
}

private static void validateProductFields(P0ProductInfoDTO product, String sku) {
    List<String> missing = new ArrayList<>();
    if (product.getLengthCm() == null) missing.add("长度");
    if (product.getWidthCm() == null) missing.add("宽度");
    if (product.getHeightCm() == null) missing.add("高度");
    if (product.getBoxCount() == null) missing.add("箱数");
    if (product.getSingleGrossWeightKgs() == null) missing.add("单件毛重");
    if (product.getDeclareQuantity() == null) missing.add("申报数量");
    if (product.getUnitPrice() == null) missing.add("单价");
    if (StringUtils.isBlank(product.getCustomsCode())) missing.add("海关编码");
    if (StringUtils.isBlank(product.getChineseProductName())) missing.add("中文品名");
    if (StringUtils.isBlank(product.getEnglishProductName())) missing.add("英文品名");
    if (StringUtils.isBlank(product.getProductMaterial())) missing.add("产品材质");
    if (StringUtils.isBlank(product.getProductUsage())) missing.add("产品用途");
    if (StringUtils.isBlank(product.getBrand())) missing.add("品牌");
    if (StringUtils.isBlank(product.getSpecificationModel())) missing.add("规格/型号");
    if (!missing.isEmpty()) {
        throw new IllegalArgumentException("SKU【" + sku + "】字段缺失：" + String.join("、", missing));
    }
}

// ========== 内部类 ==========
private static class RowGroup {
    final int startRow;
    final int endRow;
    final String boxNo;
    final String mergeSku;
    final Integer boxCount;

    RowGroup(int start, int end, int seq, Integer count) {
        this.startRow = start;
        this.endRow = end;
        this.boxNo = "BOX_" + seq;
        this.mergeSku = "MERGE_" + this.boxNo;
        this.boxCount = count;
    }
}

private static class MergeArea {
    final int firstExcelRow;
    final int lastExcelRow;
    final int firstCol;
    final int lastCol;
    final String boxNo;
    final String mergeSku;
    final Integer boxCount;
    final int firstDataRow;

    MergeArea(int firstExcelRow, int lastExcelRow, int firstCol, int lastCol,
              String boxNo, String mergeSku, Integer boxCount, int firstDataRow) {
        this.firstExcelRow = firstExcelRow;
        this.lastExcelRow = lastExcelRow;
        this.firstCol = firstCol;
        this.lastCol = lastCol;
        this.boxNo = boxNo;
        this.mergeSku = mergeSku;
        this.boxCount = boxCount;
        this.firstDataRow = firstDataRow;
    }
}

private static class SheetParseResult {
    final List<List<String>> rawData;
    final Map<Integer, MergeArea> areaMap;

    SheetParseResult(List<List<String>> data, Map<Integer, MergeArea> map) {
        this.rawData = data;
        this.areaMap = map;
    }
}

二、Maven依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>1.1.2-beta5</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

三、相关导入

import com.ship.system.domain.p0Shipment.dto.LogisticsOrderDTO;
import com.ship.system.domain.p0Shipment.dto.P0ProductInfoDTO;
import com.ship.system.service.impl.p0Shipment.helper.CustomMultipartFile;
import com.ship.system.service.impl.p0Shipment.helper.ShipmentHelper;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

通过 XSSFWorkbook 可操作 Excel 的 Sheet 和单元格内容，支持合并单元格和图片解析。

四、模板说明

Excel模板示例图片如下：