Example usage for org.apache.poi.ss.usermodel Cell setHyperlink

List of usage examples for org.apache.poi.ss.usermodel Cell setHyperlink

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. setHyperlink

Introduction

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

Prototype

void setHyperlink(Hyperlink link);

Source Link

Document

Assign a hyperlink to this cell

Usage

From source file:bad.robot.excel.cell.HyperlinkCell.java

License:Apache License

@Override
public void update(org.apache.poi.ss.usermodel.Cell cell, Workbook workbook) {
    this.getStyle().applyTo(cell, workbook);
    cell.setCellValue(text);/* w  w w .  j a v  a  2s.  c o m*/
    cell.setHyperlink(createHyperlink(workbook));
}

From source file:bad.robot.excel.row.CopyRow.java

License:Apache License

private static void copyCellHyperlink(Cell oldCell, Cell newCell) {
    if (oldCell.getHyperlink() != null)
        newCell.setHyperlink(oldCell.getHyperlink());
}

From source file:com.anritsu.mcrepositorymanager.utils.GenerateRSS.java

public String getRSS() {
    FileInputStream file = null;/*from w ww  . j  a v a 2 s. c  o m*/
    String rssFileName = rssTemplateFileName.replaceAll("template", mcVersion);
    try {
        file = new FileInputStream(
                new File(Configuration.getInstance().getRssTemplatePath() + rssTemplateFileName));
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        workbook.setSheetName(workbook.getSheetIndex("MC X.X.X"), "MC " + mcVersion);
        XSSFSheet sheet = workbook.getSheet("MC " + mcVersion);
        CreationHelper createHelper = workbook.getCreationHelper();

        Cell cell = null;

        // Update the sheet title
        cell = sheet.getRow(0).getCell(0);
        cell.setCellValue(cell.getStringCellValue().replaceAll("template", mcVersion));

        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);

        XSSFCellStyle hlinkstyle = workbook.createCellStyle();
        XSSFFont hlinkfont = workbook.createFont();
        hlinkfont.setUnderline(XSSFFont.U_SINGLE);
        hlinkfont.setColor(HSSFColor.BLUE.index);
        hlinkstyle.setFont(hlinkfont);
        hlinkstyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        hlinkstyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        hlinkstyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        hlinkstyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);

        XSSFCellStyle dateCellStyle = workbook.createCellStyle();
        dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd-MMMM-yyyy"));
        dateCellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        dateCellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        dateCellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        dateCellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);

        // Populate the table
        int rowCount = 1;
        for (RecommendedMcPackage rmcp : sortedMcPackages) {
            if (rmcp.getRecommendedVersion() != null && rmcp.isShowInTable()) {
                Row row = sheet.createRow(rowCount + 1);
                rowCount++;

                cell = row.createCell(0);
                cell.setCellValue(rmcp.getTier().replaceAll("Anritsu/MasterClaw/", ""));
                cell.setCellStyle(cellStyle);

                cell = row.createCell(1);
                cell.setCellValue(rmcp.getGroup());
                cell.setCellStyle(cellStyle);

                cell = row.createCell(2);
                cell.setCellValue(rmcp.getPackageName());

                UrlValidator defaultValidator = new UrlValidator(UrlValidator.ALLOW_LOCAL_URLS);

                if (rmcp.getRecommendedVersion().getReleaseNote() != null
                        && defaultValidator.isValid(rmcp.getRecommendedVersion().getReleaseNote())) {
                    XSSFHyperlink releaseNotelink = (XSSFHyperlink) createHelper
                            .createHyperlink(Hyperlink.LINK_URL);
                    releaseNotelink.setAddress(rmcp.getRecommendedVersion().getReleaseNote());
                    //System.out.println("Inside(if) RN: " + rmcp.getRecommendedVersion().getReleaseNote() + " Valid: " + defaultValidator.isValid(rmcp.getRecommendedVersion().getReleaseNote()));

                    cell.setHyperlink(releaseNotelink);
                }
                cell.setCellStyle(hlinkstyle);

                cell = row.createCell(3);
                cell.setCellValue(rmcp.getRecommendedVersion().getPackageVersion());
                cell.setCellStyle(cellStyle);

                cell = row.createCell(4);
                cell.setCellValue(rmcp.getAvailability());
                cell.setCellStyle(cellStyle);

                cell = row.createCell(5);
                String customers = Arrays.asList(rmcp.getRecommendedVersion().getCustomerList().toArray())
                        .toString();
                if (customers.equalsIgnoreCase("[All]")) {
                    customers = "";
                }
                cell.setCellValue(customers);
                cell.setCellStyle(cellStyle);

                cell = row.createCell(6);
                cell.setCellValue(rmcp.getRecommendedVersion().getRisk());
                cell.setCellStyle(cellStyle);

                cell = row.createCell(7);
                cell.setCellValue(rmcp.getPackageName());
                XSSFHyperlink link = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL);
                link.setAddress(rmcp.getRecommendedVersion().getDownloadLinks().iterator().next());
                cell.setHyperlink((XSSFHyperlink) link);
                cell.setCellStyle(hlinkstyle);

                cell = row.createCell(8);
                cell.setCellValue((rmcp.getRecommendedVersion() != null
                        && rmcp.getRecommendedVersion().isLessRecommended()) ? "#" : "");
                cell.setCellStyle(cellStyle);

                cell = row.createCell(9);
                cell.setCellValue(rmcp.getRecommendedVersion().getNotes());
                cell.setCellStyle(cellStyle);

                StringBuilder newFeatures = new StringBuilder();
                for (MCPackageActivities mcpa : rmcp.getRecommendedVersion().getActivities()) {
                    if (!mcpa.getActivityType().equalsIgnoreCase("epr")) {
                        newFeatures.append(mcpa.getActivityType() + " " + mcpa.getActivityId() + "; ");
                    }
                }
                cell = row.createCell(10);
                cell.setCellValue(newFeatures.toString());
                cell.setCellStyle(cellStyle);

                cell = row.createCell(11);
                cell.setCellValue(rmcp.getRecommendedVersion().getReleaseDate());
                cell.setCellStyle(dateCellStyle);
            }
            sheet.autoSizeColumn(0);
            sheet.autoSizeColumn(1);
            sheet.autoSizeColumn(2);
            sheet.autoSizeColumn(3);
            sheet.autoSizeColumn(4);
            sheet.autoSizeColumn(6);
            sheet.autoSizeColumn(7);
            sheet.autoSizeColumn(8);
            sheet.autoSizeColumn(11);

        }

        FileOutputStream outFile = new FileOutputStream(
                new File(Configuration.getInstance().getRssTemplatePath() + rssFileName));
        workbook.write(outFile);
        outFile.close();
        return Configuration.getInstance().getRssTemplatePath() + rssFileName;

    } catch (FileNotFoundException ex) {
        Logger.getLogger(GenerateRSS.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(GenerateRSS.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            file.close();
        } catch (IOException ex) {
            Logger.getLogger(GenerateRSS.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    return "";
}

From source file:com.b2international.snowowl.datastore.server.importer.AbstractTerminologyExcelExporter.java

License:Apache License

/**
 * Creates the index sheet based on the given sheet names.
 * /*from   ww  w. j a v  a2 s  . co m*/
 * @param sheetNames
 */
protected void createIndexSheet(final Collection<T> components) {

    final Sheet indexSheet = workbook.createSheet("INDEX");

    final List<T> filteredComponents = Lists.newArrayList(Iterables.filter(components, new Predicate<T>() {
        @Override
        public boolean apply(T input) {
            return isToExport(getComponentId(input));
        }
    }));

    final List<String> sheetNames = extractSheetNamesFromTerminologyComponents(filteredComponents);

    final Row firstRow = indexSheet.createRow(0);
    createCell(firstRow, getIndexSheetHeaderName(), BOLD_STYLE, 0);

    for (int i = 0; i < sheetNames.size(); i++) {

        final String sheetName = getFinalSheetName(i + 1, sheetNames.get(i));
        final Hyperlink hyperlink = workbook.getCreationHelper().createHyperlink(XSSFHyperlink.LINK_DOCUMENT);

        hyperlink.setLabel(sheetName);
        hyperlink.setAddress(String.format("'%s'!A1", sheetName));

        final Row row = indexSheet.createRow(i + 1);
        final Cell cell = row.createCell(0);

        cell.setCellValue(sheetName);
        cell.setCellStyle(hyperlinkStyle);
        cell.setHyperlink(hyperlink);

    }

    indexSheet.autoSizeColumn(0);

}

From source file:com.blackducksoftware.tools.commonframework.standard.datatable.writer.DataSetWriterExcel.java

License:Apache License

private void populateHyperlinkCell(Record record, FieldDef fieldDef, Cell cell) throws Exception {
    String cellValue = record.getHyperlinkFieldValue(fieldDef.getName()).getDisplayText();
    cell.setCellValue(cellValue);/*from  ww  w.ja  va  2s.c  o  m*/

    // cell style for hyperlinks
    // by default hyperlinks are blue and underlined
    CellStyle hlink_style = workbook.createCellStyle();
    Font hlink_font = workbook.createFont();
    hlink_font.setUnderline(Font.U_SINGLE);
    hlink_font.setColor(IndexedColors.BLUE.getIndex());
    hlink_style.setFont(hlink_font);

    // Make it a hyperlink
    CreationHelper createHelper = workbook.getCreationHelper();
    Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL);
    link.setAddress(record.getHyperlinkFieldValue(fieldDef.getName()).getHyperlinkText());
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);
}

From source file:com.github.autoprimer3.Primer3ResultViewController.java

License:Open Source License

private void writePrimersToExcel(final File f) throws IOException {
    final Service<Void> service = new Service<Void>() {
        @Override/* ww  w . ja v  a  2 s .c o m*/
        protected Task<Void> createTask() {
            return new Task<Void>() {
                @Override
                protected Void call() throws IOException {
                    BufferedOutputStream bo = new BufferedOutputStream(new FileOutputStream(f));
                    Workbook wb = new XSSFWorkbook();
                    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);
                    CreationHelper createHelper = wb.getCreationHelper();
                    Sheet listSheet = wb.createSheet();
                    Sheet detailsSheet = wb.createSheet();
                    Row row = null;
                    int rowNo = 0;
                    int sheetNo = 0;
                    wb.setSheetName(sheetNo++, "List");
                    wb.setSheetName(sheetNo++, "Details");

                    row = listSheet.createRow(rowNo++);
                    String header[] = { "Primer", "Sequence", "Product Size (bp)" };
                    for (int col = 0; col < header.length; col++) {
                        Cell cell = row.createCell(col);
                        cell.setCellValue(header[col]);
                    }

                    updateMessage("Writing primers . . .");
                    updateProgress(0, data.size() * 3);
                    int n = 0;
                    for (Primer3Result r : data) {
                        n++;
                        updateMessage("Writing primer list " + n + " . . .");
                        row = listSheet.createRow(rowNo++);
                        int col = 0;
                        Cell cell = row.createCell(col++);
                        cell.setCellValue(r.getName() + "F");
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getLeftPrimer());
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getProductSize());
                        updateProgress(n, data.size() * 3);
                        updateMessage("Writing primer list " + n + " . . .");
                        row = listSheet.createRow(rowNo++);
                        col = 0;
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getName() + "R");
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getRightPrimer());
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getProductSize());
                        n++;
                        updateProgress(n, data.size() * 3);
                    }
                    rowNo = 0;
                    row = detailsSheet.createRow(rowNo++);
                    ArrayList<String> detailsHeader = new ArrayList<>(Arrays.asList("Name", "Other IDs",
                            "Left Primer", "Right Primer", "Product Size (bp)", "Region", "in-silico PCR"));
                    if (ispcrResCol.isVisible()) {
                        detailsHeader.add("in-silico PCR Results");
                    }
                    for (int col = 0; col < detailsHeader.size(); col++) {
                        Cell cell = row.createCell(col);
                        cell.setCellValue(detailsHeader.get(col));
                    }
                    int m = 0;
                    for (Primer3Result r : data) {
                        m++;
                        updateMessage("Writing details for pair " + m + " . . .");
                        row = detailsSheet.createRow(rowNo++);
                        int col = 0;
                        Cell cell = row.createCell(col++);
                        cell.setCellValue(r.getName());
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getTranscripts());
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getLeftPrimer());
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getRightPrimer());
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getProductSize());
                        cell = row.createCell(col++);
                        cell.setCellValue(r.getRegion());
                        cell = row.createCell(col++);
                        if (r.getIsPcrUrl() != null) {
                            cell.setCellValue("isPCR");
                            org.apache.poi.ss.usermodel.Hyperlink hl = createHelper
                                    .createHyperlink(org.apache.poi.ss.usermodel.Hyperlink.LINK_URL);
                            hl.setAddress(r.getIsPcrUrl());
                            cell.setHyperlink(hl);
                            cell.setCellStyle(hlink_style);
                        } else {
                            cell.setCellValue("");
                        }
                        if (ispcrResCol.isVisible()) {
                            cell = row.createCell(col++);
                            if (r.getIsPcrResults() != null) {
                                cell.setCellValue(r.getIsPcrResults());
                            } else {
                                cell.setCellValue("");
                            }
                        }
                        updateProgress(n + m, data.size() * 3);
                    }

                    updateMessage("Wrote " + data.size() + " primer pairs to file.");
                    wb.write(bo);
                    bo.close();
                    return null;
                }
            };
        }

    };

    service.setOnSucceeded(new EventHandler<WorkerStateEvent>() {
        @Override
        public void handle(WorkerStateEvent e) {

            Action response = Dialogs.create().title("Done").masthead("Finished writing")
                    .message("Primers successfully written to " + f.getAbsolutePath()
                            + "\n\nDo you want to open " + "this file now?")
                    .actions(Dialog.ACTION_YES, Dialog.ACTION_NO).styleClass(Dialog.STYLE_CLASS_NATIVE)
                    .showConfirm();

            if (response == Dialog.ACTION_YES) {
                try {
                    openFile(f);
                } catch (IOException ex) {
                    Action openFailed = Dialogs.create().title("Open failed")
                            .masthead("Could not open output file")
                            .message("Exception encountered when attempting to open "
                                    + "the saved file. See below:")
                            .styleClass(Dialog.STYLE_CLASS_NATIVE).showException(ex);
                }
            }
            progressBar.progressProperty().unbind();
            progressBar.setVisible(false);
            summaryLabel.textProperty().unbind();
            summaryLabel.setText(summary);
            closeButton.setDisable(false);
            closeMenuItem.setDisable(false);
            setCheckIsPcrButton();
        }
    });
    service.setOnCancelled(new EventHandler<WorkerStateEvent>() {
        @Override
        public void handle(WorkerStateEvent e) {
            Dialogs writeCancelled = Dialogs.create().title("Writing Cancelled")
                    .masthead("Cancelled writing to file").message("User cancelled writing primers to file.")
                    .styleClass(Dialog.STYLE_CLASS_NATIVE);
            writeCancelled.showInformation();
            progressBar.progressProperty().unbind();
            progressBar.setVisible(false);
            summaryLabel.textProperty().unbind();
            summaryLabel.setText(summary);
            closeButton.setDisable(false);
            closeMenuItem.setDisable(false);
            setCheckIsPcrButton();
        }
    });
    service.setOnFailed(new EventHandler<WorkerStateEvent>() {
        @Override
        public void handle(WorkerStateEvent e) {
            Action writeFailed = Dialogs.create().title("Writing failed")
                    .masthead("Could not write primers to file")
                    .message("Exception encountered when attempting to write " + "primers to file. See below:")
                    .styleClass(Dialog.STYLE_CLASS_NATIVE).showException(e.getSource().getException());
            progressBar.progressProperty().unbind();
            progressBar.setVisible(false);
            summaryLabel.textProperty().unbind();
            summaryLabel.setText(summary);
            closeButton.setDisable(false);
            closeMenuItem.setDisable(false);
            setCheckIsPcrButton();
        }
    });
    progressBar.setVisible(true);
    progressBar.progressProperty().bind(service.progressProperty());
    summaryLabel.textProperty().bind(service.messageProperty());
    closeButton.setDisable(true);
    closeMenuItem.setDisable(true);
    checkIsPcrButton.setText("Cancel");
    checkIsPcrButton.setOnAction(new EventHandler<ActionEvent>() {
        @Override
        public void handle(ActionEvent actionEvent) {
            service.cancel();

        }
    });
    service.start();
}

