Examples with Hyperlink org.apache.poi.ss.usermodel.Hyperlink used on opensource projects

Example 1 with Hyperlink

use of org.apache.poi.ss.usermodel.Hyperlink in project poi by apache.

the class TestXSSFHyperlink method testLoadSave.

@Test
public void testLoadSave() {
    XSSFWorkbook workbook = XSSFTestDataSamples.openSampleWorkbook("WithMoreVariousData.xlsx");
    CreationHelper createHelper = workbook.getCreationHelper();
    assertEquals(3, workbook.getNumberOfSheets());
    XSSFSheet sheet = workbook.getSheetAt(0);
    // Check hyperlinks
    assertEquals(4, sheet.getNumHyperlinks());
    doTestHyperlinkContents(sheet);
    // Write out, and check
    // Load up again, check all links still there
    XSSFWorkbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(workbook);
    assertEquals(3, wb2.getNumberOfSheets());
    assertNotNull(wb2.getSheetAt(0));
    assertNotNull(wb2.getSheetAt(1));
    assertNotNull(wb2.getSheetAt(2));
    sheet = wb2.getSheetAt(0);
    // Check hyperlinks again
    assertEquals(4, sheet.getNumHyperlinks());
    doTestHyperlinkContents(sheet);
    // Add one more, and re-check
    Row r17 = sheet.createRow(17);
    Cell r17c = r17.createCell(2);
    Hyperlink hyperlink = createHelper.createHyperlink(HyperlinkType.URL);
    hyperlink.setAddress("http://poi.apache.org/spreadsheet/");
    hyperlink.setLabel("POI SS Link");
    r17c.setHyperlink(hyperlink);
    assertEquals(5, sheet.getNumHyperlinks());
    doTestHyperlinkContents(sheet);
    assertEquals(HyperlinkType.URL, sheet.getRow(17).getCell(2).getHyperlink().getTypeEnum());
    assertEquals("POI SS Link", sheet.getRow(17).getCell(2).getHyperlink().getLabel());
    assertEquals("http://poi.apache.org/spreadsheet/", sheet.getRow(17).getCell(2).getHyperlink().getAddress());
    // Save and re-load once more
    XSSFWorkbook wb3 = XSSFTestDataSamples.writeOutAndReadBack(wb2);
    assertEquals(3, wb3.getNumberOfSheets());
    assertNotNull(wb3.getSheetAt(0));
    assertNotNull(wb3.getSheetAt(1));
    assertNotNull(wb3.getSheetAt(2));
    sheet = wb3.getSheetAt(0);
    assertEquals(5, sheet.getNumHyperlinks());
    doTestHyperlinkContents(sheet);
    assertEquals(HyperlinkType.URL, sheet.getRow(17).getCell(2).getHyperlink().getTypeEnum());
    assertEquals("POI SS Link", sheet.getRow(17).getCell(2).getHyperlink().getLabel());
    assertEquals("http://poi.apache.org/spreadsheet/", sheet.getRow(17).getCell(2).getHyperlink().getAddress());
}

Example 2 with Hyperlink

use of org.apache.poi.ss.usermodel.Hyperlink in project poi by apache.

the class XSSFRowShifter method updateHyperlinks.

/**
     * Shift the Hyperlink anchors (not the hyperlink text, even if the hyperlink
     * is of type LINK_DOCUMENT and refers to a cell that was shifted). Hyperlinks
     * do not track the content they point to.
     *
     * @param shifter
     */
public void updateHyperlinks(FormulaShifter shifter) {
    int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet);
    List<? extends Hyperlink> hyperlinkList = sheet.getHyperlinkList();
    for (Hyperlink hyperlink : hyperlinkList) {
        XSSFHyperlink xhyperlink = (XSSFHyperlink) hyperlink;
        String cellRef = xhyperlink.getCellRef();
        CellRangeAddress cra = CellRangeAddress.valueOf(cellRef);
        CellRangeAddress shiftedRange = shiftRange(shifter, cra, sheetIndex);
        if (shiftedRange != null && shiftedRange != cra) {
            // shiftedRange should not be null. If shiftedRange is null, that means
            // that a hyperlink wasn't deleted at the beginning of shiftRows when
            // identifying rows that should be removed because they will be overwritten
            xhyperlink.setCellReference(shiftedRange.formatAsString());
        }
    }
}

Example 3 with Hyperlink

use of org.apache.poi.ss.usermodel.Hyperlink in project poi by apache.

the class TestXSSFCell method testCopyCellFrom_CellCopyPolicy_mergeHyperlink.

