springboot整合easypoi

springboot整合easypoi

官方文档
旧地址:http://easypoi.mydoc.io/
新地址:http://doc.wupaas.com/docs/easypoi/

依赖

  <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.0.3</version>
        </dependency>

导出导入工具类

public class FileUtil {

    /**
     * 导出列表数据为对象类型
     * @param list 列表集合
     * @param title 主题
     * @param sheetName 工作页名称
     * @param pojoClass 列表类型
     * @param fileName  导出后的文件名
     * @param response
     */
    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));
    }

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

    //下载到页面
    public 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) {
            System.out.println("导出异常");
        }
    }


    /**
     * 导出列表数据为map类型
     * @param list
     * @param fileName
     * @param response
     */
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
        defaultExport(list, fileName, response);
    }
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.XSSF);
        if (workbook != null);
        downLoadExcel(fileName, response, workbook);
    }


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

    /**
     * 数据导入
     * @param file 导入文件
     * @param titleRows  标题行数
     * @param headerRows 列名行数
     * @param pojoClass  实例类型
     * @return
     */
    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());*/
        }
        return list;
    }
}

Excel导出简单对象列表

创建列表实例StudentEntity

@ExcelTarget("studentEntity")
public class StudentEntity implements Serializable {

    @Excel(name = "学生编号",orderNum ="0" )
    private int id;
    @Excel(name = "学生姓名", height = 20, width = 15,orderNum ="1" )
    private String name;
    @Excel(name = "学生性别", replace = { "男_1", "女_2" }, suffix = "生",orderNum ="2")
    private int sex;
    @Excel(name = "年龄",suffix = "岁",orderNum ="3" )
    private int age;
    @Excel(name = "出生日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd",width = 20 ,orderNum ="4")
    private Date birthday;
    @Excel(name = "家庭住址", height = 20, width = 50,orderNum ="5" )
    private String address;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getSex() {
        return sex;
    }

