Example usage for org.apache.poi.xssf.usermodel XSSFCell setHyperlink

List of usage examples for org.apache.poi.xssf.usermodel XSSFCell setHyperlink

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFCell
  6. setHyperlink

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFCell setHyperlink.

Prototype

@Override
public void setHyperlink(Hyperlink hyperlink)

Source Link

Document

Assign a hyperlink to this cell.

Usage

From source file:com.appdynamics.jrbronet.projectplan.ExcelManager.java

private static void copyRow(XSSFWorkbook workbook, XSSFSheet sourceWorksheet, int sourceRowNum,
        XSSFSheet destinationWorksheet, int destinationRowNum) {
    // Get the source / new row
    XSSFRow newRow = destinationWorksheet.getRow(destinationRowNum);
    XSSFRow sourceRow = sourceWorksheet.getRow(sourceRowNum);

    // If the row exist in destination, push down all rows by 1 else create a new row

    if (newRow != null) {
        destinationWorksheet.shiftRows(destinationRowNum, destinationWorksheet.getLastRowNum(), 1);
    } else {/*from  w  ww  . j a v a 2  s.c om*/
        newRow = destinationWorksheet.createRow(destinationRowNum);
    }

    // Loop through source columns to add to new row
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        XSSFCell oldCell = sourceRow.getCell(i);
        XSSFCell newCell = newRow.createCell(i);

        // If the old cell is null jump to next cell
        if (oldCell == null) {
            newCell = null;
            continue;
        }

        // Copy style from old cell and apply to new cell
        XSSFCellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        ;
        newCell.setCellStyle(newCellStyle);

        // If there is a cell comment, copy
        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        // If there is a cell hyperlink, copy
        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());

        //newCell.setCellValue(oldCell.getRawValue());
        //newCell.setCellType(oldCell.getCellType());                        

        // Set the cell data value
        switch (oldCell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            newCell.setCellFormula(oldCell.getCellFormula());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        }

    }

    // If there are are any merged regions in the source row, copy to new row
    /*
    for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
    CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
    if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
        CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                (newRow.getRowNum() +
                        (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow()
                                )),
                cellRangeAddress.getFirstColumn(),
                cellRangeAddress.getLastColumn());
        worksheet.addMergedRegion(newCellRangeAddress);
    }            
    }
    */
}

From source file:org.azkfw.document.database.xlsx.XLSXWriter.java

License:Apache License

private XSSFCell createCell(final int col, final int size, final String text, final XSSFCellStyle style,
        final Hyperlink link, final XSSFRow row) {
    XSSFCell cell = null;
    for (int i = size - 1; i >= 0; i--) {
        cell = row.createCell(col + i, Cell.CELL_TYPE_STRING);
        if (null != style) {
            cell.setCellStyle(style);/*from   w ww . j  a  v  a 2  s  .  c  o m*/
        }
        if (null != link) {
            cell.setHyperlink(link);
        }
    }
    cell.setCellValue(s(text));
    return cell;
}

From source file:org.rapidpm.data.table.export.Table2XLSX.java

License:Apache License

@Override
public ByteArrayOutputStream export(final Table table) {
    final XSSFWorkbook workbook = new XSSFWorkbook();

    final XSSFSheet xssfSheet = workbook.createSheet(table.getTableName());
    final Collection<ColumnInformation> infoList = table.getColumnInfoList();
    final XSSFRow xssfHeaderRow = xssfSheet.createRow(0);
    for (final ColumnInformation information : infoList) {
        if (information.isExportable()) {
            final XSSFCell xssfCell = xssfHeaderRow.createCell(information.getSpaltenNr());
            xssfCell.setCellValue(information.getSpaltenName());
            xssfCell.setCellType(XSSFCell.CELL_TYPE_STRING);
        } else {/*from w  w w.  j a v a2  s  .c  o m*/
            if (logger.isDebugEnabled()) {
                logger.debug("ColInfo not exportable " + information.getSpaltenName());
            }
        }
    }

    final List<Row> tableRowList = table.getRowList();
    for (final Row row : tableRowList) {
        final XSSFRow xssfRow = xssfSheet.createRow(row.getRowNr() + 1);
        final List<Cell> cellList = row.getCells();
        for (final Cell cell : cellList) {
            if (cell.getColInfo().isExportable()) {
                final XSSFCell xssfCell = xssfRow.createCell(cell.getColInfo().getSpaltenNr());
                final CellTypeEnum cellType = cell.getCellType();
                if (cellType.equals(CellTypeEnum.RawData)) {
                    xssfCell.setCellValue(cell.getFormattedValue());
                    xssfCell.setCellType(XSSFCell.CELL_TYPE_STRING); //JIRA MOD-32 CellType in Abhngigkeit der ValueClass z.B. Number
                } else if (cellType.equals(CellTypeEnum.RawLink)) {
                    final XSSFCreationHelper helper = workbook.getCreationHelper();
                    final XSSFHyperlink xssfHyperlink = helper.createHyperlink(Hyperlink.LINK_URL);
                    xssfHyperlink.setAddress(cell.getFormattedValue());
                    xssfHyperlink.setLabel(cell.getLabel());
                    xssfCell.setCellValue(cell.getLabel());
                    xssfCell.setHyperlink(xssfHyperlink);

                    final CellStyle hlink_style = createHyperLinkStyle(workbook);
                    xssfCell.setCellStyle(hlink_style);
                } else {

                }
            } else {
                if (logger.isDebugEnabled()) {
                    logger.debug("Cell not exportable ");
                }
            }

        }
    }

    final ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    try {
        workbook.write(outputStream);
    } catch (IOException e) {
        logger.error(e);
    }
    return outputStream;
}

