EasyExcel是一款由阿里项目组提供的，基于Java的简单、省内存的读写Excel的来源项目。

1.添加依赖

<!-- easyexcel 依赖-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.0-beta3</version>
</dependency>

2.在实体类中通过注解，标识表头信息

@Data
@EqualsAndHashCode()
public class Student implements Serializable {

    private static final long serialVersionUID = -8969368116719425827L;

    @ExcelIgnore //忽略该字段
    private Integer id;

    @ExcelProperty("学号")
    private Integer stuNum;

    @ExcelProperty("姓名")
    private String name;

    @ExcelProperty("电话")
    private String phone;

    @ColumnWidth(20) //设置列宽
    @ExcelProperty("所在班级")
    private String className;

}

3.导出Excel数据

网页中导出-单个sheet

@GetMapping("download/simple")
public void downloadSimpleExcel(HttpServletResponse response) throws IOException {
    //getStudents() 数据源
    ExcelUtil.downloadSimpleExcel("学生信息",getStudents(),response);
}

效果如下:

网页中导出-多个sheet

@GetMapping("download/multipleSheet")
public void downloadMultipleSheetExcel(HttpServletResponse response) throws IOException {
    List<ExcelUtil.MultipleSheetMode> multipleSheetModes = new CopyOnWriteArrayList<>();
    multipleSheetModes.add(new ExcelUtil.MultipleSheetMode("学生",getStudents()));
    multipleSheetModes.add(new ExcelUtil.MultipleSheetMode("课程",getClazzs()));
    ExcelUtil.downloadMultipleSheetExcel("学生信息",multipleSheetModes,response);
}

效果如下:

网页中自定义表头导出excel

@GetMapping("download/dynamicHead")
public void downloadDynamicHeadExcel(HttpServletResponse response) throws IOException {
    List<String> head = Arrays.asList("学号","姓名","电话","所属班级");
    ExcelUtil.downloadDynamicHeadExcel("学生信息",getStudents(),head,response);
}

效果如下:

网页中，根据模板导出excel

@GetMapping("download/template")
public void downloadExcelByTemplate(HttpServletResponse response) throws IOException {
    String filePath = "src/main/resources/templates/fill/123.xlsx";
    String fileName = "学生信息表";
    ExcelUtil.downloadExcelByTemplate(filePath,fileName,getStudents(),response);
}

模板如下:

模板中 用{} 来表示你要用的变量，模板中{.} 多了个点 表示list

4.导入Excel数据

从excel中读取单个sheet

@GetMapping("read/one")
public void readExcel(){
    String fileName = "src/main/resources/templates/fill/学生信息.xlsx";
    EasyExcel.read(fileName, Student.class, new PageReadListener<Student>(dataList -> {
        for (Student data : dataList) {
            //todo 业务逻辑
            System.out.println(data);
        }
    })).sheet().doRead();
}

从excel中读取多个sheet

@GetMapping("read/multipleSheet")
public void readMultipleSheetExcel() {
    String fileName = "src/main/resources/templates/fill/学生信息.xlsx";

    ExcelReader excelReader = null;
    try {
        excelReader = EasyExcel.read(fileName).build();

        ReadSheet readSheet1 =
                EasyExcel.readSheet(0).head(Student.class).registerReadListener(new PageReadListener<Student>(dataList -> {
                    for (Student data : dataList) {
                        //todo 业务逻辑
                        System.out.println(data);
                    }
                })).build();
        ReadSheet readSheet2 =
                EasyExcel.readSheet(1).head(Clazz.class).registerReadListener(new PageReadListener<Clazz>(dataList -> {
                    for (Clazz data : dataList) {
                        //todo 业务逻辑
                        System.out.println(data);
                    }
                })).build();
        excelReader.read(readSheet1,readSheet2);
    } finally {
        if (excelReader != null) {
            // 关闭流
            excelReader.finish();
        }
    }
}

5.工具类

@Slf4j
public class ExcelUtil {


