Example usage for org.apache.poi.ss.usermodel Sheet shiftRows

List of usage examples for org.apache.poi.ss.usermodel Sheet shiftRows

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. shiftRows

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Sheet shiftRows.

Prototype

void shiftRows(int startRow, int endRow, int n);

Source Link

Document

Shifts rows between startRow and endRow n number of rows.

Usage

From source file:org.datanucleus.store.excel.ExcelPersistenceHandler.java

License:Open Source License

/**
 * Deletes a persistent object from the database.
 * @param op The Object Provider of the object to be deleted.
 * @throws NucleusDataStoreException when an error occurs in the datastore communication
 * @throws NucleusOptimisticException thrown if version checking fails on an optimistic transaction for this object
 *//*from   w  w w .j av a 2  s  .c o m*/
public void deleteObject(ObjectProvider op) {
    // Check if read-only so update not permitted
    assertReadOnlyForUpdateOfObject(op);

    ExecutionContext ec = op.getExecutionContext();
    ManagedConnection mconn = storeMgr.getConnection(ec);
    try {
        AbstractClassMetaData cmd = op.getClassMetaData();
        if (cmd.isVersioned()) {
            NucleusLogger.PERSISTENCE.warn(
                    "This datastore doesn't support optimistic version checks since the datastore file is for a single-connection");
        }

        Workbook wb = (Workbook) mconn.getConnection();
        Table table = ec.getStoreManager().getStoreDataForClass(op.getClassMetaData().getFullClassName())
                .getTable();
        final Sheet sheet = ExcelUtils.getSheetForClass(op, wb, table);

        // Invoke any cascade deletion
        op.loadUnloadedFields();
        op.provideFields(cmd.getAllMemberPositions(), new DeleteFieldManager(op));

        // Delete this object
        long startTime = System.currentTimeMillis();
        if (NucleusLogger.DATASTORE_PERSIST.isDebugEnabled()) {
            NucleusLogger.DATASTORE_PERSIST.debug(
                    Localiser.msg("Excel.Delete.Start", op.getObjectAsPrintable(), op.getInternalObjectId()));
        }

        int rowId = ExcelUtils.getRowNumberForObjectInWorkbook(op, wb, false, table);
        if (rowId < 0) {
            throw new NucleusObjectNotFoundException("object not found", op.getObject());
        }

        if (storeMgr instanceof XLSStoreManager && sheet.getLastRowNum() == rowId) {
            // Deleting top row which is last row so just remove all cells and leave row
            // otherwise Apache POI throws an ArrayIndexOutOfBoundsException
            Row row = sheet.getRow(rowId);
            Iterator<Cell> it = row.cellIterator();
            while (it.hasNext()) {
                row.removeCell(it.next());
            }
        } else {
            // Deleting top row so remove it
            sheet.removeRow(sheet.getRow(rowId));
            if (sheet.getLastRowNum() > rowId) {
                sheet.shiftRows(rowId + 1, sheet.getLastRowNum(), -1);
            }
        }

        if (NucleusLogger.DATASTORE_PERSIST.isDebugEnabled()) {
            NucleusLogger.DATASTORE_PERSIST
                    .debug(Localiser.msg("Excel.ExecutionTime", (System.currentTimeMillis() - startTime)));
        }
        if (ec.getStatistics() != null) {
            ec.getStatistics().incrementNumWrites();
            ec.getStatistics().incrementDeleteCount();
        }
    } finally {
        mconn.release();
    }
}

From source file:org.openpythia.utilities.SSUtilities.java

License:Apache License

public static void deleteRow(Sheet sheet, Row rowToDelete) {

    // if the row contains merged regions, delete them
    List<Integer> mergedRegionsToDelete = new ArrayList<>();
    int numberMergedRegions = sheet.getNumMergedRegions();
    for (int i = 0; i < numberMergedRegions; i++) {
        CellRangeAddress mergedRegion = sheet.getMergedRegion(i);

        if (mergedRegion.getFirstRow() == rowToDelete.getRowNum()
                && mergedRegion.getLastRow() == rowToDelete.getRowNum()) {
            // this region is within the row - so mark it for deletion
            mergedRegionsToDelete.add(i);
        }/*from  w w  w .ja  v a 2 s. c om*/
    }

    // now that we know all regions to delete just do it
    for (Integer indexToDelete : mergedRegionsToDelete) {
        sheet.removeMergedRegion(indexToDelete);
    }

    int rowIndex = rowToDelete.getRowNum();

    // this only removes the content of the row
    sheet.removeRow(rowToDelete);

    int lastRowNum = sheet.getLastRowNum();

    // shift the rest of the sheet one index down
    if (rowIndex >= 0 && rowIndex < lastRowNum) {
        sheet.shiftRows(rowIndex + 1, lastRowNum, -1);
    }
}

From source file:org.opentestsystem.delivery.testreg.upload.ExcelFileAppender.java

License:Open Source License

