使用Apache POI在Spring Boot项目中操作Excel：从模板填充数据并上传到远程服务

在现代企业应用中，Excel文件在数据交换和报告生成中扮演着重要角色。Apache POI是一个强大的开源Java库，允许开发者在Java应用中读取、创建和修改Excel文件。本文将通过一个实际的Spring Boot示例，详细介绍如何使用Apache POI操作Excel文件，从模板填充数据并将生成的文件上传到远程服务。

前言

在实际项目中，我们常常需要生成基于模板的Excel文件，例如订单报表、发票等。这些模板可能包含固定的文本和动态的数据区域。Apache POI提供了灵活的API来满足这些需求。本文将通过一个完整的示例，演示如何在Spring Boot项目中使用Apache POI完成这些任务。

Apache POI简介与前置知识

什么是Apache POI？

Apache POI（Poor Obfuscation Implementation）是一个由Apache软件基金会开发的开源Java库，专门用于读写Microsoft Office格式的文件，包括Excel、Word和PowerPoint。对于Excel操作，Apache POI提供了丰富的API，支持创建、修改和读取Excel文件（.xls和.xlsx格式）。

Apache POI的核心组件

1. HSSF（Horrible Spreadsheet Format） : 处理Excel 97-2003（.xls）格式的API。
2. XSSF（XML Spreadsheet Format） : 处理Excel 2007及以上（.xlsx）格式的API。
3. SXSSF（Streaming XML Spreadsheet Format） : 处理大数据量的Excel文件，支持流式写入，减少内存消耗。
4. SS（Spreadsheet）接口: 提供统一的接口，适用于HSSF和XSSF。

基本概念

• Workbook: 工作簿，代表一个Excel文件。对应于整个Excel文件。
• Sheet: 工作表，代表工作簿中的一个表格。
• Row: 行，代表工作表中的一行。
• Cell: 单元格，代表行中的一个单元格。

常用API介绍

Workbook

Workbook是Apache POI操作Excel的入口点。它有多个实现类，如HSSFWorkbook（处理.xls文件）和XSSFWorkbook（处理.xlsx文件）。

// 创建一个新的XSSFWorkbook
Workbook workbook = new XSSFWorkbook();

// 从文件读取
Workbook workbook = new XSSFWorkbook(new FileInputStream("example.xlsx"));

Sheet

Sheet代表一个工作表，可以通过Workbook获取。

Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表
Sheet sheet = workbook.getSheet("Sheet1"); // 通过名称获取工作表

Row

Row代表工作表中的一行，可以通过Sheet获取。

Row row = sheet.getRow(0); // 获取第一行
Row newRow = sheet.createRow(sheet.getLastRowNum() + 1); // 创建新行

Cell

Cell代表行中的一个单元格，可以通过Row获取。

Cell cell = row.getCell(0); // 获取第一列的单元格
Cell newCell = row.createCell(row.getLastCellNum()); // 创建新单元格

CellType

CellType枚举定义了单元格的数据类型，包括：

• STRING: 字符串类型
• NUMERIC: 数字类型
• BOOLEAN: 布尔类型
• FORMULA: 公式类型
• BLANK: 空白类型

CellStyle

CellStyle用于定义单元格的样式，如字体、颜色、边框等。

CellStyle style = workbook.createCellStyle();
Font font = workbook.createFont();
font.setBold(true);
style.setFont(font);
cell.setCellStyle(style);

Apache POI的优缺点

优点:

• 支持丰富的Excel功能，如公式、图表、样式等。
• 支持读取和写入多种Excel格式（.xls、.xlsx）。
• 活跃的社区和良好的文档支持。

缺点:

• 对于大文件的处理，内存消耗较高（可通过SXSSF优化）。
• 学习曲线较陡，需要理解Excel的内部结构和API。

环境准备

在开始之前，请确保您的开发环境具备以下条件：

• Java Development Kit (JDK) : 推荐使用JDK 8或更高版本。
• Spring Boot: 本文基于Spring Boot框架。
• Apache POI: 用于操作Excel文件。
• Maven: 作为项目的构建工具。

Maven依赖

在您的pom.xml文件中添加Apache POI和其他必要的依赖：

<dependencies>
    <!-- Spring Boot Starter -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
    </dependency>

    <!-- Apache POI -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.3</version>
    </dependency>

    <!-- 其他依赖，例如远程文件服务的API -->
    <dependency>
        <groupId>com.yp.resource</groupId>
        <artifactId>remote-file-service-api</artifactId>
        <version>1.0.0</version>
    </dependency>
