Example usage for org.apache.poi.ss.usermodel Sheet getNumMergedRegions

List of usage examples for org.apache.poi.ss.usermodel Sheet getNumMergedRegions

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getNumMergedRegions

Introduction

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

Prototype

int getNumMergedRegions();

Source Link

Document

Returns the number of merged regions

Usage

From source file:org.efaps.esjp.common.file.FileUtil_Base.java

License:Apache License

/**
 * @param _sheet the sheet containing the data.
 * @param _rowNum the num of the row to copy.
 * @param _cellNum the num of the cell to copy.
 * @return the CellRangeAddress created.
 *///w  ww  .ja va2 s  .  c o m
protected CellRangeAddress getMergedRegion(final Sheet _sheet, final int _rowNum, final short _cellNum) {
    CellRangeAddress ret = null;
    for (int i = 0; i < _sheet.getNumMergedRegions(); i++) {
        final CellRangeAddress merged = _sheet.getMergedRegion(i);
        if (merged.isInRange(_rowNum, _cellNum)) {
            ret = merged;
            break;
        }
    }
    return ret;
}

From source file:org.netxilia.impexp.impl.ExcelImportService.java

License:Open Source License

@Override
public List<SheetFullName> importSheets(INetxiliaSystem workbookProcessor, WorkbookId workbookName,
        InputStream is, IProcessingConsole console) throws ImportException {
    List<SheetFullName> sheetNames = new ArrayList<SheetFullName>();
    try {//from ww  w .j a v  a2s .co m
        log.info("Starting import:" + workbookName);
        Workbook poiWorkbook = new HSSFWorkbook(is);
        IWorkbook nxWorkbook = workbookProcessor.getWorkbook(workbookName);
        log.info("Read POI");

        NetxiliaStyleResolver styleResolver = new NetxiliaStyleResolver(
                styleService.getStyleDefinitions(workbookName));

        HSSFPalette palette = ((HSSFWorkbook) poiWorkbook).getCustomPalette();

        for (int s = 0; s < poiWorkbook.getNumberOfSheets(); ++s) {
            Sheet poiSheet = poiWorkbook.getSheetAt(s);

            SheetFullName sheetName = new SheetFullName(workbookName,
                    getNextFreeSheetName(nxWorkbook, poiSheet.getSheetName()));
            ISheet nxSheet = null;
            BlockCellCommandBuilder cellCommandBuilder = new BlockCellCommandBuilder();
            try {
                List<CellReference> refreshCells = new ArrayList<CellReference>();

                for (Row poiRow : poiSheet) {
                    if (poiRow.getRowNum() % 100 == 0) {
                        log.info("importing row #" + poiRow.getRowNum());
                    }
                    for (Cell poiCell : poiRow) {
                        if (nxSheet == null) {
                            // lazy creation
                            while (true) {
                                try {
                                    nxSheet = nxWorkbook.addNewSheet(sheetName.getSheetName(),
                                            SheetType.normal);
                                    nxSheet.setRefreshEnabled(false);
                                    break;
                                } catch (AlreadyExistsException e) {
                                    // may happen is simultaneous imports take place
                                    sheetName = new SheetFullName(workbookName,
                                            getNextFreeSheetName(nxWorkbook, poiSheet.getSheetName()));
                                }
                            }
                        }

                        CellReference ref = new CellReference(sheetName.getSheetName(), poiRow.getRowNum(),
                                poiCell.getColumnIndex());
                        try {
                            ICellCommand cmd = copyCell(poiCell, ref, palette, styleResolver);
                            if (cmd != null) {
                                cellCommandBuilder.command(cmd);
                            }
                            if (poiCell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                                refreshCells.add(ref);
                            }

                        } catch (Exception e) {
                            if (console != null) {
                                console.println("Could import cell " + ref + ":" + poiCell + ":" + e);
                            }
                            log.error("Could import cell " + ref + ":" + poiCell + ":" + e, e);
                        }
                    }

                    if (poiRow.getRowNum() % 100 == 0 && !cellCommandBuilder.isEmpty()) {
                        nxSheet.sendCommandNoUndo(cellCommandBuilder.build());
                        cellCommandBuilder = new BlockCellCommandBuilder();
                    }
                }

                if (nxSheet == null) {
                    // empty sheet
                    continue;
                }
                if (!cellCommandBuilder.isEmpty()) {
                    nxSheet.sendCommandNoUndo(cellCommandBuilder.build());
                }
                // add the columns after as is not very clear how to get the number of cols in poi
                for (int c = 0; c < nxSheet.getDimensions().getNonBlocking().getColumnCount(); ++c) {
                    int width = 50;
                    try {
                        width = PoiUtils.widthUnits2Pixel(poiSheet.getColumnWidth(c));
                        nxSheet.sendCommand(ColumnCommands.width(Range.range(c), width));
                    } catch (NullPointerException ex) {
                        // ignore it
                        // NPE in at org.apache.poi.hssf.model.Sheet.getColumnWidth(Sheet.java:998)
                        // defaultColumnWidth can be null !?
                    }

                    CellStyle poiStyle = poiSheet.getColumnStyle(c);
                    if (poiStyle == null) {
                        continue;
                    }
                    Styles styles = PoiUtils.poiStyle2Netxilia(poiStyle,
                            poiSheet.getWorkbook().getFontAt(poiStyle.getFontIndex()), palette, styleResolver);
                    if (styles != null) {
                        nxSheet.sendCommand(ColumnCommands.styles(Range.range(c), styles));
                    }
                }

                // merge
                List<AreaReference> spans = new ArrayList<AreaReference>(poiSheet.getNumMergedRegions());
                for (int i = 0; i < poiSheet.getNumMergedRegions(); ++i) {
                    CellRangeAddress poiSpan = poiSheet.getMergedRegion(i);
                    spans.add(new AreaReference(sheetName.getSheetName(), poiSpan.getFirstRow(),
                            poiSpan.getFirstColumn(), poiSpan.getLastRow(), poiSpan.getLastColumn()));
                }
                nxSheet.sendCommand(SheetCommands.spans(spans));

                // refresh all the cells now
                nxSheet.setRefreshEnabled(true);
                nxSheet.sendCommandNoUndo(moreCellCommands.refresh(refreshCells, false));

            } finally {
                if (nxSheet != null) {
                    sheetNames.add(sheetName);
                }
            }
        }
    } catch (IOException e) {
        throw new ImportException(null, "Cannot open workbook:" + e, e);
    } catch (StorageException e) {
        throw new ImportException(null, "Error storing sheet:" + e, e);
    } catch (NotFoundException e) {
        throw new ImportException(null, "Cannot find workbook:" + e, e);
    } catch (NetxiliaResourceException e) {
        throw new ImportException(null, e.getMessage(), e);
    } catch (NetxiliaBusinessException e) {
        throw new ImportException(null, e.getMessage(), e);
    }

    return sheetNames;
}

