Springboot实战：easypoi优雅的Excel、Word文档导出（附源码）

Springboot-cli 开发脚手架系列

Netty系列：Springboot使用Netty集成protobuf开发客户端

---

---

前言

• easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员 就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板 语言(熟悉的表达式语法),完成以前复杂的写法.
• 支持实体方式导出
• 支持模板方式自定义样式导出
• 参考官方文档 gitee地址

1. 导入依赖

pom.xml

        <!--     web依赖   -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--	工具包	-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>${hutool-all.version}</version>
        </dependency>
        <!--	easyPoi Excel导出核心依赖	-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.3.0</version>
        </dependency>

2. 编写模板实体

• 注解解析

@Excel 作用到filed上面,是对Excel一列的一个描述
@ExcelCollection 表示一个集合,主要针对一对多的导出,比如一个老师对应多个科目,科目就可以用集合表示
@ExcelEntity 表示一个继续深入导出的实体,但他没有太多的实际意义,只是告诉系统这个对象里面同样有导出的字段
@ExcelIgnore 和名字一样表示这个字段被忽略跳过这个导导出
@ExcelTarget 这个是作用于最外层的对象,描述这个对象的id,以便支持一个对象可以针对不同导出做出不同处理

• 这里我们模拟用户和课程的excel导出，首先用户和课程是一对多关系，我们先创建用户模板实体User .java

@Data
@Accessors(chain = true)
public class User {
    /**
     * 主键
     */
    private Integer userId;

    @Excel(name = "用户名", orderNum = "1", width = 30, needMerge = true)
    private String username;

    @Excel(name = "年龄", orderNum = "2", width = 10, needMerge = true)
    private Integer age;

    @Excel(name = "性别", orderNum = "3", width = 30, needMerge = true)
    private String sex;

    @ExcelCollection(name = "课程", orderNum = "4")
    private List<Course> courseList;
}

• 课程实体Course .java

@Data
@Accessors(chain = true)
public class Course implements java.io.Serializable {
    /**
     * 主键
     */
    private Integer courseId;

    @Excel(name = "课程名", orderNum = "1", width = 30)
    private String courseName;

    @Excel(name = "类型", orderNum = "2", width = 10)
    private String courseType;

    @Excel(name = "日期", format = "yyyy-MM-dd", width = 20)
    private Date createdDate;
}

2. 封装导出工具

• 创建ExelUtils .java

/**
 * Exel导出封装
 *
 * @author qiding
 */
@Slf4j
public class ExelUtils {

    /**
     * 导出xlsx文件
     *
     * @param title      标题
     * @param sheetName  页名
     * @param list       数据集合
     * @param temClass   实体模板
     * @param outputPath 导出路径
     */
    public static void exportExcel(String title, String sheetName, List<?> list, Class<?> temClass, String outputPath) throws IOException {
        ExportParams params = new ExportParams(title, sheetName, ExcelType.XSSF);
        Workbook workbook = ExcelExportUtil.exportExcel(params, temClass, list);
        FileOutputStream fos = new FileOutputStream(outputPath);
        workbook.write(fos);
        fos.close();
    }
}

• 编写main测试导出效果

public class Test {

    public static void main(String[] args) throws Exception {
        // 获取模拟数据
        List<User> userListMockData1 = MockDataUtils.getUserListMockData();
        // 1. 普通导出xlsx
           ExelUtils.exportExcel("课程表", "第一页", userListMockData1, User.class, "D://test/基础课程表.xlsx");
    }
}

3. 使用模板方式导出

• 创建模板user_course_tem.xlsx
  

更多语法参考easypoi官网

• 复制模板到项目resource/exportTemplate/user_course_tem.xlsx

• 重新编写ExelUtils.java

/**
 * Exel导出封装
 *
 * @author qiding
 */
@Slf4j
public class ExelUtils {

    /**
     * 导出xlsx文件
     *
     * @param title      标题
     * @param sheetName  页名
     * @param list       数据集合
     * @param temClass   实体模板
     * @param outputPath 导出路径
     */
    public static void exportExcel(String title, String sheetName, List<?> list, Class<?> temClass, String outputPath) throws IOException {
        ExportParams params = new ExportParams(title, sheetName, ExcelType.XSSF);
        Workbook workbook = ExcelExportUtil.exportExcel(params, temClass, list);
        FileOutputStream fos = new FileOutputStream(outputPath);
        workbook.write(fos);
        fos.close();
    }

    /**
     * 导出Excel文件（模板方式），输出到指定路径
     *
     * @param list         数据集合
     * @param xlsxTemplate 模板存放地址
     * @param outputPath   导出路径
     */
    public static void exportExcelByTem(List<?> list, XlsxTemplate xlsxTemplate, String outputPath) throws IOException {
        TemplateExportParams params = new TemplateExportParams(
                xlsxTemplate.getUrl());
        Map<String, Object> map = new HashMap<String, Object>(2);
        map.put("list", list);
        Workbook workbook = ExcelExportUtil.exportExcel(params, map);
        FileOutputStream fos = new FileOutputStream(outputPath);
        workbook.write(fos);
        fos.close();
    }


    /**
     * 生成并下载Excel文件
     *
     * @param title     标题
     * @param sheetName 页名
     * @param list      数据集合
     * @param temClass  实体模板
     * @param response  http 响应体
     */
    public static void downloadExcel(String title, String sheetName, List<?> list, Class<?> temClass, HttpServletResponse response) throws IOException {
        ExportParams params = new ExportParams(title, sheetName, ExcelType.XSSF);
        Workbook workbook = ExcelExportUtil.exportExcel(params, temClass, list);
        responseFile(title + ".xlsx", workbook, response);
    }


