使用(POI)SAX處理Excel大文件，防止內存溢出

POISAXReader

解決POI讀取XLSX文件內存占用過過多

poi處理excel分別提供比較友好的用戶模式以及比較底層的事件模式。其中，用戶模式提供良好的封裝，同時兼容2003以及2007以上的格式，使用相當方便。不過，代價是花費巨大的內存。只要超過6w條以后，基本是就是內存溢出了。
好在POI團隊也提供了更底層的的流處理模式eventMode,對于大數據的Xlsx文件的寫入，poi 3.8 提供SXSSF，采用緩存方式寫如文件。對于文件的讀取采用sax的方式直接讀取每個sheet對應的xml文件。添加微信，回復poi，邀請你加群

POI SheetContentsHandler 接口

在POI中已經對SAX當時讀取對應的Sheet的xml文件已經做了基本的封裝，所以我們僅僅需要實現接口SheetContentsHandler,就可以完成SAX的方式讀取。這個接口中需要是實現三個方法

public void startRow(int rowNum) 讀取某行開始
public void endRow(int rowNum) 讀取某行結束
public void cell(String cellReference, String formattedValue,XSSFComment comment) 讀取某行中的單元格
public void headerFooter(String text, boolean isHeader, String tagName) 暫時不清楚

POI SheetContentsHandler實現

這里我主要參照poi XLSX2CSV.java實現方式，需要提供對應的xlsx文件最大列數。其次，我在此基礎上做了擴展，在 endRow 提供了一個事件，當前處理的的行數據,讓這個解析功能更加獨立。
實現思路，在startRow方法中構造一個List對象，在cell函數中添加每個單元內容，在endRow函數中判斷當前列是否等于最大列數，如果不等循環補齊，并出發添加行事件
SheetSaxHandler詳細代碼

protected class SheetSaxHandler implements SheetContentsHandler {
        private int currentRow = -1;
        private int currentCol = -1;
        private int minColumns;

        public void setMinColumns(int minColumns) {
            this.minColumns = minColumns;
        }

        public SheetSaxHandler(int minColumns) {
            super();
            this.minColumns = minColumns;
        }

        public SheetSaxHandler() {
        }

        private List<SheetRowListener> listeners = new ArrayList<SheetRowListener>();
        private List<String> lRows = new ArrayList<String>(); // 處理一行信息

        public void rowAdded(SheetRowListener add) {
            listeners.add(add);
        }

        private void postRowAdded(List<String> row, int rowNum)
                throws SQLException {
            for (SheetRowListener hl : listeners)
                hl.addRow(row, rowNum);
        }

        @Override
        public void startRow(int rowNum) {
            currentRow = rowNum;
            currentCol = -1;
            lRows.clear();
        }

