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

Example 16 with CellAddress

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

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

the class XSSFSheet method removeRow.

/**
     * Remove a row from this sheet.  All cells contained in the row are removed as well
     *
     * @param row  the row to remove.
     */
@Override
public void removeRow(Row row) {
    if (row.getSheet() != this) {
        throw new IllegalArgumentException("Specified row does not belong to this sheet");
    }
    // collect cells into a temporary array to avoid ConcurrentModificationException
    ArrayList<XSSFCell> cellsToDelete = new ArrayList<XSSFCell>();
    for (Cell cell : row) {
        cellsToDelete.add((XSSFCell) cell);
    }
    for (XSSFCell cell : cellsToDelete) {
        row.removeCell(cell);
    }
    // Performance optimization: explicit boxing is slightly faster than auto-unboxing, though may use more memory
    final int rowNum = row.getRowNum();
    // NOSONAR
    final Integer rowNumI = new Integer(rowNum);
    // this is not the physical row number!
    final int idx = _rows.headMap(rowNumI).size();
    _rows.remove(rowNumI);
    worksheet.getSheetData().removeRow(idx);
    // also remove any comment located in that row
    if (sheetComments != null) {
        for (CellAddress ref : getCellComments().keySet()) {
            if (ref.getRow() == rowNum) {
                sheetComments.removeComment(ref);
            }
        }
    }
}

Example 18 with CellAddress

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

the class TestCommentsTable method getCellComment.

@Test
public void getCellComment() throws Exception {
    CommentsTable sheetComments = new CommentsTable();
    CTComments comments = sheetComments.getCTComments();
    CTCommentList commentList = comments.getCommentList();
    // Create 2 comments for A1 and A" cells
    CTComment comment0 = commentList.insertNewComment(0);
    comment0.setRef("A1");
    CTRst ctrst0 = CTRst.Factory.newInstance();
    ctrst0.setT(TEST_A1_TEXT);
    comment0.setText(ctrst0);
    CTComment comment1 = commentList.insertNewComment(0);
    comment1.setRef("A2");
    CTRst ctrst1 = CTRst.Factory.newInstance();
    ctrst1.setT(TEST_A2_TEXT);
    comment1.setText(ctrst1);
    // test finding the right comment for a cell
    assertSame(comment0, sheetComments.getCTComment(new CellAddress("A1")));
    assertSame(comment1, sheetComments.getCTComment(new CellAddress("A2")));
    assertNull(sheetComments.getCTComment(new CellAddress("A3")));
}

Example 19 with CellAddress

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

the class TestXSSFBSheetHyperlinkManager method testBasic.

@Test
public void testBasic() throws Exception {
    OPCPackage pkg = OPCPackage.open(_ssTests.openResourceAsStream("hyperlink.xlsb"));
    XSSFBReader reader = new XSSFBReader(pkg);
    XSSFReader.SheetIterator it = (XSSFReader.SheetIterator) reader.getSheetsData();
    it.next();
    XSSFBHyperlinksTable manager = new XSSFBHyperlinksTable(it.getSheetPart());
    List<XSSFHyperlinkRecord> records = manager.getHyperLinks().get(new CellAddress(0, 0));
    assertNotNull(records);
    assertEquals(1, records.size());
    XSSFHyperlinkRecord record = records.get(0);
    assertEquals("http://tika.apache.org/", record.getLocation());
    assertEquals("rId2", record.getRelId());
}

Example 20 with CellAddress

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

the class TestXSSFSheet method testRemoveRowWithCommentAndGapAbove.

// bug 59687:  XSSFSheet.RemoveRow doesn't handle row gaps properly when removing row comments
@Test
public void testRemoveRowWithCommentAndGapAbove() throws IOException {
    final Workbook wb = _testDataProvider.openSampleWorkbook("59687.xlsx");
    final Sheet sheet = wb.getSheetAt(0);
    // comment exists
    CellAddress commentCellAddress = new CellAddress("A4");
    assertNotNull(sheet.getCellComment(commentCellAddress));
    assertEquals("Wrong starting # of comments", 1, sheet.getCellComments().size());
    sheet.removeRow(sheet.getRow(commentCellAddress.getRow()));
    assertEquals("There should not be any comments left!", 0, sheet.getCellComments().size());
}