昨天在家闲着没事，写了一个读取Excel 数据并写入到Word 的项目。

我们知道操作office 文档一般常用的就是Apache POI 以及Easy POI.

Apache POI 是对office 早期的版本 .doc ,.xls 以及后期的*.docx 和*.xlsx API 的实现。

关于EasyPOI 据说是对Apache POI做了更好的封装和功能扩展，让开发变得更加简单。

---

好了废话不多说，本篇博文将使用Apache POI 来读取Excel 数据并写入到word 文档中。

最终效果如图所示：

0x01 读取Excel 数据并写入到Word示例

1.1 配置pom.xml

pom.xml 添加依赖如下:

   <!-- 生产应用监控,可选,可以添加也可以不添加不影响当前项目  --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-actuator</artifactId></dependency><!-- 自定义配置文件要用  --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-configuration-processor</artifactId><optional>true</optional></dependency><!-- Web应用开发 --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><!-- Apache POI --><!-- https://mvnrepository.com/artifact/org.apache.poi/poi --><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.0</version></dependency><!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.0</version></dependency><!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas --><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>4.1.0</version></dependency><!-- thymeleaf 页面模板引擎--><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-thymeleaf</artifactId></dependency><!--lombok 通过注解生成Getter Setter ToString 日志初始化等方法--><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><!-- Spring Boot Test Framework --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency>

完整的pom.xml 内容如下:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><!-- 设置当前项目的父项目为Spring Boot --><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.1.9.RELEASE</version><relativePath/> <!-- lookup parent from repository --></parent><!-- 设置当前项目的基本信息 --><groupId>com.xingyun</groupId><artifactId>transport-excel-data-to-word</artifactId><version>0.0.1-SNAPSHOT</version><name>transport-excel-data-to-word</name><description>Demo project for Spring Boot</description><properties><!-- 设置当前项目源码使用字符编码为UTF-8 --><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding><!-- 设置当前项目所需要的JDK版本 Open JDK下载地址:https://jdk.java.net/ --><java.version>1.8</java.version><!-- 设置当前项目编译所需要的JDK版本 Open JDK下载地址:https://jdk.java.net/ --><maven.compiler.source>${java.version}</maven.compiler.source><maven.compiler.target>${java.version}</maven.compiler.target><!-- 设置maven编译插件版本,可通过下面网址查看最新的版本--><!-- https://mvnrepository.com/artifact/org.apache.maven.plugins/maven-compiler-plugin --><maven.compiler.plugin.version>3.5.1</maven.compiler.plugin.version><!-- 项目所使用第三方依赖jar包的版本,建议以后都使用这种方式,方便今后维护和升级 --><apache.poi.version>4.1.0</apache.poi.version></properties><dependencies><!-- 生产应用监控,可选,可以添加也可以不添加不影响当前项目  --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-actuator</artifactId></dependency><!-- 自定义配置文件要用  --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-configuration-processor</artifactId><optional>true</optional></dependency><!-- Web应用开发 --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><!-- Apache POI --><!-- https://mvnrepository.com/artifact/org.apache.poi/poi --><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>${apache.poi.version}</version></dependency><!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>${apache.poi.version}</version></dependency><!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas --><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>${apache.poi.version}</version></dependency><!-- thymeleaf 页面模板引擎--><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-thymeleaf</artifactId></dependency><!--lombok 通过注解生成Getter Setter ToString 日志初始化等方法--><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><!-- Spring Boot Test Framework --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency></dependencies><build><plugins><!--该插件可以让我们通过maven命令将项目打包成一个可执行的Jar--><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId></plugin><!--该插件限定Maven打包时所使用的版本,避免出现版本不匹配问题--><plugin><groupId>org.apache.maven.plugins</groupId><artifactId>maven-compiler-plugin</artifactId><version>${maven.compiler.plugin.version}</version><configuration><source>${java.version}</source><target>${java.version}</target></configuration></plugin></plugins></build></project>

1.2 配置 application.properties

application.properties

spring.profiles.active=dev

application-dev.properties

