public static boolean isRowEmpty(Row row) { for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) { Cell cell = row.getCell(c); if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) return false; } return true; }
@Procedure("apoc.load.xls") @Description("apoc.load.xls('url','selector',{config}) YIELD lineNo, list, map - load XLS fom URL as stream of row values,\n config contains any of: {skip:1,limit:5,header:false,ignore:['tmp'],arraySep:';',mapping:{years:{type:'int',arraySep:'-',array:false,name:'age',ignore:false, dateFormat:'iso_date', dateParse:['dd-MM-yyyy']}}") public Stream<XLSResult> xls(@Name("url") String url, @Name("selector") String selector, @Name(value = "config",defaultValue = "{}") Map<String, Object> config) { boolean failOnError = booleanValue(config, "failOnError", true); try (CountingInputStream stream = FileUtils.inputStreamFor(url)) { Selection selection = new Selection(selector); char arraySep = separator(config, "arraySep", DEFAULT_ARRAY_SEP); long skip = longValue(config, "skip", 0L); boolean hasHeader = booleanValue(config, "header", true); long limit = longValue(config, "limit", Long.MAX_VALUE); List<String> ignore = value(config, "ignore", emptyList()); List<Object> nullValues = value(config, "nullValues", emptyList()); Map<String, Map<String, Object>> mapping = value(config, "mapping", Collections.emptyMap()); Map<String, Mapping> mappings = createMapping(mapping, arraySep, ignore); Workbook workbook = WorkbookFactory.create(stream); Sheet sheet = workbook.getSheet(selection.sheet); if (sheet==null) throw new IllegalStateException("Sheet "+selection.sheet+" not found"); selection.updateVertical(sheet.getFirstRowNum(),sheet.getLastRowNum()); Row firstRow = sheet.getRow(selection.top); selection.updateHorizontal(firstRow.getFirstCellNum(), firstRow.getLastCellNum()); String[] header = getHeader(hasHeader, firstRow,selection, ignore, mappings); boolean checkIgnore = !ignore.isEmpty() || mappings.values().stream().anyMatch( m -> m.ignore); return StreamSupport.stream(new XLSSpliterator(sheet, selection, header, url, skip, limit, checkIgnore,mappings, nullValues), false); } catch (Exception e) { if(!failOnError) return Stream.of(new XLSResult(new String[0], new Object[0], 0, true, Collections.emptyMap(), emptyList())); else throw new RuntimeException("Can't read XLS from URL " + cleanUrl(url), e); } }
/** * Returns the index of the column, the next column will be the first cell * with data in given row. * */ public static int getMinColumnIndex(int rownum, Sheet sheet) { Row row = sheet.getRow(rownum); return row == null ? 0 : row.getFirstCellNum(); }
public short getCellIdx(Row nameRow, String colName) { for (short idx = nameRow.getFirstCellNum(); idx <= nameRow.getLastCellNum(); idx++) { if (rownameRowgetCell(idx).equals(colName)) return idx; } }
private static void replacePropertyInRow(Row row, String oldProperty, String newProperty) { if (row.getFirstCellNum() >= 0 && row.getLastCellNum() >= 0) { for (int j = row.getFirstCellNum(), c = row.getLastCellNum(); j <= c; j++) { Cell cell = row.getCell(j); replacePropertyInCell(cell, oldProperty, newProperty); } } }
static void clearRow(Row row) { if (row != null && row.getFirstCellNum() >= 0 && row.getLastCellNum() >= 0) { for (int i = row.getFirstCellNum(), c = row.getLastCellNum(); i <= c; i++) { Cell cell = row.getCell(i); clearCell(cell); } } }
static void clearRow(Row row) { if (row != null && row.getFirstCellNum() >= 0 && row.getLastCellNum() >= 0) { for (int i = row.getFirstCellNum(), c = row.getLastCellNum(); i <= c; i++) { Cell cell = row.getCell(i); clearCell(cell); } } }
private static void replacePropertyInRow(Row row, String oldProperty, String newProperty) { if (row.getFirstCellNum() >= 0 && row.getLastCellNum() >= 0) { for (int j = row.getFirstCellNum(), c = row.getLastCellNum(); j <= c; j++) { Cell cell = row.getCell(j); replacePropertyInCell(cell, oldProperty, newProperty); } } }
private void extractFieldNames() throws DataSourceException { headings = new ArrayList<String>(); Row headingRow = sheet.getRow(HEADING_ROW_INDEX); checkForNull(headingRow, "No headings found at row " + HEADING_ROW_INDEX); firstCellIndex = headingRow.getFirstCellNum(); for (short i = firstCellIndex; i < headingRow.getLastCellNum(); i++) { String heading = headingRow.getCell(i).getStringCellValue(); headings.add(heading); } }
for(Row row : sheet) { short minColIx = row.getFirstCellNum(); short maxColIx = row.getLastCellNum(); for(short colIx = minColIx; colIx<maxColIx; colIx++) { Cell c = row.getCell(colIx); if(c != null) { if(c.getCellType() == Cell.CELL_TYPE_NUMERIC) { // add c.getNumericCellValue() } } } }
for (int i = 0; i < mySheet.getPhysicalNumberOfRows(); i++) { Row row = mySheet.getRow(i); for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) { Cell cell = row.getCell(c); if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) ..... } }
public static boolean isRowEmpty(Row row){ for(int c = row.getFirstCellNum(); c < 4 && c < row.getLastCellNum(); c++){ Cell cell = row.getCell(c); if(cell!=null && cell.getCellType()!=Cell.CELL_TYPE_BLANK){ return false; } } return true; } if(isRowEmpty(row)==false){ compList.add(compItem); }
private boolean isRowEmpty(Row row) { for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) { Cell cell = row.getCell(c, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK); if (cell != null && cell.getCellType() != CellType.BLANK) { return false; } } return true; }
private static void removeMergedRegions(Sheet sheet, Row row) { if (row != null && row.getFirstCellNum() >= 0 && row.getLastCellNum() >= 0) { int i = row.getRowNum(); for (int j = row.getFirstCellNum(), c = row.getLastCellNum(); j <= c; j++) { Util.removeMergedRegion(sheet, i, j); } } }
private int getFirstCellNum(Row row) { if (xlsArea != null && xlsArea.getDataX() > 0) { return xlsArea.getDataX(); } return row.getFirstCellNum(); }
private SheetMapper<T> getPoiMapper(int startRow, Sheet sheet) { Row row = sheet.getRow(startRow); List<CsvColumnKey> keys = new ArrayList<CsvColumnKey>(row.getLastCellNum() - row.getFirstCellNum()); for(short i = row.getFirstCellNum(); i <= row.getLastCellNum(); i++) { Cell cell = row.getCell(i); if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) { keys.add(new CsvColumnKey(cell.getStringCellValue(), i)); } } return getPoiMapper(new MapperKey<CsvColumnKey>(keys.toArray(new CsvColumnKey[0]))); }
@Override public List<Attribute> getFixedValueAttributes(String datasourceID) { List<Attribute> attributes = new ArrayList<>(); Row attributeHeader = workbook.getSheetAt(DatasourceId.schools.sheetIdx).rowIterator().next(); IntStream.rangeClosed(attributeHeader.getFirstCellNum(), attributeHeader.getLastCellNum() - 1) .forEach(idx -> { String label = attributeHeader.getCell(idx).getStringCellValue(); attributes.add(new Attribute(getProvider(), label, label)); } ); return attributes; }
boolean isEmptyRow(Row row){ boolean isEmptyRow = true; for(int cellNum = row.getFirstCellNum(); cellNum < row.getLastCellNum(); cellNum++){ Cell cell = row.getCell(cellNum); if(cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK && StringUtils.isNotBlank(cell.toString())){ isEmptyRow = false; } } return isEmptyRow; }
public static void autoSizeColumns(Workbook workbook) { int sheetCount = workbook.getNumberOfSheets(); for (int i = 0; i < sheetCount; i++) { Sheet sheet = workbook.getSheetAt(i); int firstRowNum = sheet.getFirstRowNum(); if (firstRowNum >= 0) { Row firstRow = sheet.getRow(firstRowNum); for (int cellnum = firstRow.getFirstCellNum(); cellnum < firstRow.getLastCellNum(); cellnum++) sheet.autoSizeColumn(cellnum); } } }
private void writeRowMetadata(IRI rowIRI, Row row, ExtractionResult er) { final int firstCellNum = row.getFirstCellNum(); final int lastCellNum = row.getLastCellNum(); er.writeTriple(rowIRI, excel.firstCell , RDFUtils.literal(firstCellNum)); er.writeTriple(rowIRI, excel.lastCell , RDFUtils.literal(lastCellNum )); }