POI Excel导入 HttpRequest 读取Exce文件流方式导入内容导出Excel实现

本文主要是介绍POI Excel导入 HttpRequest 读取Exce文件流方式导入内容导出Excel实现，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

1、使用场景

2、代码实现

2.1、Pom.xml导入 jar包

2.2、HTTPRequest 读取Excel InputStream方式导入读取Excel文件内容

2.2、导出数据内容到Excel之中

2.3、如果导入文件需要读取多个表并且有关联关系实现

3、成果展现

4、总结

5、参考文章

1、使用场景

目前大多数人做项目，都会使用Excel文件做导入导出功能。于是本人也观察了好些项目，无外乎以下两种实现方式：

把导入的Excel文件上传至Java应用服务器方式
直接从Http的Request请求之中获得上传的Excel文件流的方式直接读取内容
如果增加数据要求极高情况建议记录导入数据日志记录

其中使用把Exce文件上传到服务器方式，有如下优缺点：

优点：

因为文件上传到服务器，可以查看上传导入文件内容；并且可以查看具体上传导入文件的时间(服务器查看)

导入文件的操作是在文件上传至服务器后再执行的，意味着保障的Excel文件完整性，避免的导入过程突然断网风险。

缺点：

也是因为把文件上传至应用服务器啦，并且导入的操作仅仅执行完毕一次之后；后面的文件将没有多大意义啦，造成此类文件的占用空间。也许有人会说在服务器端写过shell脚本定时清除即可。

直接从Http的Request请求之中获得上传的Excel文件流的方式优缺点比较

优点：

能够直接使得导入过程从以前两个步骤，先上传然后在导入过程；可以直接从HttpRequest请求之中获得Excel的InputStream文件流直接读取Excel文件之中的内容进行导入。

不用存储多余的Excel文件到Java应用服务器上，节省磁盘空间。

缺点：

正因为不用先上传文件到应用服务器上，可能出现文件如果Excel文件内容较大；读取文件流过程比较慢。同时有风险如果上传过程之中突然断网；可能读取文件流不完整的情况。

如果增加数据要求极高情况建议记录导入数据日志记录

如果系统要求针对入库数据比较严格的情况下；建议吧数据导入的相关操作记录 是谁、什么时间、导入了哪些数据进入日志操作记录以备核查。

本文将主要使用直接通过读取HttpRequest之中Excel文件流的方式实现Excel的文件到导入的实现。

2、代码实现

2.1、Pom.xml导入 jar包

<!-- Excel 使用POI操作相关jar start -->
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.1</version>
</dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.1</version>
</dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>4.1.1</version>
</dependency>
<dependency><groupId>org.apache.xmlbeans</groupId><artifactId>xmlbeans</artifactId><version>3.1.0</version>
</dependency>
<dependency><groupId>org.apache.commons</groupId><artifactId>commons-collections4</artifactId><version>4.4</version>
</dependency>
<dependency><groupId>dom4j</groupId><artifactId>dom4j</artifactId><version>1.6.1</version>
</dependency>
<!-- Excel 使用POI操作相关jar end -->
<dependency><groupId>com.alibaba</groupId><artifactId>fastjson</artifactId><version>1.2.62</version>
</dependency>
<dependency><groupId>commons-fileupload</groupId><artifactId>commons-fileupload</artifactId><version>1.4</version>
</dependency>
<dependency><groupId>org.slf4j</groupId><artifactId>slf4j-api</artifactId><version>1.7.28</version>
</dependency>

