基于springboot跟poi封装的最便捷的excel导出

发布时间：2018-11-15

 

技术：springboot1.5.6 + maven3.0.5 + jdk1.8

 

概述

Springboot最便捷的Excel导出,只需要一个配置文件即可搞定

详细

代码下载：http://www.demodashi.com/demo/14471.html

一、准备工作

先在pom文件添加依赖如下图所示：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62

<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文件

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

<?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

?

1 2 3 4

ClassPathResource classPathResource = new ClassPathResource("export/person-export-config.xml");         InputStream inputStream = classPathResource.getInputStream();           ExportConfig exportConfig = ExportConfigFactory.getExportConfig(inputStream);

 

3、解析person-export-config.xml文件

主要代码如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

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对象如下：

?

1 2 3 4 5

public class ExportConfig extends BaseEntity {           private String fileName;//输出的文件名     private ExportType exportType;//0 表示 excel, 1 表示csv     private List<ExportCell> exportCells;

 

4、添加要输出到excel的list对象

?

1 2 3 4 5 6 7 8 9 10

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对象

具体代码如下：

?

1	ExportResult exportResult = FileExportor.exportResult(exportConfig, lists);

将exportConfig对象跟要输出到excel的lists对象传入

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

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

?

1 2 3 4 5

String fileName = "person统计" +".xlsx";      setResponseHeader(response, fileName); OutputStream outputStream = response.getOutputStream(); exportResult.export(outputStream);

?

1 2 3 4 5 6 7 8

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配置文件配置跟实体类一一对应的字段，可灵活配置，在实际项目中非常实用。

代码下载：http://www.demodashi.com/demo/14471.html

注：本文著作权归作者，由demo大师发表，拒绝转载，转载需要作者授权