From source file:com.github.igor_kudryashov.utils.excel.ExcelWriter.java

License:Apache License

/**
 * //  w ww  .  ja v a2 s  .  com
 * Adds a hyperlink into a cell. The contents of the cell remains
 * peronachalnoe. Do not forget to fill in the contents of the cell before
 * add a hyperlinks. If a row already has been flushed, this method not
 * work!
 * 
 * @param sheet
 *            Sheet
 * @param rownum
 *            number of row
 * @param colnum
 *            number of column
 * @param url
 *            hyperlink
 */
public void createHyperlink(Sheet sheet, int rownum, int colnum, String url) {
    Row row = sheet.getRow(rownum);
    if (url != null && !"".equals(url)) {
        Cell cell = row.getCell(colnum);
        CreationHelper createHelper = workbook.getCreationHelper();
        XSSFHyperlink hyperlink = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL);
        hyperlink.setAddress(url);
        cell.setHyperlink(hyperlink);
        cell.setCellStyle(getHyperlinkCellStyle(rownum, url));
    }
}

From source file:com.github.wnameless.workbookaccessor.WorkbookWriter.java

License:Apache License

/**
 * Adds a row to the sheet.// ww w. jav  a 2s  . c  o m
 * 
 * @param fields
 *          an Iterable of Object
 * @return this {@link WorkbookWriter}
 */
