Examples with FormulaShifter org.apache.poi.ss.formula.FormulaShifter used on opensource projects

Example 1 with FormulaShifter

use of org.apache.poi.ss.formula.FormulaShifter in project poi by apache.

the class XSSFSheet 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). All merged regions that are
     * completely overlaid by shifting will be deleted.
     * <p>
     * @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
     */
@Override
public void shiftRows(int startRow, int endRow, final int n, boolean copyRowHeight, boolean resetOriginalRowHeight) {
    XSSFVMLDrawing vml = getVMLDrawing(false);
    // first remove all rows which will be overwritten
    for (Iterator<Row> it = rowIterator(); it.hasNext(); ) {
        XSSFRow row = (XSSFRow) it.next();
        int rownum = row.getRowNum();
        // check if we should remove this row as it will be overwritten by the data later
        if (shouldRemoveRow(startRow, endRow, n, rownum)) {
            // remove row from worksheet.getSheetData row array
            // Performance optimization: explicit boxing is slightly faster than auto-unboxing, though may use more memory
            // NOSONAR
            final Integer rownumI = new Integer(row.getRowNum());
            int idx = _rows.headMap(rownumI).size();
            worksheet.getSheetData().removeRow(idx);
            // remove row from _rows
            it.remove();
            // also remove any comments associated with this row
            if (sheetComments != null) {
                CTCommentList lst = sheetComments.getCTComments().getCommentList();
                for (CTComment comment : lst.getCommentArray()) {
                    String strRef = comment.getRef();
                    CellAddress ref = new CellAddress(strRef);
                    // is this comment part of the current row?
                    if (ref.getRow() == rownum) {
                        sheetComments.removeComment(ref);
                        vml.removeCommentShape(ref.getRow(), ref.getColumn());
                    }
                }
            }
            // also remove any hyperlinks associated with this row
            if (hyperlinks != null) {
                for (XSSFHyperlink link : new ArrayList<XSSFHyperlink>(hyperlinks)) {
                    CellReference ref = new CellReference(link.getCellRef());
                    if (ref.getRow() == rownum) {
                        hyperlinks.remove(link);
                    }
                }
            }
        }
    }
    // then do the actual moving and also adjust comments/rowHeight
    // we need to sort it in a way so the shifting does not mess up the structures, 
    // i.e. when shifting down, start from down and go up, when shifting up, vice-versa
    SortedMap<XSSFComment, Integer> commentsToShift = new TreeMap<XSSFComment, Integer>(new Comparator<XSSFComment>() {

        @Override
        public int compare(XSSFComment o1, XSSFComment o2) {
            int row1 = o1.getRow();
            int row2 = o2.getRow();
            if (row1 == row2) {
                // get multiple comments per row into the map
                return o1.hashCode() - o2.hashCode();
            }
            // when shifting down, sort higher row-values first
            if (n > 0) {
                return row1 < row2 ? 1 : -1;
            } else {
                // sort lower-row values first when shifting up
                return row1 > row2 ? 1 : -1;
            }
        }
    });
    for (Iterator<Row> it = rowIterator(); it.hasNext(); ) {
        XSSFRow row = (XSSFRow) it.next();
        int rownum = row.getRowNum();
        if (sheetComments != null) {
            // calculate the new rownum
            int newrownum = shiftedRowNum(startRow, endRow, n, rownum);
            // is there a change necessary for the current row?
            if (newrownum != rownum) {
                CTCommentList lst = sheetComments.getCTComments().getCommentList();
                for (CTComment comment : lst.getCommentArray()) {
                    String oldRef = comment.getRef();
                    CellReference ref = new CellReference(oldRef);
                    // is this comment part of the current row?
                    if (ref.getRow() == rownum) {
                        XSSFComment xssfComment = new XSSFComment(sheetComments, comment, vml == null ? null : vml.findCommentShape(rownum, ref.getCol()));
                        // we should not perform the shifting right here as we would then find
                        // already shifted comments and would shift them again...
                        commentsToShift.put(xssfComment, newrownum);
                    }
                }
            }
        }
        if (rownum < startRow || rownum > endRow) {
            continue;
        }
        if (!copyRowHeight) {
            row.setHeight((short) -1);
        }
        row.shift(n);
    }
    // i.e. from down to up if shifting down, vice-versa otherwise
    for (Map.Entry<XSSFComment, Integer> entry : commentsToShift.entrySet()) {
        entry.getKey().setRow(entry.getValue());
    }
    XSSFRowShifter rowShifter = new XSSFRowShifter(this);
    int sheetIndex = getWorkbook().getSheetIndex(this);
    String sheetName = getWorkbook().getSheetName(sheetIndex);
    FormulaShifter shifter = FormulaShifter.createForRowShift(sheetIndex, sheetName, startRow, endRow, n, SpreadsheetVersion.EXCEL2007);
    rowShifter.updateNamedRanges(shifter);
    rowShifter.updateFormulas(shifter);
    rowShifter.shiftMergedRegions(startRow, endRow, n);
    rowShifter.updateConditionalFormatting(shifter);
    rowShifter.updateHyperlinks(shifter);
    //rebuild the _rows map
    Map<Integer, XSSFRow> map = new HashMap<Integer, XSSFRow>();
    for (XSSFRow r : _rows.values()) {
        // Performance optimization: explicit boxing is slightly faster than auto-unboxing, though may use more memory
        // NOSONAR
        final Integer rownumI = new Integer(r.getRowNum());
        map.put(rownumI, r);
    }
    _rows.clear();
    _rows.putAll(map);
}

Example 2 with FormulaShifter

use of org.apache.poi.ss.formula.FormulaShifter 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);
}

Example 3 with FormulaShifter