public InputStream insertAtTop(String text, InputStream inputStream) {
    try {/*from   w  w w. j a v  a 2  s .c  om*/
        final Workbook workbook = WorkbookFactory.create(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        sheet.shiftRows(0, sheet.getLastRowNum(), 1);
        Row formatTypeRow = sheet.createRow(0);
        formatTypeRow.createCell(0, Cell.CELL_TYPE_STRING).setCellValue(text);
        return new ByteArrayInputStream(new ByteArrayOutputStream() {
            {
                workbook.write(this);
            }
        }.toByteArray());
    } catch (InvalidFormatException | IOException e) {
        throw new RuntimeException("");
    }
}

From source file:org.projectforge.excel.ExportSheet.java

License:Open Source License

private static Row copyRow(Sheet worksheet, int rowNum) {
    Row sourceRow = worksheet.getRow(rowNum);

    //Save the text of any formula before they are altered by row shifting
    String[] formulasArray = new String[sourceRow.getLastCellNum()];
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        if (sourceRow.getCell(i) != null && sourceRow.getCell(i).getCellType() == Cell.CELL_TYPE_FORMULA)
            formulasArray[i] = sourceRow.getCell(i).getCellFormula();
    }/*  w w  w  . ja  v a 2 s.  com*/

    worksheet.shiftRows(rowNum, worksheet.getLastRowNum(), 1);
    Row newRow = sourceRow; //Now sourceRow is the empty line, so let's rename it
    sourceRow = worksheet.getRow(rowNum + 1); //Now the source row is at rowNum+1

    // Loop through source columns to add to new row
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        Cell oldCell = sourceRow.getCell(i);
        Cell newCell;

        // If the old cell is null jump to next cell
        if (oldCell == null) {
            continue;
        } else {
            newCell = newRow.createCell(i);
        }

        // Copy style from old cell and apply to new cell
        CellStyle newCellStyle = worksheet.getWorkbook().createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        newCell.setCellStyle(newCellStyle);

        // If there is a cell comment, copy
        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        // If there is a cell hyperlink, copy
        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());

        // Set the cell data value
        switch (oldCell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            newCell.setCellFormula(formulasArray[i]);
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        default:
            break;
        }
    }

    // If there are any merged regions in the source row, copy to new row
    for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
        CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
        if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
            CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                    (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
                    cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
            worksheet.addMergedRegion(newCellRangeAddress);
        }
    }
    return newRow;
}

From source file:org.rdcit.tools.SpreadsheetWriter.java

public void createNewRow(int sheetNum, int rowNum) {
    Sheet sheet = workbook.getSheetAt(sheetNum);
    if (rowNum <= sheet.getLastRowNum()) {
        sheet.shiftRows(rowNum, sheet.getLastRowNum(), 1);
        sheet.createRow(rowNum);/* w  ww  .ja  v  a 2 s  .co  m*/
    } else {
        sheet.createRow(sheet.getLastRowNum() + 1);
    }
}

From source file:org.tiefaces.components.websheet.utility.CommandUtility.java

License:MIT License

/**
 * Remove the rows.//from w  ww .jav a 2 s .c o  m
 *
 * @param sheet
 *            the sheet
 * @param rowIndexStart
 *            start row index.
 * @param rowIndexEnd
 *            end row index.
 * @param cachedMap
 *            the cached map
 */
public static void removeRowsInSheet(final Sheet sheet, final int rowIndexStart, final int rowIndexEnd,
        final Map<Cell, String> cachedMap) {

    for (int irow = rowIndexStart; irow <= rowIndexEnd; irow++) {
        removeCachedCellForRow(sheet, irow, cachedMap);
    }
    int irows = rowIndexEnd - rowIndexStart + 1;
    if ((irows < 1) || (rowIndexStart < 0)) {
        return;
    }
    int lastRowNum = sheet.getLastRowNum();
    if (rowIndexEnd < lastRowNum) {
        sheet.shiftRows(rowIndexEnd + 1, lastRowNum, -irows);
    }
    if (rowIndexEnd == lastRowNum) {
        // reverse order to delete rows.
        for (int i = rowIndexEnd; i >= rowIndexStart; i--) {
            removeSingleRowInSheet(sheet, rowIndexStart);
        }
    }
}

From source file:packtest.ShiftRows.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    Sheet sheet = wb.createSheet("Sheet1");

    Row row1 = sheet.createRow(1);//from   w ww . j a va2  s  .  co m
    row1.createCell(0).setCellValue(1);

    Row row2 = sheet.createRow(4);
    row2.createCell(1).setCellValue(2);

    Row row3 = sheet.createRow(5);
    row3.createCell(2).setCellValue(3);

    Row row4 = sheet.createRow(6);
    row4.createCell(3).setCellValue(4);

    Row row5 = sheet.createRow(9);
    row5.createCell(4).setCellValue(5);

    // Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5)
    sheet.shiftRows(5, 10, -4);

    FileOutputStream fileOut = new FileOutputStream("shiftRows.xlsx");
    wb.write(fileOut);
    fileOut.close();

}