</dependencies>

确保在<repositories>标签中包含Apache POI的仓库（通常Maven中央仓库已包含）。

项目结构概述

本文中的示例代码位于com.yp.crm.service.ExcelService类中。主要功能包括：

1. 加载Excel模板。
2. 替换模板中的占位符。
3. 动态插入商品和滤袋工况信息数据。
4. 合并单元格以优化显示。
5. 将生成的Excel文件上传到远程文件服务。

相关类说明

• OrderDTO: 订单数据传输对象，包含订单基本信息、商品列表和滤袋工况信息列表。
• Product: 商品实体，包含商品名称、数量、价格等字段。
• FilterCondition: 滤袋工况信息实体，包含工况名称及相关参数。
• RemoteFileService: 远程文件服务接口，用于上传生成的Excel文件。
• RemoteFile: 远程文件信息实体，包含文件的URL、名称等信息。

加载Excel模板

首先，我们需要加载一个预定义的Excel模板文件。模板通常包含固定的格式和占位符，用于后续的数据填充。

模板文件路径

在代码中，模板文件的位置定义为：

private static final String TEMPLATE_PATH = "classpath:templates/order.xlsx";

确保您的模板文件order.xlsx位于src/main/resources/templates/目录下。

加载模板

使用ResourceLoader加载模板文件：

@Autowired
private ResourceLoader resourceLoader;

private byte[] fillExcelData(OrderDTO data) throws IOException {
    Resource resource = resourceLoader.getResource(TEMPLATE_PATH);
    if (!resource.exists()) {
        throw new FileNotFoundException("模板文件未找到: " + TEMPLATE_PATH);
    }

    try (InputStream is = resource.getInputStream(); Workbook workbook = new XSSFWorkbook(is)) {
        // 后续处理...
    }
}

替换占位符

模板中的占位符通常采用${placeholder}的格式。我们需要遍历整个工作簿，查找并替换这些占位符。

替换非动态数据占位符

private void replacePlaceholders(Workbook workbook, OrderDTO data) {
    Map<String, String> placeholders = new HashMap<>();
    placeholders.put("${orderNumber}", data.getOrderNumber());
    placeholders.put("${salesperson}", data.getSalesperson());
    placeholders.put("${customerName}", data.getCustomerName());
    // 添加其他非动态占位符和对应的值

    for (Sheet sheet : workbook) {
        for (Row row : sheet) {
            for (Cell cell : row) {
                if (cell.getCellType() == CellType.STRING) {
                    String cellValue = cell.getStringCellValue();
                    for (Map.Entry<String, String> entry : placeholders.entrySet()) {
                        if (cellValue.contains(entry.getKey())) {
                            cellValue = cellValue.replace(entry.getKey(), entry.getValue());
                            cell.setCellValue(cellValue);
                        }
                    }
                }
            }
        }
    }
}

这个方法遍历每个Sheet、Row和Cell，查找并替换匹配的占位符。

动态插入数据行

除了替换固定占位符外，我们还需要根据数据动态插入行。例如，订单可能包含多个商品，每个商品需要在Excel中占据一行。

处理商品数据

private void processProducts(Workbook workbook, List<Product> products) {
    Sheet productSheet = workbook.getSheetAt(0); // 假设商品信息在第一个Sheet
    if (productSheet == null) {
        throw new IllegalStateException("商品信息Sheet未找到");
    }

    // 定位商品模板行，假设模板行包含${productName}
    Row productTemplateRow = findTemplateRow(productSheet, "${productName}");
    if (productTemplateRow == null) {
        throw new IllegalStateException("商品模板行未找到");
    }

    int productTemplateRowIndex = productTemplateRow.getRowNum();
    int currentProductRowIndex = productTemplateRowIndex;

    for (Product product : products) {
        // Shift rows down to make space for the new row
        productSheet.shiftRows(currentProductRowIndex, productSheet.getLastRowNum(), 1, true, false);

        // Create new row at currentProductRowIndex
        Row newProductRow = productSheet.createRow(currentProductRowIndex);

        // Copy the template row into the new row
        copyRow(workbook, productTemplateRow, newProductRow);

        // Replace placeholders with actual product data
        replaceProductPlaceholders(newProductRow, product);

        // 合并新插入行的A-F单元格
        mergeCellsSafely(productSheet, newProductRow.getRowNum(), 0, 5); // A=0, F=5

        // Move to the next row for insertion
        currentProductRowIndex++;
    }

    // 可选：移除模板行
    // productSheet.removeRow(productTemplateRow);
}