From source file:org.newcashel.meta.model.NCClass.java

License:Apache License

private static CellRangeAddress getCellRangeAddress(Sheet sheet, int row, int col) {
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress cellRange = sheet.getMergedRegion(i);
        if (cellRange.isInRange(row, col))
            return cellRange;
    }/* w ww  .  j ava 2  s . com*/
    return null;
}

From source file:org.openpythia.utilities.SSUtilities.java

License:Apache License

public static Row copyRow(Sheet sheet, Row sourceRow, int destination) {
    Row newRow = sheet.createRow(destination);
    // get the last row from the headings
    int lastCol = sheet.getRow(0).getLastCellNum();
    for (int currentCol = 0; currentCol <= lastCol; currentCol++) {
        Cell newCell = newRow.createCell(currentCol);

        // if there is a cell in the template, copy its content and style
        Cell currentCell = sourceRow.getCell(currentCol);
        if (currentCell != null) {
            newCell.setCellStyle(currentCell.getCellStyle());
            newCell.setCellComment(currentCell.getCellComment());
            switch (currentCell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                newCell.setCellValue(currentCell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                newCell.setCellValue(currentCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                String dummy = currentCell.getCellFormula();
                dummy = dummy.replace("Row", String.valueOf(destination + 1));
                newCell.setCellFormula(dummy);
                newCell.setCellFormula(// ww w.j av a 2 s.  c om
                        currentCell.getCellFormula().replace("Row", String.valueOf(destination + 1)));
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                newCell.setCellValue(currentCell.getBooleanCellValue());
                break;
            default:
            }
        }
    }

    // if the row contains merged regions, copy them to the new row
    int numberMergedRegions = sheet.getNumMergedRegions();
    for (int i = 0; i < numberMergedRegions; i++) {
        CellRangeAddress mergedRegion = sheet.getMergedRegion(i);

        if (mergedRegion.getFirstRow() == sourceRow.getRowNum()
                && mergedRegion.getLastRow() == sourceRow.getRowNum()) {
            // this region is within the row - so copy it
            sheet.addMergedRegion(new CellRangeAddress(destination, destination, mergedRegion.getFirstColumn(),
                    mergedRegion.getLastColumn()));
        }
    }

    return newRow;
}

From source file:org.openpythia.utilities.SSUtilities.java

License:Apache License

public static void deleteRow(Sheet sheet, Row rowToDelete) {

    // if the row contains merged regions, delete them
    List<Integer> mergedRegionsToDelete = new ArrayList<>();
    int numberMergedRegions = sheet.getNumMergedRegions();
    for (int i = 0; i < numberMergedRegions; i++) {
        CellRangeAddress mergedRegion = sheet.getMergedRegion(i);

        if (mergedRegion.getFirstRow() == rowToDelete.getRowNum()
                && mergedRegion.getLastRow() == rowToDelete.getRowNum()) {
            // this region is within the row - so mark it for deletion
            mergedRegionsToDelete.add(i);
        }//  w w w  .j av a2  s.  c o  m
    }

    // now that we know all regions to delete just do it
    for (Integer indexToDelete : mergedRegionsToDelete) {
        sheet.removeMergedRegion(indexToDelete);
    }

    int rowIndex = rowToDelete.getRowNum();

    // this only removes the content of the row
    sheet.removeRow(rowToDelete);

    int lastRowNum = sheet.getLastRowNum();

    // shift the rest of the sheet one index down
    if (rowIndex >= 0 && rowIndex < lastRowNum) {
        sheet.shiftRows(rowIndex + 1, lastRowNum, -1);
    }
}

From source file:org.pivot4j.ui.poi.ExcelExporterIT.java

License:Common Public License

/**
 * @param format/*from  w ww. j  ava  2  s  . co m*/
 * @param showParentMember
 * @param showDimensionTitle
 * @param hideSpans
 * @param rows
 * @param mergedRegions
 * @throws IOException
 * @throws InvalidFormatException
 */
protected void testExport(Format format, boolean showParentMember, boolean showDimensionTitle,
        boolean hideSpans, int rows, int mergedRegions) throws IOException, InvalidFormatException {
    OutputStream out = null;

    File file = File.createTempFile("pivot4j-", "." + format.getExtension());

    if (deleteTestFile) {
        file.deleteOnExit();
    }

    try {
        out = new FileOutputStream(file);

        TableRenderer renderer = new TableRenderer();

        renderer.setShowParentMembers(showParentMember);
        renderer.setShowDimensionTitle(showDimensionTitle);
        renderer.setHideSpans(hideSpans);

        ExcelExporter exporter = new ExcelExporter(out);
        exporter.setFormat(format);

        renderer.render(getPivotModel(), exporter);
    } finally {
        out.flush();
        IOUtils.closeQuietly(out);
    }

    Workbook workbook = WorkbookFactory.create(file);

    assertThat("Workbook cannot be null.", workbook, is(notNullValue()));

    Sheet sheet = workbook.getSheetAt(0);
    assertThat("Worksheet cannot be null.", sheet, is(notNullValue()));

    assertThat("Invalid worksheet name.", sheet.getSheetName(), is(equalTo("Sales")));

    assertThat("Wrong number of rows.", sheet.getLastRowNum(), is(equalTo(rows)));
    assertThat("Wrong number of merged regions.", sheet.getNumMergedRegions(), is(equalTo(mergedRegions)));
}

From source file:org.primefaces.extensions.component.exporter.ExcelExporter.java

License:Apache License

protected void tableColumnGroup(Sheet sheet, SubTable table, String facetType) {
    ColumnGroup cg = table.getColumnGroup(facetType);
    List<UIComponent> headerComponentList = null;
    if (cg != null) {
        headerComponentList = cg.getChildren();
    }/*ww w .ja  v  a  2s.  com*/
    if (headerComponentList != null) {
        for (UIComponent component : headerComponentList) {
            if (component instanceof org.primefaces.component.row.Row) {
                org.primefaces.component.row.Row row = (org.primefaces.component.row.Row) component;
                int sheetRowIndex = sheet.getPhysicalNumberOfRows() > 0 ? sheet.getLastRowNum() + 1 : 0;
                Row xlRow = sheet.createRow(sheetRowIndex);
                int i = 0;
                for (UIComponent rowComponent : row.getChildren()) {
                    UIColumn column = (UIColumn) rowComponent;
                    String value = null;
                    if (facetType.equalsIgnoreCase("header")) {
                        value = column.getHeaderText();
                    } else {
                        value = column.getFooterText();
                    }
                    int rowSpan = column.getRowspan();
                    int colSpan = column.getColspan();

                    Cell cell = xlRow.getCell(i);

                    if (rowSpan > 1 || colSpan > 1) {

                        if (rowSpan > 1) {
                            cell = xlRow.createCell((short) i);
                            Boolean rowSpanFlag = false;
                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    rowSpanFlag = true;
                                }

                            }
                            if (!rowSpanFlag) {
                                cell.setCellStyle(cellStyle);
                                cell.setCellValue(value);
                                sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based)
                                        sheetRowIndex + rowSpan - 1, //last row  (0-based)
                                        i, //first column (0-based)
                                        i //last column  (0-based)
                                ));
                            }
                        }
                        if (colSpan > 1) {
                            cell = xlRow.createCell((short) i);
                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    cell = xlRow.createCell((short) ++i);
                                }
                            }
                            cell.setCellStyle(cellStyle);
                            cell.setCellValue(value);
                            sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based)
                                    sheetRowIndex, //last row  (0-based)
                                    i, //first column (0-based)
                                    i + colSpan - 1 //last column  (0-based)
                            ));
                            i = i + colSpan - 1;
                        }
                    } else {
                        cell = xlRow.createCell((short) i);
                        for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                            CellRangeAddress merged = sheet.getMergedRegion(j);
                            if (merged.isInRange(sheetRowIndex, i)) {
                                cell = xlRow.createCell((short) ++i);
                            }
                        }
                        cell.setCellValue(value);
                        cell.setCellStyle(facetStyle);

                    }
                    i++;
                }
            }

        }
    }

}

