I saw a discussion posting on the NPOI discussion forum on CodePlex today, asking if there was a function like SetCell(X,Y,Value) in NPOI. Unfortunately there isn’t… At least I was never able to find one, so I created my own. Since I only needed to set values I added three basic ones into my helper class to make available to everybody using NPOI in my project. I was really tempted to add these to the HSSFWorksheet, but to keep my code save I figured a helper class for all my extras would be enough. Here is my version of a SetCellValue() helper set of functions.

private static void SetCellValue(HSSFSheet worksheet, int columnPosition, int rowPosition, DateTime value)
{
	// Get row
	using (HSSFRow row = worksheet.GetRow(rowPosition))
	{
		// Get or Create Cell
		using (HSSFCell cell = row.GetCell(columnPosition) ?? row.CreateCell(columnPosition))
		{
			cell.SetCellValue(value);
			cell.CellStyle.DataFormat = 14;
		}
	}
}

private static void SetCellValue(HSSFSheet worksheet, int columnPosition, int rowPosition, double value)
{
	// Get row
	using (HSSFRow row = worksheet.GetRow(rowPosition))
	{
		// Get or Create Cell
		using (HSSFCell cell = row.GetCell(columnPosition) ?? row.CreateCell(columnPosition))
		{
			cell.SetCellValue(value);
		}
	}
}

private static void SetCellValue(HSSFSheet worksheet, int columnPosition, int rowPosition, string value)
{
	// Get row
	using (HSSFRow row = worksheet.GetRow(rowPosition))
	{
		// Get or Create Cell
		using (HSSFCell cell = row.GetCell(columnPosition) ?? row.CreateCell(columnPosition))
		{
			cell.SetCellValue(value);
		}
	}
}

// Set Date
SetCellValue(sheet, 9, 3, DateTime.Now);
// Set Number
SetCellValue(sheet, 9, 4, 100.01);
// Set Text
SetCellValue(sheet, 9, 5, "Zach Roxs!");

As you can see it’s pretty easy to create a SetCellValue() helper. I plan to create another version of these that uses Excel coordinates (e.g. A5, Z10, etc…), so my die hard Excel teammates can use their native Excel mapping syntax!