基于springboot跟poi封装的最便捷的excel导出
技术:springboot1.5.6 + maven3.0.5 + jdk1.8
概述
Springboot最便捷的Excel导出,只需要一个配置文件即可搞定
详细
一、准备工作
先在pom文件添加依赖如下图所示:
<dependencies> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.11</version> <scope>test</scope> </dependency> <dependency> <groupId>javax.servlet</groupId> <artifactId>jstl</artifactId> <version>1.2</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-cache</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!-- 使用Jasper引擎解析JSP --> <dependency> <groupId>org.apache.tomcat.embed</groupId> <artifactId>tomcat-embed-jasper</artifactId> <scope>provided</scope> </dependency> <!-- jstl标签 --> <dependency> <groupId>javax.servlet</groupId> <artifactId>jstl</artifactId> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> <version>3.8.1</version> </dependency> <dependency> <groupId>com.google.guava</groupId> <artifactId>guava</artifactId> <version>27.0-jre</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency> </dependencies>
二、程序实现
1、添加excel导出的person-export-config文件
<?xml version="1.0" encoding="UTF-8"?> <exportFile> <fileName>exportConfig</fileName> <exportType>0</exportType> <cell> <title>序号</title> <alias>index</alias> </cell> <cell> <title>姓名</title> <alias>name</alias> </cell> <cell> <title>年龄</title> <alias>age</alias> </cell> <cell> <title>性别</title> <alias>sex</alias> </cell> <cell> <title>日期</title> <alias>date</alias> </cell> </exportFile>
其中exportType:0表示导出EXCEL2007,exportType:1表示导出csv文件
title导出展示列的title名,alias表示映射的字段名
2、从classpath获取person-export-config.xml文件
从classpath获取person-export-coing.xml文件并转为inputStresm
ClassPathResource classPathResource = new ClassPathResource("export/person-export-config.xml"); InputStream inputStream = classPathResource.getInputStream(); ExportConfig exportConfig = ExportConfigFactory.getExportConfig(inputStream);
3、解析person-export-config.xml文件
主要代码如下:
private static ExportConfig getExportCells(InputStream inputStream) throws FileExportException { ExportConfig exportConfig = new ExportConfig(); DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance(); DocumentBuilder dBuilder = null; Document document = null; try { dBuilder = dbFactory.newDocumentBuilder(); document = dBuilder.parse(inputStream); } catch (ParserConfigurationException | SAXException | IOException e) { throw new FileExportException(e, "pares xml error"); } Element root = document.getDocumentElement(); NodeList elements = root.getElementsByTagName("cell"); List<ExportCell> exportCells = initElement(elements); String fileName = ""; String exportType1 = ""; try { fileName = ConfigParser.getNodeText(root, "fileName"); exportType1 = ConfigParser.getNodeText(root, "exportType"); } catch (FileImportException e) { throw new FileExportException(e); } if (StringUtils.isEmpty(fileName)) { throw new FileExportException("用于导出的xml文档 <fileName> 为空"); } if (StringUtils.isEmpty(exportType1) || !StringUtils.isNumeric(exportType1)) { throw new FileExportException("用于导出的xml文档 <exportType> 为空"); } exportConfig.setFileName(fileName); ExportType exportType = ExportType.getExportType(Integer.valueOf(exportType1)); if (exportType == null) { throw new FileExportException("找不到相应的ExportType 解析xml得到的exportType 是" + exportType1); } exportConfig.setExportType(exportType); exportConfig.setExportCells(exportCells); return exportConfig; }
这时我们得到一个ExportConfig对象如下:
public class ExportConfig extends BaseEntity { private String fileName;//输出的文件名 private ExportType exportType;//0 表示 excel, 1 表示csv private List<ExportCell> exportCells;
4、添加要输出到excel的list对象
List<Map> lists = new LinkedList<>(); for (int i = 0; i < 10; i++) { Map<String, Object> maps = new HashMap<>(); maps.put("index", i); maps.put("name", "张三" + i); maps.put("age", Float.valueOf(i)); maps.put("sex", "男"); maps.put("date", new Date()); lists.add(maps); }
在实际项目中map可以是具体的实体类对象比如Person,只要对象里面的字段跟person-export-config里的alias标签对应上即可(如下图所示)。
5、获取ExportResult对象
具体代码如下:
ExportResult exportResult = FileExportor.exportResult(exportConfig, lists);
将exportConfig对象跟要输出到excel的lists对象传入
public static ExportResult exportResult(ExportConfig exportConfig, List<?> data) throws FileExportException { ExportType exportType = exportConfig.getExportType(); switch (exportType) { case EXCEL2007: Workbook workbook = new ExcelExportImpl().getExportResult(data, exportConfig.getExportCells()); ExportExcelResult exportExcelResult = new ExportExcelResult(); exportExcelResult.setWorkbook(workbook); exportExcelResult.setFileName(exportConfig.getFileName()); return exportExcelResult; case CSV: StringBuilder stringBuilder = new CSVExportImpl().getExportResult(data, exportConfig.getExportCells()); ExportCSVResult exportCSVResult = new ExportCSVResult(); exportCSVResult.setResult(stringBuilder.toString()); exportCSVResult.setFileName(exportConfig.getFileName()); return exportCSVResult; } throw new FileExportException("找不到对应的export type, export type is " + exportType.getNumber()); }
6、最后将数据通过outputstream导出到excel
String fileName = "person统计" +".xlsx"; setResponseHeader(response, fileName); OutputStream outputStream = response.getOutputStream(); exportResult.export(outputStream);
public void export(OutputStream outputStream) throws FileExportException{ try { workbook.write(outputStream); outputStream.close(); } catch (IOException e) { throw new FileExportException("[Error occurred while export excel message is] " + e); } }
三、项目结构图
四、运行效果图
五、补充
本例子主要对poi导出excel进行了一个封装,通过xml配置文件配置跟实体类一一对应的字段,可灵活配置,在实际项目中非常实用。
本实例支付的费用只是购买源码的费用,如有疑问欢迎在文末留言交流,如需作者在线代码指导、定制等,在作者开启付费服务后,可以点击“购买服务”进行实时联系,请知悉,谢谢
手机上随时阅读、收藏该文章 ?请扫下方二维码