Examples with HSSFRowShifter org.apache.poi.hssf.usermodel.helpers.HSSFRowShifter used on opensource projects

Example 1 with HSSFRowShifter

use of org.apache.poi.hssf.usermodel.helpers.HSSFRowShifter in project poi by apache.

the class HSSFSheet method shiftMerged.

/**
     * Shifts, grows, or shrinks the merged regions due to a row shift
     * 
     * @param startRow the start-index of the rows to shift, zero-based
     * @param endRow the end-index of the rows to shift, zero-based
     * @param n how far to shift, negative to shift up
     * @param isRow unused, kept for backwards compatibility
     * @deprecated POI 3.15 beta 2. Use {@link HSSFRowShifter#shiftMergedRegions(int, int, int)}.
     */
protected void shiftMerged(int startRow, int endRow, int n, boolean isRow) {
    RowShifter rowShifter = new HSSFRowShifter(this);
    rowShifter.shiftMergedRegions(startRow, endRow, n);
}

Example 2 with HSSFRowShifter

use of org.apache.poi.hssf.usermodel.helpers.HSSFRowShifter in project poi by apache.

the class HSSFSheet method shiftRows.

/**
     * Shifts rows between startRow and endRow n number of rows.
     * If you use a negative number, it will shift rows up.
     * Code ensures that rows don't wrap around<p>
     * 
     * Additionally shifts merged regions that are completely defined in these
     * rows (ie. merged 2 cells on a row to be shifted).<p>
     * 
     * TODO Might want to add bounds checking here
     *
     * @param startRow               the row to start shifting
     * @param endRow                 the row to end shifting
     * @param n                      the number of rows to shift
     * @param copyRowHeight          whether to copy the row height during the shift
     * @param resetOriginalRowHeight whether to set the original row's height to the default
     * @param moveComments           whether to move comments at the same time as the cells they are attached to
     */
public void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight, boolean moveComments) {
    int s, inc;
    if (endRow < startRow) {
        throw new IllegalArgumentException("startRow must be less than or equal to endRow. To shift rows up, use n<0.");
    }
    if (n < 0) {
        s = startRow;
        inc = 1;
    } else if (n > 0) {
        s = endRow;
        inc = -1;
    } else {
        // Nothing to do
        return;
    }
    final RowShifter rowShifter = new HSSFRowShifter(this);
    // bounds or deleting them
    if (moveComments) {
        final HSSFPatriarch patriarch = createDrawingPatriarch();
        for (final HSSFShape shape : patriarch.getChildren()) {
            if (!(shape instanceof HSSFComment)) {
                continue;
            }
            final HSSFComment comment = (HSSFComment) shape;
            final int r = comment.getRow();
            if (startRow <= r && r <= endRow) {
                comment.setRow(clip(r + n));
            }
        }
    }
    // Shift Merged Regions
    rowShifter.shiftMergedRegions(startRow, endRow, n);
    // Shift Row Breaks
    _sheet.getPageSettings().shiftRowBreaks(startRow, endRow, n);
    // Delete overwritten hyperlinks
    final int firstOverwrittenRow = startRow + n;
    final int lastOverwrittenRow = endRow + n;
    for (HSSFHyperlink link : getHyperlinkList()) {
        // If hyperlink is fully contained in the rows that will be overwritten, delete the hyperlink
        final int firstRow = link.getFirstRow();
        final int lastRow = link.getLastRow();
        if (firstOverwrittenRow <= firstRow && firstRow <= lastOverwrittenRow && lastOverwrittenRow <= lastRow && lastRow <= lastOverwrittenRow) {
            removeHyperlink(link);
        }
    }
    for (int rowNum = s; rowNum >= startRow && rowNum <= endRow && rowNum >= 0 && rowNum < 65536; rowNum += inc) {
        HSSFRow row = getRow(rowNum);
        // if the row contains cells included in a multi-cell array formula
        if (row != null)
            notifyRowShifting(row);
        HSSFRow row2Replace = getRow(rowNum + n);
        if (row2Replace == null)
            row2Replace = createRow(rowNum + n);
        // Remove all the old cells from the row we'll
        //  be writing to, before we start overwriting
        //  any cells. This avoids issues with cells
        //  changing type, and records not being correctly
        //  overwritten
        row2Replace.removeAllCells();
        // Nothing to do for this row
        if (row == null)
            continue;
        // Fix up row heights if required
        if (copyRowHeight) {
            row2Replace.setHeight(row.getHeight());
        }
        if (resetOriginalRowHeight) {
            row.setHeight((short) 0xff);
        }
        //  the destination row
        for (Iterator<Cell> cells = row.cellIterator(); cells.hasNext(); ) {
            HSSFCell cell = (HSSFCell) cells.next();
            HSSFHyperlink link = cell.getHyperlink();
            row.removeCell(cell);
            CellValueRecordInterface cellRecord = cell.getCellValueRecord();
            cellRecord.setRow(rowNum + n);
            row2Replace.createCellFromRecord(cellRecord);
            _sheet.addValueRecord(rowNum + n, cellRecord);
            if (link != null) {
                link.setFirstRow(link.getFirstRow() + n);
                link.setLastRow(link.getLastRow() + n);
            }
        }
        // Now zap all the cells in the source row
        row.removeAllCells();
    }
    // Re-compute the first and last rows of the sheet as needed
    if (n > 0) {
        // Rows are moving down
        if (startRow == _firstrow) {
            // Need to walk forward to find the first non-blank row
            _firstrow = Math.max(startRow + n, 0);
            for (int i = startRow + 1; i < startRow + n; i++) {
                if (getRow(i) != null) {
                    _firstrow = i;
                    break;
                }
            }
        }
        if (endRow + n > _lastrow) {
            _lastrow = Math.min(endRow + n, SpreadsheetVersion.EXCEL97.getLastRowIndex());
        }
    } else {
        // Rows are moving up
        if (startRow + n < _firstrow) {
            _firstrow = Math.max(startRow + n, 0);
        }
        if (endRow == _lastrow) {
            // Need to walk backward to find the last non-blank row
            // NOTE: n is always negative here
            _lastrow = Math.min(endRow + n, SpreadsheetVersion.EXCEL97.getLastRowIndex());
            for (int i = endRow - 1; i > endRow + n; i--) {
                if (getRow(i) != null) {
                    _lastrow = i;
                    break;
                }
            }
        }
    }
    // Update any formulas on this sheet that point to
    //  rows which have been moved
    int sheetIndex = _workbook.getSheetIndex(this);
    String sheetName = _workbook.getSheetName(sheetIndex);
    short externSheetIndex = _book.checkExternSheet(sheetIndex);
    FormulaShifter shifter = FormulaShifter.createForRowShift(externSheetIndex, sheetName, startRow, endRow, n, SpreadsheetVersion.EXCEL97);
    _sheet.updateFormulasAfterCellShift(shifter, externSheetIndex);
    int nSheets = _workbook.getNumberOfSheets();
    for (int i = 0; i < nSheets; i++) {
        InternalSheet otherSheet = _workbook.getSheetAt(i).getSheet();
        if (otherSheet == this._sheet) {
            continue;
        }
        short otherExtSheetIx = _book.checkExternSheet(i);
        otherSheet.updateFormulasAfterCellShift(shifter, otherExtSheetIx);
    }
    _workbook.getWorkbook().updateNamesAfterCellShift(shifter);
}