2.2、HTTPRequest 读取Excel InputStream方式导入读取Excel文件内容

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.commons.CommonsMultipartResolver;import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.Part;
import java.io.IOException;
import java.io.InputStream;
import java.util.Collection;
import java.util.Iterator;/*** @Classname ExcelDirectRequestInputStreamUtil* @Description 直接从Request之中获得导入的Excel文件流内容* @Date 2019/11/9 11:46* @Created by jianxiapc*/
public class ExcelImportByRequestInputStreamUtil {private static Logger logger = LoggerFactory.getLogger(ExcelImportByRequestInputStreamUtil.class);/*** 获得上传文件未单个Excel文件时候 通过MultipartFile参数获得文件流信息* @param request* @param response* @param file* @return* @throws IOException*/public static InputStream getInputStreamByMultipartFileParameter(HttpServletRequest request, HttpServletResponse response,MultipartFile file) throws IOException {InputStream  excelInputStream = file.getInputStream();int length=excelInputStream.available();logger.info("length： "+length);logger.info("excelInputStream： "+excelInputStream.toString());return excelInputStream;}/*** 通过request获得InputStream获得文件内容* @param request* @param response* @param frontEndFileInputName* @return* @throws IOException*/public static InputStream getInputStreamByRequestAndFileInputName(HttpServletRequest request, HttpServletResponse response,String frontEndFileInputName) throws IOException {MultipartHttpServletRequest params=((MultipartHttpServletRequest) request);MultipartFile fileContent  = ((MultipartHttpServletRequest) request).getFiles(frontEndFileInputName).get(0);// fis 既是上传的文件流InputStream  excelInputStream = fileContent.getInputStream();int length=excelInputStream.available();logger.info("length： "+length);logger.info("excelInputStream： "+excelInputStream.toString());return excelInputStream;}/*** 通过request之中获得file的Parts** @param request* @param response* @throws IOException* @throws ServletException*/public static InputStream getInputStreamByRequestParts(HttpServletRequest request, HttpServletResponse response,String frontEndFileInputName) throws IOException, ServletException {logger.info("request.getContentType(): " + request.getContentType());if (!request.getContentType().split(";")[0].equals("multipart/form-data"))return null;Collection<Part> parts = request.getParts();InputStream excelInputStream =null;//通过获得part信息组装为InputStream流用读取文件内容int i=0;for (Iterator<Part> iterator = parts.iterator(); iterator.hasNext(); ) {Part part = iterator.next();logger.info("-----类型名称------->" + part.getName());logger.info("-----类型------->" + part.getContentType());logger.info("-----提交的类型名称------->" + part.getSubmittedFileName());logger.info("----流-------->" + part.getInputStream());logger.info("循环次数=== "+i);excelInputStream=part.getInputStream();i++;}return excelInputStream;}/*** 使用 commons-fileupload 上传Excel文件返回文件流信息* @param request* @param response* @return* @throws IOException*/public static InputStream getInputStreamByMultipartHttpServletRequest(HttpServletRequest request, HttpServletResponse response) throws IOException {InputStream excelInputStream =null;//将当前上下文初始化给  CommonsMutipartResolver （多部分解析器）CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(request.getSession().getServletContext());// 判断是否是多数据段提交格式if (multipartResolver.isMultipart(request)) {MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;Iterator<String> iter = multiRequest.getFileNames();logger.info("iter.hasNext(): " + iter.hasNext());Integer fileCount = 0;while (iter.hasNext()) {MultipartFile multipartFile = multiRequest.getFile(iter.next());String fileName = multipartFile.getOriginalFilename();logger.info("upload filename: " + fileName);if (fileName == null || fileName.trim().equals("")) {continue;}//20170207 针对IE环境下filename是整个文件路径的情况而做以下处理Integer index = fileName.lastIndexOf("\\");String newStr = "";if (index > -1) {newStr = fileName.substring(index + 1);} else {newStr = fileName;}if (!newStr.equals("")) {fileName = newStr;}logger.info("new filename: " + fileName);if (multipartFile != null) {excelInputStream= multipartFile.getInputStream();long fileSize= multipartFile.getSize();logger.info("fileSize: " + fileSize);}}}return excelInputStream;}
}

2.2、导出数据内容到Excel之中

import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder.BorderSide;
import org.spring.springboot.excel.poi.export.model.ExcelModelData;import javax.servlet.http.HttpServletResponse;
import java.awt.Color;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;/*** @Classname ExportExcelUtils* @Description Excel导出工具集合* @Date 2019/11/7 14:41* @Created by jianxiapc*/
public class ExcelWriterExportUtils {public static void exportExcel(HttpServletResponse response, String fileName, ExcelModelData data) throws Exception {// 告诉浏览器用什么软件可以打开此文件response.setHeader("content-Type", "application/vnd.ms-excel");// 下载文件的默认名称response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(fileName, "utf-8"));exportExcel(data, response.getOutputStream());}public static void exportExcel(ExcelModelData data, OutputStream out) throws Exception {XSSFWorkbook wb = new XSSFWorkbook();try {String sheetName = data.getName();if (null == sheetName) {sheetName = "Sheet1";}XSSFSheet sheet = wb.createSheet(sheetName);writeExcel(wb, sheet, data);wb.write(out);} finally {wb.close();}}private static void writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelModelData data) {int rowIndex = 0;rowIndex = writeTitlesToExcel(wb, sheet, data.getHeadTitles());writeRowsToExcel(wb, sheet, data.getRows(), rowIndex);autoSizeColumns(sheet, data.getHeadTitles().size() + 1);}private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles) {int rowIndex = 0;int colIndex = 0;Font titleFont = wb.createFont();titleFont.setFontName("simsun");titleFont.setBold(true);// titleFont.setFontHeightInPoints((short) 14);titleFont.setColor(IndexedColors.BLACK.index);XSSFCellStyle titleStyle = wb.createCellStyle();titleStyle.setAlignment(HorizontalAlignment.CENTER);titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);titleStyle.setFont(titleFont);setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));Row titleRow = sheet.createRow(rowIndex);// titleRow.setHeightInPoints(25);colIndex = 0;for (String field : titles) {Cell cell = titleRow.createCell(colIndex);cell.setCellValue(field);cell.setCellStyle(titleStyle);colIndex++;}rowIndex++;return rowIndex;}private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) {int colIndex = 0;Font dataFont = wb.createFont();dataFont.setFontName("simsun");// dataFont.setFontHeightInPoints((short) 14);dataFont.setColor(IndexedColors.BLACK.index);XSSFCellStyle dataStyle = wb.createCellStyle();dataStyle.setAlignment(HorizontalAlignment.CENTER);dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);dataStyle.setFont(dataFont);setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));for (List<Object> rowData : rows) {Row dataRow = sheet.createRow(rowIndex);// dataRow.setHeightInPoints(25);colIndex = 0;for (Object cellData : rowData) {Cell cell = dataRow.createCell(colIndex);if (cellData != null) {cell.setCellValue(cellData.toString());} else {cell.setCellValue("");}cell.setCellStyle(dataStyle);colIndex++;}rowIndex++;}return rowIndex;}private static void autoSizeColumns(Sheet sheet, int columnNumber) {for (int i = 0; i < columnNumber; i++) {int orgWidth = sheet.getColumnWidth(i);sheet.autoSizeColumn(i, true);int newWidth = (int) (sheet.getColumnWidth(i) + 100);if (newWidth > orgWidth) {sheet.setColumnWidth(i, newWidth);} else {sheet.setColumnWidth(i, orgWidth);}}}private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {style.setBorderTop(border);style.setBorderLeft(border);style.setBorderRight(border);style.setBorderBottom(border);style.setBorderColor(BorderSide.TOP, color);style.setBorderColor(BorderSide.LEFT, color);style.setBorderColor(BorderSide.RIGHT, color);style.setBorderColor(BorderSide.BOTTOM, color);}
}

2.3、如果导入文件需要读取多个表并且有关联关系实现

如果我们实际开发之中可能会针对读取的Excel文件内容；同时涉及到过个Excel的Sheet表格；并且每个表格之间有导入的前后关系。于是会使用读取Excel文件之中Sheet表格循环读取，具体代码如下所示：

