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;@Resourceprivate ResourceLoader resourceLoader;private final String TEMPLATE_EXCEPTION_EXCEL_XLSX = "classpath:template/exception_excel.xlsx";@Overridepublic 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<ExceptionExcelVo> crossdockSeaFinanceVoList = baseMapper.listExceptionExcelVo(request);if (CollectionUtils.isNotEmpty(crossdockSeaFinanceVoList)) {AtomicInteger index = new AtomicInteger(0);AtomicReference<String> 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 id="listExceptionExcelVo" resultType="com.ctsfreight.oseb.common.vo.excel.ExceptionExcelVo">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 packageNumFROM (SELECT id, order_no, container_no, total_amount, create_timeFROM exception_case_summaryWHERE delete_flag = 0<if test="request.summaryIdList != null and !request.summaryIdList.isEmpty()">AND id IN<foreach item="id" collection="request.summaryIdList" open="(" separator="," close=")">#{id}</foreach></if>ORDER BY create_time DESCLIMIT 100) ecsLEFT JOIN exception_case_sorting_item ecsitON ecs.id = ecsit.exception_case_summary_idWHERE ecsit.delete_flag = 0ORDER BY ecs.create_time DESC;</select>

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;/*** <p>*  信息VO* </p>***/
@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<Integer> pkColumnIndex = new ArrayList<>();/*** 需要合并的列的下標集合*/private List<Integer> needMergeColumnIndex = new ArrayList<>();/*** DTO數據類型*/private Class<?> elementType;public CustomRowMergeStrategy(Class<?> elementType) {this.elementType = elementType;}@Overridepublic void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {// 如果是標題,則直接返回if (isHead) {return;}// 獲取當前sheetSheet 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) {// 獲取當前sheetSheet 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 {@Overrideprotected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {// 如果你也希望表頭居中，可以在這里設置setCellStyle(cell);}@Overrideprotected 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);