Java实现读取Excel数据并写入到Word
目录
- 读取Excel 数据并写入到Word
- 配置pom.XML
- 配置 application.properties
- 自定义配置属性
- 配置首页请求拦截并初始化文件夹
- 配置上传页面
- 上传文件
- 读取Excel 数据工具类
- 将List<String>写入到word中工具类
- 静态值存储
- 处理下载模块
- 文件下载工具类
读取Excel 数据并写入到Word
我们知道操作office 文档一般常用的就是Apache POI 以及Easy POI.
Apache POI 是对office 早期的版本 .doc ,.xls 以及后期的*.docx 和*.xlsx API 的实现。
关于EasyPOI 据说是对Apache POI做了更好的封装和功能扩展,让开发变得更加简单。
好了废话不多说,本篇博文将使用Apache POI 来读取Excel 数据并写入到word 文档中。
最终效果如图所示:
配置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>
配置 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
自定义配置属性
由于上面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
配置首页请求拦截并初始化文件夹
代码如下:
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"; } }
注意:这样当访问首页的时候就会初始化上传文件夹了
配置上传页面
然后我们需要一个上传页面,编写代码如下:
<!DOCTYPE html> <html lang="en" xmlns:th="http://www.thymeleaf.org"> <head> <!-- Required meta tags --> <meta charset="UTF-8"> <meta namephp="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}" rel="external nofollow" > <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>
上传文件
处理文件上传的控制器编写如下:
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 = "uploadFilpythoneName") 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中,最后重定向到一个处理文件下载的控制器中。
读取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()); php 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 candroidharacters: * 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; } }
将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(); } }
静态值存储
这里为了上传和下载分开,将下载路径暂时保存到一个静态值中存储。
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<>(); }
处理下载模块
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("下载失败"); } } }
文件下载工具类
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; } }
最终项目结构
最终项目结构如下
源码下载:https://github.com/geekxingyun/transport-excel-data-to-word
到此这篇关于Java实现读取Excel数据并写入到Word的文章就介绍到这了,更多相关Java读取Excel数据写入Word内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)!
精彩评论