辅助方法：查找模板行

private Row findTemplateRow(Sheet sheet, String placeholder) {
    for (Row row : sheet) {
        for (Cell cell : row) {
            if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().contains(placeholder)) {
                return row;
            }
        }
    }
    return null;
}

辅助方法：复制行

private void copyRow(Workbook workbook, Row templateRow, Row newRow) {
    for (int i = 0; i < templateRow.getLastCellNum(); i++) {
        Cell templateCell = templateRow.getCell(i);
        Cell newCell = newRow.createCell(i);

        if (templateCell == null) {
            newCell.setCellType(CellType.BLANK);
            continue;
        }

        // 复制单元格样式
        CellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(templateCell.getCellStyle());
        newCell.setCellStyle(newCellStyle);

        // 复制单元格内容
        switch (templateCell.getCellType()) {
            case STRING:
                newCell.setCellValue(templateCell.getStringCellValue());
                break;
            case NUMERIC:
                newCell.setCellValue(templateCell.getNumericCellValue());
                break;
            case BOOLEAN:
                newCell.setCellValue(templateCell.getBooleanCellValue());
                break;
            case FORMULA:
                newCell.setCellFormula(templateCell.getCellFormula());
                break;
            case BLANK:
                newCell.setCellType(CellType.BLANK);
                break;
            default:
                break;
        }
    }
}

辅助方法：替换商品占位符

private void replaceProductPlaceholders(Row row, Product product) {
    for (Cell cell : row) {
        if (cell.getCellType() == CellType.STRING) {
            String cellValue = cell.getStringCellValue();
            if (cellValue.contains("${productName}")) {
                cellValue = cellValue.replace("${productName}", product.getProductName());
                cell.setCellValue(cellValue);
            }
            if (cellValue.contains("${quantity}")) {
                cellValue = cellValue.replace("${quantity}", String.valueOf(product.getQuantity()));
                cell.setCellValue(cellValue);
            }
            if (cellValue.contains("${price}")) {
                cellValue = cellValue.replace("${price}", String.valueOf(product.getPrice()));
                cell.setCellValue(cellValue);
            }
            // 添加其他商品字段的替换
        }
    }
}

合并单元格的处理

在动态插入数据行后，通常需要合并一些单元格以保持格式的一致性。

合并单元格的方法

private void mergeCellsSafely(Sheet sheet, int rowIndex, int startCol, int endCol) {
    CellRangeAddress newRegion = new CellRangeAddress(rowIndex, rowIndex, startCol, endCol);
    if (!isOverlapping(sheet, newRegion)) {
        sheet.addMergedRegion(newRegion);
    } else {
        // 记录日志或处理重叠情况
        System.out.println("跳过重叠的合并区域: " + newRegion.formatAsString());
        // 如果使用日志框架，如 SLF4J，可以替换为：
        // logger.warn("跳过重叠的合并区域: {}", newRegion.formatAsString());
    }
}

private boolean isOverlapping(Sheet sheet, CellRangeAddress newRegion) {
    int numMergedRegions = sheet.getNumMergedRegions();
    for (int i = 0; i < numMergedRegions; i++) {
        CellRangeAddress existingRegion = sheet.getMergedRegion(i);
        if (existingRegion.intersects(newRegion)) {
            return true;
        }
    }
    return false;
}

删除合并区域的方法

在处理滤袋工况信息时，需要删除模板行块中的所有合并区域：

private void removeMergedRegionsInRange(Sheet sheet, int startRowIndex, int endRowIndex) {
    // 收集需要移除的合并区域索引
    List<Integer> regionsToRemove = new java.util.ArrayList<>();
    int numMergedRegions = sheet.getNumMergedRegions();
    for (int i = 0; i < numMergedRegions; i++) {
        CellRangeAddress region = sheet.getMergedRegion(i);
        if (region.getFirstRow() >= startRowIndex && region.getLastRow() <= endRowIndex) {
            regionsToRemove.add(i);
        }
    }
    // 从高到低删除，避免索引偏移
    for (int i = regionsToRemove.size() - 1; i >= 0; i--) {
        sheet.removeMergedRegion(regionsToRemove.get(i));
    }
}

处理滤袋工况信息数据

类似于商品数据，我们还需要处理滤袋工况信息，这些信息可能需要在Excel中占据多个行，并在每组数据后留出空白行以便阅读。

处理滤袋工况信息

