EasyExcelUtils

提供常见的文件同步和异步读取、文件导出、模板填充、自定义表头、自定义内容、从指定行开始读取数据、多Sheet导出等方法

public class EasyExcelUtils {

    /**
     * 同步无模型读（默认读取sheet0,从第2行开始读）
     *
     * @param filePath excel文件的绝对路径
     */
    public static List

EasyExcelWriterFactory

文件导出和模板填充实现多Sheet写入（链式使用）

public class EasyExcelWriterFactory {

    private int sheetNo = 0;
    private ExcelWriter excelWriter = null;

    public EasyExcelWriterFactory(OutputStream outputStream) {
        excelWriter = EasyExcel.write(outputStream).build();
    }

    public EasyExcelWriterFactory(File file) {
        excelWriter = EasyExcel.write(file).build();
    }

    public EasyExcelWriterFactory(String filePath) {
        excelWriter = EasyExcel.write(filePath).build();
    }

    /**
     * 链式模板表头写入
     *
     * @param headClazz 表头格式
     * @param data      数据 List 或者List>
     * @return
     */
    public EasyExcelWriterFactory writeModel(Class headClazz, List data) {
        excelWriter.write(data, EasyExcel.writerSheet(this.sheetNo++).head(headClazz).build());
        return this;
    }


    /**
     * 链式模板表头写入
     *
     * @param headClazz 表头格式
     * @param data      数据 List 或者List>
     * @return
     */
    public EasyExcelWriterFactory writeModel(Class headClazz, List data, String sheetName) {
        excelWriter.write(data, EasyExcel.writerSheet(this.sheetNo++, sheetName).head(headClazz).build());
        return this;
    }

    /**
     * 链式自定义表头写入
     *
     * @param head
     * @param data      数据 List 或者List>
     * @param sheetName
     * @return
     */
    public EasyExcelWriterFactory write(List> head, List data, String sheetName) {
        excelWriter.write(data, EasyExcel.writerSheet(this.sheetNo++, sheetName).head(head).build());
        return this;
    }

    /**
     * 使用此类结束后，一定要关闭流
     */
    public void finish() {
        excelWriter.finish();
    }
}

ExcelListener

文件读取大数据和多Sheet导入监听器（使用线程池和批量插入方法）

public class ExcelListener extends AnalysisEventListener {

    Logger log = LoggerFactory.getLogger(getClass());

    private static final Integer BATCH_SIZE = 1000;

    private Integer sheetNo;

    private Executor executor;

    private List dataList = new ArrayList();

    public ExcelListener(Integer sheetNo, Executor executor) {
        this.sheetNo = sheetNo;
        this.executor = executor;
    }

    @Override
    public void invoke(T data, AnalysisContext analysisContext) {
        log.info("解析到一条数据：{}", JSON.toJSONString(data));
        dataList.add(data);
        if (dataList.size() >= BATCH_SIZE) {
            CompletableFuture.runAsync(() -> {
                // 业务操作
                // saveToDB(dataList);
            }, executor);
            dataList.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("已解析完所有数据!");
        if (!dataList.isEmpty()) {
            CompletableFuture.runAsync(() -> {
                // 业务操作
                // saveToDB(dataList);
            }, executor);
            dataList.clear();
        }
    }

    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException convertException = (ExcelDataConvertException) exception;
            Integer row = convertException.getRowIndex();
            log.error("sheetNo：{}，第{}行数据转换失败，异常信息：{}", sheetNo, row, exception.getMessage());
        } else {
            log.error("导入其他异常信息：{}", exception.getMessage());
        }
    }
}

使用案例

@GetMapping("/download1")
public void download1(HttpServletResponse response) {
    try {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");

        User user = new User();
        user.setUserId(123);
        user.setName("as");
        user.setPhone("15213");
        user.setEmail("5456");
        user.setCreateTime(new Date());
        EasyExcelUtils.write(response.getOutputStream(), User.class, Arrays.asList(user));
    } catch (Exception e) {
        e.printStackTrace();
    }
}

@GetMapping("/download2")
public void download2(HttpServletResponse response) {
    try {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");

        User user = new User();
        user.setUserId(123);
        user.setName("as");
        user.setPhone("15213");
        user.setEmail("5456");
        user.setCreateTime(new Date());
        EasyExcelUtils.write(response.getOutputStream(), User.class, Arrays.asList(user), 2);
    } catch (Exception e) {
        e.printStackTrace();
    }
}

@GetMapping("/download3")
public void download3(HttpServletResponse response) {
    try {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");

        User user = new User();
        user.setUserId(123);
        user.setName("as");
        user.setPhone("15213");
        user.setEmail("5456");
        user.setCreateTime(new Date());
        EasyExcelUtils.writeWithSheets(response.getOutputStream())
                .writeModel(User.class, Arrays.asList(user))
                .writeModel(User.class, Arrays.asList(user))
                .finish();
    } catch (Exception e) {
        e.printStackTrace();
    }
}