EasyExcel 模板导出数据 + 自定义策略（合并单元格）
需求：
数据库里的主表+明细表，联查出数据并导出Excel，合并主表数据的单元格。
代码：
controller
@PostMapping(\"export\") @ApiOperation(value = \"导出数据\") protected void export(@ApiParam @Valid @RequestBody NewWmsExceptionCaseSearchCondition request, HttpServletResponse response) throws IOException { getService().export(request, response); }service
import com.alibaba.excel.EasyExcel;import com.alibaba.excel.ExcelWriter;import com.alibaba.excel.write.metadata.WriteSheet;import com.ctsfreight.oseb.common.strategy.CustomRowMergeStrategy;import com.ctsfreight.oseb.common.utils.TokenUtil;import com.ctsfreight.oseb.common.vo.*;import com.ctsfreight.oseb.common.vo.excel.ExceptionExcelVo;import org.apache.commons.io.FileUtils;import org.apache.commons.io.IOUtils;import org.apache.commons.lang3.ObjectUtils;import org.apache.commons.lang3.StringUtils;import org.springframework.beans.BeanUtils;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.core.io.ByteArrayResource;import org.springframework.core.io.InputStreamSource;import org.springframework.core.io.ResourceLoader;import java.io.ByteArrayOutputStream;import java.io.File;import java.io.IOException;import java.io.InputStream;import java.math.BigDecimal;import java.net.URLEncoder;import java.text.MessageFormat;import java.time.LocalDateTime;import java.time.format.DateTimeFormatter;import java.util.*;import java.util.concurrent.atomic.AtomicInteger;import java.util.concurrent.atomic.AtomicReference; @Resource private ResourceLoader resourceLoader; private final String TEMPLATE_EXCEPTION_EXCEL_XLSX = \"classpath:template/exception_excel.xlsx\"; @Override public void export(NewWmsExceptionCaseSearchCondition request, HttpServletResponse response) throws IOException { String fileName = \"明细_\" + LocalDateTime.now(); response.setContentType(\"application/vnd.ms-excel;charset=utf-8\"); response.setHeader(\"Content-disposition\", \"attachment; filename=\" + URLEncoder.encode(fileName + \".xlsx\", \"utf-8\")); String template = TEMPLATE_EXCEPTION_EXCEL_XLSX; InputStream inputStream = resourceLoader.getResource(template).getInputStream(); File xlsx = null; try { ByteArrayOutputStream bos = new ByteArrayOutputStream(); List crossdockSeaFinanceVoList = baseMapper.listExceptionExcelVo(request); if (CollectionUtils.isNotEmpty(crossdockSeaFinanceVoList)) { AtomicInteger index = new AtomicInteger(0); AtomicReference lastId = new AtomicReference(\"\"); crossdockSeaFinanceVoList.forEach(item -> { String currentId = item.getId(); if (!lastId.get().equals(currentId)) { index.set(index.get() + 1); lastId.set(currentId); } item.setId(String.valueOf(index.get())); }); ExcelWriter excelWriter = EasyExcel.write(bos).registerWriteHandler(new CustomRowMergeStrategy(ExceptionExcelVo.class)) .withTemplate(inputStream).build(); WriteSheet writeSheet = EasyExcel.writerSheet(0).build(); excelWriter.write(crossdockSeaFinanceVoList, writeSheet); excelWriter.finish(); } InputStreamSource inputStreamSource = new ByteArrayResource(bos.toByteArray()); xlsx = File.createTempFile(\"明细_\" + UUID.randomUUID(), \".xlsx\"); FileUtils.copyInputStreamToFile(inputStreamSource.getInputStream(), xlsx); IOUtils.copy(inputStreamSource.getInputStream(), response.getOutputStream()); } catch (Exception e) { log.error(\"export error\", e); throw new ApiException(ResultCode.FAULT); } finally { if (xlsx != null) { xlsx.delete(); } inputStream.close(); } }这里的
template 是放在了src/main/resources/template/delivery_export_en.xlsx
xml：
SELECT ecs.id AS id, ecs.order_no AS orderNo, ecs.container_no AS containerNo, ecs.total_amount AS totalAmount, ecsit.sort_note AS sortNote, ecsit.consignee_name AS consigneeName, ecsit.fba_id AS fbaId, ecsit.fba_number AS fbaNumber, ecsit.package_num AS packageNum FROM ( SELECT id, order_no, container_no, total_amount, create_time FROM exception_case_summary WHERE delete_flag = 0 AND id IN #{id} ORDER BY create_time DESC LIMIT 100 ) ecs LEFT JOIN exception_case_sorting_item ecsit ON ecs.id = ecsit.exception_case_summary_id WHERE ecsit.delete_flag = 0 ORDER BY ecs.create_time DESC; LIMIT 100，是为了查询最新的100条数据，不然后面数据太多了
vo：
package com.ctsfreight.oseb.common.vo.excel;import com.alibaba.excel.annotation.ExcelProperty;import com.ctsfreight.oseb.common.strategy.annotations.CustomRowMerge;import io.swagger.annotations.ApiModel;import io.swagger.annotations.ApiModelProperty;import lombok.Data;import lombok.experimental.Accessors;/** * * 信息VO *
* * */@Data@Accessors(chain = true)@ApiModel(value = \"信息VO\")public class ExceptionExcelVo { @ApiModelProperty(\"主表id\") @ExcelProperty(index = 0) @CustomRowMerge(needMerge = true, isPk = true) private String id; @ApiModelProperty(\"号\") @ExcelProperty(index = 1) @CustomRowMerge(needMerge = true) private String containerNo; @ApiModelProperty(\"单号\") @ExcelProperty(index = 2) @CustomRowMerge(needMerge = true) private String orderNo; @ApiModelProperty(\"总箱数\") @ExcelProperty(index = 3) @CustomRowMerge(needMerge = true) private Integer totalAmount; @ApiModelProperty(\"标\") @ExcelProperty(index = 4) private String sortNote; @ApiModelProperty(\"\") @ExcelProperty(index = 5) private String consigneeName; @ApiModelProperty(\"\") @ExcelProperty(index = 6) private String fbaId; @ApiModelProperty(\"\") @ExcelProperty(index = 7) private String fbaNumber; @ApiModelProperty(\"箱数\") @ExcelProperty(index = 8) private Integer packageNum;}自定义单元格合并策略：
package com.ctsfreight.oseb.common.strategy;import com.alibaba.excel.annotation.ExcelProperty;import com.alibaba.excel.write.handler.RowWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.metadata.holder.WriteTableHolder;import com.ctsfreight.oseb.common.strategy.annotations.CustomRowMerge;import org.apache.commons.lang3.StringUtils;import org.apache.poi.ss.usermodel.CellType;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.util.CellRangeAddress;import java.lang.reflect.Field;import java.util.ArrayList;import java.util.List;/** * 自定义单元格合并策略 */public class CustomRowMergeStrategy implements RowWriteHandler { /** * 主键下标集合 */ private List pkColumnIndex = new ArrayList(); /** * 需要合并的列的下标集合 */ private List needMergeColumnIndex = new ArrayList(); /** * DTO数据类型 */ private Class elementType; public CustomRowMergeStrategy(Class elementType) { this.elementType = elementType; } @Override public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) { // 如果是标题,则直接返回 if (isHead) { return; } // 获取当前sheet Sheet sheet = writeSheetHolder.getSheet(); // 获取标题行 Row titleRow = sheet.getRow(0); if (pkColumnIndex.isEmpty()) { this.lazyInit(writeSheetHolder); } // 判断是否需要和上一行进行合并 // 不能和标题合并，只能数据行之间合并 if (row.getRowNum() <= 1) { return; } // 获取上一行数据 Row lastRow = sheet.getRow(row.getRowNum() - 1); // 将本行和上一行是同一类型的数据(通过主键字段进行判断)，则需要合并 boolean margeBol = true; for (Integer pkIndex : pkColumnIndex) { String lastKey = lastRow.getCell(pkIndex).getCellType() == CellType.STRING ? lastRow.getCell(pkIndex).getStringCellValue() : String.valueOf(lastRow.getCell(pkIndex).getNumericCellValue()); String currentKey = row.getCell(pkIndex).getCellType() == CellType.STRING ? row.getCell(pkIndex).getStringCellValue() : String.valueOf(row.getCell(pkIndex).getNumericCellValue()); if (!StringUtils.equalsIgnoreCase(lastKey, currentKey)) { margeBol = false; break; } } if (margeBol) { for (Integer needMerIndex : needMergeColumnIndex) { CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum() - 1, row.getRowNum(), needMerIndex, needMerIndex); sheet.addMergedRegionUnsafe(cellRangeAddress); } } } /** * 初始化主键下标和需要合并字段的下标 */ private void lazyInit(WriteSheetHolder writeSheetHolder) { // 获取当前sheet Sheet sheet = writeSheetHolder.getSheet(); // 获取标题行 Row titleRow = sheet.getRow(0); // 获取DTO的类型 Class eleType = this.elementType; // 获取DTO所有的属性 Field[] fields = eleType.getDeclaredFields(); int i = 0; // 遍历所有的字段，因为是基于DTO的字段来构建excel，所以字段数 >= excel的列数 for (Field theField : fields) { // 获取@ExcelProperty注解，用于获取该字段对应在excel中的列的下标 ExcelProperty easyExcelAnno = theField.getAnnotation(ExcelProperty.class); // 为空,则表示该字段不需要导入到excel,直接处理下一个字段 if (null == easyExcelAnno) { continue; } // 获取自定义的注解，用于合并单元格 CustomRowMerge customMerge = theField.getAnnotation(CustomRowMerge.class); // 没有@CustomMerge注解的默认不合并 if (null == customMerge) { continue; } // 判断是否有主键标识 if (customMerge.isPk()) { pkColumnIndex.add(i); } // 判断是否需要合并 if (customMerge.needMerge()) { needMergeColumnIndex.add(i); } i++; } // 没有指定主键，则异常 if (pkColumnIndex.isEmpty()) { throw new IllegalStateException(\"使用@CustomMerge注解必须指定主键\"); } }}效果图：
拓展：
可以增加居中策略
可以通过 EasyExcel 的 WriteHandler 或 AbstractCellStyleStrategy 来设置 Excel 单元格内容的 水平居中 和 垂直居中
使用 WriteHandler 自定义单元格样式
你可以创建一个继承自 AbstractCellStyleStrategy 或 AbstractCellWriteHandler 的类，设置单元格样式。
import com.alibaba.excel.write.handler.AbstractCellStyleStrategy;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.metadata.holder.WriteTableHolder;import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class CenterCellStyleStrategy extends AbstractCellStyleStrategy { @Override protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) { // 如果你也希望表头居中，可以在这里设置 setCellStyle(cell); } @Override protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) { setCellStyle(cell); } private void setCellStyle(Cell cell) { Workbook workbook = cell.getSheet().getWorkbook(); CellStyle cellStyle = workbook.createCellStyle(); // 设置水平居中 cellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置垂直居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 可选：自动换行 cellStyle.setWrapText(true); cell.setCellStyle(cellStyle); }}注册样式策略到导出逻辑中
ExcelWriter excelWriter = EasyExcel.write(bos) .registerWriteHandler(new CenterCellStyleStrategy()) // 设置居中样式 .registerWriteHandler(new CustomRowMergeStrategy(Arrays.asList( \"containerNo\", \"orderNo\", \"totalAmount\", \"sortNote\", \"consigneeName\" ))) .withTemplate(inputStream) .build();如果你只想对某些列设置居中（可选）
你可以修改 setCellStyle 方法，根据 cell.getColumnIndex() 判断是否对某些列应用居中
private void setCellStyle(Cell cell) { Workbook workbook = cell.getSheet().getWorkbook(); CellStyle cellStyle = workbook.createCellStyle(); // 只对第 0 列（柜号）和第 2 列（登记总箱数）设置居中 if (cell.getColumnIndex() == 0 || cell.getColumnIndex() == 2) { cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setWrapText(true); } else { // 其他列左对齐 cellStyle.setAlignment(HorizontalAlignment.LEFT); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); } cell.setCellStyle(cellStyle);}如果你使用的是 .xlsx 模板，并希望保留模板样式
你可以这样设置：
// 从模板中读取样式，避免覆盖原有样式CellStyle originalStyle = cell.getCellStyle();CellStyle newStyle = workbook.createCellStyle();newStyle.cloneStyleFrom(originalStyle); // 复制原样式newStyle.setAlignment(HorizontalAlignment.CENTER);newStyle.setVerticalAlignment(VerticalAlignment.CENTER);newStyle.setWrapText(true);cell.setCellStyle(newStyle);
