List of usage examples for org.apache.poi.ss.usermodel Row removeCell
void removeCell(Cell cell);
From source file:org.alanwilliamson.openbd.plugin.spreadsheet.SheetUtility.java
License:Open Source License
/** * Given a sheet, this method inserts a row to a sheet and moves * all the rows to the bottom down one/*from www . jav a 2 s .c o m*/ * * Note, this method will not update any formula references. * * @param sheet * @param rowPosition */ public static void insertRow(Sheet sheet, int rowPosition) { //Row Position maybe beyond the last if (rowPosition > sheet.getLastRowNum()) { sheet.createRow(rowPosition); return; } //Create a new Row at the end sheet.createRow(sheet.getLastRowNum() + 1); Row row; for (int r = sheet.getLastRowNum(); r > rowPosition; r--) { row = sheet.getRow(r); if (row == null) row = sheet.createRow(r); //Clear the row for (int c = 0; c < row.getLastCellNum(); c++) { Cell cell = row.getCell(c); if (cell != null) row.removeCell(cell); } //Move the row Row previousRow = sheet.getRow(r - 1); if (previousRow == null) { sheet.createRow(r - 1); continue; } for (int c = 0; c < previousRow.getLastCellNum(); c++) { Cell cell = previousRow.getCell(c); if (cell != null) { Cell newCell = row.createCell(c, cell.getCellType()); cloneCell(newCell, cell); } } } //Clear the newly inserted row row = sheet.getRow(rowPosition); for (int c = 0; c < row.getLastCellNum(); c++) { Cell cell = row.getCell(c); if (cell != null) row.removeCell(cell); } }
From source file:org.alanwilliamson.openbd.plugin.spreadsheet.SheetUtility.java
License:Open Source License
/** * Given a sheet, this method deletes a column from a sheet and moves * all the columns to the right of it to the left one cell. * /*from ww w .j a v a2 s . co m*/ * Note, this method will not update any formula references. * * @param sheet * @param column */ public static void deleteColumn(Sheet sheet, int columnToDelete) { int maxColumn = 0; for (int r = 0; r < sheet.getLastRowNum() + 1; r++) { Row row = sheet.getRow(r); // if no row exists here; then nothing to do; next! if (row == null) continue; int lastColumn = row.getLastCellNum(); if (lastColumn > maxColumn) maxColumn = lastColumn; // if the row doesn't have this many columns then we are good; next! if (lastColumn < columnToDelete) continue; for (int x = columnToDelete + 1; x < lastColumn + 1; x++) { Cell oldCell = row.getCell(x - 1); if (oldCell != null) row.removeCell(oldCell); Cell nextCell = row.getCell(x); if (nextCell != null) { Cell newCell = row.createCell(x - 1, nextCell.getCellType()); cloneCell(newCell, nextCell); } } } // Adjust the column widths for (int c = 0; c < maxColumn; c++) { sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1)); } }
From source file:org.alanwilliamson.openbd.plugin.spreadsheet.SheetUtility.java
License:Open Source License
/** * Shifts all the cells from the specified column to the right * @param row//from www.j a va 2 s .co m * @param column */ public static void shiftCellRight(Row row, int column) { int lastColumnCell = row.getLastCellNum(); if (column > lastColumnCell) return; for (int x = lastColumnCell; x > column; --x) { Cell cell = row.getCell(x - 1); if (cell == null) continue; Cell newCell = row.createCell(x, cell.getCellType()); cloneCell(newCell, cell); row.removeCell(cell); } }
From source file:org.aludratest.util.ExcelUtil.java
License:Apache License
private static void moveCell(Row row, int fromIndex, int toIndex) { Cell oldCell = row.getCell(fromIndex); if (oldCell != null) { Cell newCell = row.createCell(toIndex, oldCell.getCellType()); newCell.setCellStyle(oldCell.getCellStyle()); newCell.setCellValue(oldCell.getStringCellValue()); row.removeCell(oldCell); }/* www. j a va2s.c o m*/ }
From source file:org.bbreak.excella.core.util.PoiUtil.java
License:Open Source License
/** * ??//from w w w . j a va2 s .c om * * @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); } }
From source file:org.bbreak.excella.reports.listener.BreakAdapter.java
License:Open Source License
protected void setRowBreak(Sheet sheet, Row row, Cell cell) { sheet.setRowBreak(row.getRowNum()); row.removeCell(cell); }
From source file:org.bbreak.excella.reports.listener.RemoveAdapter.java
License:Open Source License
@Override public void postParse(Sheet sheet, SheetParser sheetParser, SheetData sheetData) throws ParseException { int firstRowNum = sheet.getFirstRowNum(); int lastRowNum = sheet.getLastRowNum(); for (int rowIndex = firstRowNum; rowIndex <= lastRowNum; rowIndex++) { Row row = sheet.getRow(rowIndex); if (row != null) { int firstColNum = row.getFirstCellNum(); int lastColNum = row.getLastCellNum() - 1; boolean isRowFlag = false; for (int colIndex = firstColNum; colIndex <= lastColNum; colIndex++) { Cell cell = row.getCell(colIndex); if (cell != null) { if (cell.getCellTypeEnum() == CellType.STRING && cell.getStringCellValue().contains(RemoveParamParser.DEFAULT_TAG)) { // ?? String[] paramArray = getStrParam(sheet, rowIndex, colIndex); // ?? String removeUnit = paramArray[0]; // ?? row.removeCell(cell); // ???? if (removeUnit.equals("") || removeUnit.equals(ROW)) { removeRegion(sheet, rowIndex, -1); removeControlRow(sheet, rowIndex); isRowFlag = true; break; } else if (removeUnit.equals(CELL) || removeUnit.equals(COLUMN)) { // ??????? removeCellOrCol(paramArray, removeUnit, sheet, row, cell, rowIndex, colIndex); }/*from www .j av a 2 s.co m*/ lastColNum = row.getLastCellNum() - 1; colIndex--; } // ?? if (isControlRow(sheet, sheetParser, row, cell)) { removeControlRow(sheet, rowIndex); isRowFlag = true; break; } } } // ??? if (isRowFlag) { lastRowNum = sheet.getLastRowNum(); rowIndex--; } } } }
From source file:org.bbreak.excella.reports.listener.RemoveAdapter.java
License:Open Source License
/** * ???????/*from w w w .j a v a2s .c o m*/ * * @param paramArray * @param removeUnit * @param sheet * @param row * @param cell * @param rowIndex * @param colIndex * @return paramArray */ private void removeCellOrCol(String[] paramArray, String removeUnit, Sheet sheet, Row row, Cell cell, int rowIndex, int colIndex) { if (removeUnit.equals(CELL)) { removeRegion(sheet, rowIndex, colIndex); if (paramArray.length > 1) { Row removeRow = sheet.getRow(rowIndex); if (removeRow != null) { Cell removeCell = removeRow.getCell(colIndex); if (removeCell != null) { removeRow.removeCell(removeCell); } } // ?? String direction = paramArray[1]; if (direction.equals(LEFT)) { shiftLeft(row, cell, colIndex); } else if (direction.equals(UP)) { shiftUp(sheet, cell, rowIndex, colIndex); } } else { // ????????? shiftLeft(row, cell, colIndex); } } else if (removeUnit.equals(COLUMN)) { removeRegion(sheet, -1, colIndex); // ?? for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) { Row removeCol = sheet.getRow(rowNum); if (removeCol != null) { Cell removeCell = removeCol.getCell(colIndex); if (removeCell != null) { removeCol.removeCell(removeCell); } } shiftLeft(sheet.getRow(rowNum), cell, colIndex); } } }
From source file:org.bbreak.excella.reports.listener.RemoveAdapter.java
License:Open Source License
/** * ??????//from www .j a v a2 s.c om * * @param row * @param cell * @param colIndex */ private void shiftLeft(Row row, Cell cell, int colIndex) { // int startCopyIndex = colIndex + 1; if (row == null) { return; } int finishCopyIndex = row.getLastCellNum() - 1; for (int copyColNum = startCopyIndex; copyColNum <= finishCopyIndex; copyColNum++) { // Cell fromCell = row.getCell(copyColNum); // Cell toCell = row.getCell(copyColNum - 1); if (fromCell != null) { if (toCell == null) { toCell = row.createCell(copyColNum - 1); } PoiUtil.copyCell(fromCell, toCell); row.removeCell(fromCell); } } }
From source file:org.bbreak.excella.reports.listener.RemoveAdapter.java
License:Open Source License
/** * ??????// w w w .j a v a 2s.c om * * @param sheet * @param cell * @param rowIndex * @param colIndex */ private void shiftUp(Sheet sheet, Cell cell, int rowIndex, int colIndex) { // ? int startCopyIndex = rowIndex + 1; int finishCopyIndex = sheet.getLastRowNum(); for (int copyRowNum = startCopyIndex; copyRowNum <= finishCopyIndex; copyRowNum++) { Row row = sheet.getRow(copyRowNum); if (row != null) { Row preRow = sheet.getRow(copyRowNum - 1); // Cell fromCell = row.getCell(colIndex); if (fromCell != null) { // Cell toCell = null; if (preRow == null) { preRow = sheet.createRow(copyRowNum - 1); } toCell = preRow.getCell(colIndex); if (toCell == null) { toCell = preRow.createCell(colIndex); } PoiUtil.copyCell(fromCell, toCell); row.removeCell(fromCell); } } } }