for(Row row : sheet) { for(int cn=0; cn<row.getLastCellNum(); cn++) { // If the cell is missing from the file, generate a blank one // (Works by specifying a MissingCellPolicy) Cell cell = row.getCell(cn, Row.CREATE_NULL_AS_BLANK); // Print the cell for debugging System.out.println("CELL: " + cn + " --> " + cell.toString()); } }
protected void addColumnValue(Row row, String value) { int cellIndex = row.getLastCellNum() == -1 ? 0 : row.getLastCellNum(); Cell cell = row.createCell(cellIndex); cell.setCellValue(createRichTextString(value)); if (facetStyle != null) { cell.setCellStyle(facetStyle); } }
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; }
/** * 读取一行 * * @param row 行 * @param cellEditor 单元格编辑器 * @return 单元格值列表 */ public static List<Object> readRow(Row row, CellEditor cellEditor) { if (null == row) { return new ArrayList<>(0); } final short length = row.getLastCellNum(); if (length < 0) { return new ArrayList<>(0); } final List<Object> cellValues = new ArrayList<>((int) length); Object cellValue; boolean isAllNull = true; for (short i = 0; i < length; i++) { cellValue = CellUtil.getCellValue(row.getCell(i), cellEditor); isAllNull &= StrUtil.isEmptyIfStr(cellValue); cellValues.add(cellValue); } if (isAllNull) { // 如果每个元素都为空,则定义为空行 return new ArrayList<>(0); } return cellValues; }
/** * 读取一行 * * @param row 行 * @param cellEditor 单元格编辑器 * @return 单元格值列表 */ public static List<Object> readRow(Row row, CellEditor cellEditor) { if (null == row) { return new ArrayList<>(0); } final short length = row.getLastCellNum(); if (length < 0) { return new ArrayList<>(0); } final List<Object> cellValues = new ArrayList<>((int) length); Object cellValue; boolean isAllNull = true; for (short i = 0; i < length; i++) { cellValue = CellUtil.getCellValue(row.getCell(i), cellEditor); isAllNull &= StrUtil.isEmptyIfStr(cellValue); cellValues.add(cellValue); } if (isAllNull) { // 如果每个元素都为空,则定义为空行 return new ArrayList<>(0); } return cellValues; }
public KCell[] getRow( int rownr ) { if ( rownr < sheet.getFirstRowNum() ) { return new KCell[] {}; } else if ( rownr > sheet.getLastRowNum() ) { throw new ArrayIndexOutOfBoundsException( "Read beyond last row: " + rownr ); } Row row = sheet.getRow( rownr ); if ( row == null ) { // read an empty row return new KCell[] {}; } int cols = row.getLastCellNum(); if ( cols < 0 ) { // this happens if a row has no cells, POI returns -1 then return new KCell[] {}; } PoiCell[] xlsCells = new PoiCell[cols]; for ( int i = 0; i < cols; i++ ) { Cell cell = row.getCell( i ); if ( cell != null ) { xlsCells[i] = new PoiCell( cell ); } } return xlsCells; }
protected void addColumnValue(Row row, List<UIComponent> components, UIColumn column) { int cellIndex = row.getLastCellNum() == -1 ? 0 : row.getLastCellNum(); Cell cell = row.createCell(cellIndex); FacesContext context = FacesContext.getCurrentInstance(); if (column.getExportFunction() != null) { cell.setCellValue(createRichTextString(exportColumnByFunction(context, column))); } else { StringBuilder builder = new StringBuilder(); for (UIComponent component : components) { if (component.isRendered()) { String value = exportValue(context, component); if (value != null) { builder.append(value); } } } cell.setCellValue(createRichTextString(builder.toString())); } if (cellStyle != null) { cell.setCellStyle(cellStyle); } }
private String[] readRow(int index) { Row row = sheet.getRow(index); if (row == null || row.getPhysicalNumberOfCells() == 0) { return new String[0]; } String[] record = new String[row.getLastCellNum()]; for (int i = 0; i < record.length; i++) { record[i] = Optional.ofNullable(row.getCell(i)).map(Object::toString).orElse(null); } return record; } }
public static void main(String[] args) { Path myFile = Paths.get(System.getProperty("user.home"), "Desktop", "tester.xlsx"); try { XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(myFile.toFile())); XSSFSheet sheet = wb.getSheetAt(0); makeRowBold(wb, sheet.getRow(0)); wb.write(new FileOutputStream(myFile.toFile())); } catch (IOException e) { e.printStackTrace(); } } public static void makeRowBold(Workbook wb, Row row){ CellStyle style = wb.createCellStyle();//Create style Font font = wb.createFont();//Create font font.setBoldweight(Font.BOLDWEIGHT_BOLD);//Make font bold style.setFont(font);//set it to bold for(int i = 0; i < row.getLastCellNum(); i++){//For each cell in the row row.getCell(i).setCellStyle(style);//Set the sty;e } }
public static void makeRowBold(Workbook wb, Row row){ CellStyle style = wb.createCellStyle();//Create style Font font = wb.createFont();//Create font font.setBoldweight(Font.BOLDWEIGHT_BOLD);//Make font bold style.setFont(font);//set it to bold for(int i = 0; i < row.getLastCellNum(); i++){//For each cell in the row row.getCell(i).setCellStyle(style);//Set the style } }
@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); } }
public void setHeaders(Row row) { headers.clear(); for (int i = 0; i < row.getLastCellNum(); i++) { headers.add(XLSParser.getCellValue(row.getCell(i))); } }
/** * 获取最后一个列号 * @return 结果 */ public int getLastCellNum(){ return this.getRow(headerNum).getLastCellNum(); }
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); } } }
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() } } } }
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); } } }
@Override public ExcelCell getCell(int index) { getTable().notifyMaxColumn(row.getLastCellNum() - 1); Cell c = row.getCell(index); if (c == null) { return new ExcelCell(index - 1, this); } else { return new ExcelCell(c, this); } }
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); } } }
public void removeRowCells(Row row, int startCellNum, int endCellNum) { transformations.add( new RemoveTransformation( new Block(sheet, row.getRowNum(), startCellNum, row.getRowNum(), endCellNum)) ); ShiftTransformation shiftTransformation = new ShiftTransformation(new Block(sheet, row.getRowNum(), (int) (endCellNum + 1), row.getRowNum(), Integer.MAX_VALUE), 0, endCellNum - startCellNum + 1); transformations.add( shiftTransformation ); transformations.add( new RemoveTransformation( new Block(sheet, row.getRowNum(), (int) (row.getLastCellNum() - (endCellNum - startCellNum)), row.getRowNum(), row.getLastCellNum()))); formulaController.updateWorkbookFormulas( shiftTransformation ); TagBodyHelper.removeRowCells( sheet.getPoiSheet(), row, startCellNum, endCellNum ); }