@Override public SXSSFRow createRow(int rownum) { int maxrow = SpreadsheetVersion.EXCEL2007.getLastRowIndex(); if (rownum < 0 || rownum > maxrow) { thrownew IllegalArgumentException("Invalid row number (" + rownum + ") outside allowable range (0.." + maxrow + ")"); }
// attempt to overwrite a row that is already flushed to disk if(rownum <= _writer.getLastFlushedRow() ) { thrownew IllegalArgumentException( "Attempting to write a row["+rownum+"] " + "in the range [0," + _writer.getLastFlushedRow() + "] that is already written to disk."); }
// attempt to overwrite a existing row in the input template if(_sh.getPhysicalNumberOfRows() > 0 && rownum <= _sh.getLastRowNum() ) { thrownew IllegalArgumentException( "Attempting to write a row["+rownum+"] " + "in the range [0," + _sh.getLastRowNum() + "] that is already written to disk."); }
privatevoidflushOneRow()throws IOException { Integer firstRowNum = _rows.firstKey(); if (firstRowNum!=null) { int rowIndex = firstRowNum.intValue(); SXSSFRow row = _rows.get(firstRowNum); // Update the best fit column widths for auto-sizing just before the rows are flushed _autoSizeColumnTracker.updateColumnWidths(row); //写盘 _writer.writeRow(rowIndex, row); //然后把row remove掉,这里的_rows是一个TreeMap结构 _rows.remove(firstRowNum); lastFlushedRowNumber = rowIndex; } }
publicvoidwriteCell(int columnIndex, Cell cell)throws IOException { if (cell == null) { return; } String ref = new CellReference(_rownum, columnIndex).formatAsString(); _out.write("); writeAttribute("r", ref); CellStyle cellStyle = cell.getCellStyle(); if (cellStyle.getIndex() != 0) { // need to convert the short to unsigned short as the indexes can be up to 64k // ideally we would use int for this index, but that would need changes to some more // APIs writeAttribute("s", Integer.toString(cellStyle.getIndex() & 0xffff)); } CellType cellType = cell.getCellTypeEnum(); switch (cellType) { case BLANK: { _out.write('>'); break; } case FORMULA: { _out.write(">"); outputQuotedString(cell.getCellFormula()); _out.write(""); switch (cell.getCachedFormulaResultTypeEnum()) { case NUMERIC: double nval = cell.getNumericCellValue(); if (!Double.isNaN(nval)) { _out.write(""); _out.write(Double.toString(nval)); _out.write(""); } break; default: break; } break; } case STRING: { if (_sharedStringSource != null) { XSSFRichTextString rt = new XSSFRichTextString(cell.getStringCellValue()); int sRef = _sharedStringSource.addEntry(rt.getCTRst());
User Event Model也是采用流式解析,但是不同于Event Model,POI基于Event Model为我们封装了一层。我们不再面对Element的事件编程,而是面向StartRow,EndRow,Cell等事件编程。而提供的数据,也不再像之前是原始数据,而是全部格式化好,方便开发者开箱即用。大大简化了我们的开发效率。
@Override publicvoidstartElement(String uri, String localName, String name, Attributes attributes)throws SAXException { //c代表是一个单元格cell,判断c这个xml元素里面属性attribute t // c => cell if(name.equals("c")) { // Print the cell reference System.out.print(attributes.getValue("r") + " - "); // Figure out if the value is an index in the SST String cellType = attributes.getValue("t"); nextIsString = cellType != null && cellType.equals("s"); inlineStr = cellType != null && cellType.equals("inlineStr"); } // Clear contents cache lastContents = ""; }
@Override publicvoidendElement(String uri, String localName, String name) throws SAXException { // Process the last contents as required. // Do now, as characters() may be called more than once if(nextIsString) { Integer idx = Integer.valueOf(lastContents); lastContents = lruCache.get(idx); if (lastContents == null && !lruCache.containsKey(idx)) { lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString(); lruCache.put(idx, lastContents); } nextIsString = false; }
//v 元素代表这个cell的内容 // v => contents of a cell // Output after we've seen the string contents if(name.equals("v") || (inlineStr && name.equals("c"))) { System.out.println(lastContents); } }
简单来说就是继承XSSFSheetXMLHandler.SheetContentsHandler,覆盖其startRow,endRow,cell,endSheet 等方法。POI每开始读行,结束读行,读取一个cell,结束读取一个sheet时回调的方法。从方法名上看Event User Model有更好的用户体验。
原理
其实Event User Model也是 Event Model的封装,在XSSFSheetXMLHandler(其实也是一个DefaultHandler来的)中持有一个SheetContentsHandler,在其startElement,endElement方法中会调用SheetContentsHandler的startRow,endRow,cell,endSheet等方法。
// Mark us as being a formula if not already if(nextDataType == xssfDataType.NUMBER) { nextDataType = xssfDataType.FORMULA; }
// Decide where to get the formula string from String type = attributes.getValue("t"); if(type != null && type.equals("shared")) { // Is it the one that defines the shared, or uses it? String ref = attributes.getValue("ref"); String si = attributes.getValue("si");
if(ref != null) { // This one defines it // TODO Save it somewhere fIsOpen = true; } else { // This one uses a shared formula // TODO Retrieve the shared formula and tweak it to // match the current cell if(formulasNotResults) { logger.log(POILogger.WARN, "shared formulas not yet supported!"); } /*else { // It's a shared formula, so we can't get at the formula string yet // However, they don't care about the formula string, so that's ok! }*/ } } else { fIsOpen = true; } } elseif("oddHeader".equals(localName) || "evenHeader".equals(localName) || "firstHeader".equals(localName) || "firstFooter".equals(localName) || "oddFooter".equals(localName) || "evenFooter".equals(localName)) { hfIsOpen = true; // Clear contents cache headerFooter.setLength(0); } elseif("row".equals(localName)) { String rowNumStr = attributes.getValue("r"); if(rowNumStr != null) { rowNum = Integer.parseInt(rowNumStr) - 1; } else { rowNum = nextRowNum; } //回调了SheetContentsHandler的startRow方法 output.startRow(rowNum); } // c => cell elseif ("c".equals(localName)) { // Set up defaults. this.nextDataType = xssfDataType.NUMBER; this.formatIndex = -1; this.formatString = null; cellRef = attributes.getValue("r"); String cellType = attributes.getValue("t"); String cellStyleStr = attributes.getValue("s"); if ("b".equals(cellType)) nextDataType = xssfDataType.BOOLEAN; elseif ("e".equals(cellType)) nextDataType = xssfDataType.ERROR; elseif ("inlineStr".equals(cellType)) nextDataType = xssfDataType.INLINE_STRING; elseif ("s".equals(cellType)) nextDataType = xssfDataType.SST_STRING; elseif ("str".equals(cellType)) nextDataType = xssfDataType.FORMULA; else { // Number, but almost certainly with a special style or format XSSFCellStyle style = null; if (stylesTable != null) { if (cellStyleStr != null) { int styleIndex = Integer.parseInt(cellStyleStr); style = stylesTable.getStyleAt(styleIndex); } elseif (stylesTable.getNumCellStyles() > 0) { style = stylesTable.getStyleAt(0); } } if (style != null) { this.formatIndex = style.getDataFormat(); this.formatString = style.getDataFormatString(); if (this.formatString == null) this.formatString = BuiltinFormats.getBuiltinFormat(this.formatIndex); } } } }
if (uri != null && ! uri.equals(NS_SPREADSHEETML)) { return; }
String thisStr = null;
// v => contents of a cell if (isTextTag(localName)) { vIsOpen = false;
// Process the value contents as required, now we have it all switch (nextDataType) { case BOOLEAN: char first = value.charAt(0); thisStr = first == '0' ? "FALSE" : "TRUE"; break;
if (this.formatString != null) { try { // Try to use the value as a formattable number double d = Double.parseDouble(fv); thisStr = formatter.formatRawCellContents(d, this.formatIndex, this.formatString); } catch(NumberFormatException e) { // Formula is a String result not a Numeric one thisStr = fv; } } else { // No formatting applied, just do raw value in all cases thisStr = fv; } } break;
case INLINE_STRING: // TODO: Can these ever have formatting on them? XSSFRichTextString rtsi = new XSSFRichTextString(value.toString()); thisStr = rtsi.toString(); break;
case SST_STRING: String sstIndex = value.toString(); try { int idx = Integer.parseInt(sstIndex); XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx)); thisStr = rtss.toString(); } catch (NumberFormatException ex) { logger.log(POILogger.ERROR, "Failed to parse SST index '" + sstIndex, ex); } break;
case NUMBER: String n = value.toString(); if (this.formatString != null && n.length() > 0) thisStr = formatter.formatRawCellContents(Double.parseDouble(n), this.formatIndex, this.formatString); else thisStr = n; break;
// Do we have a comment for this cell? checkForEmptyCellComments(EmptyCellCommentsCheckType.CELL); XSSFComment comment = commentsTable != null ? commentsTable.findCellComment(new CellAddress(cellRef)) : null;
//回调了SheetContentsHandler的endRow方法 // Finish up the row output.endRow(rowNum);
// some sheets do not have rowNum set in the XML, Excel can read them so we should try to read them as well nextRowNum = rowNum + 1; } elseif ("sheetData".equals(localName)) { // Handle any "missing" cells which had comments attached checkForEmptyCellComments(EmptyCellCommentsCheckType.END_OF_SHEET_DATA); } elseif("oddHeader".equals(localName) || "evenHeader".equals(localName) || "firstHeader".equals(localName)) { hfIsOpen = false; output.headerFooter(headerFooter.toString(), true, localName); } elseif("oddFooter".equals(localName) || "evenFooter".equals(localName) || "firstFooter".equals(localName)) { hfIsOpen = false; output.headerFooter(headerFooter.toString(), false, localName); } }