private void processFilterConditions(Workbook workbook, List<FilterCondition> filterConditions) {
    Sheet filterSheet = workbook.getSheetAt(1); // 假设滤袋工况信息在第二个Sheet
    if (filterSheet == null) {
        throw new IllegalStateException("滤袋工况输入信息表Sheet未找到");
    }

    // 定位滤袋工况模板行块，假设模板行占据从第0行到第21行，共22行
    int templateStartRowIndex = 0;
    int templateEndRowIndex = 21; // 索引从0开始，21代表第22行

    // 设置当前插入行的起始位置
    int currentFilterRowIndex = templateEndRowIndex + 1; // 插入位置在模板行之后

    for (FilterCondition condition : filterConditions) {
        // 复制模板行块到新位置
        for (int i = 0; i <= templateEndRowIndex - templateStartRowIndex; i++) {
            Row templateRow = filterSheet.getRow(templateStartRowIndex + i);
            Row newRow = filterSheet.createRow(currentFilterRowIndex + i);
            copyRow(workbook, templateRow, newRow);
        }

        // 替换新插入行块中的占位符
        for (int i = 0; i <= templateEndRowIndex - templateStartRowIndex; i++) {
            Row newRow = filterSheet.getRow(currentFilterRowIndex + i);
            replaceFilterConditionPlaceholders(newRow, condition);
        }

        // 合并新插入行块的第一行（A-F）
        Row newFirstRow = filterSheet.getRow(currentFilterRowIndex);
        if (newFirstRow != null) {
            mergeCellsSafely(filterSheet, newFirstRow.getRowNum(), 0, 5); // A=0, F=5
        }

        // 更新插入位置，跳过22行数据块和2行空白
        currentFilterRowIndex += (templateEndRowIndex - templateStartRowIndex + 1) + 2; // 22 + 2 = 24
    }

    // 删除模板行块中的所有合并区域（如果有）
    removeMergedRegionsInRange(filterSheet, templateStartRowIndex, templateEndRowIndex);

    // 删除模板行（第0行到第21行）
    for (int i = templateStartRowIndex; i <= templateEndRowIndex; i++) {
        Row row = filterSheet.getRow(i);
        if (row != null) {
            filterSheet.removeRow(row);
        }
    }

    // 将插入的数据块向上移动，以填补删除模板行后留下的空白
    int shiftStartRow = templateEndRowIndex + 1;
    int shiftEndRow = filterSheet.getLastRowNum();
    int shiftAmount = -(templateEndRowIndex - templateStartRowIndex + 1); // -22

    if (shiftAmount != 0 && shiftEndRow >= shiftStartRow) {
        filterSheet.shiftRows(shiftStartRow, shiftEndRow, shiftAmount, true, false);

        // 调整合并区域的行号，因为移动了行
       // adjustMergedRegions(filterSheet, shiftStartRow, shiftEndRow, shiftAmount);
    }
}

辅助方法：替换滤袋工况占位符

private void replaceFilterConditionPlaceholders(Row row, FilterCondition condition) {
    for (Cell cell : row) {
        if (cell.getCellType() == CellType.STRING) {
            String cellValue = cell.getStringCellValue();
            if (cellValue.contains("${conditionName}")) {
                cellValue = cellValue.replace("${conditionName}", condition.getConditionName());
                cell.setCellValue(cellValue);
            }
            if (cellValue.contains("${parameter1}")) {
                cellValue = cellValue.replace("${parameter1}", condition.getParameter1());
                cell.setCellValue(cellValue);
            }
            if (cellValue.contains("${parameter2}")) {
                cellValue = cellValue.replace("${parameter2}", condition.getParameter2());
                cell.setCellValue(cellValue);
            }
            // 添加其他滤袋工况字段的替换
        }
    }
}

辅助方法：调整合并区域（可选）

如果在移动行后需要调整合并区域，可以使用以下方法：

private void adjustMergedRegions(Sheet sheet, int startRow, int endRow, int shiftAmount) {
    int numMergedRegions = sheet.getNumMergedRegions();
    List<CellRangeAddress> mergedRegions = new java.util.ArrayList<>();
    for (int i = 0; i < numMergedRegions; i++) {
        CellRangeAddress region = sheet.getMergedRegion(i);
        mergedRegions.add(region);
    }

    // 清除所有合并区域
    for (int i = numMergedRegions - 1; i >= 0; i--) {
        sheet.removeMergedRegion(i);
    }

    // 重新添加合并区域，调整行号
    for (CellRangeAddress region : mergedRegions) {
        if (region.getFirstRow() >= startRow && region.getLastRow() <= endRow) {
            CellRangeAddress newRegion = new CellRangeAddress(
                    region.getFirstRow() + shiftAmount,
                    region.getLastRow() + shiftAmount,
                    region.getFirstColumn(),
                    region.getLastColumn()
            );
            sheet.addMergedRegion(newRegion);
        } else {
            sheet.addMergedRegion(region);
        }
    }
}

