用户模式

基本使用

usermodel相关包，对用户友好，在ss包下有统一的接口，但它是把整个文件读取到内存中，对于大量数据很容易内存溢出，所以只能用来处理小量数据

示例代码

public static List<Map<String, String>> readWorkbook(Workbook workbook) {if (workbook == null || workbook.getNumberOfSheets() == 0) {return null;}List<Map<String, String>> result = Lists.newLinkedList();Map<String, String> map;Sheet sheet;Cell cell;Row head, row;for (int i = 0; i < workbook.getNumberOfSheets(); i++) {sheet = workbook.getSheetAt(i);if (sheet == null || sheet.getPhysicalNumberOfRows() == 0) {continue;}head = sheet.getRow(0);if (head == null || head.getPhysicalNumberOfCells() == 0) {continue;}int rows = sheet.getPhysicalNumberOfRows(), cols = head.getPhysicalNumberOfCells();for (int j = 1; j < rows; j++) {row = sheet.getRow(j);map = Maps.newLinkedHashMap();for (int k = 0; k < cols; k++) {cell = row.getCell(k);if (cell.getCellTypeEnum().equals(CellType.BOOLEAN)) {map.put(head.getCell(k).getStringCellValue(), Boolean.valueOf(cell.getBooleanCellValue()).toString());} else if (cell.getCellTypeEnum().equals(CellType.NUMERIC)) {map.put(head.getCell(k).getStringCellValue(), Double.valueOf(cell.getNumericCellValue()).toString());} else if (cell.getCellTypeEnum().equals(CellType.STRING)) {map.put(head.getCell(k).getStringCellValue(), cell.getStringCellValue());} else {map.put(head.getCell(k).getStringCellValue(), StringUtils.EMPTY);}}result.add(map);}}return result;
}

继承关系

Workbook

用户模式继承关系

事件模式

基本使用

eventusermodel相关包，使用比较复杂，但是它处理速度快，占用内存少，可以用来处理海量数据

示例代码

public static List<Map<String, String>> readLargeExcel(InputStream is, Map<String, String> replaceHead) throws Exception {// Excel ReaderOPCPackage opcPackage = OPCPackage.open(is);XSSFReader xssfReader = new XSSFReader(opcPackage);SharedStringsTable sharedStringsTable = xssfReader.getSharedStringsTable();// Sheet ParserXMLReader xmlReader = new SAXParser();SheetHandler sheetHandler = new SheetHandler(sharedStringsTable, replaceHead);xmlReader.setContentHandler(sheetHandler);// SheetInputStream sheet = xssfReader.getSheet("rId1");InputSource sheetSource = new InputSource(sheet);// Read SheetxmlReader.parse(sheetSource);return sheetHandler.getData();
}private static class SheetHandler extends DefaultHandler {private SharedStringsTable sharedStringsTable;private boolean nextIsString;private int rowNumber = 1; // current row，base 1private int cellRowNumber; // current cell's row numberprivate String cellColumnTag; // current cell's row tag, such as A-Zprivate String cellString;private HashMap<String, String> head = new HashMap<>();private Map<String, String> replaceHead;private LinkedHashMap<String, String> row;private List<Map<String, String>> data = new LinkedList<>();public SheetHandler(SharedStringsTable sharedStringsTable, Map<String, String> replaceHead) {this.sharedStringsTable = sharedStringsTable;this.replaceHead = replaceHead;}public List<Map<String, String>> getData() {return data;}@Overridepublic void startElement(String uri, String localName, String qName, Attributes attributes) {if (qName.equals("c")) {String cellPosition = attributes.getValue("r"); // A1,B1,AA1,AB1...cellRowNumber = Integer.parseInt(cellPosition.replaceAll("\\D", ""));cellColumnTag = cellPosition.replaceAll("\\d", "");String cellType = attributes.getValue("t");nextIsString = cellType != null && cellType.equals("s");}cellString = StringUtils.EMPTY;}@Overridepublic void endElement(String uri, String localName, String qName) {if (nextIsString) {cellString = new XSSFRichTextString(sharedStringsTable.getEntryAt(Integer.parseInt(cellString))).toString();nextIsString = false;}if (qName.equals("v")) {// initializing the headif (rowNumber == 1 && rowNumber == cellRowNumber) {if (replaceHead == null || replaceHead.size() == 0) {head.put(cellColumnTag, cellString);} else if (replaceHead.containsKey(cellString)) {head.put(cellColumnTag, replaceHead.get(cellString));}return;}// next row and isn't the first, adding to the data listif (rowNumber != cellRowNumber) {row = new LinkedHashMap<>();data.add(row);rowNumber = cellRowNumber;}// don't read the column if head not exist the tagif (head.containsKey(cellColumnTag)) {row.put(head.get(cellColumnTag), cellString);}}}@Overridepublic void characters(char[] ch, int start, int length) {cellString += new String(ch, start, length);}
}