EasyExcel详细导出案例

1.EasyExcel简单介绍

EasyExcel工具是阿里的一个操作excel的开源项目，对现有的POI框架进行性能优化，解决了大数据量时内存溢出的问题。同时封装的更加简单灵活，适合初学者上手。

EasyExcel仓库

EasyExcel官方文档

2.快速开始

maven依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.2</version>
</dependency>

本地导出excel、通过web请求导出Excel请直接移步官方文档

本文主要介绍通过web请求导出Excel的复杂案例（excel模板、拦截器，填充功能）

需求介绍：

• 导出excel，要求根据单元格内容动态改变字体样式（拦截器实现）
• 最好能够保证单元格表头、内容美观度（模板实现）
• 在excel文档末尾有补充说明文字（填充功能实现）

具体需求文档样例

模板

ExportExcelServiceImpl

@Service
@Slf4j
public class ExportExcelServiceImpl implements ExportExcelService {

    private static Random random = new Random();

    @Override
    public void exportStudentInfoExcel(HttpServletResponse response) throws IOException {
        try {
            export(response);
        } catch (IOException e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<String, String>();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }
    }

    private void export(HttpServletResponse response) throws IOException {
        String templatePath = ExportExcelServiceImpl.class.getResource("/templates/score.xlsx").toString().replace("file:/","");
        List<StudentInfo> list = getStudentInfoList();

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("月考成绩.xlsx", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

        ExcelWriter excelWriter = EasyExcel
                .write(response.getOutputStream())
                .registerWriteHandler(new CellWriteHandler()) // 自定义拦截器
                .withTemplate(templatePath)
                .autoCloseStream(Boolean.FALSE) // 这里需要设置不关闭流
                .build();
        WriteSheet writeSheet = EasyExcel.writerSheet("测试").build();

        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
        excelWriter.fill(list,fillConfig, writeSheet);
        //设置填充内容
        Map<String,String> map = new HashMap<>();
        map.put("grade","高三10班");
        map.put("key","说明");
        map.put("value","* 英语成绩达到80分以上才算优秀");
        excelWriter.fill(map, writeSheet);

        excelWriter.finish();
    }

    /**
     * @return 随机生成学生成绩列表
     */
    private List<StudentInfo> getStudentInfoList(){
        List<StudentInfo> list = Lists.newLinkedList();
        for (int i = 0; i < 15; i++) {
            StudentInfo studentInfo = new StudentInfo();
            studentInfo.setName("张三" + i);
            //随机生成450-600之间的总分
            int totalScore = random.nextInt(151) + 450;
            studentInfo.setTotalScore(String.valueOf(totalScore));
            //随机生成成绩在90-150之间的分数
            int englishScore = random.nextInt(61) + 90;
            studentInfo.setEnglishScore(String.valueOf(englishScore));
            list.add(studentInfo);
        }
        return orderByTotalScoreDesc(list);
    }

    private List<StudentInfo> orderByTotalScoreDesc(List<StudentInfo> list){
        list.sort(((o1, o2) -> Integer.parseInt(o2.getTotalScore()) - Integer.parseInt(o1.getTotalScore())));
        for (int i = 0; i < list.size(); i++) {
            list.get(i).setNum(String.valueOf(i + 1));
        }
        return list;
    }
}

CellWriteHandler拦截器

public class CellWriteHandler extends AbstractCellWriteHandler {

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        int columnIndex = cell.getColumnIndex();
        int rowIndex = cell.getRowIndex();
        if(rowIndex > 1){
        	//判断是否是第五列单元格,(单元格下标从0开始的)
            if(columnIndex == 4){
                String stringCellValue = cell.getStringCellValue();
                long cellValue = Long.parseLong(stringCellValue);
                //如果第5列单元格内容大于80，则将字体样式设置为红色
                if(cellValue > 80){
                    Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
                    CellStyle cellStyle = workbook.createCellStyle();
                    //设置单元格边框类型
                    cellStyle.setBorderTop(BorderStyle.THIN);
                    cellStyle.setBorderBottom(BorderStyle.THIN);
                    cellStyle.setBorderLeft(BorderStyle.THIN);
                    cellStyle.setBorderRight(BorderStyle.THIN);
                    //水平居中
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    Font font = workbook.createFont();
                    //字体设置为红色
                    font.setColor(IndexedColors.RED.index);
                    cellStyle.setFont(font);
                    cell.setCellStyle(cellStyle);
                }
            }
        }
    }
}

StudentInfo

/**
* 建议将业务相关的成员变量类型设置为String类型，以便后面拦截器内对单元格的内容进行强转
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class StudentInfo {
    //学号 
    private String num;
    //姓名
    private String name;
    //总分
    private String totalScore;
    //是否进前十
    private String isTop3;
    //英语成绩
    private String englishScore;
}