@Test public void testReadAllRows() throws Exception { for (int i = 0; i < testFiles.length; i++) { final List<Integer> rows = new ArrayList<Integer>(Arrays.asList(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14)); spreadsheetReader.read(getClass().getResourceAsStream(testFiles[i]), new Range(0, -1), new Range(0, 4), false, new SpreadsheetRowProcessor() { public void processRow(int rowIndex, SortedMap<Integer, String> row) { assertTrue(rows.remove((Integer) rowIndex)); List<Integer> columns = new ArrayList<Integer>(Arrays.asList(0, 1, 2, 3, 4)); for (Entry<Integer, String> cell : row.entrySet()) { assertTrue(columns.remove(cell.getKey())); if (rowIndex == 0) { if (cell.getKey().equals(0)) { assertEquals("A", cell.getValue()); } else if (cell.getKey().equals(1)) { assertEquals("5.0", cell.getValue()); } else if (cell.getKey().equals(2)) { assertEquals("C", cell.getValue()); } else if (cell.getKey().equals(3)) { assertEquals("1.0", cell.getValue()); } else { assertNull(cell.getValue()); } } else if (rowIndex == 1) { if (cell.getKey().equals(0)) { assertEquals("A", cell.getValue()); } else if (cell.getKey().equals(1)) { assertEquals("5.0", cell.getValue()); } else if (cell.getKey().equals(2)) { assertEquals("C", cell.getValue()); } else if (cell.getKey().equals(3)) { assertEquals("1.0", cell.getValue()); } else { assertNull(cell.getValue()); } } else if (rowIndex == 2) { if (cell.getKey().equals(0)) { assertEquals("true", cell.getValue()); } else if (cell.getKey().equals(1)) { assertTrue("Unexpected date format: " + cell.getValue(), cell.getValue().matches("Mon Jun 15 00:00:00 ....? 2009")); } else if (cell.getKey().equals(2)) { assertNull(cell.getValue()); } else if (cell.getKey().equals(3)) { assertEquals("2.0", cell.getValue()); } else { assertNull(cell.getValue()); } } else if (rowIndex == 3 || rowIndex == 4) { if (cell.getKey().equals(4)) { assertNull(cell.getValue()); } else { assertEquals("X", cell.getValue()); } } else if (rowIndex == 5 || rowIndex == 6 || rowIndex == 7 || rowIndex == 8) { assertNull(cell.getValue()); } else if (rowIndex == 9 || rowIndex == 10 || rowIndex == 11 || rowIndex == 12 || rowIndex == 13 || rowIndex == 14) { if (cell.getKey().equals(4)) { assertNull(cell.getValue()); } else { assertEquals("y", cell.getValue()); } } } assertTrue(columns.isEmpty()); } }); assertTrue(rows.isEmpty()); } }
public void read(InputStream inputStream, Range rowRange, Range columnRange, boolean ignoreBlankRows, SpreadsheetRowProcessor rowProcessor) throws SpreadsheetReadException { Workbook workbook; try { workbook = WorkbookFactory.create(inputStream); } catch (InvalidFormatException e) { throw new SpreadsheetReadException( "The file does not have a compatible spreadsheet format", e); } catch (IOException e) { throw new SpreadsheetReadException("The spreadsheet stream could not be read", e); } catch (IllegalArgumentException e) { throw new SpreadsheetReadException("The spreadsheet stream could not be read", e); } DataFormatter dataFormatter = new DataFormatter(); workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK); Sheet sheet = workbook.getSheetAt(0); if (rowRange.getEnd() < 0) { rowRange.setEnd(sheet.getLastRowNum()); logger.debug("No end of row range specified, setting to " + rowRange.getEnd()); } SortedMap<Integer, String> currentDataRow = new TreeMap<Integer, String>(); for (int rowIndex = rowRange.getStart(); rowIndex <= rowRange.getEnd(); rowIndex++) { boolean blankRow = true; if (rowRange.contains(rowIndex)) { Row row = sheet.getRow(rowIndex); for (int columnIndex = columnRange.getStart(); columnIndex <= columnRange.getEnd(); columnIndex++) { if (columnRange.contains(columnIndex)) { String value = null; if (row != null) { Cell cell = row.getCell(columnIndex); if (cell != null) { value = getCellValue(cell, dataFormatter); } } if (value != null) { blankRow = false; } currentDataRow.put(columnIndex, value); if (columnIndex == columnRange.getEnd()) { if (!ignoreBlankRows || !blankRow) { rowProcessor.processRow(rowIndex, currentDataRow); } currentDataRow = new TreeMap<Integer, String>(); } } } } } }
ExcelSpreadsheetReader implements SpreadsheetReader { public void read(InputStream inputStream, Range rowRange, Range columnRange, boolean ignoreBlankRows, SpreadsheetRowProcessor rowProcessor) throws SpreadsheetReadException { Workbook workbook; try { workbook = WorkbookFactory.create(inputStream); } catch (InvalidFormatException e) { throw new SpreadsheetReadException( "The file does not have a compatible spreadsheet format", e); } catch (IOException e) { throw new SpreadsheetReadException("The spreadsheet stream could not be read", e); } catch (IllegalArgumentException e) { throw new SpreadsheetReadException("The spreadsheet stream could not be read", e); } DataFormatter dataFormatter = new DataFormatter(); workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK); Sheet sheet = workbook.getSheetAt(0); if (rowRange.getEnd() < 0) { rowRange.setEnd(sheet.getLastRowNum()); logger.debug("No end of row range specified, setting to " + rowRange.getEnd()); } SortedMap<Integer, String> currentDataRow = new TreeMap<Integer, String>(); for (int rowIndex = rowRange.getStart(); rowIndex <= rowRange.getEnd(); rowIndex++) { boolean blankRow = true; if (rowRange.contains(rowIndex)) { Row row = sheet.getRow(rowIndex); for (int columnIndex = columnRange.getStart(); columnIndex <= columnRange.getEnd(); columnIndex++) { if (columnRange.contains(columnIndex)) { String value = null; if (row != null) { Cell cell = row.getCell(columnIndex); if (cell != null) { value = getCellValue(cell, dataFormatter); } } if (value != null) { blankRow = false; } currentDataRow.put(columnIndex, value); if (columnIndex == columnRange.getEnd()) { if (!ignoreBlankRows || !blankRow) { rowProcessor.processRow(rowIndex, currentDataRow); } currentDataRow = new TreeMap<Integer, String>(); } } } } } } }
ExcelSpreadsheetReader implements SpreadsheetReader { public void read(InputStream inputStream, Range rowRange, Range columnRange, boolean ignoreBlankRows, SpreadsheetRowProcessor rowProcessor) throws SpreadsheetReadException { Workbook workbook; try { workbook = WorkbookFactory.create(inputStream); } catch (InvalidFormatException e) { throw new SpreadsheetReadException( "The file does not have a compatible spreadsheet format", e); } catch (IOException e) { throw new SpreadsheetReadException("The spreadsheet stream could not be read", e); } catch (IllegalArgumentException e) { throw new SpreadsheetReadException("The spreadsheet stream could not be read", e); } DataFormatter dataFormatter = new DataFormatter(); workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK); Sheet sheet = workbook.getSheetAt(0); if (rowRange.getEnd() < 0) { rowRange.setEnd(sheet.getLastRowNum()); logger.debug("No end of row range specified, setting to " + rowRange.getEnd()); } SortedMap<Integer, String> currentDataRow = new TreeMap<Integer, String>(); for (int rowIndex = rowRange.getStart(); rowIndex <= rowRange.getEnd(); rowIndex++) { boolean blankRow = true; if (rowRange.contains(rowIndex)) { Row row = sheet.getRow(rowIndex); for (int columnIndex = columnRange.getStart(); columnIndex <= columnRange.getEnd(); columnIndex++) { if (columnRange.contains(columnIndex)) { String value = null; if (row != null) { Cell cell = row.getCell(columnIndex); if (cell != null) { value = getCellValue(cell, dataFormatter); } } if (value != null) { blankRow = false; } currentDataRow.put(columnIndex, value); if (columnIndex == columnRange.getEnd()) { if (!ignoreBlankRows || !blankRow) { rowProcessor.processRow(rowIndex, currentDataRow); } currentDataRow = new TreeMap<Integer, String>(); } } } } } }  }
ExcelSpreadsheetReader implements SpreadsheetReader { public void read(InputStream inputStream, Range rowRange, Range columnRange, boolean ignoreBlankRows, SpreadsheetRowProcessor rowProcessor) throws SpreadsheetReadException { Workbook workbook; try { workbook = WorkbookFactory.create(inputStream); } catch (InvalidFormatException e) { throw new SpreadsheetReadException( "The file does not have a compatible spreadsheet format", e); } catch (IOException e) { throw new SpreadsheetReadException("The spreadsheet stream could not be read", e); } catch (IllegalArgumentException e) { throw new SpreadsheetReadException("The spreadsheet stream could not be read", e); } DataFormatter dataFormatter = new DataFormatter(); workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK); Sheet sheet = workbook.getSheetAt(0); if (rowRange.getEnd() < 0) { rowRange.setEnd(sheet.getLastRowNum()); logger.debug("No end of row range specified, setting to " + rowRange.getEnd()); } SortedMap<Integer, String> currentDataRow = new TreeMap<Integer, String>(); for (int rowIndex = rowRange.getStart(); rowIndex <= rowRange.getEnd(); rowIndex++) { boolean blankRow = true; if (rowRange.contains(rowIndex)) { Row row = sheet.getRow(rowIndex); for (int columnIndex = columnRange.getStart(); columnIndex <= columnRange.getEnd(); columnIndex++) { if (columnRange.contains(columnIndex)) { String value = null; if (row != null) { Cell cell = row.getCell(columnIndex); if (cell != null) { value = getCellValue(cell, dataFormatter); } } if (value != null) { blankRow = false; } currentDataRow.put(columnIndex, value); if (columnIndex == columnRange.getEnd()) { if (!ignoreBlankRows || !blankRow) { rowProcessor.processRow(rowIndex, currentDataRow); } currentDataRow = new TreeMap<Integer, String>(); } } } } } }  void read(InputStream inputStream, Range rowRange, Range columnRange, boolean ignoreBlankRows, SpreadsheetRowProcessor rowProcessor); }
ExcelSpreadsheetReader implements SpreadsheetReader { public void read(InputStream inputStream, Range rowRange, Range columnRange, boolean ignoreBlankRows, SpreadsheetRowProcessor rowProcessor) throws SpreadsheetReadException { Workbook workbook; try { workbook = WorkbookFactory.create(inputStream); } catch (InvalidFormatException e) { throw new SpreadsheetReadException( "The file does not have a compatible spreadsheet format", e); } catch (IOException e) { throw new SpreadsheetReadException("The spreadsheet stream could not be read", e); } catch (IllegalArgumentException e) { throw new SpreadsheetReadException("The spreadsheet stream could not be read", e); } DataFormatter dataFormatter = new DataFormatter(); workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK); Sheet sheet = workbook.getSheetAt(0); if (rowRange.getEnd() < 0) { rowRange.setEnd(sheet.getLastRowNum()); logger.debug("No end of row range specified, setting to " + rowRange.getEnd()); } SortedMap<Integer, String> currentDataRow = new TreeMap<Integer, String>(); for (int rowIndex = rowRange.getStart(); rowIndex <= rowRange.getEnd(); rowIndex++) { boolean blankRow = true; if (rowRange.contains(rowIndex)) { Row row = sheet.getRow(rowIndex); for (int columnIndex = columnRange.getStart(); columnIndex <= columnRange.getEnd(); columnIndex++) { if (columnRange.contains(columnIndex)) { String value = null; if (row != null) { Cell cell = row.getCell(columnIndex); if (cell != null) { value = getCellValue(cell, dataFormatter); } } if (value != null) { blankRow = false; } currentDataRow.put(columnIndex, value); if (columnIndex == columnRange.getEnd()) { if (!ignoreBlankRows || !blankRow) { rowProcessor.processRow(rowIndex, currentDataRow); } currentDataRow = new TreeMap<Integer, String>(); } } } } } }  void read(InputStream inputStream, Range rowRange, Range columnRange, boolean ignoreBlankRows, SpreadsheetRowProcessor rowProcessor);  }