struts2上传文件代码XSSFWorkbook报错
前言
struts2上传文件报错，各种报错不断，上传的excel2007文件 new XSSFWorkbook(fileInputStream)报错，尝试了各种方法，还是无法解决，原因和导包版本有问题No result defined for action/NoClassDefFoundError/ClassNotFoundException/NoSuchMethodError
com.opensymphony.xwork2.config.ConfigurationException: No result defined for action com.chinatelecom.shield.action.fee.export.ImportFileAction$$EnhancerBySpringCGLIB$$c0df3a2e and result exception
at com.opensymphony.xwork2.DefaultActionInvocation.executeResult(DefaultActionInvocation.java:378) ~[struts2-core-2.5.33.jar:2.5.33]
java.lang.ClassNotFoundException: org.apache.commons.io.output.UnsynchronizedByteArrayOutputStream
at org.apache.catalina.loader.WebappClassLoaderBase.loadClass(WebappClassLoaderBase.java:1358) ~[catalina.jar:8.5.57]
at org.apache.catalina.loader.WebappClassLoaderBase.loadClass(WebappClassLoaderBase.java:1180) ~[catalina.jar:8.5.57]
... 120 more
java.lang.NoClassDefFoundError: org/apache/commons/io/output/UnsynchronizedByteArrayOutputStream
at com.chinatelecom.shield.action.fee.export.ImportFileAction.execute(ImportFileAction.java:116) ~[classes/:?]
Caused by: java.lang.NoSuchMethodError: org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCols.getColList()Ljava/util/List;
at org.apache.poi.xssf.usermodel.helpers.ColumnHelper.cleanColumns(ColumnHelper.java:56) ~[poi-ooxml-4.0.1.jar:4.0.1]
at org.apache.poi.xssf.usermodel.helpers.ColumnHelper.<init>(ColumnHelper.java:46) ~[poi-ooxml-4.0.1.jar:4.0.1]
代码
按照常规方式导入，应该没问题，结果一直出错
FileInputStream fileInputStream = new FileInputStream(file);
//读取导入的Excel文件内容 这一段报错
Workbook xssfWorkbook = new XSSFWorkbook(fileInputStream);
原因
POI版本问题，太坑了
去官网查看每个POI的版本发布时间，发现4.0.1版本是2018-11-27发布的，这去解析个Excel2007应该是绰绰有余的，尝试升到5.2.3问题还是没解决，结果是因为我漏看了ooxml-schemas这个包，看起来无关痛痒，查了下2008-11-01发布，虽然比Excel2007晚了一年，但是和4.0.1版本差了10年，这一看就有问题，赶紧按照版本时间升级到了1.4 2018-09-02发布，目前是最新版，上传即可解决
org.apache.poiMaven仓库
<!-- 2018-09-02 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>ooxml-schemas</artifactId>
<version>1.4</version>
</dependency>
<!-- 2018-11-27 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<!-- 2018-11-27 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<!-- 2018-11-27 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>
<!-- 2008-11-01 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>ooxml-schemas</artifactId>
<version>1.0</version>
</dependency>
参考文章：解决 struts2上传的excel2007文件,在用poi处理时通过new XSSFWorkbook(inputStream)出错
原问题文章：new一个xssfworkbook时出错_struts2上传的excel2007文件,在用poi处理时通过new XSSFWorkbook(inputStream)出错...
导入代码
package com.chinatelecom.shield.action.fee.export;
import com.chinatelecom.shield.common.annotation.ControllerLog;
import com.chinatelecom.shield.common.enums.BusinessType;
import com.chinatelecom.shield.common.pojo.R;
import com.ctid.core.base.BaseAction;
import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.struts2.convention.annotation.Action;
import org.apache.struts2.convention.annotation.Namespace;
import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
/**
* 导入预警因子
* @ClassName ImportAction
* @Description TODO
* @Author zl
* @Date 2025/7/16 9:10
*/
@Getter
@Slf4j
@Namespace("/feeInfoApi")
@Action(value = "upload")
public class ImportFileAction2 extends BaseAction {
@Setter//上传文件自动注入 需要其他参数 仿写 写入名字即可注入
private File file;
@Override
@ControllerLog(title = "费用模块-导入", businessType = BusinessType.EXPORT, saveLog = true)
public String execute() throws Exception {
if (null == file) {
log.error("file参数为空！");
throw new NullPointerException("file参数为空！");
}
log.info(file.getName());
//获取多少列值
int forCount = 0;
//获取输入流
FileInputStream fileInputStream = new FileInputStream(file);
//读取导入的Excel文件内容 file.getAbsolutePath() 这个也可以读取
//Workbook xssfWorkbook = new XSSFWorkbook( file.getAbsolutePath());
Workbook xssfWorkbook = new XSSFWorkbook(fileInputStream);
//获取第一个sheet工作薄
Sheet sheet = xssfWorkbook.getSheetAt(0);
//读取出来的数据放到数组 保存每列
List<Object> itemArr = new ArrayList<>();
//获取sheet的最大row下标，实际获取的行数-1
int lastRowNum = sheet.getLastRowNum();
if (lastRowNum < 0) {
return ERROR;
}
for (Row row : sheet) {
//跳过表头
if (row.getRowNum() < 1) {
continue;
}
List<String> fieldArrs = new ArrayList<>();
for (int i = 0; i < row.getLastCellNum(); i++) {
//判断导入的excel文件中的是否存在空
Cell cell = row.getCell(i);
if (cell == null) {
continue;
}
//单元格值
String stringCellValue = cell.getStringCellValue();
fieldArrs.add(stringCellValue);
}
itemArr.add(fieldArrs);
}
if (itemArr.size() == 0) {
log.info("未读取到数组保存");
return ERROR;
}
//返回信息
Object returnObj = new Object();
//保存数据
R.ok(returnObj).toResponse();
return SUCCESS;
}
}
草稿代码
真的尝试了很多方法
package com.chinatelecom.shield.action.fee.export;
import com.chinatelecom.shield.common.annotation.ControllerLog;
import com.chinatelecom.shield.common.enums.BusinessType;
import com.chinatelecom.shield.common.pojo.R;
import com.chinatelecom.shield.common.util.WebUtils;
import com.chinatelecom.shield.service.fee.FeeWarnFactorInvoiceContentService;
import com.chinatelecom.shield.service.fee.FeeWarnFactorInvoiceDeptService;
import com.ctid.core.base.BaseAction;
import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.FileUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.struts2.convention.annotation.Action;
import org.apache.struts2.convention.annotation.Namespace;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
/**
* 导入预警因子
* @ClassName ImportAction
* @Description TODO
* @Author zl
* @Date 2025/7/16 9:10
*/
@Getter
@Slf4j
@Namespace("/feeInfoApi")
@Action(value = "upload")
public class ImportFileAction2 extends BaseAction {
@Setter
private File file;
@Setter
private String exportMethod;
//接收不到
// private MultipartFile upload;
@Resource//发票内容因子
private FeeWarnFactorInvoiceContentService feeWarnFactorInvoiceContentService;
@Resource//开票单位因子
private FeeWarnFactorInvoiceDeptService feeWarnFactorInvoiceDeptService;
@Override
@ControllerLog(title = "费用模块-导入", businessType = BusinessType.EXPORT, saveLog = true)
public String execute() throws Exception {
log.info("进入系统上传文件控制层，类名称：UploadFileController");
try {
HttpServletRequest request = WebUtils.getRequest();
// MultipartResolver resolver = new CommonsMultipartResolver(request.getSession().getServletContext());
// MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
// //获取上传上来的文件
//
// Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
// for (Map.Entry<String, MultipartFile> entry : fileMap.entrySet()) {
// MultipartFile file = entry.getValue();
// String fileName = file.getOriginalFilename();
// System.out.println(fileName);
// XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file.getInputStream());
//// String path = "C:\\Users\\user\\Desktop\\" + fileName;
//// file.transferTo(new File(path));
// }
if (null == file) {
log.error("file参数为空！");
throw new NullPointerException("file参数为空！");
}
//设置文件类型
// Files.setAttribute(file.toPath(),"basic:system:filetype","xlsx");
// Desktop.getDesktop().edit(file);
// Collection<Part> parts = request.getParts();
// Part filePart = request.getPart("file");
// String fileName = filePart.getSubmittedFileName();
// InputStream inputStream = filePart.getInputStream();
// Path path = file.toPath();
// File absoluteFile = file.getAbsoluteFile();
log.info(file.getName());
// InputStream resourceAsStream = getClass().getResourceAsStream("/");
/*将文件上传到upload文件夹下*/
File savefile = new File(getClass().getResourceAsStream("/").toString(), "上传.xlsx");
if(savefile.exists()){
savefile.delete();
}
FileUtils.copyFile(file, savefile);
System.out.println(savefile.getAbsolutePath());
// if(savefile!=null){
// return ERROR;
// }
//获取多少列值
int forCount=0;
switch (exportMethod){
case "importFactorInvoiceContentTemplate":
forCount=1;
break;
case "importFactorInvoiceDeptTemplate":
forCount=4;
break;
default:
return ERROR;
}
// BufferedInputStream bufferedInputStream = new BufferedInputStream(Files.newInputStream(file.toPath()), 15192);
FileInputStream fileInputStream = new FileInputStream(file);
//读取导入的Excel文件内容
// String absolutePath = file.getAbsolutePath();
Workbook xssfWorkbook = new XSSFWorkbook(fileInputStream);
//获取第一个sheet工作薄
Sheet sheet = xssfWorkbook.getSheetAt(0);
//读取出来的数据放到数组 保存每列
List<Object> itemArr = new ArrayList<>();
//获取sheet的最大row下标，实际获取的行数-1
int lastRowNum = sheet.getLastRowNum();
if (lastRowNum < 0) {
return ERROR;
}
for (Row row : sheet) {
//跳过表头
if (row.getRowNum() < 1) {
continue;
}
List<String> fieldArrs = new ArrayList<>();
for (int i = 0; i < forCount; i++) {
//判断导入的excel文件中的是否存在空
Cell cell = row.getCell(i);
if(cell==null){
continue;
}
//单元格值
String stringCellValue = cell.getStringCellValue();
fieldArrs.add(stringCellValue);
}
itemArr.add(fieldArrs);
}
if(itemArr.size()==0){
log.info("未读取到数组保存");
return ERROR;
}
//返回信息
Object returnObj = new Object();
//保存数据
switch (exportMethod){
case "importFactorInvoiceContentTemplate":
returnObj= feeWarnFactorInvoiceContentService.saveImport(itemArr);
break;
case "importFactorInvoiceDeptTemplate":
returnObj= feeWarnFactorInvoiceDeptService.saveImport(itemArr);
break;
default:
return ERROR;
}
R.ok(returnObj).toResponse();
return SUCCESS;
} catch (Exception e) {
log.error(e.getMessage(), e);
R.error(e.getMessage()).toResponse();
return ERROR;
}
}
}
浙公网安备 33010602011771号