    /**
     * 生成并下载Excel文件(模板方式)，直接供前端下载
     *
     * @param list         数据集合
     * @param xlsxTemplate 模板存放地址
     * @param response     http 响应体
     */
    public static void downloadExcelByTem(List<?> list, XlsxTemplate xlsxTemplate, HttpServletResponse response) throws IOException {
        TemplateExportParams params = new TemplateExportParams(
                xlsxTemplate.getUrl());
        Map<String, Object> map = new HashMap<String, Object>(2);
        map.put("list", list);
        Workbook workbook = ExcelExportUtil.exportExcel(params, map);
        responseFile(xlsxTemplate.getTitle() + ".xlsx", workbook, response);
    }

    /**
     * 设置http响应体为文件
     */
    private static void responseFile(String fileName, Workbook workbook, HttpServletResponse response) throws IOException {
        response.setCharacterEncoding("utf8");
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition",
                "attachment; filename=" + URLEncoder.encode(fileName, StandardCharsets.UTF_8));
        response.setHeader("Cache-Control", "no-store");
        response.addHeader("Cache-Control", "max-age=0");
        OutputStream out = response.getOutputStream();
        workbook.write(out);
        out.flush();
        out.close();
    }

    /**
     * 模板
     */
    public enum XlsxTemplate {

        /**
         * 模板地址
         */
        USER_COURSE("exportTemplate/user_course_tem.xlsx", "课程表"),
        TEST("exportTemplate/xx.xlsx", "xx模板文件"),
        ;

        private final String url;

        private final String title;

        XlsxTemplate(String url, String title) {
            this.url = url;
            this.title = title;
        }

        public String getUrl() {
            return this.url;
        }

        public String getTitle() {
            return this.title;
        }
    }

}

• 测试

public static void main(String[] args) throws Exception {
        // 获取模拟数据
        List<User> userListMockData1 = MockDataUtils.getUserListMockData();
        List<User> userListMockData2 = MockDataUtils.getUserListMockData();

        // 2. 通过模板导出xlsx
         ExelUtils.exportExcelByTem(userListMockData2, ExelUtils.XlsxTemplate.USER_COURSE, "D://test/模板课程表.xlsx");
         
    }

4. Work文档导出

• 创建模板simple.docx，复制模板到项目resource/exportTemplate/simple.docx
  模板参考
  

• 编写WordUtils .java

/**
 * doc 文件导出
 *
 * @author qiding
 */
public class WordUtils {

    public static void exportWord() {
        Map<String, Object> map = new HashMap<>(2);
        map.put("time", "2022-04-19");
        map.put("content", "国内地址：https://gitee.com/liangqiding/springboot-cli 欢迎参与开源！");
        try {
            XWPFDocument doc = WordExportUtil.exportWord07(
                    "exportTemplate/simple.docx", map);
            FileOutputStream fos = new FileOutputStream("d://test/simple.docx");
            doc.write(fos);
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void downloadWord(HttpServletResponse response) {
        Map<String, Object> map = new HashMap<>(2);
        map.put("time", "2022-04-19");
        map.put("content", "国内地址：https://gitee.com/liangqiding/springboot-cli 欢迎参与开源！");
        try {
            XWPFDocument doc = WordExportUtil.exportWord07(
                    "exportTemplate/simple.docx", map);
            response.setCharacterEncoding("utf8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "attachment; filename=" + URLEncoder.encode("work文档生成测试.docx", StandardCharsets.UTF_8));
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            OutputStream out = response.getOutputStream();
            doc.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

5. 提供web下载

• 编写apiExportController .java

/**
 * 附件导出下载，前端api接口
 *
 * @author qiding
 */
@RestController
@Slf4j
public class ExportController {

    /**
     * 普通导出EXCEL
     */
    @GetMapping("excel/download")
    public void downloadExcel(HttpServletResponse response) throws IOException {
        // 获取模拟数据
        List<User> userListMockData = MockDataUtils.getUserListMockData();
        ExelUtils.downloadExcel("课程表", "第一页", userListMockData, User.class, response);
    }

    /**
     * 根据模板导出EXCEL
     */
    @GetMapping("excel/downloadByTem")
    public void downloadExcelByTem(HttpServletResponse response) throws IOException {
        // 获取模拟数据
        List<User> userListMockData = MockDataUtils.getUserListMockData();
        ExelUtils.downloadExcelByTem(userListMockData, ExelUtils.XlsxTemplate.USER_COURSE, response);
    }

    /**
     * 根据导出Word文档
     */
    @GetMapping("word/download")
    public void downloadWord(HttpServletResponse response) {
        WordUtils.downloadWord(response);
    }
}

6.效果演示

• 启动Springboot项目
• 浏览器访问

1. 普通导出EXCEL:http://localhost:20000/excel/download
2. 根据模板导出EXCEL:http://localhost:20000/excel/downloadByTem
3. 导出Work文档:http://localhost:20000/word/download
   

• 模板自定义样式导出
  

• word文档导出
  

7. 源码分享

• Springboot-cli开发脚手架，集合各种常用框架使用案例，完善的文档，致力于让开发者快速搭建基础环境并让应用跑起来。
• 项目源码github地址
• 项目源码国内gitee地址