@Test
public final void testCopyCellFrom_CellCopyPolicy_mergeHyperlink() throws IOException {
    setUp_testCopyCellFrom_CellCopyPolicy();
    final Workbook wb = srcCell.getSheet().getWorkbook();
    final CreationHelper createHelper = wb.getCreationHelper();
    srcCell.setCellValue("URL LINK");
    Hyperlink link = createHelper.createHyperlink(HyperlinkType.URL);
    link.setAddress("http://poi.apache.org/");
    destCell.setHyperlink(link);
    // Set link cell style (optional)
    CellStyle hlinkStyle = wb.createCellStyle();
    Font hlinkFont = wb.createFont();
    hlinkFont.setUnderline(Font.U_SINGLE);
    hlinkFont.setColor(IndexedColors.BLUE.getIndex());
    hlinkStyle.setFont(hlinkFont);
    destCell.setCellStyle(hlinkStyle);
    // Pre-condition assumptions. This test is broken if either of these fail.
    assertSame("unit test assumes srcCell and destCell are on the same sheet", srcCell.getSheet(), destCell.getSheet());
    assertNull(srcCell.getHyperlink());
    // Merge hyperlink - since srcCell doesn't have a hyperlink, destCell's hyperlink is not overwritten (cleared).
    final CellCopyPolicy policy = new CellCopyPolicy.Builder().mergeHyperlink(true).copyHyperlink(false).build();
    destCell.copyCellFrom(srcCell, policy);
    assertNull(srcCell.getHyperlink());
    assertNotNull(destCell.getHyperlink());
    assertSame(link, destCell.getHyperlink());
    List<XSSFHyperlink> links;
    links = srcCell.getSheet().getHyperlinkList();
    assertEquals("number of hyperlinks on sheet", 1, links.size());
    assertEquals("source hyperlink", new CellReference(destCell).formatAsString(), links.get(0).getCellRef());
    // Merge destCell's hyperlink to srcCell. Since destCell does have a hyperlink, this should copy destCell's hyperlink to srcCell.
    srcCell.copyCellFrom(destCell, policy);
    assertNotNull(srcCell.getHyperlink());
    assertNotNull(destCell.getHyperlink());
    links = srcCell.getSheet().getHyperlinkList();
    assertEquals("number of hyperlinks on sheet", 2, links.size());
    assertEquals("dest hyperlink", new CellReference(destCell).formatAsString(), links.get(0).getCellRef());
    assertEquals("source hyperlink", new CellReference(srcCell).formatAsString(), links.get(1).getCellRef());
    wb.close();
}

Example 4 with Hyperlink

use of org.apache.poi.ss.usermodel.Hyperlink in project ocvn by devgateway.

the class AbstractExcelSheet method writeCellLink.

/**
     * Creates a cell that is a link to another sheet in the document {@link Hyperlink#LINK_DOCUMENT}.
     *
     * @param value
     * @param row
     * @param column
     * @param sheetName
     * @param rowNumber
     */
public void writeCellLink(final Object value, final Row row, final int column, final String sheetName, final int rowNumber) {
    this.writeCell(value, row, column);
    Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
    // always point to first column A in excel file
    link.setAddress("'" + sheetName + "'!A" + rowNumber);
    cell.setHyperlink(link);
    cell.setCellStyle(linkStyleCell);
}

Example 5 with Hyperlink

use of org.apache.poi.ss.usermodel.Hyperlink in project poi by apache.

the class HyperlinkExample method main.

public static void main(String[] args) throws IOException {
    //or new HSSFWorkbook();
    Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    //cell style for hyperlinks
    //by default hyperlinks are blue and underlined
    CellStyle hlink_style = wb.createCellStyle();
    Font hlink_font = wb.createFont();
    hlink_font.setUnderline(Font.U_SINGLE);
    hlink_font.setColor(IndexedColors.BLUE.getIndex());
    hlink_style.setFont(hlink_font);
    Cell cell;
    Sheet sheet = wb.createSheet("Hyperlinks");
    //URL
    cell = sheet.createRow(0).createCell(0);
    cell.setCellValue("URL Link");
    Hyperlink link = createHelper.createHyperlink(HyperlinkType.URL);
    link.setAddress("http://poi.apache.org/");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);
    //link to a file in the current directory
    cell = sheet.createRow(1).createCell(0);
    cell.setCellValue("File Link");
    link = createHelper.createHyperlink(HyperlinkType.FILE);
    link.setAddress("link1.xls");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);
    //e-mail link
    cell = sheet.createRow(2).createCell(0);
    cell.setCellValue("Email Link");
    link = createHelper.createHyperlink(HyperlinkType.EMAIL);
    //note, if subject contains white spaces, make sure they are url-encoded
    link.setAddress("mailto:poi@apache.org?subject=Hyperlinks");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);
    //link to a place in this workbook
    //create a target sheet and cell
    Sheet sheet2 = wb.createSheet("Target Sheet");
    sheet2.createRow(0).createCell(0).setCellValue("Target Cell");
    cell = sheet.createRow(3).createCell(0);
    cell.setCellValue("Worksheet Link");
    Hyperlink link2 = createHelper.createHyperlink(HyperlinkType.DOCUMENT);
    link2.setAddress("'Target Sheet'!A1");
    cell.setHyperlink(link2);
    cell.setCellStyle(hlink_style);
    FileOutputStream out = new FileOutputStream("hyperinks.xlsx");
    wb.write(out);
    out.close();
    wb.close();
}