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!