public WorkbookWriter addRow(Iterable<? extends Object> fields) {
    Row row;
    if (sheet.getLastRowNum() == 0 && sheet.getPhysicalNumberOfRows() == 0)
        row = sheet.createRow(0);
    else
        row = sheet.createRow(sheet.getLastRowNum() + 1);

    int i = 0;
    for (Object o : fields) {
        Cell cell = row.createCell(i);
        if (o != null) {
            if (o instanceof Boolean)
                cell.setCellValue((Boolean) o);
            else if (o instanceof Calendar)
                cell.setCellValue((Calendar) o);
            else if (o instanceof Date)
                cell.setCellValue((Date) o);
            else if (o instanceof Double)
                cell.setCellValue((Double) o);
            else if (o instanceof RichTextString)
                if ((o instanceof HSSFRichTextString && workbook instanceof HSSFWorkbook)
                        || (o instanceof XSSFRichTextString && workbook instanceof XSSFWorkbook)) {
                    cell.setCellValue((RichTextString) o);
                } else {
                    cell.setCellValue(o.toString());
                }
            else if (o instanceof Hyperlink)
                cell.setHyperlink((Hyperlink) o);
            else if (o instanceof Number)
                cell.setCellValue(((Number) o).doubleValue());
            else
                cell.setCellValue(o.toString());
        }
        i++;
    }
    return this;
}