        @Override
        public void endRow(int rowNum) {
            // 添加數據
            for (int i = currentCol; i < minColumns; i++) {
                lRows.add("");
            }
            try {
                postRowAdded(lRows, rowNum);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        @Override
        public void cell(String cellReference, String formattedValue,
                XSSFComment comment) {
            if (cellReference == null) {
                cellReference = new CellAddress(currentRow, currentCol)
                        .formatAsString();
            }
            int thisCol = (new CellReference(cellReference)).getCol();
            int missedCols = thisCol - currentCol - 1;//處理數據中間存在空白
            for (int i = 0; i < missedCols; i++) {
                this.lRows.add("");
            }
            currentCol = thisCol;

            // TODO 數據類型處理
            try {
                Double.parseDouble(formattedValue);
                this.lRows.add(formattedValue);
            } catch (NumberFormatException e) {
                this.lRows.add(formattedValue);
            }
        }

        @Override
        public void headerFooter(String text, boolean isHeader, String tagName) {
            System.out.println(text + "==" + isHeader + "==" + tagName);
        }

    }

事件接口

interface SheetRowListener {
        void addRow(List<String> row, int rowNum);
    }

調用方式

打開文件
找到對應sheet的xml文件
使用上邊的方法依次處理每一個sheet

處理文件

             @Override
    public int saveToOracle(String filePath, String pcId)
            throws FileNotFoundException, EncryptedDocumentException,
            InvalidFormatException, IOException, ClassNotFoundException,
            SQLException, OpenXML4JException, SAXException,
            ParserConfigurationException {
        File f = new File(filePath);
        OPCPackage p = null;
        int num = 0;
        Connection conn = null;
        if (f.exists()) {
            try {
                JSONArray sheetCfgs = this.cfgJson.getJSONArray("sheets");
                dataBuferRows = this.cfgJson.getInteger("dataBuferRows");
                dataBuferRows = dataBuferRows == null ? 1000 : dataBuferRows;
                conn = ca.getConnection(ca.getSqlCfg(serverPath));
                String importTime = new SimpleDateFormat(
                        "yyyy-MM-dd HH:mm:ss.SSS").format(new Date());
                p = OPCPackage.open(f, PackageAccess.READ);
                ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(
                        p);
                XSSFReader xssfReader = new XSSFReader(p);
                StylesTable styles = xssfReader.getStylesTable();
                XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader
                        .getSheetsData();
                HashMap<Integer, JSONObject> hSheetCfg = new HashMap<Integer, JSONObject>();
                for (int i = 0; i < sheetCfgs.size(); i++) {
                    JSONObject sheetCfg = sheetCfgs.getJSONObject(i);
                    hSheetCfg.put(sheetCfg.getInteger("sheetIndex"), sheetCfg);
                }
                int index = 1;
                while (iter.hasNext()) {
                    InputStream sheetStream = iter.next();
                    if (hSheetCfg.containsKey(index)) {
                        processSheet(styles, strings, new SheetSaxHandler(),
                                sheetStream, hSheetCfg.get(index), conn, pcId,
                                this.fileName, importTime);
                    }
                    index++;
                }

                p.close();
                f = null;
                conn.close();
            } catch (SQLException e) {
                conn.close();
                conn = null;
                throw e;
            }
        }
        return num;
    }

處理Sheet

public void processSheet(StylesTable styles,
            ReadOnlySharedStringsTable strings, SheetSaxHandler sheetHandler,
            InputStream sheetInputStream, final JSONObject sheetCfg,
            final Connection conn, String PcID, String fileName,
            String importTime) throws IOException,
            ParserConfigurationException, SAXException, SQLException {

        final PreparedStatement ps = conn.prepareStatement(ca.buildInsertSql(
                sheetCfg, PcID, fileName, importTime));
        final int dataStartNum = sheetCfg.getIntValue("dataStartNum");
        sheetHandler.setMinColumns(sheetCfg.getJSONArray("fieldReference")
                .size());
        sheetHandler.rowAdded(new SheetRowListener() {
            @Override
            public void addRow(List<String> row, int rowNum) {
                if (rowNum < dataStartNum - 1)
                    return;
                try {
                    ca.setParamter(ps, sheetCfg, row, rowNum - dataStartNum);
                    if (rowNum % dataBuferRows == 0) {
                        ps.executeBatch();
                        ps.clearBatch();
                    }
                } catch (SQLException e) {
                    try {
                        ps.close();
                        conn.close();
                        throw e;
                    } catch (SQLException e1) {
                        e1.printStackTrace();
                    }
                    e.printStackTrace();
                }

            }
        });
        XMLReader sheetParser = SAXHelper.newXMLReader();
        DataFormatter formatter = new DataFormatter();
        InputSource sheetSource = new InputSource(sheetInputStream);
        ContentHandler handler = new XSSFSheetXMLHandler(styles, null, strings,
                sheetHandler, formatter, false);
        sheetParser.setContentHandler(handler);
        sheetParser.parse(sheetSource);
        // 處理剩下的數據
        ps.executeBatch();
        ps.clearBatch();
        // 關閉當前ps
        ps.close();
    }
`

總結

在最初使用poi的用戶模式，很快的就完成一個excel文件的解析，很方便。隨著項目的逐漸深入，處理的excel文件越來越大，用戶模式已經不能勝任。于是開始查找資料，在官網上看到了轉csv的實例。
這段代碼的主要功能將excel文件中的數據導入到oracle數據庫對應的表中，在實現功能方面，我主要遇到了以下問題

解決excel文件解析內存泄露（2007以后文件采用sax方式基本解決）

對應大量數據的保存，速度一直很慢，盡管我這里采用了批量提交的方式（目前這問題我依然沒找到很好的方案，如果有同行看到的，還希望多多指教）

posted @ 2016-07-20 18:27 _herbert 閱讀(14731) 評論(0) 收藏舉報

刷新頁面返回頂部

楊瀚博