当前位置：首页 > news >正文

EasyExcel 自定义头信息导出

news 2025/8/13 1:51:51

需求：需要在导出 excel时，合并单元格自定义头信息(动态生成)，然后才是字段列表头即导出数据。

EasyExcel - 使用table去写入：https://easyexcel.opensource.alibaba.com/docs/current/quickstart/write#%E4%BD%BF%E7%94%A8table%E5%8E%BB%E5%86%99%E5%85%A5

一、代码实现

1、查询导出数据

1.1 导出实体类

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.converters.localdatetime.LocalDateTimeStringConverter;
import lombok.Data;import java.io.Serializable;
import java.time.LocalDateTime;/*** 用户信息 导出DTO*/
@Data
@ExcelIgnoreUnannotated
public class UserExportExcelDTO implements Serializable {private static final long serialVersionUID = -5255117385546252447L;/*** 用户ID*/@ExcelIgnoreprivate Long userId;/*** 用户名*/@ExcelProperty(value = "用户名")private String username;/*** 年龄*/@ExcelProperty(value = "年龄")private Integer age;/*** 电话号码*/@ExcelProperty(value = "电话号码")private String phone;/*** 昵称*/@ExcelProperty(value = "昵称")private String nickname;/*** 姓名*/@ExcelProperty(value = "姓名")private String name;/*** 创建时间* 使用 converter或者 @DateTimeFormat格式化时间，如果两者同时使用，@DateTimeFormat优先级高*/@ExcelProperty(value = "创建时间", converter = LocalDateTimeStringConverter.class) // yyyy-MM-dd HH:mm:ss@DateTimeFormat("yyyy年MM月dd日")private LocalDateTime createTime;}

1.2 查询导出数据方法

    @Overridepublic List<UserExportExcelDTO> listExportExcelData(UserPageQueryRequest pageRequest) {pageRequest.setCurrentPage(1L);pageRequest.setPageSize((long) Integer.MAX_VALUE);BasePageResult<UserPageDTO> basePageResult = pageQuery(pageRequest);if(!basePageResult.isSuccess()){return Collections.EMPTY_LIST;}return basePageResult.getPageList().stream().map(userPageDTO -> {UserExportExcelDTO exportExcelDTO = new UserExportExcelDTO();BeanUtils.copyProperties(userPageDTO, exportExcelDTO);return exportExcelDTO;}).collect(Collectors.toList());}

2、EasyExcel使用 table写入

    /*** 用户导出接口** @param pageRequest 分页查询请求体*/@SneakyThrows@ApiOperation(value = "用户导出接口")@GetMapping("/exportExcel")public void exportExcel(HttpServletResponse response, @RequestBody UserPageQueryRequest pageRequest) {response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");String fileName = URLEncoder.encode("用户信息", StandardCharsets.UTF_8.name()).replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");// 查询导出数据List<UserExportExcelDTO> exportExcelDataList = userService.listExportExcelData(pageRequest);// 自定义头信息StringBuilder headName = new StringBuilder();LocalDateTime startTime = pageRequest.getCreateTimeStart();LocalDateTime endTime = pageRequest.getCreateTimeEnd();if (startTime != null && endTime != null) {String startTimeStr = LocalDateTimeUtil.format(startTime, DatePattern.CHINESE_DATE_PATTERN);String endTimeStr = LocalDateTimeUtil.format(endTime, DatePattern.CHINESE_DATE_PATTERN);headName.append(startTimeStr).append("至").append(endTimeStr);}headName.append("时间段的用户信息");List<List<String>> headList = new ArrayList<>();List<String> head = new ArrayList<>();head.add(headName.toString());headList.add(head);/*** 创建 ExcelWriter* 1.创建写入的工作表，不需要表头。命名为"sheet1"。*/ExcelWriter excelWriter = null;try {excelWriter = EasyExcelFactory.write(response.getOutputStream()).build();} catch (IOException e) {log.error("导出车辆在线率统计异常：e=", e);}WriteSheet writeSheet = EasyExcel.writerSheet("sheet1").needHead(Boolean.FALSE).build();/*** 2.创建并写入表格数据，设置表头为 headList，需要表头。* tableNo：表格序号，从0开始。* OnceAbsoluteMergeStrategy：创建一个合并单元格策略。*/WriteTable writeTable1 = EasyExcel.writerTable(0).head(headList).registerWriteHandler(new OnceAbsoluteMergeStrategy(0, 2, 0, 5)).needHead(Boolean.TRUE).build();// 写入空数据到 writeSheetexcelWriter.write(new ArrayList<>(), writeSheet, writeTable1);/*** 3.创建写入表格，基于AnysCarOfflineStatisticsVo类定义表头，需要表头。* writerTable(3)：因为 writeTable1从0开始，并合并了 3行，所以 writeTable2是从第4个行（tableNo = 3）开始写入。* SimpleColumnWidthStyleStrategy：设置列宽策略*/WriteTable writeTable2 = EasyExcel.writerTable(3).head(UserExportExcelDTO.class).registerWriteHandler(new SimpleColumnWidthStyleStrategy(25)).relativeHeadRowIndex(2).needHead(Boolean.TRUE).build();// 写入导出数据到 writeSheetexcelWriter.write(exportExcelDataList, writeSheet, writeTable2);excelWriter.finish();}