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

Example 31 with CellAddress

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

the class TestCellAddress method testCompareTo.

@Test
public void testCompareTo() {
    final CellAddress A1 = new CellAddress(0, 0);
    final CellAddress A2 = new CellAddress(1, 0);
    final CellAddress B1 = new CellAddress(0, 1);
    final CellAddress B2 = new CellAddress(1, 1);
    assertEquals(0, A1.compareTo(A1));
    assertEquals(-1, A1.compareTo(B1));
    assertEquals(-1, A1.compareTo(A2));
    assertEquals(-1, A1.compareTo(B2));
    assertEquals(1, B1.compareTo(A1));
    assertEquals(0, B1.compareTo(B1));
    assertEquals(-1, B1.compareTo(A2));
    assertEquals(-1, B1.compareTo(B2));
    assertEquals(1, A2.compareTo(A1));
    assertEquals(1, A2.compareTo(B1));
    assertEquals(0, A2.compareTo(A2));
    assertEquals(-1, A2.compareTo(B2));
    assertEquals(1, B2.compareTo(A1));
    assertEquals(1, B2.compareTo(B1));
    assertEquals(1, B2.compareTo(A2));
    assertEquals(0, B2.compareTo(B2));
    CellAddress[] sorted = { A1, B1, A2, B2 };
    CellAddress[] unsorted = { B1, B2, A1, A2 };
    assumeTrue(!sorted.equals(unsorted));
    Arrays.sort(unsorted);
    assertArrayEquals(sorted, unsorted);
}

Example 32 with CellAddress

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

the class BaseTestSheetShiftRows method testBug46742_52903_shiftHyperlinks.

/**
     * Unified test for:
     * bug 46742: XSSFSheet.shiftRows should shift hyperlinks
     * bug 52903: HSSFSheet.shiftRows should shift hyperlinks
     *
     * @throws IOException
     */
@Test
public void testBug46742_52903_shiftHyperlinks() throws IOException {
    Workbook wb = _testDataProvider.createWorkbook();
    Sheet sheet = wb.createSheet("test");
    Row row = sheet.createRow(0);
    // How to create hyperlinks
    // https://poi.apache.org/spreadsheet/quick-guide.html#Hyperlinks
    CreationHelper helper = wb.getCreationHelper();
    CellStyle hlinkStyle = wb.createCellStyle();
    Font hlinkFont = wb.createFont();
    hlinkFont.setUnderline(Font.U_SINGLE);
    hlinkFont.setColor(IndexedColors.BLUE.getIndex());
    hlinkStyle.setFont(hlinkFont);
    // 3D relative document link
    // CellAddress=A1, shifted to A4
    Cell cell = row.createCell(0);
    cell.setCellStyle(hlinkStyle);
    createHyperlink(helper, cell, HyperlinkType.DOCUMENT, "test!E1");
    // URL
    cell = row.createCell(1);
    // CellAddress=B1, shifted to B4
    cell.setCellStyle(hlinkStyle);
    createHyperlink(helper, cell, HyperlinkType.URL, "http://poi.apache.org/");
    // row0 will be shifted on top of row1, so this URL should be removed from the workbook
    Row overwrittenRow = sheet.createRow(3);
    cell = overwrittenRow.createCell(2);
    // CellAddress=C4, will be overwritten (deleted)
    cell.setCellStyle(hlinkStyle);
    createHyperlink(helper, cell, HyperlinkType.EMAIL, "mailto:poi@apache.org");
    // hyperlinks on this row are unaffected by the row shifting, so the hyperlinks should not move
    Row unaffectedRow = sheet.createRow(20);
    cell = unaffectedRow.createCell(3);
    // CellAddress=D21, will be unaffected
    cell.setCellStyle(hlinkStyle);
    createHyperlink(helper, cell, HyperlinkType.FILE, "54524.xlsx");
    cell = wb.createSheet("other").createRow(0).createCell(0);
    // CellAddress=Other!A1, will be unaffected
    cell.setCellStyle(hlinkStyle);
    createHyperlink(helper, cell, HyperlinkType.URL, "http://apache.org/");
    int startRow = 0;
    int endRow = 0;
    int n = 3;
    sheet.shiftRows(startRow, endRow, n);
    Workbook read = _testDataProvider.writeOutAndReadBack(wb);
    wb.close();
    Sheet sh = read.getSheet("test");
    Row shiftedRow = sh.getRow(3);
    // document link anchored on a shifted cell should be moved
    // Note that hyperlinks do not track what they point to, so this hyperlink should still refer to test!E1
    verifyHyperlink(shiftedRow.getCell(0), HyperlinkType.DOCUMENT, "test!E1");
    // URL, EMAIL, and FILE links anchored on a shifted cell should be moved
    verifyHyperlink(shiftedRow.getCell(1), HyperlinkType.URL, "http://poi.apache.org/");
    // Make sure hyperlinks were moved and not copied
    assertNull("Document hyperlink should be moved, not copied", sh.getHyperlink(0, 0));
    assertNull("URL hyperlink should be moved, not copied", sh.getHyperlink(0, 1));
    // Make sure hyperlink in overwritten row is deleted
    assertEquals(3, sh.getHyperlinkList().size());
    CellAddress unexpectedLinkAddress = new CellAddress("C4");
    for (Hyperlink link : sh.getHyperlinkList()) {
        final CellAddress linkAddress = new CellAddress(link.getFirstRow(), link.getFirstColumn());
        if (linkAddress.equals(unexpectedLinkAddress)) {
            fail("Row 4, including the hyperlink at C4, should have " + "been deleted when Row 1 was shifted on top of it.");
        }
    }
    // Make sure unaffected rows are not shifted
    Cell unaffectedCell = sh.getRow(20).getCell(3);
    assertTrue(cellHasHyperlink(unaffectedCell));
    verifyHyperlink(unaffectedCell, HyperlinkType.FILE, "54524.xlsx");
    // Make sure cells on other sheets are not affected
    unaffectedCell = read.getSheet("other").getRow(0).getCell(0);
    assertTrue(cellHasHyperlink(unaffectedCell));
    verifyHyperlink(unaffectedCell, HyperlinkType.URL, "http://apache.org/");
    read.close();
}