    public void setSex(int sex) {
        this.sex = sex;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

导出接口

 /**
     * excel导出StudentEntity
     * @return
     */
    @RequestMapping("exportStudentEntity")
    public void exportStudentEntity(HttpServletResponse response){
        //从数据库获取需要导出的数据
        List<StudentEntity> list=new ArrayList<>();
        for(int i=0;i<20;i++){
            StudentEntity studentEntity = new StudentEntity();
            studentEntity.setId(i+1);
            studentEntity.setName("zhansan"+i);
            if(i%2==0){
                studentEntity.setSex(1);
            }else{
                studentEntity.setSex(2);
            }
            studentEntity.setAge(20+(i%5));
            studentEntity.setBirthday(new Date());
            studentEntity.setAddress("随便编辑一个地址吧");
            list.add(studentEntity);
        }
        //导出操作
        FileUtil.exportExcel(list,"学生信息详情表","学生信息",StudentEntity.class,"学生信息-"+new Date().getTime()+".xls",response);
    }

在这里插入图片描述

Excel导出复杂对象列表

课程CourseEntity中包括老师和学生列表
创建实例CourseEntity

@ExcelTarget("courseEntity")
public class CourseEntity implements Serializable {

    @Excel(name = "课程编号",orderNum ="0" )
    private String   id;
    /** 课程名称 */
    @Excel(name = "课程名称", orderNum = "1", width = 25)
    private String  name;

    /** 老师主键 */
    @ExcelEntity(id = "teacherEntity")
    private TeacherEntity teacherEntity;

    @ExcelCollection(name = "学生列表",orderNum = "4")
    private List<StudentEntity> students;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public TeacherEntity getTeacherEntity() {
        return teacherEntity;
    }

    public void setTeacherEntity(TeacherEntity teacherEntity) {
        this.teacherEntity = teacherEntity;
    }

    public List<StudentEntity> getStudents() {
        return students;
    }

    public void setStudents(List<StudentEntity> students) {
        this.students = students;
    }
}

创建实例TeacherEntity

@ExcelTarget("teacherEntity")
public class TeacherEntity implements Serializable {
   /* @Excel(name = "老师编号",orderNum ="0" )*/
    private int id;
    @Excel(name = "老师姓名", height = 20, width = 15,orderNum ="2" )
    private String name;
    @Excel(name = "性别", replace = { "男_1", "女_2" }, suffix = "生",orderNum ="3")
    private int sex;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getSex() {
        return sex;
    }

    public void setSex(int sex) {
        this.sex = sex;
    }
}

创建实例TeacherEntity

@ExcelTarget("studentEntity")
public class StudentEntity implements Serializable {

    /*@Excel(name = "学生编号",orderNum ="0" )*/
    private int id;
    @Excel(name = "学生姓名", height = 20, width = 15,orderNum ="1" )
    private String name;
    @Excel(name = "学生性别", replace = { "男_1", "女_2" }, suffix = "生",orderNum ="2")
    private int sex;
    /*@Excel(name = "年龄",suffix = "岁",orderNum ="3" )*/
    private int age;
    /*@Excel(name = "出生日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd",width = 20 ,orderNum ="4")*/
    private Date birthday;
    /*@Excel(name = "家庭住址", height = 20, width = 50,orderNum ="5" )*/
    private String address;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getSex() {
        return sex;
    }

    public void setSex(int sex) {
        this.sex = sex;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

导出接口

 /**
     * excel导出CourseEntity
     * @return
     */
    @RequestMapping("exportCourseEntity")
    public void exportCourseEntity(HttpServletResponse response){
        //从数据库获取需要导出的数据
        List<StudentEntity> list1=new ArrayList<>();
        for(int i=0;i<7;i++){
            StudentEntity studentEntity = new StudentEntity();
            studentEntity.setId(i+1);
            studentEntity.setName("zhansan"+i);
            if(i%2==0){
                studentEntity.setSex(1);
            }else{
                studentEntity.setSex(2);
            }
            studentEntity.setAge(20+(i%5));
            studentEntity.setBirthday(new Date());
            studentEntity.setAddress("随便编辑一个地址吧");
            list1.add(studentEntity);
        }
        List<StudentEntity> list2=new ArrayList<>();
        for(int i=0;i<8;i++){
            StudentEntity studentEntity = new StudentEntity();
            studentEntity.setId(i+7);
            studentEntity.setName("lisi"+i);
            if(i%2==0){
                studentEntity.setSex(1);
            }else{
                studentEntity.setSex(2);
            }
            studentEntity.setAge(20+(i%5));
            studentEntity.setBirthday(new Date());
            studentEntity.setAddress("随便编辑一个地址吧");
            list2.add(studentEntity);
        }

       List<CourseEntity>  courseEntityList=new ArrayList<>();
        CourseEntity courseEntity1 = new CourseEntity();
        courseEntity1.setId("1");
        courseEntity1.setName("数学");
        courseEntity1.setStudents(list1);
        TeacherEntity teacherEntity = new TeacherEntity();
        teacherEntity.setName("王老师");
        teacherEntity.setSex(1);
        courseEntity1.setTeacherEntity(teacherEntity);

        CourseEntity courseEntity2 = new CourseEntity();
        courseEntity2.setId("2");
        courseEntity2.setName("数学");
        courseEntity2.setStudents(list2);
        TeacherEntity teacherEntity2 = new TeacherEntity();
        teacherEntity2.setName("马老师");
        teacherEntity2.setSex(2);
        courseEntity2.setTeacherEntity(teacherEntity);

        courseEntityList.add(courseEntity1);
        courseEntityList.add(courseEntity2);
        //导出操作
        FileUtil.exportExcel(courseEntityList,"课程信息详情表","课程信息",CourseEntity.class,"课程信息-"+new Date().getTime()+".xls",response);
    }

在这里插入图片描述

Excel创建模板导出

导出列表类studentEntity

@ExcelTarget("studentEntity")
public class StudentEntity implements Serializable {

    @Excel(name = "学生编号",orderNum ="0" )
    private int id;
    @Excel(name = "学生姓名", height = 20, width = 15,orderNum ="1" )
    private String name;
    @Excel(name = "学生性别", replace = { "男_1", "女_2" }, suffix = "生",orderNum ="2")
    private int sex;
    @Excel(name = "年龄",suffix = "岁",orderNum ="3" )
    private int age;
    @Excel(name = "出生日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd",width = 20 ,orderNum ="4")
    private Date birthday;
    @Excel(name = "家庭住址", height = 20, width = 50,orderNum ="5" )
    private String address;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getSex() {
        return sex;
    }

    public void setSex(int sex) {
        this.sex = sex;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

创建模板
在这里插入图片描述
导出接口

  @GetMapping("export")
    public void export( HttpServletResponse response) throws Exception {

        List<StudentEntity> list=new ArrayList<>();
        for(int i=0;i<20;i++){
            StudentEntity studentEntity = new StudentEntity();
            studentEntity.setId(i);
            studentEntity.setName("zhansan"+i);
            studentEntity.setSex(1);
            studentEntity.setAge(20+i);
            studentEntity.setBirthday(new Date());
            studentEntity.setAddress("随便写个地址");
            list.add(studentEntity);
        }
        Map<String, Object> params=new HashMap<>();
        String fileNmae = "学生信息-"+new Date().getTime()+".xlsx";
        TemplateExportParams param = new TemplateExportParams("/excel/student.xlsx", true);

        Map<String, Object> data = new HashMap<String, Object>();
        data.put("title", "学生信息");//导出一个对象
        data.put("list", list);//导出list集合
        try {
            Workbook book = ExcelExportUtil.exportExcel(param, data);
            //下载方法
            FileUtil.downLoadExcel(fileNmae,response,book);
        } catch (Exception e) {
            System.out.println("导出模板Excel,失败:" + e);
        }
    }

访问接口
http://localhost:8080/export
在这里插入图片描述

Excel导入

导入的列表实例

@ExcelTarget("studentEntity")
public class StudentEntity implements Serializable {

    @Excel(name = "学生编号",orderNum ="0" )
    private int id;
    @Excel(name = "学生姓名", height = 20, width = 15,orderNum ="1" )
    private String name;
    @Excel(name = "学生性别", replace = { "男_1", "女_2" }, suffix = "生",orderNum ="2")
    private int sex;
    @Excel(name = "年龄",suffix = "岁",orderNum ="3" )
    private int age;
    @Excel(name = "出生日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd",width = 20 ,orderNum ="4")
    private Date birthday;
    @Excel(name = "家庭住址", height = 20, width = 50,orderNum ="5" )
    private String address;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getSex() {
        return sex;
    }

    public void setSex(int sex) {
        this.sex = sex;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "StudentEntity{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex=" + sex +
                ", age=" + age +
                ", birthday=" + birthday +
                ", address='" + address + '\'' +
                '}';
    }
}

接口

    //跳转到EXCEL导入数据页面
    @GetMapping("/jumpFileUpload")
    public String jumpFileUpload(){
        return "/fileUpload";
    }

    /**
     * 导入
     */
    @PostMapping("importExcel")
    @ResponseBody
    public String  importExcel(@RequestParam(name = "file") MultipartFile file) throws Exception {
        List<StudentEntity> list= FileUtil.importExcel(file,1,1,StudentEntity.class);
        for (StudentEntity  studentEntity:list) {
            System.out.println(studentEntity);
        }
        return "SUCCESS";
    }

页面

<!DOCTYPE html>
<html lang="en"  xmlns:th="http://www.thymeleaf.org">
<head>

</head>
<body>
<div style="text-align: center">
    导入EXCEL文件
    <form th:action="@{/importExcel}" method="post" enctype="multipart/form-data">
        <input type="file" name="file"/>
        <input type="submit"  value="导入数据"/>
    </form>
</div>
</body>
</html>

导入模板
在这里插入图片描述
导入结果
在这里插入图片描述

Word导出

导出模板
在这里插入图片描述
导出接口

    @RequestMapping("wordExport")
    public void wordExport(HttpServletResponse response) {
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("university", "北京大学");
        map.put("studentName", "JueYue");
        map.put("AdmissionTime","2021年9月1日上午8点");
        map.put("writingDate","2021年8月1日");
        try {
            XWPFDocument doc = WordExportUtil.exportWord07(
                    "/excel/通知书.docx", map);
            String fileName= "通知书-"+new Date().getTime()+".docx";
            downLoadExcel(fileName,response,doc);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    
    //下载到页面
    public static void downLoadExcel(String fileName, HttpServletResponse response, XWPFDocument doc) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            doc.write(response.getOutputStream());
        } catch (IOException e) {
            System.out.println("导出异常");
        }
    }

访问http://localhost:8080/importExcelwordExport
在这里插入图片描述

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

桀骜浮沉

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值