将填充后的Excel上传到远程服务

一旦Excel文件被填充，我们需要将其上传到远程文件服务（例如OSS）。这里假设使用的是一个名为RemoteFileService的远程服务。

上传方法

@Autowired
private RemoteFileService remoteFileService; // 注入OSS上传服务

public RemoteFile fillAndUploadExcelData(OrderDTO orderDTO) throws IOException, ServiceException {
    try {
        // 填充Excel数据
        byte[] excelBytes = fillExcelData(orderDTO);

        // 调用上传方法
        String name = "filled_template.xlsx";
        String originalFilename = "filled_template.xlsx";
        String contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        RemoteFile remoteFile = remoteFileService.upload(name, originalFilename, contentType, excelBytes);

        return remoteFile;
    } catch (Exception e) {
        // 记录日志或进一步处理异常
        throw new ServiceException("填充并上传Excel数据失败: " + e.getMessage(), 500);
    }
}

注意事项

• 文件名与内容类型: 确保上传时指定正确的文件名和MIME类型，以便远程服务正确处理。
• 远程服务配置: 确保RemoteFileService的配置正确，包括API端点、认证信息等。
• 网络与权限: 上传过程中可能会遇到网络问题或权限限制，需做好相应的处理。

错误处理与最佳实践

在处理文件操作时，错误处理至关重要。以下是一些建议：

1. 资源管理: 使用try-with-resources确保InputStream和Workbook等资源被正确关闭，避免内存泄漏。

   try (InputStream is = resource.getInputStream(); Workbook workbook = new XSSFWorkbook(is)) {
       // 处理逻辑
   }
   

2. 异常捕获: 捕获并处理可能的异常，如FileNotFoundException、IOException等，提供有意义的错误信息。

   try {
       // 可能抛出异常的代码
   } catch (IOException e) {
       // 处理异常
       throw new ServiceException("IO错误: " + e.getMessage(), 500);
   }
   

3. 日志记录: 在生产环境中，使用日志框架（如SLF4J）记录关键操作和异常，便于调试和监控。

   private static final Logger logger = LoggerFactory.getLogger(ExcelService.class);
   
   try {
       // 处理逻辑
   } catch (Exception e) {
       logger.error("填充并上传Excel数据失败", e);
       throw new ServiceException("填充并上传Excel数据失败: " + e.getMessage(), 500);
   }
   

4. 模板验证: 在加载模板后，验证其结构是否符合预期，避免后续操作因模板问题而失败。

   if (productSheet == null) {
       throw new IllegalStateException("商品信息Sheet未找到");
   }
   

5. 性能优化: 对于大文件操作，考虑内存和性能的优化，例如使用SXSSFWorkbook进行流式写入。

   try (Workbook workbook = new SXSSFWorkbook()) {
       // 流式处理逻辑
   }
   

6. 线程安全: 如果ExcelService可能在多线程环境中使用，确保线程安全，避免共享Workbook实例。

总结

本文通过一个Spring Boot示例，详细介绍了如何使用Apache POI操作Excel文件，包括加载模板、替换占位符、动态插入数据行、合并单元格以及上传生成的文件到远程服务。Apache POI提供了丰富的API，能够满足各种复杂的Excel操作需求。通过合理的代码结构和错误处理，可以构建健壮且高效的Excel处理模块，提升企业应用的数据处理能力。

关键要点回顾

• Apache POI基础: 理解Workbook、Sheet、Row和Cell等核心概念及其关系。
• 模板加载与占位符替换: 使用预定义模板，通过遍历单元格替换占位符，实现数据填充。
• 动态行插入: 根据数据动态插入行，复制模板行的样式和内容，保持Excel格式的一致性。
• 单元格合并: 合并单元格以优化Excel的展示效果，避免格式混乱。
• 文件上传: 将生成的Excel文件上传到远程服务，确保文件的可访问性和持久性。
• 错误处理与最佳实践: 通过资源管理、异常捕获和日志记录等手段，提升代码的健壮性和可维护性。

希望本文对您在项目中使用Apache POI有所帮助！如果有任何问题或建议，欢迎在评论区留言讨论。