@Test public void testBasicExcel() throws Exception { try (InputStream stream = ParseAvroFileTest.class.getClassLoader().getResourceAsStream("titanic.xlsx")) { byte[] data = IOUtils.toByteArray(stream); String[] directives = new String[]{ "parse-as-excel :body '0'", }; List<Row> rows = new ArrayList<>(); rows.add(new Row("body", data)); List<Row> results = TestingRig.execute(directives, rows); Assert.assertEquals(892, results.size()); Assert.assertEquals(0, results.get(0).getValue("fwd")); Assert.assertEquals(891, results.get(0).getValue("bkd")); } }
@Override public List<Row> execute(List<Row> records, final ExecutorContext context) throws DirectiveExecutionException, ErrorRowException { List<Row> results = new ArrayList<>(); ByteArrayInputStream input = null; try { for (Row record : records) { int idx = record.find(column); if (idx != -1) { Object object = record.getValue(idx); byte[] bytes = null; if (object instanceof byte[]) { bytes = (byte[]) object; } else if (object instanceof ByteBuffer) { ByteBuffer buffer = (ByteBuffer) object; bytes = new byte[buffer.remaining()]; buffer.get(bytes); } else { throw new DirectiveExecutionException( NAME, String.format("Column '%s' should be of type 'byte array' or 'ByteBuffer'.", column)); } if (bytes != null) { input = new ByteArrayInputStream(bytes); XSSFWorkbook book = new XSSFWorkbook(input); XSSFSheet excelsheet; if (Types.isInteger(sheet)) { excelsheet = book.getSheetAt(Integer.parseInt(sheet)); } else { excelsheet = book.getSheet(sheet); } if (excelsheet == null) { throw new DirectiveExecutionException( NAME, String.format("Failed to extract sheet '%s' from the excel. " + "Sheet '%s' does not exist.", sheet, sheet)); } Map<Integer, String> columnNames = new TreeMap<>(); Iterator<org.apache.poi.ss.usermodel.Row> it = excelsheet.iterator(); int rows = 0; while (it.hasNext()) { org.apache.poi.ss.usermodel.Row row = it.next(); Iterator<Cell> cellIterator = row.cellIterator(); if (checkIfRowIsEmpty(row)) { continue; } Row newRow = new Row(); newRow.add("fwd", rows); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String name = columnName(cell.getAddress().getColumn()); if (firstRowAsHeader && rows > 0) { String value = columnNames.get(cell.getAddress().getColumn()); if (value != null) { name = value; } } String value = ""; switch (cell.getCellTypeEnum()) { case STRING: newRow.add(name, cell.getStringCellValue()); value = cell.getStringCellValue(); break; case NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { newRow.add(name, cell.getDateCellValue()); value = cell.getDateCellValue().toString(); } else { newRow.add(name, cell.getNumericCellValue()); value = String.valueOf(cell.getNumericCellValue()); } break; case BOOLEAN: newRow.add(name, cell.getBooleanCellValue()); value = String.valueOf(cell.getBooleanCellValue()); break; } if (rows == 0 && firstRowAsHeader) { columnNames.put(cell.getAddress().getColumn(), value); } } if (firstRowAsHeader && rows == 0) { rows++; continue; } results.add(newRow); rows++; } if (firstRowAsHeader) { rows = rows - 1; } for (int i = rows - 1; i >= 0; --i) { results.get(rows - i - 1).addOrSetAtIndex(1, "bkd", i); } } } } } catch (Exception e) { throw new ErrorRowException(NAME, e.getMessage(), 1); } finally { if (input != null) { Closeables.closeQuietly(input); } } return results; }
ParseExcel implements Directive, Lineage { @Override public List<Row> execute(List<Row> records, final ExecutorContext context) throws DirectiveExecutionException, ErrorRowException { List<Row> results = new ArrayList<>(); ByteArrayInputStream input = null; try { for (Row record : records) { int idx = record.find(column); if (idx != -1) { Object object = record.getValue(idx); byte[] bytes = null; if (object instanceof byte[]) { bytes = (byte[]) object; } else if (object instanceof ByteBuffer) { ByteBuffer buffer = (ByteBuffer) object; bytes = new byte[buffer.remaining()]; buffer.get(bytes); } else { throw new DirectiveExecutionException( NAME, String.format("Column '%s' should be of type 'byte array' or 'ByteBuffer'.", column)); } if (bytes != null) { input = new ByteArrayInputStream(bytes); XSSFWorkbook book = new XSSFWorkbook(input); XSSFSheet excelsheet; if (Types.isInteger(sheet)) { excelsheet = book.getSheetAt(Integer.parseInt(sheet)); } else { excelsheet = book.getSheet(sheet); } if (excelsheet == null) { throw new DirectiveExecutionException( NAME, String.format("Failed to extract sheet '%s' from the excel. " + "Sheet '%s' does not exist.", sheet, sheet)); } Map<Integer, String> columnNames = new TreeMap<>(); Iterator<org.apache.poi.ss.usermodel.Row> it = excelsheet.iterator(); int rows = 0; while (it.hasNext()) { org.apache.poi.ss.usermodel.Row row = it.next(); Iterator<Cell> cellIterator = row.cellIterator(); if (checkIfRowIsEmpty(row)) { continue; } Row newRow = new Row(); newRow.add("fwd", rows); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String name = columnName(cell.getAddress().getColumn()); if (firstRowAsHeader && rows > 0) { String value = columnNames.get(cell.getAddress().getColumn()); if (value != null) { name = value; } } String value = ""; switch (cell.getCellTypeEnum()) { case STRING: newRow.add(name, cell.getStringCellValue()); value = cell.getStringCellValue(); break; case NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { newRow.add(name, cell.getDateCellValue()); value = cell.getDateCellValue().toString(); } else { newRow.add(name, cell.getNumericCellValue()); value = String.valueOf(cell.getNumericCellValue()); } break; case BOOLEAN: newRow.add(name, cell.getBooleanCellValue()); value = String.valueOf(cell.getBooleanCellValue()); break; } if (rows == 0 && firstRowAsHeader) { columnNames.put(cell.getAddress().getColumn(), value); } } if (firstRowAsHeader && rows == 0) { rows++; continue; } results.add(newRow); rows++; } if (firstRowAsHeader) { rows = rows - 1; } for (int i = rows - 1; i >= 0; --i) { results.get(rows - i - 1).addOrSetAtIndex(1, "bkd", i); } } } } } catch (Exception e) { throw new ErrorRowException(NAME, e.getMessage(), 1); } finally { if (input != null) { Closeables.closeQuietly(input); } } return results; } }
ParseExcel implements Directive, Lineage { @Override public List<Row> execute(List<Row> records, final ExecutorContext context) throws DirectiveExecutionException, ErrorRowException { List<Row> results = new ArrayList<>(); ByteArrayInputStream input = null; try { for (Row record : records) { int idx = record.find(column); if (idx != -1) { Object object = record.getValue(idx); byte[] bytes = null; if (object instanceof byte[]) { bytes = (byte[]) object; } else if (object instanceof ByteBuffer) { ByteBuffer buffer = (ByteBuffer) object; bytes = new byte[buffer.remaining()]; buffer.get(bytes); } else { throw new DirectiveExecutionException( NAME, String.format("Column '%s' should be of type 'byte array' or 'ByteBuffer'.", column)); } if (bytes != null) { input = new ByteArrayInputStream(bytes); XSSFWorkbook book = new XSSFWorkbook(input); XSSFSheet excelsheet; if (Types.isInteger(sheet)) { excelsheet = book.getSheetAt(Integer.parseInt(sheet)); } else { excelsheet = book.getSheet(sheet); } if (excelsheet == null) { throw new DirectiveExecutionException( NAME, String.format("Failed to extract sheet '%s' from the excel. " + "Sheet '%s' does not exist.", sheet, sheet)); } Map<Integer, String> columnNames = new TreeMap<>(); Iterator<org.apache.poi.ss.usermodel.Row> it = excelsheet.iterator(); int rows = 0; while (it.hasNext()) { org.apache.poi.ss.usermodel.Row row = it.next(); Iterator<Cell> cellIterator = row.cellIterator(); if (checkIfRowIsEmpty(row)) { continue; } Row newRow = new Row(); newRow.add("fwd", rows); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String name = columnName(cell.getAddress().getColumn()); if (firstRowAsHeader && rows > 0) { String value = columnNames.get(cell.getAddress().getColumn()); if (value != null) { name = value; } } String value = ""; switch (cell.getCellTypeEnum()) { case STRING: newRow.add(name, cell.getStringCellValue()); value = cell.getStringCellValue(); break; case NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { newRow.add(name, cell.getDateCellValue()); value = cell.getDateCellValue().toString(); } else { newRow.add(name, cell.getNumericCellValue()); value = String.valueOf(cell.getNumericCellValue()); } break; case BOOLEAN: newRow.add(name, cell.getBooleanCellValue()); value = String.valueOf(cell.getBooleanCellValue()); break; } if (rows == 0 && firstRowAsHeader) { columnNames.put(cell.getAddress().getColumn(), value); } } if (firstRowAsHeader && rows == 0) { rows++; continue; } results.add(newRow); rows++; } if (firstRowAsHeader) { rows = rows - 1; } for (int i = rows - 1; i >= 0; --i) { results.get(rows - i - 1).addOrSetAtIndex(1, "bkd", i); } } } } } catch (Exception e) { throw new ErrorRowException(NAME, e.getMessage(), 1); } finally { if (input != null) { Closeables.closeQuietly(input); } } return results; }  }
ParseExcel implements Directive, Lineage { @Override public List<Row> execute(List<Row> records, final ExecutorContext context) throws DirectiveExecutionException, ErrorRowException { List<Row> results = new ArrayList<>(); ByteArrayInputStream input = null; try { for (Row record : records) { int idx = record.find(column); if (idx != -1) { Object object = record.getValue(idx); byte[] bytes = null; if (object instanceof byte[]) { bytes = (byte[]) object; } else if (object instanceof ByteBuffer) { ByteBuffer buffer = (ByteBuffer) object; bytes = new byte[buffer.remaining()]; buffer.get(bytes); } else { throw new DirectiveExecutionException( NAME, String.format("Column '%s' should be of type 'byte array' or 'ByteBuffer'.", column)); } if (bytes != null) { input = new ByteArrayInputStream(bytes); XSSFWorkbook book = new XSSFWorkbook(input); XSSFSheet excelsheet; if (Types.isInteger(sheet)) { excelsheet = book.getSheetAt(Integer.parseInt(sheet)); } else { excelsheet = book.getSheet(sheet); } if (excelsheet == null) { throw new DirectiveExecutionException( NAME, String.format("Failed to extract sheet '%s' from the excel. " + "Sheet '%s' does not exist.", sheet, sheet)); } Map<Integer, String> columnNames = new TreeMap<>(); Iterator<org.apache.poi.ss.usermodel.Row> it = excelsheet.iterator(); int rows = 0; while (it.hasNext()) { org.apache.poi.ss.usermodel.Row row = it.next(); Iterator<Cell> cellIterator = row.cellIterator(); if (checkIfRowIsEmpty(row)) { continue; } Row newRow = new Row(); newRow.add("fwd", rows); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String name = columnName(cell.getAddress().getColumn()); if (firstRowAsHeader && rows > 0) { String value = columnNames.get(cell.getAddress().getColumn()); if (value != null) { name = value; } } String value = ""; switch (cell.getCellTypeEnum()) { case STRING: newRow.add(name, cell.getStringCellValue()); value = cell.getStringCellValue(); break; case NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { newRow.add(name, cell.getDateCellValue()); value = cell.getDateCellValue().toString(); } else { newRow.add(name, cell.getNumericCellValue()); value = String.valueOf(cell.getNumericCellValue()); } break; case BOOLEAN: newRow.add(name, cell.getBooleanCellValue()); value = String.valueOf(cell.getBooleanCellValue()); break; } if (rows == 0 && firstRowAsHeader) { columnNames.put(cell.getAddress().getColumn(), value); } } if (firstRowAsHeader && rows == 0) { rows++; continue; } results.add(newRow); rows++; } if (firstRowAsHeader) { rows = rows - 1; } for (int i = rows - 1; i >= 0; --i) { results.get(rows - i - 1).addOrSetAtIndex(1, "bkd", i); } } } } } catch (Exception e) { throw new ErrorRowException(NAME, e.getMessage(), 1); } finally { if (input != null) { Closeables.closeQuietly(input); } } return results; }  @Override UsageDefinition define(); @Override void initialize(Arguments args); @Override void destroy(); @Override List<Row> execute(List<Row> records, final ExecutorContext context); @Override Mutation lineage(); }
ParseExcel implements Directive, Lineage { @Override public List<Row> execute(List<Row> records, final ExecutorContext context) throws DirectiveExecutionException, ErrorRowException { List<Row> results = new ArrayList<>(); ByteArrayInputStream input = null; try { for (Row record : records) { int idx = record.find(column); if (idx != -1) { Object object = record.getValue(idx); byte[] bytes = null; if (object instanceof byte[]) { bytes = (byte[]) object; } else if (object instanceof ByteBuffer) { ByteBuffer buffer = (ByteBuffer) object; bytes = new byte[buffer.remaining()]; buffer.get(bytes); } else { throw new DirectiveExecutionException( NAME, String.format("Column '%s' should be of type 'byte array' or 'ByteBuffer'.", column)); } if (bytes != null) { input = new ByteArrayInputStream(bytes); XSSFWorkbook book = new XSSFWorkbook(input); XSSFSheet excelsheet; if (Types.isInteger(sheet)) { excelsheet = book.getSheetAt(Integer.parseInt(sheet)); } else { excelsheet = book.getSheet(sheet); } if (excelsheet == null) { throw new DirectiveExecutionException( NAME, String.format("Failed to extract sheet '%s' from the excel. " + "Sheet '%s' does not exist.", sheet, sheet)); } Map<Integer, String> columnNames = new TreeMap<>(); Iterator<org.apache.poi.ss.usermodel.Row> it = excelsheet.iterator(); int rows = 0; while (it.hasNext()) { org.apache.poi.ss.usermodel.Row row = it.next(); Iterator<Cell> cellIterator = row.cellIterator(); if (checkIfRowIsEmpty(row)) { continue; } Row newRow = new Row(); newRow.add("fwd", rows); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String name = columnName(cell.getAddress().getColumn()); if (firstRowAsHeader && rows > 0) { String value = columnNames.get(cell.getAddress().getColumn()); if (value != null) { name = value; } } String value = ""; switch (cell.getCellTypeEnum()) { case STRING: newRow.add(name, cell.getStringCellValue()); value = cell.getStringCellValue(); break; case NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { newRow.add(name, cell.getDateCellValue()); value = cell.getDateCellValue().toString(); } else { newRow.add(name, cell.getNumericCellValue()); value = String.valueOf(cell.getNumericCellValue()); } break; case BOOLEAN: newRow.add(name, cell.getBooleanCellValue()); value = String.valueOf(cell.getBooleanCellValue()); break; } if (rows == 0 && firstRowAsHeader) { columnNames.put(cell.getAddress().getColumn(), value); } } if (firstRowAsHeader && rows == 0) { rows++; continue; } results.add(newRow); rows++; } if (firstRowAsHeader) { rows = rows - 1; } for (int i = rows - 1; i >= 0; --i) { results.get(rows - i - 1).addOrSetAtIndex(1, "bkd", i); } } } } } catch (Exception e) { throw new ErrorRowException(NAME, e.getMessage(), 1); } finally { if (input != null) { Closeables.closeQuietly(input); } } return results; }  @Override UsageDefinition define(); @Override void initialize(Arguments args); @Override void destroy(); @Override List<Row> execute(List<Row> records, final ExecutorContext context); @Override Mutation lineage(); static final String NAME; }