Example 33 with CellAddress

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

the class BaseTestSheet method getHyperlink.

@Test
public void getHyperlink() throws IOException {
    Workbook workbook = _testDataProvider.createWorkbook();
    Hyperlink hyperlink = workbook.getCreationHelper().createHyperlink(HyperlinkType.URL);
    hyperlink.setAddress("https://poi.apache.org/");
    Sheet sheet = workbook.createSheet();
    Cell cell = sheet.createRow(5).createCell(1);
    assertEquals("list size before add", 0, sheet.getHyperlinkList().size());
    cell.setHyperlink(hyperlink);
    assertEquals("list size after add", 1, sheet.getHyperlinkList().size());
    assertEquals("list", hyperlink, sheet.getHyperlinkList().get(0));
    CellAddress B6 = new CellAddress(5, 1);
    assertEquals("row, col", hyperlink, sheet.getHyperlink(5, 1));
    assertEquals("addr", hyperlink, sheet.getHyperlink(B6));
    assertEquals("no hyperlink at A1", null, sheet.getHyperlink(CellAddress.A1));
    workbook.close();
}

Example 34 with CellAddress

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

the class BaseTestSheet method getCellComments.

@Test
public void getCellComments() throws IOException {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet("TEST");
    // a sheet with no cell comments should return an empty map (not null or raise NPE).
    assertEquals(Collections.emptyMap(), sheet.getCellComments());
    Drawing<?> dg = sheet.createDrawingPatriarch();
    ClientAnchor anchor = workbook.getCreationHelper().createClientAnchor();
    int nRows = 5;
    int nCols = 6;
    for (int r = 0; r < nRows; r++) {
        sheet.createRow(r);
        // Create columns in reverse order
        for (int c = nCols - 1; c >= 0; c--) {
            // When the comment box is visible, have it show in a 1x3 space
            anchor.setCol1(c);
            anchor.setCol2(c);
            anchor.setRow1(r);
            anchor.setRow2(r);
            // Create the comment and set the text-author
            Comment comment = dg.createCellComment(anchor);
            Cell cell = sheet.getRow(r).createCell(c);
            comment.setAuthor("Author " + r);
            RichTextString text = workbook.getCreationHelper().createRichTextString("Test comment at row=" + r + ", column=" + c);
            comment.setString(text);
            // Assign the comment to the cell
            cell.setCellComment(comment);
        }
    }
    Workbook wb = _testDataProvider.writeOutAndReadBack(workbook);
    Sheet sh = wb.getSheet("TEST");
    Map<CellAddress, ? extends Comment> cellComments = sh.getCellComments();
    assertEquals(nRows * nCols, cellComments.size());
    for (Entry<CellAddress, ? extends Comment> e : cellComments.entrySet()) {
        CellAddress ref = e.getKey();
        Comment aComment = e.getValue();
        assertEquals("Author " + ref.getRow(), aComment.getAuthor());
        String text = "Test comment at row=" + ref.getRow() + ", column=" + ref.getColumn();
        assertEquals(text, aComment.getString().getString());
    }
    workbook.close();
    wb.close();
}

Example 35 with CellAddress

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

the class BaseTestCellComment method find.

@Test
public final void find() throws IOException {
    Workbook book = _testDataProvider.createWorkbook();
    Sheet sheet = book.createSheet();
    assertNull(sheet.getCellComment(new CellAddress(0, 0)));
    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);
    assertNull(sheet.getCellComment(new CellAddress(0, 0)));
    assertNull(cell.getCellComment());
    book.close();
}