NPOI – Copy Row Helper

/// <summary>
/// HSSFRow Copy Command
///
/// Description:  Inserts a existing row into a new row, will automatically push down
///               any existing rows.  Copy is done cell by cell and supports, and the
///               command tries to copy all properties available (style, merged cells, values, etc...)
/// </summary>
/// <param name="workbook">Workbook containing the worksheet that will be changed</param>
/// <param name="worksheet">WorkSheet containing rows to be copied</param>
/// <param name="sourceRowNum">Source Row Number</param>
/// <param name="destinationRowNum">Destination Row Number</param>
private void CopyRow(HSSFWorkbook workbook, HSSFSheet worksheet, int sourceRowNum, int destinationRowNum)
{
    // Get the source / new row
    HSSFRow newRow = worksheet.GetRow(destinationRowNum);
    HSSFRow sourceRow = worksheet.GetRow(sourceRowNum);
 
    // If the row exist in destination, push down all rows by 1 else create a new row
    if (newRow != null)
    {
        worksheet.ShiftRows(destinationRowNum, worksheet.LastRowNum, 1);
    }
    else
    {
        newRow = worksheet.CreateRow(destinationRowNum);
    }
 
    // Loop through source columns to add to new row
    for (int i = 0; i < sourceRow.LastCellNum; i++)
    {
        // Grab a copy of the old/new cell
        HSSFCell oldCell = sourceRow.GetCell(i);
        HSSFCell 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
        HSSFCellStyle newCellStyle = workbook.CreateCellStyle();
        newCellStyle.CloneStyleFrom(oldCell.CellStyle); ;
        newCell.CellStyle = newCellStyle;
 
        // If there is a cell comment, copy
        if (newCell.CellComment != null) newCell.CellComment = oldCell.CellComment;
 
        // If there is a cell hyperlink, copy
        if (oldCell.Hyperlink != null) newCell.Hyperlink = oldCell.Hyperlink;
 
        // Set the cell data type
        newCell.SetCellType(oldCell.CellType);
 
        // Set the cell data value
        switch (oldCell.CellType)
        {
            case HSSFCellType.BLANK:
                newCell.SetCellValue(oldCell.StringCellValue);
                break;
            case HSSFCellType.BOOLEAN:
                newCell.SetCellValue(oldCell.BooleanCellValue);
                break;
            case HSSFCellType.ERROR:
                newCell.SetCellErrorValue(oldCell.ErrorCellValue);
                break;
            case HSSFCellType.FORMULA:
                newCell.SetCellFormula(oldCell.CellFormula);
                break;
            case HSSFCellType.NUMERIC:
                newCell.SetCellValue(oldCell.NumericCellValue);
                break;
            case HSSFCellType.STRING:
                newCell.SetCellValue(oldCell.RichStringCellValue);
                break;
            case HSSFCellType.Unknown:
                newCell.SetCellValue(oldCell.StringCellValue);
                break;
        }
    }
 
    // If there are are any merged regions in the source row, copy to new row
    for (int i = 0; i < worksheet.NumMergedRegions; i++)
    {
        CellRangeAddress cellRangeAddress = worksheet.GetMergedRegion(i);
        if (cellRangeAddress.FirstRow == sourceRow.RowNum)
        {
            CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
                                                                        (newRow.RowNum +
                                                                         (cellRangeAddress.FirstRow -
                                                                          cellRangeAddress.LastRow)),
                                                                        cellRangeAddress.FirstColumn,
                                                                        cellRangeAddress.LastColumn);
            worksheet.AddMergedRegion(newCellRangeAddress);
        }
    }
 
}
 
 
 
// Grab my NPOI workbook memorystream
HSSFWorkbook workbook = new HSSFWorkbook(memoryStream);
 
// Grab my test worksheet
HSSFSheet sheet = workbook.GetSheet("Sheet1");
 
// Copy Excel Row 1 to Excel Row 3
CopyRow(workbook, sheet, 0, 2);
 
// Copy Excel Row 2 to Excel Row 4
CopyRow(workbook, sheet, 1, 3);