EasyExcel导出

EasyExcel导出

以员工实体类为例：

 
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Employee implements Serializable {
 
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private Long id;
 
    @ExcelProperty("员工名")
    private String name;
 
    @ExcelProperty("员工年龄")
    private Integer age;
 
    @ExcelProperty(value = "员工工资",index = 3)
    private BigDecimal salary;
 
    @ColumnWidth(15)
    @DateTimeFormat("yyyy年MM月dd日")
    @ExcelProperty(value = "入职时间",index = 2)
    private Date entryTime;
 
}

相关注解：

@ExcelIgnore：忽略导出的字段信息

@ExcelProperty：设置导出的字段名（表头），导出的字段默认按照顺序从0开始排序，如果想指定导出字段的在第几列的话，可以设置对应的index，index的值：字段所在列数-1，类似数组下标索引

@DateTimeFormat：定义日期格式

@ColumnWidth：设置字段宽度

控制器实现：

@GetMapping("/export")
public void exportEmployeeData(HttpServletResponse response) throws IOException {
    // 设置响应头
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("utf-8");
    String fileName = URLEncoder.encode("员工信息", "UTF-8").replaceAll("\\+", "%20");
    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    
    // 查询员工信息数据
    List<Employee> employeeDataList = getEmployeeDataList();
    
    // 写入Excel
    EasyExcel.write(response.getOutputStream(), Employee.class)
             .sheet("员工信息")
             .doWrite(employeeDataList);
}

代码理解

1.代码：

public class ReflectionOut {
    public static void main(String[] args) throws IOException {
        List<List<List<String>>> heads = new ArrayList<>();
        List<List<List<Object>>> datasList = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            List<List<String>> head = new ArrayList<>();
            head.add(Collections.singletonList("表头1-" + (i + 1)));
            head.add(Collections.singletonList("表头2-" + (i + 1)));
            head.add(Collections.singletonList("表头3-" + (i + 1)));
            head.add(Collections.singletonList("表头4-" + (i + 1)));
            head.add(Collections.singletonList("表头5-" + (i + 1)));
            heads.add(head);
        }
        for (int i = 0; i < 10; i++) {
            List<List<Object>> datas = new ArrayList<>();
            for (int d = 0; d < 10; d++) {
                List<Object> data = new ArrayList<>();
                data.add("数据1-" + (d + 1));
                data.add("数据2-" + (d + 1));
                data.add("数据3-" + (d + 1));
                data.add("数据4-" + (d + 1));
                data.add("数据5-" + (d + 1));
                data.add("数据6-" + (d + 1));
                datas.add(data);
            }
            datasList.add(datas);
        }
        FileOutputStream fileOutputStream = new FileOutputStream(new File("C:\\Users\\03062000419\\Desktop\\输出\\out.xlsx"));
        try (ExcelWriter build = EasyExcel.write(fileOutputStream).build()) {
            for (int i = 0; i < heads.size(); i++) {
                WriteSheet excelListSheet = EasyExcel.writerSheet(i, "sheet"+i).head(heads.get(i)).build();
                build.write(datasList.get(i), excelListSheet);
            }
        }
        fileOutputStream.close();
    }
}

2.输出形式：

动态导出

1.动态表头导出：

public void dynamicExport(HttpServletResponse response) throws IOException {
    // 模拟动态表头数据
    List<List<String>> headList = new ArrayList<>();
    headList.add(Collections.singletonList("姓名"));
    headList.add(Collections.singletonList("年龄"));
    headList.add(Collections.singletonList("邮箱"));
    
    // 模拟动态数据
    List<List<Object>> dataList = new ArrayList<>();
    dataList.add(Arrays.asList("张三", 25, "zhangsan@example.com"));
    dataList.add(Arrays.asList("李四", 30, "lisi@example.com"));
    
    // 设置响应头
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("utf-8");
    String fileName = URLEncoder.encode("动态导出", "UTF-8").replaceAll("\\+", "%20");
    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    
    // 写入Excel
    EasyExcel.write(response.getOutputStream())
            .head(headList)
            .sheet("Sheet1")
            .doWrite(dataList);
}

2.动态列导出：

public void dynamicColumnExport(HttpServletResponse response, List<String> columns) throws IOException {
    // 根据传入的columns动态生成表头
    List<List<String>> headList = columns.stream()
            .map(Collections::singletonList)
            .collect(Collectors.toList());
    
    // 获取对应列的数据
    List<List<Object>> dataList = getDataByColumns(columns);
    
    // 导出
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("utf-8");
    String fileName = URLEncoder.encode("动态列导出", "UTF-8").replaceAll("\\+", "%20");
    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    
    EasyExcel.write(response.getOutputStream())
            .head(headList)
            .sheet("Sheet1")
            .doWrite(dataList);
}

3.动态合并单元格：

public void dynamicMergeExport(HttpServletResponse response) throws IOException {
    // 准备数据
    List<DemoData> list = getData();
    
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("utf-8");
    String fileName = URLEncoder.encode("合并单元格导出", "UTF-8").replaceAll("\\+", "%20");
    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    
    // 注册自定义合并策略
    EasyExcel.write(response.getOutputStream(), DemoData.class)
            .registerWriteHandler(new CellWriteHandler() {
                @Override
                public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, 
                        List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
                    // 实现自定义合并逻辑
                    if (!isHead && cell.getColumnIndex() == 0) {
                        // 第一列相同内容合并
                        mergeSameContent(cell);
                    }
                }
            })
            .sheet("Sheet1")
            .doWrite(list);
}