java - 获取 Apache POI java.lang.IllegalStateException : Zip File is closed when trying to read a xlsx file

Question

当我尝试读取 xlsx 文件时，出现 java.lang.IllegalStateException:Zip 文件已关闭。该代码是从基于 spring 的应用程序访问的。我使用的是 apache poi 3.9。使用 apache POI 3.8 我遇到 Can't read file 。当从我的 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());
    }

}

Answer 1

我遇到了同样的问题，但后来我意识到我的文件名是 TestData.xlsx 并且我使用的是 TestData.csv