List of usage examples for org.apache.poi.ss.usermodel Sheet removeRow
void removeRow(Row row);
From source file:IO.FILES.java
public static void removeRow(Sheet sheet, Row row) { int rowIndex = row.getRowNum(); sheet.removeRow(row); int lastRowNum = sheet.getLastRowNum(); if ((rowIndex >= 0) && (rowIndex < lastRowNum)) sheet.shiftRows(rowIndex + 1, lastRowNum, -1); }
From source file:IO.REDACCIONES.java
public static void overWrite(Workbook wb, Persona p, String texto) throws FileNotFoundException, IOException { if ((p == null) || (wb == null)) return;/*from w w w . j a va 2 s. co m*/ Sheet h = wb.getSheetAt(1); boolean encontrado = false; Row row = null; Iterator it = h.rowIterator(); while (it.hasNext()) { row = (Row) it.next(); if (row.getCell(0).getStringCellValue().equalsIgnoreCase(p.getId())) { encontrado = true; } } if (!encontrado) return; int aux = row.getRowNum(); h.removeRow(row); row = h.createRow(aux); Cell cell1 = row.createCell(0); cell1.setCellValue(p.getId()); Cell cell2 = row.createCell(1); cell2.setCellValue(texto); }
From source file:net.sf.excelutils.tags.ForeachTag.java
License:Apache License
public int[] parseTag(Object context, Workbook wb, Sheet sheet, Row curRow, Cell curCell) throws ExcelException { int forstart = curRow.getRowNum(); int forend = -1; int forCount = 0; String foreach = ""; boolean bFind = false; LOG.debug("ForeachTag: start=" + forstart); for (int rownum = forstart; rownum <= sheet.getLastRowNum(); rownum++) { Row row = sheet.getRow(rownum);/* www. j av a2s. c o m*/ if (null == row) continue; for (short colnum = row.getFirstCellNum(); colnum <= row.getLastCellNum(); colnum++) { Cell cell = row.getCell(colnum, Row.RETURN_NULL_AND_BLANK); if (null == cell) continue; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { String cellstr = cell.getStringCellValue(); // get the tag instance for the cellstr ITag tag = ExcelParser.getTagClass(cellstr); if (null != tag) { if (tag.hasEndTag()) { if (0 == forCount) { forstart = rownum; foreach = cellstr; } forCount++; break; } } if (cellstr.startsWith(KEY_END)) { forend = rownum; forCount--; if (forstart >= 0 && forend >= 0 && forend > forstart && forCount == 0) { bFind = true; } break; } } } if (bFind) break; } if (!bFind) return new int[] { 0, 0, 1 }; String properties = ""; String property = ""; // parse the collection an object StringTokenizer st = new StringTokenizer(foreach, " "); int pos = 0; while (st.hasMoreTokens()) { String str = st.nextToken(); if (pos == 1) { property = str; } if (pos == 3) { properties = str; } pos++; } // get collection Object collection = ExcelParser.parseStr(context, properties); if (null == collection) { return new int[] { 0, 0, 1 }; } // get the iterator of collection Iterator iterator = ExcelParser.getIterator(collection); // iterator int shiftNum = forend - forstart - 1; // set the start row number ExcelUtils.addValue(context, property + "StartRowNo", new Integer(forstart + 1)); int old_forend = forend; int propertyId = 0; int shift = 0; if (null != iterator) { while (iterator.hasNext()) { Object obj = iterator.next(); ExcelUtils.addValue(context, property, obj); // Iterator ID ExcelUtils.addValue(context, property + "Id", new Integer(propertyId)); // Index start with 1 ExcelUtils.addValue(context, property + "Index", new Integer(propertyId + 1)); // shift the #foreach #end block sheet.shiftRows(forstart, sheet.getLastRowNum(), shiftNum, true, true); // copy the body fo #foreach #end block WorkbookUtils.copyRow(sheet, forstart + shiftNum + 1, forstart, shiftNum); // parse shift = ExcelParser.parse(context, wb, sheet, forstart, forstart + shiftNum - 1); forstart += shiftNum + shift; forend += shiftNum + shift; propertyId++; } ExcelUtils.addValue(context, property + "Size", new Integer(propertyId)); } // set the end row number ExcelUtils.addValue(context, property + "EndRowNo", new Integer(forstart)); // delete #foreach #end block for (int rownum = forstart; rownum <= forend; rownum++) { sheet.removeRow(WorkbookUtils.getRow(rownum, sheet)); } // remove merged region in forstart & forend for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() >= forstart && r.getLastRow() <= forend) { sheet.removeMergedRegion(i); // we have to back up now since we removed one i = i - 1; } } if (forend + 1 <= sheet.getLastRowNum()) { sheet.shiftRows(forend + 1, sheet.getLastRowNum(), -(forend - forstart + 1), true, true); } return new int[] { ExcelParser.getSkipNum(forstart, forend), ExcelParser.getShiftNum(old_forend, forstart), 1 }; }
From source file:net.sf.excelutils.tags.IfTag.java
License:Apache License
public int[] parseTag(Object context, Workbook wb, Sheet sheet, Row curRow, Cell curCell) throws ExcelException { int ifstart = curRow.getRowNum(); int ifend = -1; int ifCount = 0; String ifstr = ""; boolean bFind = false; for (int rownum = ifstart; rownum <= sheet.getLastRowNum(); rownum++) { Row row = sheet.getRow(rownum);//from ww w. j a v a2 s.c o m if (null == row) continue; for (short colnum = row.getFirstCellNum(); colnum <= row.getLastCellNum(); colnum++) { Cell cell = row.getCell(colnum, Row.RETURN_NULL_AND_BLANK); if (null == cell) continue; if (cell.getCellType() == Cell.CELL_TYPE_STRING) { String cellstr = cell.getStringCellValue(); // get the tag instance for the cellstr ITag tag = ExcelParser.getTagClass(cellstr); if (null != tag) { if (tag.hasEndTag()) { if (0 == ifCount) { ifstart = rownum; ifstr = cellstr; } ifCount++; break; } } if (cellstr.startsWith(KEY_END)) { ifend = rownum; ifCount--; if (ifstart >= 0 && ifend >= 0 && ifend > ifstart && ifCount == 0) { bFind = true; } break; } } } if (bFind) break; } if (!bFind) return new int[] { 0, 0, 1 }; // test if condition boolean bResult = false; // remove #if tag and get condition expression String expr = ifstr.trim().substring(KEY_IF.length()).trim(); // parse the condition expression expr = (String) ExcelParser.parseStr(context, expr, true); // use beanshell to eval expression value try { Interpreter in = createInterpreter(context); LOG.debug("IfTag test expr=" + expr); Object v = in.eval(expr); bResult = ((Boolean) v).booleanValue(); } catch (Exception e) { LOG.error("IfTag test expr error", e); bResult = false; } if (bResult) { // if condition is true // remove #if tag and #end tag only sheet.removeRow(WorkbookUtils.getRow(ifstart, sheet)); sheet.removeRow(WorkbookUtils.getRow(ifend, sheet)); // remove merged region in ifstart & ifend for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() == ifstart && r.getLastRow() == ifstart || r.getFirstRow() == ifend && r.getLastRow() == ifend) { sheet.removeMergedRegion(i); // we have to back up now since we removed one i = i - 1; } } if (ifend + 1 <= sheet.getLastRowNum()) { sheet.shiftRows(ifend + 1, sheet.getLastRowNum(), -1, true, true); } if (ifstart + 1 <= sheet.getLastRowNum()) { sheet.shiftRows(ifstart + 1, sheet.getLastRowNum(), -1, true, true); } return new int[] { 1, -2, 1 }; } else { // if condition is false // remove #if #end block for (int rownum = ifstart; rownum <= ifend; rownum++) { sheet.removeRow(WorkbookUtils.getRow(rownum, sheet)); } // remove merged region in ifstart & ifend for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() >= ifstart && r.getLastRow() <= ifend) { sheet.removeMergedRegion(i); // we have to back up now since we removed one i = i - 1; } } if (ifend + 1 <= sheet.getLastRowNum()) { sheet.shiftRows(ifend + 1, sheet.getLastRowNum(), -(ifend - ifstart + 1), true, true); } return new int[] { ExcelParser.getSkipNum(ifstart, ifend), ExcelParser.getShiftNum(ifend, ifstart), 1 }; } }
From source file:net.sf.excelutils.tags.PageTag.java
License:Apache License
public int[] parseTag(Object context, Workbook wb, Sheet sheet, Row curRow, Cell curCell) throws ExcelException { int rowNum = curRow.getRowNum(); LOG.debug("#page at rownum = " + rowNum); sheet.setRowBreak(rowNum - 1);//from ww w. j a va2 s. c o m sheet.removeRow(curRow); if (rowNum + 1 <= sheet.getLastRowNum()) { sheet.shiftRows(rowNum + 1, sheet.getLastRowNum(), -1, true, true); } return new int[] { 0, -1, 0 }; }
From source file:net.sf.excelutils.tags.SheetTag.java
License:Apache License
/** * Parse #sheet detail in list by sheetName *//*from w w w .j a va2 s .c o m*/ public int[] parseTag(Object context, Workbook wb, Sheet sheet, Row curRow, Cell curCell) throws ExcelException { String sheetExpr = curCell.getStringCellValue(); StringTokenizer st = new StringTokenizer(sheetExpr, " "); String properties = ""; String property = ""; String sheetName = ""; // parse the collection an object int pos = 0; while (st.hasMoreTokens()) { String str = st.nextToken(); if (pos == 1) { property = str; } if (pos == 3) { properties = str; } if (pos == 5) { sheetName = str; } pos++; } // get collection Object collection = ExcelParser.parseStr(context, properties); if (null == collection) { return new int[] { 0, 0, 1 }; } // remove #sheet tag sheet.removeRow(curRow); // remove merged region in forstart & forend for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() >= curRow.getRowNum() && r.getLastRow() <= curRow.getRowNum()) { sheet.removeMergedRegion(i); i = i - 1; } } sheet.shiftRows(curRow.getRowNum() + 1, sheet.getLastRowNum(), -1, true, true); // get the iterator of collection Iterator iterator = ExcelParser.getIterator(collection); if (null != iterator) { // first obj, use parse method Object firstObj = null; if (iterator.hasNext()) { firstObj = iterator.next(); } // next obj, clone sheet and use parseSheet method while (iterator.hasNext()) { Object obj = iterator.next(); ExcelUtils.addValue(context, property, obj); try { int sheetIndex = WorkbookUtils.getSheetIndex(wb, sheet); // clone sheet Sheet cloneSheet = wb.cloneSheet(sheetIndex); // set cloneSheet name int cloneSheetIndex = WorkbookUtils.getSheetIndex(wb, cloneSheet); setSheetName(obj, wb, cloneSheetIndex, sheetName); // parse cloneSheet ExcelUtils.parseSheet(context, wb, cloneSheet); } catch (Exception e) { if (LOG.isErrorEnabled()) { LOG.error("parse sheet error", e); } } } if (null != firstObj) { ExcelUtils.addValue(context, property, firstObj); // set sheet name int sheetIndex = WorkbookUtils.getSheetIndex(wb, sheet); setSheetName(firstObj, wb, sheetIndex, sheetName); } } return new int[] { 0, -1, 0 }; }
From source file:net.sf.excelutils.WorkbookUtils.java
License:Apache License
/** * Set Print Area/*from ww w . j a v a 2 s. c o m*/ * * @param wb * @param sheetIndex */ public static void setPrintArea(Workbook wb, int sheetIndex) { // sheet Sheet sheet = wb.getSheetAt(sheetIndex); if (null != sheet) { // #endRow Row endRow = sheet.getRow(sheet.getLastRowNum()); if (null != endRow) { Cell cell = WorkbookUtils.getCell(endRow, 0); String cellStr = cell.getStringCellValue(); cellStr = cellStr == null ? "" : cellStr.trim(); if (cellStr.startsWith(EndRowTag.KEY_ENDROW)) { // search #endColumn int endColumn = endRow.getLastCellNum(); for (int i = endRow.getLastCellNum(); i >= endRow.getFirstCellNum(); i--) { Cell endCell = WorkbookUtils.getCell(endRow, i); String endCellStr = endCell.getStringCellValue(); endCellStr = endCellStr == null ? "" : endCellStr.trim(); if (endCellStr.startsWith(EndRowTag.KEY_ENDCOLUMN)) { endColumn = i; break; } } wb.setPrintArea(sheetIndex, endRow.getFirstCellNum(), endColumn, sheet.getFirstRowNum(), sheet.getLastRowNum() - 1); sheet.removeRow(endRow); } } } }
From source file:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetDeleteRow.java
License:Open Source License
public cfData execute(cfSession _session, List<cfData> parameters) throws cfmRunTimeException { cfSpreadSheetData spreadsheet = null; String rows;//from ww w .j a v a 2 s .c o m /* * Collect up the parameters */ spreadsheet = (cfSpreadSheetData) parameters.get(1); rows = parameters.get(0).getString(); Sheet sheet = spreadsheet.getActiveSheet(); Set<Integer> numbers = tagUtils.getNumberSet(rows); Iterator<Integer> it = numbers.iterator(); while (it.hasNext()) { Row row = sheet.getRow(it.next() - 1); if (row != null) sheet.removeRow(row); } return cfBooleanData.TRUE; }
From source file:org.apache.metamodel.excel.ExcelDeleteBuilder.java
License:Apache License
@Override public void execute() throws MetaModelException { // close the update callback will flush any changes _updateCallback.close();/* w ww .j a va 2s . com*/ // read the workbook without streaming, since this will not wrap it in a // streaming workbook implementation (which do not support random // accessing rows). final Workbook workbook = _updateCallback.getWorkbook(false); final String tableName = getTable().getName(); final SelectItem[] selectItems = MetaModelHelper.createSelectItems(getTable().getColumns()); final DataSetHeader header = new SimpleDataSetHeader(selectItems); final Sheet sheet = workbook.getSheet(tableName); final Iterator<Row> rowIterator = ExcelUtils.getRowIterator(sheet, _updateCallback.getConfiguration(), true); final List<Row> rowsToDelete = new ArrayList<Row>(); while (rowIterator.hasNext()) { final Row excelRow = rowIterator.next(); final DefaultRow row = ExcelUtils.createRow(workbook, excelRow, header); final boolean deleteRow = deleteRow(row); if (deleteRow) { rowsToDelete.add(excelRow); } } // reverse the list to not mess up any row numbers Collections.reverse(rowsToDelete); for (Row row : rowsToDelete) { sheet.removeRow(row); } }
From source file:org.bbreak.excella.core.util.PoiUtil.java
License:Open Source License
/** * ??/* www. j av a 2s . c o m*/ * * @param sheet * @param rangeAddress */ public static void clearCell(Sheet sheet, CellRangeAddress rangeAddress) { int fromRowIndex = rangeAddress.getFirstRow(); int fromColumnIndex = rangeAddress.getFirstColumn(); int toRowIndex = rangeAddress.getLastRow(); int toColumnIndex = rangeAddress.getLastColumn(); // ??? List<Row> removeRowList = new ArrayList<Row>(); Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (fromRowIndex <= row.getRowNum() && row.getRowNum() <= toRowIndex) { Set<Cell> removeCellSet = new HashSet<Cell>(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (fromColumnIndex <= cell.getColumnIndex() && cell.getColumnIndex() <= toColumnIndex) { removeCellSet.add(cell); } } for (Cell cell : removeCellSet) { row.removeCell(cell); } } if (row.getLastCellNum() == -1) { removeRowList.add(row); } } for (Row row : removeRowList) { sheet.removeRow(row); } }