spring boot 根据Excex模板导出数据（Easy poi）

在pom文件中引入

<!--easypoi-base 导入导出的工具包,可以完成Excel导出,导入,Word的导出,Excel的导出功能-->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>3.2.0</version>
</dependency>
<!--easypoi-web 耦合了spring-mvc 基于AbstractView,极大的简化spring-mvc下的导出功能-->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>3.2.0</version>
</dependency>
<!--easypoi-annotation 基础注解包,作用与实体对象上,拆分后方便maven多工程的依赖管理-->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>3.2.0</version>
</dependency>

模板 指令

具体可去easy poi教程网站查看EasyPoi教程_V1.0

 poi使用

@Component
public class ExcelComponent {

    /**
     * 使用模板文件导出excel
     *
     * @param tempFileName
     * @param map
     */
    public Workbook exportExcelByTemplate(Map<String, Object> map) {
        try {
            TemplateExportParams params = new TemplateExportParams("模板文件全路径+名称+后缀");
            Workbook w = ExcelExportUtil.exportExcel(params, map);
            return w;
        } catch (Exception e) {
            logger.error("excel文件导出异常", e);
        }
    }

    /**
     * 使用模板文件导出excel(多sheet)
     *
     * @param tempFileName
     * @param map
     */
    public Workbook exportExcelByTemplates( Map<String, Object> map) {
        try {
            TemplateExportParams params = new TemplateExportParams("模板文件全路径+名称+后缀",true);
            Workbook w = ExcelExportUtil.exportExcel(params, map);
            return w;
        } catch (Exception e) {
            logger.error("excel文件导出异常", e);
        }
    }

}

Excel模板导出多个sheet

excel模板

 具体业务实现代码

public class test {

    @Autowired
    ExcelComponent excelService;

    
    /**
     * 测试导出Excel模板(多sheet)
     * @return
     */
    public void testExportSheets(HttpServletResponse response) {
        Map<String, Object> map = new HashMap<>();
        List<Teacher> teacherList= new ArrayList<>();
        List<Student> studentList= new ArrayList<>();
        Teacher teacher1 = new Teacher();
        teacher1.setName("张老师");
        teacher1.setGender("男");
        teacher1.setAge(32);
        teacher1.setMajor("体育");
        Teacher teacher2 = new Teacher();
        teacher2.setName("李老师");
        teacher2.setGender("女");
        teacher2.setAge(26);
        teacher2.setMajor("英语");
        Teacher teacher2 = new Teacher();
        teacherList.add(teacher1);
        teacherList.add(teacher2);
        Student student1 = new Student();
        student1.setName("张同学");
        student1.setGender("男");
        student1.setAge(11);
        student1.setGrade("4年级");
        Student student2 = new Student();
        student2.setName("李同学");
        student2.setGender("女");
        student2.setAge(12);
        student2.setGrade("5年级");
        studentList.add(student1);
        studentList.add(student2);
        map.put("teacherList",teacherList);
        map.put("studentList",studentList);
        Workbook workbook = excelService.exportExcelByTemplates("测试多sheet模板.xlsx", map);
        excelService.downloadExcel(response, workbook, "导出老师和学生");
    }

}

导出结果展示

 Excel模板导出单个sheet

excel模板

 具体业务实现代码

public class test {

    @Autowired
    ExcelComponent excelService;

    
    /**
     * 测试导出Excel模板(单sheet)
     * @return
     */
    public void testExportSheet(HttpServletResponse response) {
        Map<String, Object> map = new HashMap<>();
        List<StudentScore> studentScoreList= new ArrayList<>();
        StudentScore studentScore1 = new StudentScore();
        studentScore1.setName("张同学");
        studentScore1.setMajor("英语");
        studebtScore1.setScore(72);
        StudentScore studentScore2 = new StudentScore();
        studentScore2.setName("李同学");
        studentScore2.setMajor("英语");
        studentScore2.setScore(88);
        studentScoreList.add(studentScore1);
        studentScoreList.add(studentScore2);
        map.put("studentScoreList",studentScoreList);
        Workbook workbook = excelService.exportExcelByTemplates("测试单sheet导出.xlsx", map);
        excelService.downloadExcel(response, workbook, "导出学生成绩");
    }

}

 导出结果展示