SpringBoot集成EasyPoi实现Excel导入导出

欢迎阅览

---

作者介绍：
本人Java特工，代号：Cris Li ； 中文名：克瑞斯理
简书地址: https://www.jianshu.com/u/c508b0afaaee
CSDN地址: https://blog.csdn.net/jianli95
个人纯洁版博客: https://lijian69.github.io/blog/

---

为什么要使用 EasyPoi

分析：当下流行的Excel导出的Poi工具

类型	优缺点	地址
EasyPOI	作者推荐：快，方便，集成性高	http://easypoi.mydoc.io/
EasyExcel	alibaba出品的，也是快，方便，但是在样式方面 做的不够好	https://github.com/alibaba/easyexcel
POI	poi工具的老大哥，功能超级全，但是学习难度较高，代码的数量较多

EasyPoi的详细介绍

easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员
就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板
语言(熟悉的表达式语法),完成以前复杂的写法

独特的功能

• 基于注解的导入导出,修改注解就可以修改Excel
• 支持常用的样式自定义
• 基于map可以灵活定义的表头字段
• 支持一堆多的导出,导入
• 支持模板的导出,一些常见的标签,自定义标签
• 支持HTML/Excel转换,如果模板还不能满足用户的变态需求,请用这个功能
• 支持word的导出,支持图片,Excel

使用步骤

1.maven 或者 Gradle 引入相关依赖

<dependencies>
        <!-- 集成easypoi组件 .导出excel http://easypoi.mydoc.io/ -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.2.0</version>
        </dependency>
        
        <!-- 这个依赖相当于上面 easypoi-base、easypoi-web、easypoi-annotation这3个依赖,可以引入上面3个依赖，也可以引入下面这一个，两个方案二选一-->
    <!--    <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>3.2.0</version>
        </dependency> -->
    </dependencies>

2.定义实体对象(也是 你下载的对象，这里只是简单注解)

@ExcelTarget("20")
@Data
public class User implements java.io.Serializable{
    @Excel(name = "id", width=15)
    @NotBlank(message = "该字段不能为空")
    private Integer id;
 
    @Excel(name = "姓名", orderNum = "0", width=30)
    private String name;
 
    @Excel(name = "性别", replace = { "男_1", "女_2" }, orderNum = "1", width=30)
    private String sex;
 
    @Excel(name = "生日", exportFormat = "yyyy-MM-dd",  orderNum = "2", width=30)
    private String birthday;
}

3. Excel导入导出工具类 、封装了调用EasyPoi APi底层接口的Excel导入导出工具类，直接调用工具类即可

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
 
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
 
//Excel导入导出工具类
public class ExcelUtils {
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
            boolean isCreateHeader, HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }
 
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
            HttpServletResponse response) {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }
 
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        defaultExport(list, fileName, response);
    }
 
    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response,
            ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null)
            ;
        downLoadExcel(fileName, response, workbook);
    }
 
    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            // throw new NormalException(e.getMessage());
        }
    }
 
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null)
            ;
        downLoadExcel(fileName, response, workbook);
    }
 
    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            // throw new NormalException("模板不能为空");
        } catch (Exception e) {
            e.printStackTrace();
            // throw new NormalException(e.getMessage());
        }
        return list;
    }
 
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows,
            Class<T> pojoClass) {
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        } catch (NoSuchElementException e) {
            // throw new NormalException("excel文件不能为空");
        } catch (Exception e) {
            // throw new NormalException(e.getMessage());
            System.out.println(e.getMessage());
        }
        return list;
    }
 
}

4.导出即可

List<User> personList = userService.findAll();
// 导出操作
ExcelUtils.exportExcel(personList, "easypoi导出功能", "导出sheet1", User.class, "测试user.xls", response);