Examples with CellReference org.apache.poi.ss.util.CellReference used on opensource projects

Example 41 with CellReference

use of org.apache.poi.ss.util.CellReference 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 42 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class XSSFSheet method getTopRow.

/**
     * The top row in the visible view when the sheet is
     * first viewed after opening it in a viewer
     *
     * @return integer indicating the rownum (0 based) of the top row
     */
@Override
public short getTopRow() {
    String cellRef = getSheetTypeSheetView().getTopLeftCell();
    if (cellRef == null) {
        return 0;
    }
    CellReference cellReference = new CellReference(cellRef);
    return (short) cellReference.getRow();
}

Example 43 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class XSSFSheet method getReferenceBuiltInRecord.

private static String getReferenceBuiltInRecord(String sheetName, int startC, int endC, int startR, int endR) {
    // Excel example for built-in title:
    //   'second sheet'!$E:$F,'second sheet'!$2:$3
    CellReference colRef = new CellReference(sheetName, 0, startC, true, true);
    CellReference colRef2 = new CellReference(sheetName, 0, endC, true, true);
    CellReference rowRef = new CellReference(sheetName, startR, 0, true, true);
    CellReference rowRef2 = new CellReference(sheetName, endR, 0, true, true);
    String escapedName = SheetNameFormatter.format(sheetName);
    String c = "";
    String r = "";
    if (startC != -1 || endC != -1) {
        String col1 = colRef.getCellRefParts()[2];
        String col2 = colRef2.getCellRefParts()[2];
        c = escapedName + "!$" + col1 + ":$" + col2;
    }
    if (startR != -1 || endR != -1) {
        String row1 = rowRef.getCellRefParts()[1];
        String row2 = rowRef2.getCellRefParts()[1];
        if (!row1.equals("0") && !row2.equals("0")) {
            r = escapedName + "!$" + row1 + ":$" + row2;
        }
    }
    StringBuilder rng = new StringBuilder();
    rng.append(c);
    if (rng.length() > 0 && r.length() > 0) {
        rng.append(',');
    }
    rng.append(r);
    return rng.toString();
}

Example 44 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class XSSFSheet method setAutoFilter.

@SuppressWarnings("resource")
@Override
public XSSFAutoFilter setAutoFilter(CellRangeAddress range) {
    CTAutoFilter af = worksheet.getAutoFilter();
    if (af == null) {
        af = worksheet.addNewAutoFilter();
    }
    CellRangeAddress norm = new CellRangeAddress(range.getFirstRow(), range.getLastRow(), range.getFirstColumn(), range.getLastColumn());
    String ref = norm.formatAsString();
    af.setRef(ref);
    XSSFWorkbook wb = getWorkbook();
    int sheetIndex = getWorkbook().getSheetIndex(this);
    XSSFName name = wb.getBuiltInName(XSSFName.BUILTIN_FILTER_DB, sheetIndex);
    if (name == null) {
        name = wb.createBuiltInName(XSSFName.BUILTIN_FILTER_DB, sheetIndex);
    }
    name.getCTName().setHidden(true);
    CellReference r1 = new CellReference(getSheetName(), range.getFirstRow(), range.getFirstColumn(), true, true);
    CellReference r2 = new CellReference(null, range.getLastRow(), range.getLastColumn(), true, true);
    String fmla = r1.formatAsString() + ":" + r2.formatAsString();
    name.setRefersToFormula(fmla);
    return new XSSFAutoFilter(this);
}

Example 45 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class XSSFSheet method getLeftCol.

@Override
public short getLeftCol() {
    String cellRef = worksheet.getSheetViews().getSheetViewArray(0).getTopLeftCell();
    if (cellRef == null) {
        return 0;
    }
    CellReference cellReference = new CellReference(cellRef);
    return cellReference.getCol();
}