Examples with XSSFRowShifter org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter used on opensource projects

Example 1 with XSSFRowShifter

use of org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter 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 XSSFRowShifter

use of org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter 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());
        }
    }
}