    /**
     * web 导出单个sheet的excel，
     *
     * @param fileName excel 文件名
     * @param data 数据源
     * @param response  HttpServletResponse
     * @throws IOException 异常
     */
    public static void downloadSimpleExcel(String fileName, List<?> data, HttpServletResponse response) throws IOException {

        if(CollectionUtils.isEmpty(data)){
            return;
        }

        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码
            fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), data.get(0).getClass()).sheet("sheet")
                    .doWrite(()->{
                        return data;
                    });
        } catch (IOException e) {
            log.error("excel文件导出失败!",e);
        }
    }

    /**
     * web 导出多个sheet的excel，
     *
     * @param fileName excel文件名称
     * @param multipleSheetModes 数据源，里面包括sheet名称，以及sheet内容
     * @param response HttpServletResponse
     * @throws IOException 异常
     */
    public static void downloadMultipleSheetExcel(String fileName, List<MultipleSheetMode> multipleSheetModes, HttpServletResponse response) throws IOException {

        if(CollectionUtils.isEmpty(multipleSheetModes)){
            return;
        }

        ExcelWriter excelWriter = null;
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码
            fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

            excelWriter = EasyExcel.write(response.getOutputStream()).build();
            int i = 0;
            for (MultipleSheetMode multipleSheetMode : multipleSheetModes) {
                // 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样
                WriteSheet writeSheet = EasyExcel.writerSheet(i, multipleSheetMode.getSheetName()).head(multipleSheetMode.getData().get(0).getClass()).build();
                excelWriter.write(multipleSheetMode.getData(), writeSheet);
                i++;
            }
        } catch (IOException e){
            log.error("excel文件导出失败!",e);
        } finally {
            // 调用finish，关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    /**
     * web 自定义表头导出单个sheet的excel,
     * @param fileName excel文件名称
     * @param data 数据源
     * @param head 表头信息
     * @param response HttpServletResponse
     * @throws IOException
     */
    public static void downloadDynamicHeadExcel(String fileName, List<?> data, List<String> head, HttpServletResponse response) throws IOException {

        if(CollectionUtils.isEmpty(data)){
            return;
        }

        ExcelWriter excelWriter = null;
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

            excelWriter = EasyExcel.write(response.getOutputStream()).build();
            WriteSheet writeSheet = null;

            if(!CollectionUtils.isEmpty(head)){
                List<List<String>> list = new ArrayList<>();
                head.forEach(h->{list.add(Collections.singletonList(h));});
                writeSheet = EasyExcel.writerSheet("sheet").head(list).build();
            }else {
                writeSheet = EasyExcel.writerSheet("sheet").head(data.get(0).getClass()).build();
            }
            excelWriter.write(data, writeSheet);
        } catch (IOException e){
            log.error("excel文件导出失败!",e);
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    /**
     * web 根据excel模板导出excel
     * @param filePath 模板文件的绝对路径
     * @param data 数据源
     * @param response HttpServletResponse
     */
    public static void downloadExcelByTemplate(String filePath, String fileName, List<?> data, HttpServletResponse response){
        if(CollectionUtils.isEmpty(data)){
            return;
        }
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

            EasyExcel.write(response.getOutputStream()).withTemplate(filePath).sheet()
                    .doFill(() -> {
                        return data;
                    });

        } catch (FileNotFoundException e){
            log.error("找不到模板文件或文件路径错误，文件：{}",filePath);
        } catch (IOException e) {
            log.error("excel文件导出失败!",e);
        }
    }

    /**
     * 用于临时存储每个sheet的数据信息
     */
    public static class MultipleSheetMode{

        public MultipleSheetMode() {
        }

        public MultipleSheetMode(String sheetName, List<?> data) {
            this.sheetName = sheetName;
            this.data = data;
        }

        /**
         * sheet名称
         */
        private String sheetName;
        /**
         * 填充的数据
         */
        private List<?> data;

        public String getSheetName() {
            return sheetName;
        }

        public void setSheetName(String sheetName) {
            this.sheetName = sheetName;
        }

        public List<?> getData() {
            return data;
        }

        public void setData(List<?> data) {
            this.data = data;
        }
    }
}

若是需要获取更详细的内容，请前往 

开源地址：https://Github.com/alibaba/easyexcel