获取 Apache POI java.lang.IllegalStateException：尝试读取 xlsx 文件时 Zip 文件已关闭
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14256001/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-10-31 15:38:01 来源:igfitidea点击:
Getting Apache POI java.lang.IllegalStateException: Zip File is closed when trying to read a xlsx file
提问by sandy
I am getting java.lang.IllegalStateException: Zip File is closed when i try to read a xlsx file. The code is being accessed from a spring based app. I am using apache poi 3.9. With apache POI 3.8 i was getting Can't read file. The same code works fine when ran from my eclipse locally, but i get the exception when the app is deployed on my websphere server and is accessed when hosted on websphere. Can anyone please let me know what the problem is? The source code can be found below.
我收到 java.lang.IllegalStateException: Zip File is closed 当我尝试读取 xlsx 文件时。正在从基于 spring 的应用程序访问代码。我正在使用 apache poi 3.9。使用 apache POI 3.8，我无法读取文件。当从本地 eclipse 运行时，相同的代码工作正常，但是当应用程序部署在我的 websphere 服务器上并且在 websphere 上托管时被访问时，我得到了异常。任何人都可以让我知道问题是什么吗？源代码可以在下面找到。
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import org.apache.commons.validator.GenericValidator;
import org.apache.log4j.Logger;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.struts.action.ActionMessage;
import org.apache.struts.action.ActionMessages;
import org.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
public class XSSFListenerUtil {
private static List<String> allColValList = null;
private static boolean invalidTemplate = false;
private final static Logger log = Logger.getLogger(HSSFListenerUtil.class.getName()); // logger for the process.
private static BaseViewBean baseViewBean$Session;
/**
* The type of the data value is indicated by an attribute on the cell.
* The value is usually in a "v" element within the cell.
*/
enum xssfDataType {
BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER,
}
class MyXSSFSheetHandler extends DefaultHandler {
/**
* Table with styles
*/
private StylesTable stylesTable;
/**
* Table with unique strings
*/
private ReadOnlySharedStringsTable sharedStringsTable;
/**
* Destination for data
*/
//private final PrintStream output;
/**
* Number of columns to read starting with leftmost
*/
private final int minColumnCount;
// Set when V start element is seen
private boolean vIsOpen;
// Set when cell start element is seen;
// used when cell close element is seen.
private xssfDataType nextDataType;
// Used to format numeric cell values.
private short formatIndex;
private String formatString;
private final DataFormatter formatter;
private int thisColumn = -1;
// The last column printed to the output stream
private int lastColumnNumber = -1;
// Gathers characters as they are seen.
private StringBuffer value;
/**
* Accepts objects needed while parsing.
*
* @param styles Table of styles
* @param strings Table of shared strings
* @param cols Minimum number of columns to show
* @param target Sink for output
*/
public MyXSSFSheetHandler(
StylesTable styles,
ReadOnlySharedStringsTable strings,
int cols) {
this.stylesTable = styles;
this.sharedStringsTable = strings;
this.minColumnCount = cols;
this.value = new StringBuffer();
this.nextDataType = xssfDataType.NUMBER;
this.formatter = new DataFormatter();
}
/*
* (non-Javadoc)
* @see org.xml.sax.helpers.DefaultHandler#startElement(java.lang.String, java.lang.String, java.lang.String, org.xml.sax.Attributes)
*/
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
if ("inlineStr".equals(name) || "v".equals(name)) {
vIsOpen = true;
// Clear contents cache
value.setLength(0);
}
// c => cell
else if ("c".equals(name)) {
// Get the cell reference
String r = attributes.getValue("r");
int firstDigit = -1;
for (int c = 0; c < r.length(); ++c) {
if (Character.isDigit(r.charAt(c))) {
firstDigit = c;
break;
}
}
thisColumn = nameToColumn(r.substring(0, firstDigit));
// Set up defaults.
this.nextDataType = xssfDataType.NUMBER;
this.formatIndex = -1;
this.formatString = null;
String cellType = attributes.getValue("t");
String cellStyleStr = attributes.getValue("s");
if ("b".equals(cellType))
nextDataType = xssfDataType.BOOL;
else if ("e".equals(cellType))
nextDataType = xssfDataType.ERROR;
else if ("inlineStr".equals(cellType))
nextDataType = xssfDataType.INLINESTR;
else if ("s".equals(cellType))
nextDataType = xssfDataType.SSTINDEX;
else if ("str".equals(cellType))
nextDataType = xssfDataType.FORMULA;
else if (cellStyleStr != null) {
// It's a number, but almost certainly one with a special style or format
int styleIndex = Integer.parseInt(cellStyleStr);
XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
this.formatIndex = style.getDataFormat();
this.formatString = style.getDataFormatString();
if (this.formatString == null)
this.formatString = BuiltinFormats.getBuiltinFormat(this.formatIndex);
}
}
}
StringBuffer columnvalue = new StringBuffer();
/*
* (non-Javadoc)
* @see org.xml.sax.helpers.DefaultHandler#endElement(java.lang.String, java.lang.String, java.lang.String)
*/
public void endElement(String uri, String localName, String name)
throws SAXException {
String thisStr = null;
// v => contents of a cell
if ("v".equals(name)) {
// Process the value contents as required.
// Do now, as characters() may be called more than once
switch (nextDataType) {
case BOOL:
char first = value.charAt(0);
thisStr = first == '0' ? "FALSE" : "TRUE";
break;
case ERROR:
thisStr = "\"ERROR:" + value.toString() + '"';
break;
case FORMULA:
// A formula could result in a string value, so always add double-quote characters.
thisStr = '"' + value.toString() + '"';
break;
case INLINESTR:
XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
thisStr = '"' + rtsi.toString() + '"';
break;
case SSTINDEX:
String sstIndex = value.toString();
try {
int idx = Integer.parseInt(sstIndex);
XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx));
thisStr = '"' + rtss.toString() + '"';
}
catch (NumberFormatException ex) {
log.error("Failed to parse SST index '" + sstIndex + "': " + ex.toString());
throw new SAXException("Failed to parse SST index '" + sstIndex + "': " + ex.toString());
}
break;
case NUMBER:
String n = value.toString();
if (this.formatString != null)
thisStr = formatter.formatRawCellContents(Double.parseDouble(n), this.formatIndex, this.formatString);
else
thisStr = n;
break;
default:
thisStr = "(TODO: Unexpected type: " + nextDataType + ")";
break;
}
// Output after we've seen the string contents
// Emit commas for any fields that were missing on this row
if (lastColumnNumber == -1) {
lastColumnNumber = 0;
}
for (int i = lastColumnNumber; i < thisColumn; ++i){
//output.print(',');
columnvalue.append(",");
}
// Might be an empty string.
columnvalue.append(thisStr);
// Update column
if (thisColumn > -1)
lastColumnNumber = thisColumn;
} else if ("row".equals(name)) {
// Print out any missing commas if needed
if (minColumns > 0) {
// Columns are 0 based
if (lastColumnNumber == -1) {
lastColumnNumber = 0;
}
for (int i = lastColumnNumber; i < (this.minColumnCount); i++) {
//output.print(',');
columnvalue.append(",");
}
}
// We're onto a new row
if(!GenericValidator.isBlankOrNull(columnvalue.toString())){
String completeVal = columnvalue.toString().replaceAll("\"", "");
allColValList.add(completeVal);
}
columnvalue = new StringBuffer("");
lastColumnNumber = -1;
}
}
/**
* Captures characters only if a suitable element is open.
* Originally was just "v"; extended for inlineStr also.
*/
public void characters(char[] ch, int start, int length)
throws SAXException {
if (vIsOpen)
value.append(ch, start, length);
}
/**
* Converts an Excel column name like "C" to a zero-based index.
*
* @param name
* @return Index corresponding to the specified name
*/
private int nameToColumn(String name) {
int column = -1;
for (int i = 0; i < name.length(); ++i) {
int c = name.charAt(i);
column = (column + 1) * 26 + c - 'A';
}
return column;
}
}
private OPCPackage xlsxPackage;
private int minColumns;
/**
*
* @param pkg The XLSX package to process
* @param output The PrintStream to output the CSV to
* @param minColumns The minimum number of columns to output, or -1 for no minimum
*/
public XSSFListenerUtil(OPCPackage pkg, int minCol, ArrayList<String> colList, BaseViewBean baseViewBean$Session) {
this.xlsxPackage = pkg;
this.minColumns = minCol;
XSSFListenerUtil.allColValList = colList;
XSSFListenerUtil.baseViewBean$Session = baseViewBean$Session;
}
/**
* Parses and shows the content of one sheet
* using the specified styles and shared-strings tables.
*
* @param styles
* @param strings
* @param sheetInputStream
*/
public void processSheet(
StylesTable styles,
ReadOnlySharedStringsTable strings,
InputStream sheetInputStream)
throws IOException, ParserConfigurationException, SAXException {
InputSource sheetSource = new InputSource(sheetInputStream);
SAXParserFactory saxFactory = SAXParserFactory.newInstance();
SAXParser saxParser = saxFactory.newSAXParser();
XMLReader sheetParser = saxParser.getXMLReader();
ContentHandler handler = new MyXSSFSheetHandler(styles, strings, this.minColumns);
sheetParser.setContentHandler(handler);
sheetParser.parse(sheetSource);
}
/**
* Initiates the processing of the XLS workbook file to CSV.
*
* @throws IOException
* @throws OpenXML4JException
* @throws ParserConfigurationException
* @throws SAXException
*/
public void process()
throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {
ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(xlsxPackage);
XSSFReader xssfReader = new XSSFReader(xlsxPackage);
StylesTable styles = xssfReader.getStylesTable();
XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
int index = 0;
while (iter.hasNext()) {
InputStream stream = iter.next();
processSheet(styles, strings, stream);
stream.close();
++index;
}
}
public static void parseAndRetrieveExcelDetails(String filePath, InvestmentDimDao investmentDao,
BaseViewBean baseViewBean$Session, ActionMessages errors, boolean invalidTemplateIdentifier) throws Exception{
int minColumns = -1;
invalidTemplate = invalidTemplateIdentifier;
ArrayList<String> initialList = new ArrayList<String>();
//OPCPackage pkg = OPCPackage.open(filePath, PackageAccess.READ);
OPCPackage pkg = OPCPackage.open(filePath);
XSSFListenerUtil xssfSheetProcessor = new XSSFListenerUtil(pkg, minColumns, initialList, baseViewBean$Session);
xssfSheetProcessor.process();
validateAndExtractExcelData();
log.info("Finished processing all records");
if(invalidTemplate){
errors.add("failure", new ActionMessage("secaccess.file.invaliddata"));
}else{
log.info("All records parsed successfully");
}
allColValList.clear();
allColValList = null;
pkg.close();
System.gc();
}
private static void validateAndExtractExcelData() throws NumberFormatException {
int rowCnt = 1;
int colCnt = 0;
ExcelListenerBean listenerBean;
ExcelListenerBean bean;
Map <String, ExcelListenerBean> excelRecords = new TreeMap<String, ExcelListenerBean>();
for(String colVal : allColValList) {
if(rowCnt==1 && !AppGlobalConstants.HardCodedValues.G_L_CALC_HEADER1.equals(colVal)) {
invalidTemplate = true;
}
if(rowCnt==2 && !AppGlobalConstants.HardCodedValues.G_L_CALC_HEADER2.equals(colVal) & !invalidTemplate) {
invalidTemplate = true;
}
if(rowCnt==3 && !AppGlobalConstants.HardCodedValues.G_L_CALC_HEADER3.equals(colVal) & !invalidTemplate) {
invalidTemplate = true;
}
if(rowCnt > 3 && !invalidTemplate) {
listenerBean = new ExcelListenerBean(AppGlobalConstants.HardCodedValues.SQL_TYPE);
String[] allValues = colVal.split(",");
for(String cellContents : allValues){
switch (colCnt) {
case 0:
if(GenericValidator.isBlankOrNull(cellContents)){
invalidTemplate = true;
}else{
listenerBean = new ExcelListenerBean(AppGlobalConstants.HardCodedValues.SQL_TYPE);
listenerBean.setId(rowCnt);
listenerBean.setShortName(cellContents);
}
break;
case 1:
if(GenericValidator.isBlankOrNull(cellContents)){
invalidTemplate = true;
break;
}else{
listenerBean.setFmrCusip(cellContents);
}
break;
case 2:
if(GenericValidator.isBlankOrNull(cellContents) || !GenericValidator.isDouble(cellContents)){
invalidTemplate = true;
break;
}else{
listenerBean.setIncorrectTrdShares(Double.valueOf(cellContents));
}
break;
case 3:
if(GenericValidator.isBlankOrNull(cellContents) || !GenericValidator.isDouble(cellContents)){
invalidTemplate = true;
break;
}else{
listenerBean.setIncorrectTrdPrice(Double.valueOf(cellContents));
}
break;
case 4:
if(GenericValidator.isBlankOrNull(cellContents)){
invalidTemplate = true;
break;
}else{
listenerBean.setIncorrectTrdBuySell(cellContents);
}
break;
case 5:
if(GenericValidator.isBlankOrNull(cellContents) || !GenericValidator.isDouble(cellContents)){
invalidTemplate = true;
break;
}else{
listenerBean.setIncorrectTrdCommRate(Double.valueOf(cellContents));
}
break;
case 6:
if(GenericValidator.isBlankOrNull(cellContents) || !GenericValidator.isDouble(cellContents)){
invalidTemplate = true;
break;
}else{
listenerBean.setIncorrectTrdCommission(Double.valueOf(cellContents));
}
break;
case 7:
if(GenericValidator.isBlankOrNull(cellContents) || !GenericValidator.isDouble(cellContents)){
invalidTemplate = true;
break;
}else{
listenerBean.setIncorrectTrdFees(Double.valueOf(cellContents));
}
break;
case 8:
if(GenericValidator.isBlankOrNull(cellContents.toString()) || !GenericValidator.isDouble(cellContents.toString())){
invalidTemplate = true;
break;
}else{
listenerBean.setIncorrectTrdNet(Double.valueOf(cellContents.toString()));
}
break;
case 9:
if(GenericValidator.isBlankOrNull(cellContents) || !GenericValidator.isDouble(cellContents)){
invalidTemplate = true;
break;
}else{
listenerBean.setCorrectionTrdShares(Double.valueOf(cellContents));
}
break;
case 10:
if(GenericValidator.isBlankOrNull(cellContents) || !GenericValidator.isDouble(cellContents)){
invalidTemplate = true;
break;
}else{
listenerBean.setCorrectionTrdPrice(Double.valueOf(cellContents));
}
break;
case 11:
if(GenericValidator.isBlankOrNull(cellContents)){
invalidTemplate = true;
break;
}else{
listenerBean.setCorrectionTrdBuySell(cellContents);
}
break;
case 12:
if(GenericValidator.isBlankOrNull(cellContents) || !GenericValidator.isDouble(cellContents)){
invalidTemplate = true;
break;
}else{
listenerBean.setCorrectionTrdCommRate(Double.valueOf(cellContents));
}
break;
case 13:
if(GenericValidator.isBlankOrNull(cellContents) || !GenericValidator.isDouble(cellContents)){
invalidTemplate = true;
break;
}else{
listenerBean.setCorrectionTrdCommission(Double.valueOf(cellContents));
}
break;
case 14:
if(GenericValidator.isBlankOrNull(cellContents) || !GenericValidator.isDouble(cellContents)){
invalidTemplate = true;
break;
}else{
listenerBean.setCorrectionTrdFees(Double.valueOf(cellContents));
}
break;
case 15:
if(GenericValidator.isBlankOrNull(cellContents) || !GenericValidator.isDouble(cellContents)){
invalidTemplate = true;
break;
}else{
listenerBean.setCorrectionTrdNet(Double.valueOf(cellContents));
}
break;
}
colCnt++;
}
bean = excelRecords.get(listenerBean.getShortName());
if (bean != null){
listenerBean.setAcctGainLossAmt(new BigDecimal(Double.valueOf(bean.getAcctGainLossAmt()) + listenerBean.getCorrectionTrdNet() + listenerBean.getIncorrectTrdNet()).toPlainString());
} else {
listenerBean.setAcctGainLossAmt(new BigDecimal(listenerBean.getCorrectionTrdNet() + listenerBean.getIncorrectTrdNet()).toPlainString());
}
excelRecords.put(listenerBean.getShortName(), listenerBean);
}
colCnt = 0;
rowCnt++;
}
baseViewBean$Session.setExcelRecLst(new ArrayList<ExcelListenerBean>(excelRecords.values()));
log.info("Number of records for update is "+excelRecords.size());
}
}
回答by Chirag Khimani
I was running into same problem but then I realize that my file name was TestData.xlsx and I was using TestData.csv
我遇到了同样的问题，但后来我意识到我的文件名是 TestData.xlsx 并且我正在使用 TestData.csv
