有点强！excel的导入导出用它就够了！

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);
}

/**
 * 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);
    }
}

效果如下：

网页中导出-多个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);
}

/**
 * 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();
        }
    }
}

/**
 * 用于临时存储每个sheet的数据信息
 */
public 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;
    }
}

效果如下：

网页中自定义表头导出excel

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

/**
 * 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();
       }
   }
}

效果如下：

网页中，根据模板导出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);
}

/**
 * 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);
    }
}

模板如下:
模板中 用{} 来表示你要用的变量，模板中{.} 多了个点 表示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();
        }
    }
}

若是需要获取更详细的内容，请前往
开源地址：https://github.com/alibaba/easyexcel