读取Excel 数据并写入到Word示例
昨天在家闲着没事,写了一个读取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-word
server.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 {
@Autowired
SmartUploadProperties 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 {
@Autowired
SmartUploadProperties 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.Calendar
cell = 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 源码下载
感谢关注我的博客,如果需要可以点击下方链接自行下载或查看该项目源码。
本篇完~