From source file:sv.com.mined.sieni.controller.GestionNotasController.java

public static void copyRow(XSSFSheet worksheetSource, XSSFSheet worksheetDestination, int sourceRowNum,
        int destinationRowNum) {
    // Get the source / new row
    XSSFRow origen = worksheetSource.getRow(sourceRowNum);
    XSSFRow destino = worksheetDestination.createRow(destinationRowNum);

    // Loop through source columns to add to new row
    for (int i = 0; i < origen.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        XSSFCell oldCell = origen.getCell(i);
        XSSFCell newCell = destino.createCell(i);
        // If the old cell is null jump to next cell
        if (oldCell == null) {
            newCell = null;//from   ww w . j a  va  2  s .co m
            continue;
        }

        //Ajustar tamaos columnas
        worksheetDestination.setColumnWidth(i, worksheetSource.getColumnWidth(i));

        // Copy style from old cell and apply to new cell
        XSSFCellStyle newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        newCell.setCellStyle(newCellStyle);

        // If there is a cell comment, copy
        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        // If there is a cell hyperlink, copy
        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());
        // Set the cell data value
        switch (oldCell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            newCell.setCellFormula(oldCell.getCellFormula());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        }

    }

}

From source file:tan.jam.jsf.Shifting.java

public static void InsertRow(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum,
        int destinationRowNum) {

    worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
    XSSFRow newRow = worksheet.getRow(destinationRowNum);
    XSSFRow sourceRow = worksheet.getRow(sourceRowNum);

    if (newRow != null) {
        worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
    } else {/*from  ww  w .j a  v  a 2 s  .  c  om*/
        newRow = worksheet.createRow(destinationRowNum);
    }

    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        XSSFCell oldCell = sourceRow.getCell(i);
        XSSFCell newCell = newRow.createCell(i);

        if (oldCell == null) {
            newCell = null;
            continue;
        }

        XSSFCellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        ;
        newCell.setCellStyle(newCellStyle);

        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        newCell.setCellType(oldCell.getCellType());

        switch (oldCell.getCellType()) {
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA:
            newCell.setCellFormula("+" + "F" + destinationRowNum + "*G" + destinationRowNum);
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
            //newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
            newCell.setCellValue("");
            break;
        }
    }

    for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
        CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
        if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
            CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                    (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
                    cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
            worksheet.addMergedRegion(newCellRangeAddress);
        }
    }
    int inc = destinationRowNum + 1;
    worksheet.getRow(destinationRowNum).getCell(7).setCellFormula("+F" + inc + "*G" + inc);
}

From source file:tan.jam.jsf.Shifting.java

private static void CopyData(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum,
        int destinationRowNum, int Mov) {
    XSSFRow newRow = worksheet.getRow(destinationRowNum);
    XSSFRow sourceRow = worksheet.getRow(sourceRowNum);

    for (int i = sourceRow.getLastCellNum(); i > 8 + Mov; i--) {

        int d = i - 1;
        XSSFCell oldCell = sourceRow.getCell(d);
        XSSFCell newCell = newRow.createCell(i);

        if (oldCell == null) {
            newCell = null;/*  ww  w. j a v a 2  s  .  c o  m*/
            continue;
        }

        XSSFCellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        ;
        newCell.setCellStyle(newCellStyle);

        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        newCell.setCellType(oldCell.getCellType());

        switch (oldCell.getCellType()) {
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA:
            newCell.setCellFormula(oldCell.getCellFormula());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        }
    }
}

From source file:uk.ac.ebi.generic.util.ExcelWorkBook.java

License:Apache License

public ExcelWorkBook(String[] titles, Object[][] tableData, String sheetTitle) throws Exception {

    this.wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();

    // create a new sheet
    XSSFSheet sheet = wb.createSheet(sheetTitle);
    XSSFPrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);//ww w. jav  a2 s  . c  o m
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    //header row
    XSSFRow headerRow = sheet.createRow(0);
    //headerRow.setHeightInPoints(40);

    XSSFCell headerCell;
    for (int j = 0; j < titles.length; j++) {
        headerCell = headerRow.createCell(j);
        headerCell.setCellValue(titles[j]);
        //headerCell.setCellStyle(styles.get("header"));
    }

    // data rows
    // Create a row and put some cells in it. Rows are 0 based.
    // Then set value for that created cell
    for (int k = 0; k < tableData.length; k++) {
        XSSFRow row = sheet.createRow(k + 1); // data starts from row 1   
        for (int l = 0; l < tableData[k].length; l++) {
            XSSFCell cell = row.createCell(l);
            String cellStr = null;

            try {
                cellStr = tableData[k][l].toString();
            } catch (Exception e) {
                cellStr = "";
            }

            //System.out.println("cell " + l + ":  " + cellStr);

            // make hyperlink in cell
            if ((cellStr.startsWith("http://") || cellStr.startsWith("https://")) && !cellStr.contains("|")) {

                //need to encode URI for this version of ExcelWorkBook
                cellStr = URIUtil.encodePath(cellStr, "UTF-8");

                cellStr = cellStr.replace("%3F", "?"); // so that url link would work

                //System.out.println("cellStr: " + cellStr);
                XSSFHyperlink url_link = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL);

                url_link.setAddress(cellStr);

                cell.setCellValue(cellStr);
                cell.setHyperlink(url_link);
            } else {
                cell.setCellValue(cellStr);
            }

            //System.out.println((String)tableData[k][l]);
        }
    }
}