Another day and another little tidbit on using NPOI. I was doing tool mock-up at work today when I ran across a need for a copy row function. After searching high and low, I realized NPOI does not currently offer this capability. After looking around (Google, NPOI and POI threads) I decided to create my own helper function. I’m sure there might be a few things I missed in my routine since the library is a bit new to me, but after testing this against a bunch of different scenarios I’m pretty confident this will work for 99% of my needs and maybe a high percent of yours as well.

Here is the function in all it’s glory, I thought about modify the NPOI source but since I’m not sure where it’s going I figured I’d just add this in my own little NPOI.CustomHelpers class that I can use with my NPOI project.

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

}

The code comments above should give you a good idea of what I’m doing, if something doesn’t make sense just ask. The key things I wanted to make sure got copied were; Cell Style, Cell Value, Cell Type, Merged Cell Settings. In the end I noticed a few other things that I thought I might use in the future, so I included them as well. Here is an example of how to call to CopyRow along with a snapshot of the end result.

// 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);
NPOI – Copy Row Helper Function Test Results