From source file:org.primefaces.extensions.showcase.util.ExcelCustomExporter.java

License:Apache License

protected void tableColumnGroup(Sheet sheet, DataTable table, String facetType) {
    ColumnGroup cg = table.getColumnGroup(facetType);
    List<UIComponent> headerComponentList = null;
    if (cg != null) {
        headerComponentList = cg.getChildren();
    }//from   w  w w  .j a va2s  .  c o  m
    if (headerComponentList != null) {
        for (UIComponent component : headerComponentList) {
            if (component instanceof org.primefaces.component.row.Row) {
                org.primefaces.component.row.Row row = (org.primefaces.component.row.Row) component;
                int sheetRowIndex = sheet.getLastRowNum() + 1;
                Row xlRow = sheet.createRow(sheetRowIndex);
                int i = 0;
                for (UIComponent rowComponent : row.getChildren()) {
                    UIColumn column = (UIColumn) rowComponent;
                    String value = null;
                    if (facetType.equalsIgnoreCase("header")) {
                        value = column.getHeaderText();
                    } else {
                        value = column.getFooterText();
                    }
                    int rowSpan = column.getRowspan();
                    int colSpan = column.getColspan();

                    Cell cell = xlRow.getCell(i);

                    if (rowSpan > 1 || colSpan > 1) {
                        if (rowSpan > 1) {
                            cell = xlRow.createCell((short) i);
                            Boolean rowSpanFlag = false;
                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    rowSpanFlag = true;
                                }

                            }
                            if (!rowSpanFlag) {
                                cell.setCellValue(value);
                                cell.setCellStyle(facetStyle);
                                sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, // first row (0-based)
                                        sheetRowIndex + rowSpan - 1, // last row (0-based)
                                        i, // first column (0-based)
                                        i // last column (0-based)
                                ));
                            }
                        }
                        if (colSpan > 1) {
                            cell = xlRow.createCell((short) i);

                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    cell = xlRow.createCell((short) ++i);
                                }
                            }
                            cell.setCellValue(value);
                            cell.setCellStyle(facetStyle);
                            sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, // first row (0-based)
                                    sheetRowIndex, // last row (0-based)
                                    i, // first column (0-based)
                                    i + colSpan - 1 // last column (0-based)
                            ));
                            i = i + colSpan - 1;
                        }
                    } else {
                        cell = xlRow.createCell((short) i);
                        for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                            CellRangeAddress merged = sheet.getMergedRegion(j);
                            if (merged.isInRange(sheetRowIndex, i)) {
                                cell = xlRow.createCell((short) ++i);
                            }
                        }
                        cell.setCellValue(value);
                        cell.setCellStyle(facetStyle);
                    }

                    i++;
                }
            }

        }

    }
}

