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);
}
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();
}
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();
}
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();
}
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();
}
Aggregations