    /*** 通过读取Excel的文件路径 读取 .xls or .xlsx 都行* @param excelFileDirPath* @param titleRow* @return*/public static List<Map<String,Object>> readExcelFileContentToList(String excelFileDirPath,String titleRow[]) throws IOException, InvalidFormatException {//通过工厂模式创建Excel  Workbook from an Excel file (.xls or .xlsx)Workbook workbook = WorkbookFactory.create(new File(excelFileDirPath));logger.info(" Excel Workbook 包含有 " + workbook.getNumberOfSheets() + " 张 Sheets : ");List<Map<String,Object>>  readExcelFileRowsContentList=new ArrayList<Map<String,Object>>();/*=============================================================迭代在workbook之中的所有的sheets (多种方式)=============================================================*///1、 获得一个sheetIterator 和 iterate 覆盖它Iterator<Sheet> sheetIterator = workbook.sheetIterator();logger.info("使用 Iterator 返回 Sheets ");while (sheetIterator.hasNext()) {Sheet sheet = sheetIterator.next();logger.info("=> " + sheet.getSheetName());}/**//2、 使用 for-each loop 返回 workbook之中的 sheetlogger.info("使用 for-each loop 返回 Sheets ");for(Sheet sheet: workbook) {logger.info("=> " + sheet.getSheetName());}// 3、使用 Java 8 forEach wih lambdalogger.info("使用 Java 8 forEach with lambda 返回 Sheets ");workbook.forEach(sheet -> {logger.info("=> " + sheet.getSheetName());});*//*==================================================================遍历工作表(Sheet)中的所有行(rows)和列(columns) (Multiple ways)==================================================================*/// 获得 索引为0 的 SheetSheet sheet = workbook.getSheetAt(0);// 创建一个数据格式器 目的是为 格式化 获得每cell的值为字符串DataFormatter dataFormatter = new DataFormatter();// 1、 你能够 obtain a rowIterator and columnIterator and iterate over themlogger.info(" 使用 Iterator Iterating over Rows and Columns ");Iterator<Row> rowIterator = sheet.rowIterator();boolean isFirstRow=true;//是否是第一行while (rowIterator.hasNext()) {Row row = rowIterator.next();Map<String,Object> rowMap= new HashMap<String,Object>();// Now let's iterate over the columns of the current rowIterator<Cell> cellIterator = row.cellIterator();int titleIndex=0;if(!isFirstRow){while (cellIterator.hasNext()) {Cell cell = cellIterator.next();String cellValue = dataFormatter.formatCellValue(cell);rowMap.put(titleRow[titleIndex],cellValue);logger.info(titleRow[titleIndex]+" ===>"+cellValue);titleIndex++;}logger.info("row content: "+rowMap.toString());readExcelFileRowsContentList.add(rowMap);}isFirstRow=false;}