From source file:com.hp.amss.util.HyperlinkExample.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    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);//  w w  w  .ja  v  a  2s .com

    Cell cell;
    Sheet sheet = wb.createSheet("Hyperlinks");
    //URL
    cell = sheet.createRow(0).createCell((short) 0);
    cell.setCellValue("URL Link");

    Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_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((short) 0);
    cell.setCellValue("File Link");
    link = createHelper.createHyperlink(Hyperlink.LINK_FILE);
    link.setAddress("link1.xls");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);

    //e-mail link
    cell = sheet.createRow(2).createCell((short) 0);
    cell.setCellValue("Email Link");
    link = createHelper.createHyperlink(Hyperlink.LINK_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);
    //TODO
    cell.setCellValue(createHelper.createRichTextString(""));
    cell.setCellType(Cell.CELL_TYPE_STRING);

    //link to a place in this workbook

    //create a target sheet and cell
    Sheet sheet2 = wb.createSheet("Target Sheet");
    sheet2.createRow(0).createCell((short) 0).setCellValue("Target Cell");

    cell = sheet.createRow(3).createCell((short) 0);
    cell.setCellValue("Worksheet Link");
    Hyperlink link2 = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
    link2.setAddress("'Target Sheet'!A1");
    cell.setHyperlink(link2);
    cell.setCellStyle(hlink_style);

    FileOutputStream out = new FileOutputStream("C:\\hyperinks.xlsx");
    wb.write(out);
    out.close();

}

From source file:com.kybelksties.excel.ExcelSheetTableModel.java

License:Open Source License

/**
 * Insert a row at a given index.//w  ww  .  ja v  a  2  s.  c  om
 *
 * @param createAtIndex row-number of the cell at which to create a new row
 * @param sourceRow     the row to insert
 */
public void insertRowAt(int createAtIndex, Row sourceRow) {
    Row newRow = getRow(createAtIndex);
    if (newRow != null) {
        // shift all rows >= createAtIndex up by one
        getSheet().shiftRows(createAtIndex, getSheet().getLastRowNum(), 1);
    } else {
        newRow = getSheet().createRow(createAtIndex);
    }

    // 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
        Cell oldCell = sourceRow.getCell(i);
        Cell newCell = newRow.createCell(i);

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

        // Copy style from old cell and apply to new cell
        CellStyle 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());

        // 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 < getSheet().getNumMergedRegions(); i++) {
        CellRangeAddress cellRangeAddress = getSheet().getMergedRegion(i);
        if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
            CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                    (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
                    cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
            getSheet().addMergedRegion(newCellRangeAddress);
        }
    }
}