{"id":1357,"date":"2016-02-22T22:48:31","date_gmt":"2016-02-22T14:48:31","guid":{"rendered":"http:\/\/coderbee.net\/?p=1357"},"modified":"2016-02-28T22:51:55","modified_gmt":"2016-02-28T14:51:55","slug":"%e5%9f%ba%e4%ba%8epoi%e6%a3%80%e6%9f%a5-excel-%e6%96%87%e4%bb%b6%e7%9a%84%e8%a1%8c%e6%95%b0%e3%80%81%e5%88%97%e6%95%b0%e6%98%af%e5%90%a6%e8%b6%85%e5%87%ba%e9%99%90%e5%88%b6","status":"publish","type":"post","link":"https:\/\/coderbee.net\/index.php\/java\/20160222\/1357","title":{"rendered":"\u57fa\u4e8ePOI\u68c0\u67e5 excel \u6587\u4ef6\u7684\u884c\u6570\u3001\u5217\u6570\u662f\u5426\u8d85\u51fa\u9650\u5236"},"content":{"rendered":"<p>\u7528 POI \u89e3\u6790 xlsx \u65f6\uff0c\u5982\u679c\u7528\u8fd9\u6837\u7684\u65b9\u5f0f <code>Workbook workbook = WorkbookFactory.create(new File(\"super-many-row.xlsx\"));<\/code> \u89e3\u6790\u5177\u6709\u8d85\u8fc7 100 \u4e07\u884c\u7684 excel \uff0c\u5f88\u53ef\u80fd\u5185\u5b58\u5c31\u88ab\u8017\u5c3d\u4e86\u3002<\/p>\n<p>\u56e0\u4e3a\u8fd9\u6837\u867d\u7136\u7b80\u5355\uff0c\u4f46\u662f\u8981\u7b49\u6574\u4e2a excel \u90fd\u89e3\u6790\u5b8c\u6210\u3001\u8f6c\u6362\u6210 POI \u5b9a\u4e49\u7684\u5bf9\u8c61\u540e\u624d\u4f1a\u8fd4\u56de\u5230\u4e1a\u52a1\u4ee3\u7801\uff0c\u8fd9\u65f6\u5019\u624d\u5224\u65ad\u884c\u6570\u662f\u5426\u8d85\u8fc7\u9650\u5236\u5c31\u665a\u4e86\u3002<\/p>\n<p>\u9700\u8981\u4e00\u79cd\u66f4\u9ad8\u6548\u7684\u65b9\u5f0f\uff0c\u5728\u89e3\u6790\u5b8c\u6210\u524d\u5c31\u5224\u65ad\u662f\u5426\u8d85\u8fc7\u9650\u5236\uff0c\u4e86\u89e3\u4e86 POI \u7684 API \u548c xlsx \u7684\u57fa\u672c\u683c\u5f0f\u540e\uff0c\u5c31\u6709\u4e86\u4e0b\u9762\u8fd9\u4e2a\u5de5\u5177\u7c7b\uff0c\u57fa\u4e8e POI 3.13\u3002<\/p>\n<p>2016.02.28 \u66f4\u65b0\uff1a\u589e\u52a0\u5bf9 sheet \u6570\u91cf\u7684\u6821\u9a8c\u624d\u591f\u5b8c\u6574\u3002<\/p>\n<p><!--more--><\/p>\n<pre><code class=\"java\">\n\/**\n * \u68c0\u67e5 excel \u6587\u4ef6\u7684\u884c\u6570\u3001\u5217\u6570\u662f\u5426\u8d85\u51fa\u9650\u5236\n *\n *\/\npublic class RowColLimitCheck {\n    \/**\n     * \u8868\u793a\u4e0d\u68c0\u6d4b\u7684\u957f\u5ea6\n     *\/\n    public static final int UN_LIMITED = -1;\n\n    \/**\n     * \u8868\u793a excel \u8d85\u51fa\u6700\u5927\u884c\u6570\u3001\u5217\u6570\u7684\u5f02\u5e38\u7c7b\n     *\/\n    @SuppressWarnings(\"serial\")\n    static class RowColLimitBreakException extends RuntimeException {\n        public RowColLimitBreakException(String msg) {\n            super(msg);\n        }\n    }\n\n    \/**\n     * \u68c0\u6d4b HSSF\uff08xls\uff09\u683c\u5f0f\u7684 excel \u6587\u4ef6\u662f\u5426\u8d85\u8fc7\u6700\u5927\u884c\u6570\u3001\u5217\u6570\u7684\u4e8b\u4ef6\u76d1\u542c\u7c7b\n     *\/\n    static class ColRowLimitHSSFListener implements HSSFListener {\n        private int maxSheetCount;\n        private int maxRowCount;\n        private int maxColCount;\n        private int sheetCount;\n        private int rowCount;\n\n        public ColRowLimitHSSFListener(int maxSheetCount, int maxRowCount,\n                int maxColCount) {\n            this.maxRowCount = maxRowCount;\n            this.maxColCount = maxColCount;\n        }\n\n        @Override\n        public void processRecord(Record record) {\n            if (record.getSid() == BoundSheetRecord.sid) {\n                if ((++sheetCount) > maxSheetCount) {\n                    throw new RowColLimitBreakException(\"sheet_exceed\");\n                }\n                rowCount = 0; \/\/ \u65b0\u7684sheet\uff0c\u91cd\u7f6e\u8ba1\u6570\n            } else if (record.getSid() == RowRecord.sid) {\n                if (maxRowCount != UN_LIMITED && (rowCount++) > maxRowCount) {\n                    throw new RowColLimitBreakException(\"row_exceed\");\n                }\n\n                RowRecord recordRow = (RowRecord) record;\n                if (maxColCount != UN_LIMITED\n                        && recordRow.getLastCol() > maxColCount) {\n                    throw new RowColLimitBreakException(\"col_exceed\");\n                }\n            }\n        }\n    }\n\n    \/**\n     * \u68c0\u6d4b\u540e\u7f00\u4e3a xls \u7684excel \u662f\u5426\u6ee1\u8db3\u6700\u5927\u884c\u6570\u3001\u5217\u6570\u7684\u9650\u5236\u3002\n     *\n     * @param file\n     *            excel \u6587\u4ef6\u8def\u5f84\n     * @param maxSheetCount\n     *            \u5141\u8bb8\u7684\u6700\u5927sheet\u6570\n     * @param maxRowCount\n     *            \u5141\u8bb8\u7684\u6700\u5927\u884c\u6570\n     * @param maxColCount\n     *            \u5141\u8bb8\u7684\u6700\u5927\u5217\u6570\n     * @return\n     * null: \u8868\u793a\u6ca1\u6709\u95ee\u9898\uff1b\n     * \"sheet_exceed\":sheet\u6570\u91cf\u8d85\u8fc7\u9650\u5236\uff1b\n     * \"row_exceed\":\u884c\u6570\u8d85\u51fa\u9650\u5236\uff1b\n     * \"col_exceed\":\u5217\u6570\u8d85\u51fa\u9650\u5236\n     * \n     * @throws IOException\n     *\/\n    public static String checkRowColCount(InputStream fin, String postFix,\n            int maxSheetCount, int maxRowCount, int maxColCount)\n                    throws Exception {\n        if (!postFix.equalsIgnoreCase(\"xls\")\n                && !postFix.equalsIgnoreCase(\"xlsx\")) {\n            throw new IllegalArgumentException(\"not xls or xlsx file\");\n        }\n\n        if (maxRowCount == UN_LIMITED && maxColCount == UN_LIMITED\n                && maxSheetCount == UN_LIMITED) {\n            return null;\n        }\n\n        if (postFix.equals(\"xls\")) {\n            return checkXls(fin, maxSheetCount, maxRowCount, maxColCount);\n        } else {\n            return checkXlsx(fin, maxSheetCount, maxRowCount, maxColCount);\n        }\n    }\n\n    private static String checkXls(InputStream fin, int maxSheetCount,\n            int maxRowCount, int maxColCount) throws IOException {\n        InputStream din = null;\n        POIFSFileSystem poifs = new POIFSFileSystem(fin);\n        try {\n            din = poifs.createDocumentInputStream(\"Workbook\");\n            HSSFRequest req = new HSSFRequest();\n            req.addListenerForAllRecords(new ColRowLimitHSSFListener(\n                    maxSheetCount, maxRowCount, maxColCount));\n\n            HSSFEventFactory factory = new HSSFEventFactory();\n            factory.processEvents(req, din);\n            return null;\n        } catch (RowColLimitBreakException ex) {\n            return ex.getMessage();\n        } finally {\n            IOUtils.closeQuietly(din);\n            poifs.close();\n        }\n    }\n\n    \/**\n     * \u6807\u7b7e\u5143\u7d20\u540d\u542b\u4e49\uff1a\n     * \"sheetData\":sheet \u6807\u7b7e\uff1b\n     * \"row\":\u884c\u6807\u7b7e\uff1b\n     * \"c\":\u5355\u5143\u683c\uff1b\n     * \"v\":\u5355\u5143\u683c\u7684\u503c\n     *\/\n    static class SheetHandler extends DefaultHandler {\n        private int maxRowCount;\n        private int maxColCount;\n        private int rowCount;\n        private int colCount;\n        private List<String> valueList = new LinkedList<String>();\n        private StringBuilder cellValue = new StringBuilder(32);\n\n        public SheetHandler(int maxRowCount, int maxColCount) {\n            this.maxRowCount = maxRowCount;\n            this.maxColCount = maxColCount;\n        }\n\n        private boolean isRowElement(String name) {\n            return \"row\".equals(name);\n        }\n\n        private boolean isValueElement(String name) {\n            return \"v\".equals(name);\n        }\n\n        public void characters(char[] ch, int start, int length)\n                throws SAXException {\n            cellValue.append(new String(ch, start, length));\n        }\n\n        public void startElement(String uri, String localName, String name,\n                Attributes attributes) throws SAXException {\n            if (isValueElement(name)) {\n                \/\/ \u5f00\u59cb\u65b0\u7684\u5355\u5143\u683c\uff0c\u6e05\u9664\u4e4b\u524d\u7684\u503c\n                cellValue.delete(0, cellValue.length());\n\n                \/\/ \u503c\n                colCount++;\n                if (maxColCount != UN_LIMITED && colCount > maxColCount) {\n                    throw new RowColLimitBreakException(\"col_exceed\");\n                }\n            }\n        }\n\n        public void endElement(String uri, String localName, String name)\n                throws SAXException {\n            if (isValueElement(name)) {\n                valueList.add(cellValue.toString());\n            }\n\n            if (isRowElement(name)) {\n                \/\/ \u65b0\u7684\u8bb0\u5f55\u884c\u5f00\u59cb\n                if (colCount > 0) {\n                    rowCount++;\n                }\n\n                if (maxRowCount != UN_LIMITED && rowCount > maxRowCount) {\n                    System.out.println(rowCount + \":\" + valueList);\n                    throw new RowColLimitBreakException(\"row_exceed\");\n                }\n\n                valueList.clear();\n                colCount = 0;\n            }\n        }\n\n        private void reset() {\n            rowCount = 0;\n            colCount = 0;\n        }\n    }\n\n    private static String checkXlsx(InputStream fin, int maxSheetCount,\n            int maxRowCount, int maxColCount) throws Exception {\n        OPCPackage pkg = OPCPackage.open(fin); \/\/ \u8017\u65f6\n        try {\n            XSSFReader xssfReader = new XSSFReader(pkg);\n            XMLReader parser = XMLReaderFactory\n                    .createXMLReader(\"org.apache.xerces.parsers.SAXParser\");\n            SheetHandler handler = new SheetHandler(maxRowCount, maxColCount);\n            parser.setContentHandler(handler);\n\n            Iterator<InputStream> sheetsData = xssfReader.getSheetsData();\n            int sheetNum = 0;\n            while (sheetsData.hasNext()) { \/\/ \u68c0\u67e5\u6bcf\u4e2a Sheet\n                if ((++sheetNum) > maxSheetCount) {\n                    return \"sheet_exceed\";\n                }\n                InputStream sheetStream = (InputStream) sheetsData.next();\n                InputSource sheetSource = new InputSource(sheetStream);\n                parser.parse(sheetSource);\n                handler.reset();\n                \/\/ IOUtils.closeQuietly(sheetStream);\n            }\n            return null;\n        } catch (RowColLimitBreakException e) {\n            return e.getMessage();\n        } finally {\n            IOUtils.closeQuietly(pkg);\n        }\n    }\n}\n<\/code><\/pre>\n<hr\/>\n<p>\u6b22\u8fce\u5173\u6ce8\u6211\u7684\u5fae\u4fe1\u516c\u4f17\u53f7: <strong>coderbee\u7b14\u8bb0<\/strong>\uff0c\u53ef\u4ee5\u66f4\u53ca\u65f6\u56de\u590d\u4f60\u7684\u8ba8\u8bba\u3002<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"258\" height=\"258\" src=\"https:\/\/coderbee.net\/wp-content\/uploads\/2019\/01\/coderbee-note.jpg\" class=\"alignnone size-full wp-image-1707\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u7528 POI \u89e3\u6790 xlsx \u65f6\uff0c\u5982\u679c\u7528\u8fd9\u6837\u7684\u65b9\u5f0f Workbook workbo &hellip; <a href=\"https:\/\/coderbee.net\/index.php\/java\/20160222\/1357\">\u7ee7\u7eed\u9605\u8bfb <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18],"tags":[246,247],"_links":{"self":[{"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/posts\/1357"}],"collection":[{"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/comments?post=1357"}],"version-history":[{"count":7,"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/posts\/1357\/revisions"}],"predecessor-version":[{"id":1362,"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/posts\/1357\/revisions\/1362"}],"wp:attachment":[{"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/media?parent=1357"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/categories?post=1357"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/tags?post=1357"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}