spring.application.name=transport-excel-data-to-wordserver.address=127.0.0.1
server.port=8080
server.servlet.context-path=/spring.thymeleaf.prefix=classpath:/templates/bootstrap-ui-framework/
spring.thymeleaf.suffix=.html
spring.thymeleaf.encoding=UTF-8# 上次文件配置
com.xingyun.customize.upload-folder=C:/opt/upload
# Servlet配置
# 连接超时设置为最大值
server.connection-timeout=999999999
spring.servlet.multipart.max-file-size=4096GB
spring.servlet.multipart.max-request-size=4096GB
spring.servlet.multipart.enabled=true
# Tomcat配置
# 解决大文件上传问题
# Tomcat针对中止上载将吞下的最大请求正文字节数（不包括传输编码开销）
#中止上传是指Tomcat知道请求体将被忽略但客户端仍然发送它
# 如果Tomcat没有吞下身体，则客户端不太可能看到响应
# 如果未指定，将使用默认值2097152（2兆字节）
# 值小于零表示不应强制执行限制
server.tomcat.max-swallow-size=-1
server.tomcat.max-connections=10000
server.tomcat.max-http-post-size=4096GB

1.3 自定义配置属性

由于上面com.xingyun.customize.upload-folder=C:/opt/upload

我们使用了一些自定义配置，因此按照最佳实践来做，需要做点代码配置支持。

• 创建一个Java Config 类，并激活配置属性文件。

import com.xingyun.transportexceldatatoword.customize.SmartUploadProperties;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Configuration;/*** @author 星云* @功能* @date 10/13/2019 12:00 PM*/
@EnableConfigurationProperties({SmartUploadProperties.class
})
@Configuration
public class CustomizePropertiesConfig {
}

• 然后创建一个自定义配置类

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import org.springframework.boot.context.properties.ConfigurationProperties;/*** @author 星云* @功能* @date 10/13/2019 12:01 PM*/
@Getter
@Setter
@ToString
@ConfigurationProperties(prefix="com.xingyun.customize")
public class SmartUploadProperties {/*** 注意"这里的变量名称不可以有下划线 否则会出错*/private String uploadFolder;
}

注意：
这样配置后，映射到配置文件就是
com.xingyun.customize.upload-folder=C:/opt/upload

1.4 配置首页请求拦截并初始化文件夹

代码如下:

import com.xingyun.transportexceldatatoword.customize.SmartUploadProperties;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;import java.io.File;/*** @author 星云* @功能* @date 10/13/2019 9:34 AM*/
@Controller
public class HomePageController {@AutowiredSmartUploadProperties smartUploadProperties;@GetMapping(value = "/")public String homePage(){File file=new File(smartUploadProperties.getUploadFolder());if(!file.exists()){file.mkdirs();}return "index";}
}

注意：
这样当访问首页的时候就会初始化上传文件夹了

1.5 配置上传页面

然后我们需要一个上传页面，编写代码如下:

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head><!-- Required meta tags --><meta charset="UTF-8"><meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no"><link rel="stylesheet" th:href="@{../static/third-party/bootstrap-4.3.1-dist/css/bootstrap.min.css}" ><title>Excel Data To Word App</title>
</head>
<body><div align="center"><h1>Excel Data To World App</h1></div><div class="jumbotron text-center"><div align="left"><form action="/upload.do" enctype="multipart/form-data" method="post"><input th:type="file" name="uploadFileName" ><input type="submit" value="提交"></form></div></div><!-- Optional JavaScript --><!-- jQuery first, then Popper.js, then Bootstrap JS --><script th:src="@{../static/third-party/jquery/jquery-3.3.1.slim.min.js}"></script><script th:src="@{../static/third-party/ajax/libs/popper.js/1.14.7/umd/popper.min.js}"/><script th:src="@{../static/third-party/bootstrap-4.3.1-dist/js/bootstrap.min.js}"/>
</body>
</html>

1.7 上传文件

处理文件上传的控制器编写如下:

import com.xingyun.transportexceldatatoword.constant.CommonConstant;
import com.xingyun.transportexceldatatoword.customize.SmartUploadProperties;
import com.xingyun.transportexceldatatoword.util.SmartPoiExcelUtils;
import com.xingyun.transportexceldatatoword.util.SmartPoiWordUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.util.List;/*** @author 星云* @功能* @date 10/13/2019 9:43 AM*/
@Slf4j
@Controller
public class UploadApiController {@AutowiredSmartUploadProperties smartUploadProperties;@PostMapping(value = "/upload.do")public String upload(@RequestParam(value = "uploadFileName") MultipartFile multipartFile, HttpServletResponse response){log.info(multipartFile.getOriginalFilename());//构建保存文件路径StringBuilder stringBuilder=new StringBuilder();stringBuilder.append(smartUploadProperties.getUploadFolder());stringBuilder.append(File.separator);stringBuilder.append(multipartFile.getOriginalFilename());//上传文件路径String uploadFilePath=stringBuilder.toString();//文件File file=new File(uploadFilePath);try {//将上传的文件保存下来multipartFile.transferTo(file);} catch (IOException e) {log.error(e.getMessage(),e);}//将Excel中的数据进行解析成对象List<String> dataList= SmartPoiExcelUtils.parseExcelData(file.getAbsolutePath());//生成World 的文件路径StringBuilder worldName=new StringBuilder();worldName.append(smartUploadProperties.getUploadFolder());worldName.append(File.separator);worldName.append("data.docx");//将数据写入到文档中try {SmartPoiWordUtils.writeDataToWord(worldName.toString(),dataList);} catch (IOException e) {log.error("IO Exception:",e);}//写入完成后放入这个列表中CommonConstant.shareFileMap.put("downloadFile",worldName.toString());return "redirect:/api/v1/download.do";}
}

这里使用了两个工具类，将上传的excel 文件保存到指定的路径，然后将内容写入到word中，最后重定向到一个处理文件下载的控制器中。

1.8 读取Excel 数据工具类

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.WorkbookUtil;import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;/*** @author 星云* @功能* @date 10/13/2019 10:01 AM*/
@Slf4j
public final class SmartPoiExcelUtils {private static final DataFormatter DATA_FORMATTER = new DataFormatter();public static List<String> parseExcelData(String fileName){List<String> dataList=new ArrayList<>();try {Workbook workbook= SmartPoiExcelUtils.createExcelWithXLSX(fileName);for (Sheet sheetItem : workbook ) {for (Row row : sheetItem) {for (Cell cell : row) {String text = DATA_FORMATTER.formatCellValue(cell);log.info(text);log.debug("cell type:{}",cell.getCellType());dataList.add(text);}}}workbook.close();} catch (IOException e) {e.printStackTrace();}return dataList;}public static Workbook createExcelWithXLS(String fileName) throws IOException {Workbook workbook = new HSSFWorkbook();try  (OutputStream fileOut = new FileOutputStream(fileName)) {workbook.write(fileOut);}return workbook;}public static Workbook createExcelWithXLSX(String fileName) throws IOException {Workbook workbook = WorkbookFactory.create(new File(fileName));return workbook;}/*** Note that sheet name is Excel must not exceed 31 characters* and must not contain any of the any of the following characters:* 0x0000* 0x0003* colon (:)* backslash (\)* asterisk (*)* question mark (?)* forward slash (/)* opening square bracket ([)* closing square bracket (])* @param workbook* @param sheetName* @return*/public static Sheet createSheet(Workbook workbook,String sheetName){// You can use org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}// for a safe way to create valid names, this utility replaces invalid characters with a space (' ')// returns " O'Brien's sales   "String safeName = WorkbookUtil.createSafeSheetName(sheetName);Sheet sheet = workbook.createSheet(safeName);return sheet;}public static Cell createCell(Workbook workbook,Sheet sheet){CreationHelper createHelper = workbook.getCreationHelper();// Create a row and put some cells in it. Rows are 0 based.Row row = sheet.createRow(0);// Create a cell and put a value in it.Cell cell = row.createCell(0);cell.setCellValue(1);// Or do it on one line.row.createCell(1).setCellValue(1.2);row.createCell(2).setCellValue(createHelper.createRichTextString("This is a string"));row.createCell(3).setCellValue(true);// Create a cell and put a date value in it.  The first cell is not styled// as a date.row.createCell(4).setCellValue(new Date());// we style the second cell as a date (and time).  It is important to// create a new cell style from the workbook otherwise you can end up// modifying the built in style and effecting not only this cell but other cells.CellStyle cellStyle = workbook.createCellStyle();cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm"));cell = row.createCell(1);cell.setCellValue(new Date());cell.setCellStyle(cellStyle);//you can also set date as java.util.Calendarcell = row.createCell(2);cell.setCellValue(Calendar.getInstance());cell.setCellStyle(cellStyle);row.createCell(0).setCellValue(1.1);row.createCell(1).setCellValue(new Date());row.createCell(2).setCellValue(Calendar.getInstance());row.createCell(3).setCellValue("a string");row.createCell(4).setCellValue(true);return cell;}
}

1.9 将List<String>写入到word中工具类

import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;/*** @author 星云* @功能* @date 10/13/2019 12:22 PM*/
public final class SmartPoiWordUtils {public static void writeDataToWord(String fileName, List<String> dataListArg) throws IOException {FileOutputStream out = new FileOutputStream(new File(fileName));//创建一个文档XWPFDocument xwpfDocument=new XWPFDocument();//创建一个段落XWPFParagraph xwpfParagraph;//创建一片区域XWPFRun run;for (String lineData:dataListArg) {xwpfParagraph= xwpfDocument.createParagraph();run=xwpfParagraph.createRun();run.setText(lineData);}xwpfDocument.write(out);xwpfDocument.close();out.close();}
}

1.9 静态值存储

这里为了上传和下载分开，将下载路径暂时保存到一个静态值中存储。

import java.util.HashMap;
import java.util.Map;/*** @author 星云* @功能* @date 10/13/2019 2:59 PM*/
public class CommonConstant {/*** 存储查询使用*/public static Map<String,String> shareFileMap=new HashMap<>();
}

1.10 处理下载模块

import com.xingyun.transportexceldatatoword.constant.CommonConstant;
import com.xingyun.transportexceldatatoword.util.DownloadFileUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;import javax.servlet.http.HttpServletResponse;/*** @author 星云* @功能* @date 10/13/2019 2:56 PM*/
@Slf4j
@RequestMapping(value = "/api/v1")
@RestController
public class DownloadController {@GetMapping(value = "/download.do")public  void downloadFtpFileList(HttpServletResponse response) throws Exception{String downloadFileName="download.docx";//获取下载文件路径String downloadFilePath= CommonConstant.shareFileMap.get("downloadFile");log.info("下载文件名称:"+downloadFileName);log.info("下载文件路径:"+downloadFilePath);//执行下载文件Boolean downloadResult= DownloadFileUtils.downloadFile(downloadFilePath,downloadFileName,response);if(downloadResult){log.info("下载成功");}else{log.info("下载失败");}}
}

1.11 文件下载工具类

import lombok.extern.slf4j.Slf4j;import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;/*** @author 星云* @功能* @date 10/13/2019 3:00 PM*/
@Slf4j
public class DownloadFileUtils {public final static Boolean downloadFile(String downloadFilePath, String downloadFileName, HttpServletResponse response){//配置文件下载try {response.setHeader("content-type", "application/octet-stream");response.setContentType("application/octet-stream");// 下载文件能正常显示中文response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(downloadFileName, "UTF-8"));} catch (UnsupportedEncodingException e) {e.printStackTrace();return false;}// 实现文件下载byte[] buffer = new byte[1024];FileInputStream fis = null;BufferedInputStream bis = null;try {fis = new FileInputStream(downloadFilePath);bis = new BufferedInputStream(fis);OutputStream os = response.getOutputStream();int i = bis.read(buffer); while (i != -1) {os.write(buffer, 0, i); i = bis.read(buffer);}} catch (Exception e) {log.error("Download the file failed!：{}",e.getMessage());return false;}finally {//关闭流资源if (bis != null) {try {bis.close();}catch (IOException e) {e.printStackTrace();log.error("关闭bis出错:{}",e.getMessage());return false;}}if (fis != null) {try { fis.close();} catch (IOException e) {e.printStackTrace();log.error("关闭fis出错:{}",e.getMessage());return false;}}}return true;}
}

1.12 最终项目结构

最终项目结构如下

好了，到这里就写完了。

1.13 源码下载

感谢关注我的博客，如果需要可以点击下方链接自行下载或查看该项目源码。

查看源码

本篇完~