From source file:org.primefaces.extensions.showcase.util.ExcelCustomExporter.java

License:Apache License

protected void tableColumnGroup(Sheet sheet, SubTable table, String facetType) {
    ColumnGroup cg = table.getColumnGroup(facetType);
    List<UIComponent> headerComponentList = null;
    if (cg != null) {
        headerComponentList = cg.getChildren();
    }/*from w  w  w  .j  a va2s. c o  m*/
    if (headerComponentList != null) {
        for (UIComponent component : headerComponentList) {
            if (component instanceof org.primefaces.component.row.Row) {
                org.primefaces.component.row.Row row = (org.primefaces.component.row.Row) component;
                int sheetRowIndex = sheet.getLastRowNum() + 1;
                Row xlRow = sheet.createRow(sheetRowIndex);
                int i = 0;
                for (UIComponent rowComponent : row.getChildren()) {
                    UIColumn column = (UIColumn) rowComponent;
                    String value = null;
                    if (facetType.equalsIgnoreCase("header")) {
                        value = column.getHeaderText();
                    } else {
                        value = column.getFooterText();
                    }
                    int rowSpan = column.getRowspan();
                    int colSpan = column.getColspan();

                    Cell cell = xlRow.getCell(i);

                    if (rowSpan > 1 || colSpan > 1) {

                        if (rowSpan > 1) {
                            cell = xlRow.createCell((short) i);
                            Boolean rowSpanFlag = false;
                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    rowSpanFlag = true;
                                }

                            }
                            if (!rowSpanFlag) {
                                cell.setCellStyle(cellStyle);
                                cell.setCellValue(value);
                                sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, // first row (0-based)
                                        sheetRowIndex + rowSpan - 1, // last row (0-based)
                                        i, // first column (0-based)
                                        i // last column (0-based)
                                ));
                            }
                        }
                        if (colSpan > 1) {
                            cell = xlRow.createCell((short) i);
                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    cell = xlRow.createCell((short) ++i);
                                }
                            }
                            cell.setCellStyle(cellStyle);
                            cell.setCellValue(value);
                            sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, // first row (0-based)
                                    sheetRowIndex, // last row (0-based)
                                    i, // first column (0-based)
                                    i + colSpan - 1 // last column (0-based)
                            ));
                            i = i + colSpan - 1;
                        }
                    } else {
                        cell = xlRow.createCell((short) i);
                        for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                            CellRangeAddress merged = sheet.getMergedRegion(j);
                            if (merged.isInRange(sheetRowIndex, i)) {
                                cell = xlRow.createCell((short) ++i);
                            }
                        }
                        cell.setCellValue(value);
                        cell.setCellStyle(facetStyle);

                    }
                    i++;
                }
            }

        }
    }

}

From source file:org.projectforge.excel.ExportSheet.java

License:Open Source License

private static Row copyRow(Sheet worksheet, int rowNum) {
    Row sourceRow = worksheet.getRow(rowNum);

    //Save the text of any formula before they are altered by row shifting
    String[] formulasArray = new String[sourceRow.getLastCellNum()];
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        if (sourceRow.getCell(i) != null && sourceRow.getCell(i).getCellType() == Cell.CELL_TYPE_FORMULA)
            formulasArray[i] = sourceRow.getCell(i).getCellFormula();
    }//  w  ww .  j  a v a  2s.co  m

    worksheet.shiftRows(rowNum, worksheet.getLastRowNum(), 1);
    Row newRow = sourceRow; //Now sourceRow is the empty line, so let's rename it
    sourceRow = worksheet.getRow(rowNum + 1); //Now the source row is at rowNum+1

    // 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;

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

        // Copy style from old cell and apply to new cell
        CellStyle newCellStyle = worksheet.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:
            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(formulasArray[i]);
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        default:
            break;
        }
    }

    // If there 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);
        }
    }
    return newRow;
}