use of org.apache.poi.ss.formula.FormulaShifter in project poi by apache.

the class HSSFWorkbook method setSheetOrder.

/**
     * sets the order of appearance for a given sheet.
     *
     * @param sheetname the name of the sheet to reorder
     * @param pos the position that we want to insert the sheet into (0 based)
     */
@Override
public void setSheetOrder(String sheetname, int pos) {
    int oldSheetIndex = getSheetIndex(sheetname);
    _sheets.add(pos, _sheets.remove(oldSheetIndex));
    workbook.setSheetOrder(sheetname, pos);
    FormulaShifter shifter = FormulaShifter.createForSheetShift(oldSheetIndex, pos);
    for (HSSFSheet sheet : _sheets) {
        sheet.getSheet().updateFormulasAfterCellShift(shifter, /* not used */
        -1);
    }
    workbook.updateNamesAfterCellShift(shifter);
    updateNamedRangesAfterSheetReorder(oldSheetIndex, pos);
    updateActiveSheetAfterSheetReorder(oldSheetIndex, pos);
}

Example 4 with FormulaShifter

use of org.apache.poi.ss.formula.FormulaShifter in project poi by apache.

the class XSSFRow method copyRowFrom.

/**
     * Copy the cells from srcRow to this row
     * If this row is not a blank row, this will merge the two rows, overwriting
     * the cells in this row with the cells in srcRow
     * If srcRow is null, overwrite cells in destination row with blank values, styles, etc per cell copy policy
     * srcRow may be from a different sheet in the same workbook
     * @param srcRow the rows to copy from
     * @param policy the policy to determine what gets copied
     */
@Beta
public void copyRowFrom(Row srcRow, CellCopyPolicy policy) {
    if (srcRow == null) {
        // srcRow is blank. Overwrite cells with blank values, blank styles, etc per cell copy policy
        for (Cell destCell : this) {
            final XSSFCell srcCell = null;
            // FIXME: remove type casting when copyCellFrom(Cell, CellCopyPolicy) is added to Cell interface
            ((XSSFCell) destCell).copyCellFrom(srcCell, policy);
        }
        if (policy.isCopyMergedRegions()) {
            // Remove MergedRegions in dest row
            final int destRowNum = getRowNum();
            int index = 0;
            final Set<Integer> indices = new HashSet<Integer>();
            for (CellRangeAddress destRegion : getSheet().getMergedRegions()) {
                if (destRowNum == destRegion.getFirstRow() && destRowNum == destRegion.getLastRow()) {
                    indices.add(index);
                }
                index++;
            }
            getSheet().removeMergedRegions(indices);
        }
        if (policy.isCopyRowHeight()) {
            // clear row height
            setHeight((short) -1);
        }
    } else {
        for (final Cell c : srcRow) {
            final XSSFCell srcCell = (XSSFCell) c;
            final XSSFCell destCell = createCell(srcCell.getColumnIndex(), srcCell.getCellTypeEnum());
            destCell.copyCellFrom(srcCell, policy);
        }
        final XSSFRowShifter rowShifter = new XSSFRowShifter(_sheet);
        final int sheetIndex = _sheet.getWorkbook().getSheetIndex(_sheet);
        final String sheetName = _sheet.getWorkbook().getSheetName(sheetIndex);
        final int srcRowNum = srcRow.getRowNum();
        final int destRowNum = getRowNum();
        final int rowDifference = destRowNum - srcRowNum;
        final FormulaShifter shifter = FormulaShifter.createForRowCopy(sheetIndex, sheetName, srcRowNum, srcRowNum, rowDifference, SpreadsheetVersion.EXCEL2007);
        rowShifter.updateRowFormulas(this, shifter);
        // FIXME: is this something that rowShifter could be doing?
        if (policy.isCopyMergedRegions()) {
            for (CellRangeAddress srcRegion : srcRow.getSheet().getMergedRegions()) {
                if (srcRowNum == srcRegion.getFirstRow() && srcRowNum == srcRegion.getLastRow()) {
                    CellRangeAddress destRegion = srcRegion.copy();
                    destRegion.setFirstRow(destRowNum);
                    destRegion.setLastRow(destRowNum);
                    getSheet().addMergedRegion(destRegion);
                }
            }
        }
        if (policy.isCopyRowHeight()) {
            setHeight(srcRow.getHeight());
        }
    }
}

Example 5 with FormulaShifter

use of org.apache.poi.ss.formula.FormulaShifter in project poi by apache.

the class TestSheet method testShiftFormulasAddCondFormat_bug46547.

/**
	 * Prior to the fix for bug 46547, shifting formulas would have the side-effect
	 * of creating a {@link ConditionalFormattingTable}.  There was no impairment to
	 * functionality since empty record aggregates are equivalent to missing record
	 * aggregates. However, since this unnecessary creation helped expose bug 46547b,
	 * and since there is a slight performance hit the fix was made to avoid it.
	 */
@Test
public void testShiftFormulasAddCondFormat_bug46547() {
    // Create a sheet with data validity (similar to bugzilla attachment id=23131).
    InternalSheet sheet = InternalSheet.createSheet();
    List<RecordBase> sheetRecs = sheet.getRecords();
    assertEquals(23, sheetRecs.size());
    FormulaShifter shifter = FormulaShifter.createForRowShift(0, "", 0, 0, 1, SpreadsheetVersion.EXCEL97);
    sheet.updateFormulasAfterCellShift(shifter, 0);
    if (sheetRecs.size() == 24 && sheetRecs.get(22) instanceof ConditionalFormattingTable) {
        throw new AssertionFailedError("Identified bug 46547a");
    }
    assertEquals(23, sheetRecs.size());
}