ODFSpreadsheetReader implements SpreadsheetReader { public void read(InputStream inputStream, Range rowRange, Range columnRange, boolean ignoreBlankRows, SpreadsheetRowProcessor rowProcessor) throws SpreadsheetReadException { NodeList rowList = null; try { OdfDocument odfDoc = OdfDocument.loadDocument(inputStream); logger.debug("Reading document of type : " + odfDoc.getMediaType()); OdfFileDom odfContent = odfDoc.getContentDom(); XPath xpath = odfDoc.getXPath(); String rowsPath = (" rowList = (NodeList) xpath.evaluate(rowsPath, odfContent, XPathConstants.NODESET); } catch (Exception e) { if (e instanceof RuntimeException) { throw (RuntimeException) e; } throw new SpreadsheetReadException("The spreadsheet file could not be read", e); } if (rowRange.getEnd() < 0) { rowRange.setEnd(calculateRowCount(rowList) - 1); } SortedMap<Integer, String> currentDataRow = new TreeMap<Integer, String>(); int rowRep = 0; for (int rowIndex = rowRange.getStart(); rowIndex <= rowRange.getEnd(); rowIndex++) { boolean blankRow = true; OdfTableRow row = (OdfTableRow) rowList.item(rowIndex); int columnRep = 0; for (int columnIndex = columnRange.getStart(); columnIndex <= columnRange.getEnd(); columnIndex++) { String value = null; OdfTableCell cell = null; if (row != null) { cell = (OdfTableCell) row.getCellAt(columnIndex); if (cell != null) { String type = cell.getOfficeValueTypeAttribute(); if ("float".equals(type)) { value = cell.getOfficeValueAttribute().toString(); } else if ("percentage".equals(type)) { value = cell.getOfficeValueAttribute().toString(); } else if ("currency".equals(type)) { value = cell.getOfficeValueAttribute().toString(); } else if ("date".equals(type)) { value = cell.getOfficeDateValueAttribute(); } else if ("time".equals(type)) { value = cell.getOfficeTimeValueAttribute(); } else if ("boolean".equals(type)) { value = cell.getOfficeBooleanValueAttribute().toString(); } else if ("string".equals(type)) { value = cell.getOfficeStringValueAttribute(); if (value == null) { value = cell.getTextContent(); } } else { value = cell.getTextContent(); } } } value = "".equals(value) ? null : value; if (value != null) { blankRow = false; } if (columnRange.contains(columnIndex + columnRep)) { currentDataRow.put(columnIndex + columnRep, value); } int repeatedCells = cell == null ? 0 : cell .getTableNumberColumnsRepeatedAttribute() - 1; while (repeatedCells > 0 && columnIndex + columnRep < columnRange.getEnd()) { columnRep++; if (columnRange.contains(columnIndex + columnRep)) { currentDataRow .put(columnIndex + columnRep, value); } repeatedCells--; } if (columnIndex == columnRange.getEnd()) { if (rowRange.contains(rowIndex + rowRep)) { if (!ignoreBlankRows || !blankRow) { rowProcessor.processRow(rowIndex + rowRep, currentDataRow); } } int repeatedRows = row == null ? 0 : row.getTableNumberRowsRepeatedAttribute() - 1; while (repeatedRows > 0 && rowIndex + rowRep < rowRange.getEnd()) { rowRep++; if (rowRange.contains(rowIndex + rowRep)) { if (!ignoreBlankRows || !blankRow) { rowProcessor.processRow(rowIndex + rowRep, currentDataRow); } } repeatedRows--; } currentDataRow = new TreeMap<Integer, String>(); } } } }  void read(InputStream inputStream, Range rowRange, Range columnRange, boolean ignoreBlankRows, SpreadsheetRowProcessor rowProcessor);  }
@Test public void testRead() throws Exception { String[] testFiles2 = new String[] { "/test-spreadsheet.ods" }; for (int i = 0; i < testFiles2.length; i++) { final List<Integer> rows = new ArrayList<Integer>(Arrays.asList(0, 1, 2, 3, 4, 5)); spreadsheetReader.read(getClass().getResourceAsStream(testFiles2[i]), new Range(0, 5), 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)) { assertEquals("2009-06-15", cell.getValue()); } 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 { assertNull(cell.getValue()); } } assertTrue(columns.isEmpty()); } }); assertTrue(rows.isEmpty()); } }
@Test(expected=SpreadsheetReadException.class) public void testReadException() throws Exception { spreadsheetReader.read(getClass().getResourceAsStream("/test-spreadsheet.csv"), new Range(0,1), new Range(0,1), false, new SpreadsheetRowProcessor() { public void processRow(int rowIndex, SortedMap<Integer, String> rowData) { } }); }
@Test(expected=RuntimeException.class) public void testReadRuntimeException() throws Exception { spreadsheetReader.read(new InputStream() { public int read() throws IOException { throw new RuntimeException(); } }, new Range(0,1), new Range(0,1), false, new SpreadsheetRowProcessor() { public void processRow(int rowIndex, SortedMap<Integer, String> rowData) { } }); }
@Test public void testReadAllRows() throws Exception { String[] testFiles2 = new String[] { "/test-spreadsheet.ods" }; for (int i = 0; i < testFiles2.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(testFiles2[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)) { assertEquals("2009-06-15", cell.getValue()); } 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()); } }
@Test public void testIgnoreBlankRows() throws Exception { String[] testFiles2 = new String[] { "/test-spreadsheet.ods" }; for (int i = 0; i < testFiles2.length; i++) { final List<Integer> rows = new ArrayList<Integer>(Arrays.asList(0, 1, 2, 3, 4, 9, 10, 11, 12, 13, 14)); spreadsheetReader.read(getClass().getResourceAsStream(testFiles2[i]), new Range(0, -1), new Range(0, 4), true, 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)) { assertEquals("2009-06-15", cell.getValue()); } 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 == 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()); } }