如何用POI SAX处理Excel大文件避免内存溢出？

POISAXReader p, blockquote, ul, ol, dl, table, pre { margin: 15px 0 } h1, h2, h3, h4, h5, h6 { margin: 20px 0 10px; padding: 0; font-weight: bold; -webkit-font-smoothing: antialiased } h1 tt, h1 code, h2 tt, h2 code, h3 tt, h3 code, h4 tt, h4 code, h5 tt, h5 code, h6 tt, h6 code { font-size: inherit } h1 { font-size: 28px; color: rgba(0, 0, 0, 1) } h2 { font-size: 24px; border-bottom: 1px solid rgba(204, 204, 204, 1); color: rgba(0, 0, 0, 1) } h3 { font-size: 18px } h4 { font-size: 16px } h5 { font-size: 14px } h6 { color: rgba(119, 119, 119, 1); font-size: 14px } body>h2:first-child, body>h1:first-child, body>h1:first-child+h2, body>h3:first-child, body>h4:first-child, body>h5:first-child, body>h6:first-child { margin-top: 0; padding-top: 0 } a:first-child h1, a:first-child h2, a:first-child h3, a:first-child h4, a:first-child h5, a:first-child h6 { margin-top: 0; padding-top: 0 } h1+p, h2+p, h3+p, h4+p, h5+p, h6+p { margin-top: 10px } a { color: rgba(65, 131, 196, 1); text-decoration: none } a:hover { text-decoration: underline } pre, code, tt { font-size: 12px; font-family: Consolas, "Liberation Mono", Courier, monospace } code, tt { margin: 0; padding: 0; white-space: nowrap; border: 1px solid rgba(234, 234, 234, 1); background-color: rgba(248, 248, 248, 1); border-radius: 3px } pre>code { margin: 0; padding: 0; white-space: pre; border: none; background: rgba(0, 0, 0, 0) } pre { background-color: rgba(248, 248, 248, 1); border: 1px solid rgba(204, 204, 204, 1); font-size: 13px; line-height: 19px; overflow: auto; padding: 6px 10px; border-radius: 3px } pre code, pre tt { background-color: rgba(0, 0, 0, 0); border: none } kbd { -moz-border-bottom-colors: none; -moz-border-left-colors: none; -moz-border-right-colors: none; -moz-border-top-colors: none; background-color: rgba(221, 221, 221, 1); background-image: linear-gradient(rgba(241, 241, 241, 1), rgba(221, 221, 221, 1)); background-repeat: repeat-x; border-top: 1px solid rgba(221, 221, 221, 1); border-right: 1px solid rgba(204, 204, 204, 1); border-bottom: 1px solid rgba(204, 204, 204, 1); border-left: 1px solid rgba(221, 221, 221, 1); border-image: none; border-radius: 2px; font-family: "Helvetica Neue", Helvetica, Arial, sans-serif; line-height: 10px; padding: 1px 4px } blockquote { border-left: 4px solid rgba(221, 221, 221, 1); padding: 0 15px; color: rgba(119, 119, 119, 1) } blockquote>:first-child { margin-top: 0 } blockquote>:last-child { margin-bottom: 0 } hr { clear: both; margin: 15px 0; height: 0; overflow: hidden; border-top: none; border-right: none; border-bottom: 4px solid rgba(221, 221, 221, 1); border-left: none; background: rgba(0, 0, 0, 0); padding: 0 } img { max-width: 100% } .highlight { background: rgba(255, 255, 255, 1) } .highlight .c { color: rgba(153, 153, 136, 1); font-style: italic } .highlight .err { color: rgba(166, 23, 23, 1); background-color: rgba(227, 210, 210, 1) } .highlight .k { font-weight: bold } .highlight .o { font-weight: bold } .highlight .cm { color: rgba(153, 153, 136, 1); font-style: italic } .highlight .cp { color: rgba(153, 153, 153, 1); font-weight: bold } .highlight .c1 { color: rgba(153, 153, 136, 1); font-style: italic } .highlight .cs { color: rgba(153, 153, 153, 1); font-weight: bold; font-style: italic } .highlight .gd { color: rgba(0, 0, 0, 1); background-color: rgba(255, 221, 221, 1) } .highlight .gd .x { color: rgba(0, 0, 0, 1); background-color: rgba(255, 170, 170, 1) } .highlight .ge { font-style: italic } .highlight .gr { color: rgba(170, 0, 0, 1) } .highlight .gh { color: rgba(153, 153, 153, 1) } .highlight .gi { color: rgba(0, 0, 0, 1); background-color: rgba(221, 255, 221, 1) } .highlight .gi .x { color: rgba(0, 0, 0, 1); background-color: rgba(170, 255, 170, 1) } .highlight .go { color: rgba(136, 136, 136, 1) } .highlight .gp { color: rgba(85, 85, 85, 1) } .highlight .gs { font-weight: bold } .highlight .gu { color: rgba(170, 170, 170, 1) } .highlight .gt { color: rgba(170, 0, 0, 1) } .highlight .kc { font-weight: bold } .highlight .kd { font-weight: bold } .highlight .kp { font-weight: bold } .highlight .kr { font-weight: bold } .highlight .kt { color: rgba(68, 85, 136, 1); font-weight: bold } .highlight .m { color: rgba(0, 153, 153, 1) } .highlight .s { color: rgba(221, 17, 68, 1) } .highlight .na { color: rgba(0, 128, 128, 1) } .highlight .nb { color: rgba(0, 134, 179, 1) } .highlight .nc { color: rgba(68, 85, 136, 1); font-weight: bold } .highlight .no { color: rgba(0, 128, 128, 1) } .highlight .ni { color: rgba(128, 0, 128, 1) } .highlight .ne { color: rgba(153, 0, 0, 1); font-weight: bold } .highlight .nf { color: rgba(153, 0, 0, 1); font-weight: bold } .highlight .nn { color: rgba(85, 85, 85, 1) } .highlight .nt { color: rgba(0, 0, 128, 1) } .highlight .nv { color: rgba(0, 128, 128, 1) } .highlight .ow { font-weight: bold } .highlight .w { color: rgba(187, 187, 187, 1) } .highlight .mf { color: rgba(0, 153, 153, 1) } .highlight .mh { color: rgba(0, 153, 153, 1) } .highlight .mi { color: rgba(0, 153, 153, 1) } .highlight .mo { color: rgba(0, 153, 153, 1) } .highlight .sb { color: rgba(221, 17, 68, 1) } .highlight .sc { color: rgba(221, 17, 68, 1) } .highlight .sd { color: rgba(221, 17, 68, 1) } .highlight .s2 { color: rgba(221, 17, 68, 1) } .highlight .se { color: rgba(221, 17, 68, 1) } .highlight .sh { color: rgba(221, 17, 68, 1) } .highlight .si { color: rgba(221, 17, 68, 1) } .highlight .sx { color: rgba(221, 17, 68, 1) } .highlight .sr { color: rgba(0, 153, 38, 1) } .highlight .s1 { color: rgba(221, 17, 68, 1) } .highlight .ss { color: rgba(153, 0, 115, 1) } .highlight .bp { color: rgba(153, 153, 153, 1) } .highlight .vc { color: rgba(0, 128, 128, 1) } .highlight .vg { color: rgba(0, 128, 128, 1) } .highlight .vi { color: rgba(0, 128, 128, 1) } .highlight .il { color: rgba(0, 153, 153, 1) } .pl-c { color: rgba(150, 152, 150, 1) } .pl-c1, .pl-mdh, .pl-mm, .pl-mp, .pl-mr, .pl-s1 .pl-v, .pl-s3, .pl-sc, .pl-sv { color: rgba(0, 134, 179, 1) } .pl-e, .pl-en { color: rgba(121, 93, 163, 1) } .pl-s1 .pl-s2, .pl-smi, .pl-smp, .pl-stj, .pl-vo, .pl-vpf { color: rgba(51, 51, 51, 1) } .pl-ent { color: rgba(99, 163, 92, 1) } .pl-k, .pl-s, .pl-st { color: rgba(167, 29, 93, 1) } .pl-pds, .pl-s1, .pl-s1 .pl-pse .pl-s2, .pl-sr, .pl-sr .pl-cce, .pl-sr .pl-sra, .pl-sr .pl-sre, .pl-src, .pl-v { color: rgba(223, 80, 0, 1) } .pl-id { color: rgba(181, 42, 29, 1) } .pl-ii { background-color: rgba(181, 42, 29, 1); color: rgba(248, 248, 248, 1) } .pl-sr .pl-cce { color: rgba(99, 163, 92, 1); font-weight: bold } .pl-ml { color: rgba(105, 58, 23, 1) } .pl-mh, .pl-mh .pl-en, .pl-ms { color: rgba(29, 62, 129, 1); font-weight: bold } .pl-mq { color: rgba(0, 128, 128, 1) } .pl-mi { color: rgba(51, 51, 51, 1); font-style: italic } .pl-mb { color: rgba(51, 51, 51, 1); font-weight: bold } .pl-md, .pl-mdhf { background-color: rgba(255, 236, 236, 1); color: rgba(189, 44, 0, 1) } .pl-mdht, .pl-mi1 { background-color: rgba(234, 255, 234, 1); color: rgba(85, 165, 50, 1) } .pl-mdr { color: rgba(121, 93, 163, 1); font-weight: bold } .pl-mo { color: rgba(29, 62, 129, 1) } .task-list { padding-left: 10px; margin-bottom: 0 } .task-list li { margin-left: 20px } .task-list-item { list-style-type: none; padding-left: 10px } .task-list-item label { font-weight: 400 } .task-list-item.enabled label { cursor: pointer } .task-list-item+.task-list-item { margin-top: 3px } .task-list-item-checkbox { display: inline-block; margin-left: -20px; margin-right: 3px; vertical-align: 1px } 解决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方式基本解决） 对应大量数据的保存，速度一直很慢，尽管我这里采用了批量提交的方式（目前这问题我依然没找到很好的方案，如果有同行看到的，还希望多多指教）