读取Excel 数据并写入到Word示例
0x01 读取Excel 数据并写入到Word示例1.1 配置pom.xml1.2 配置 application.properties1.3 自定义配置属性1.4 配置首页请求拦截并初始化文件夹1.5 配置上传页面1.7 上传文件1.8 读取Excel 数据工具类1.9 将`List`写入到word中工具类1.9 静态值存储1.10 处理下载模块1.11 文件下载工具类1.12 最终项目结构1.13 源码下载昨天在家闲着没事，写了一个读取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 --><!-- /artifact/org.apache.poi/poi --><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.0</version></dependency><!-- /artifact/org.apache.poi/poi-ooxml --><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.0</version></dependency><!-- /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="/POM/4.0.0" xmlns:xsi="/2001/XMLSchema-instance"xsi:schemaLocation="/POM/4.0.0 /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下载地址:/ --><java.version>1.8</java.version><!-- 设置当前项目编译所需要的JDK版本 Open JDK下载地址:/ --><piler.source>${java.version}</piler.source><piler.target>${java.version}</piler.target><!-- 设置maven编译插件版本,可通过下面网址查看最新的版本--><!-- /artifact/org.apache.maven.plugins/maven-compiler-plugin --><piler.plugin.version>3.5.1</piler.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 --><!-- /artifact/org.apache.poi/poi --><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>${apache.poi.version}</version></dependency><!-- /artifact/org.apache.poi/poi-ooxml --><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>${apache.poi.version}</version></dependency><!-- /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>${piler.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.1server.port=8080server.servlet.context-path=/spring.thymeleaf.prefix=classpath:/templates/bootstrap-ui-framework/spring.thymeleaf.suffix=.htmlspring.thymeleaf.encoding=UTF-8# 上次文件配置com.xingyun.customize.upload-folder=C:/opt/upload# Servlet配置# 连接超时设置为最大值server.connection-timeout=999999999spring.servlet.multipart.max-file-size=4096GBspring.servlet.multipart.max-request-size=4096GBspring.servlet.multipart.enabled=true# Tomcat配置# 解决大文件上传问题# Tomcat针对中止上载将吞下的最大请求正文字节数（不包括传输编码开销）#中止上传是指Tomcat知道请求体将被忽略但客户端仍然发送它# 如果Tomcat没有吞下身体，则客户端不太可能看到响应# 如果未指定，将使用默认值2097152（2兆字节）# 值小于零表示不应强制执行限制server.tomcat.max-swallow-size=-1server.tomcat.max-connections=10000server.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/ 12:00 PM*/@EnableConfigurationProperties({SmartUploadProperties.class})@Configurationpublic class CustomizePropertiesConfig {}
然后创建一个自定义配置类
import lombok.Getter;import lombok.Setter;import lombok.ToString;import org.springframework.boot.context.properties.ConfigurationProperties;/*** @author 星云* @功能* @date 10/13/ 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/ 9:34 AM*/@Controllerpublic 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=""><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.monConstant;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/ 9:43 AM*/@Slf4j@Controllerpublic 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/ 10:01 AM*/@Slf4jpublic 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/ 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/ 2:59 PM*/public class CommonConstant {/*** 存储查询使用*/public static Map<String,String> shareFileMap=new HashMap<>();}
1.10 处理下载模块
import com.xingyun.monConstant;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/ 2:56 PM*/@Slf4j@RequestMapping(value = "/api/v1")@RestControllerpublic 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 .URLEncoder;/*** @author 星云* @功能* @date 10/13/ 3:00 PM*/@Slf4jpublic 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 源码下载
感谢关注我的博客，如果需要可以点击下方链接自行下载或查看该项目源码。
查看源码
本篇完~
