Springboot实战:easypoi优雅的Excel、Word文档导出(附源码)
Springboot-cli 开发脚手架系列
Netty系列:Springboot使用Netty集成protobuf开发客户端
文章目录
- Springboot-cli 开发脚手架系列
- 前言
前言
- easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员 就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板 语言(熟悉的表达式语法),完成以前复杂的写法.
- 支持实体方式导出
- 支持模板方式自定义样式导出
- 参考官方文档 gitee地址
1. 导入依赖
pom.xml
<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> <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 */@Slf4jpublic 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 */@Slf4jpublic 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下载
- 编写api
ExportController .java
/** * 附件导出下载,前端api接口 * * @author qiding */@RestController@Slf4jpublic 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项目
- 浏览器访问
- 普通导出EXCEL:
http://localhost:20000/excel/download
- 根据模板导出EXCEL:
http://localhost:20000/excel/downloadByTem
- 导出Work文档:
http://localhost:20000/word/download
-
模板自定义样式导出
-
word文档导出
7. 源码分享
- Springboot-cli开发脚手架,集合各种常用框架使用案例,完善的文档,致力于让开发者快速搭建基础环境并让应用跑起来。
- 项目源码github地址
- 项目源码国内gitee地址