Background
I have an Excel-file (xlxs) with a number of datetime and decimals that I want to convert into a two-dimensional string-array. The strings in the array should look exactly as the user has entered them into the excel. To do this I'm using NPOI version 2.2.1 with C#.
Note that column A in the example is formatted as a date in Excel!
COLUMN A COLUMN B
2016-07-20 -46,95
2016-07-20 283,59
2016-07-20 -46,95
2016-07-20 52194,64
I have a generic method ConvertCellToString() that converts a cell into it's proper string representation:
private string GetCellAsString(ICell cell)
{
switch (cell.CellType)
{
case CellType.Boolean:
return cell.BooleanCellValue.ToString();
case CellType.Error:
return cell.ErrorCellValue.ToString();
case CellType.Formula:
return cell.CellFormula.ToString();
//ALL CELLS IN THE EXEMPEL ARE NUMERIC AND GOES HERE
case CellType.Numeric:
return cell.NumericCellValue.ToString();
case CellType.String:
return cell.StringCellValue.ToString();
case CellType.Blank:
case CellType.Unknown:
default:
return "";
}
}
However, since even the datetime cells are numeric cells, this solution results in all the dates below being incorrectly represented as numbers rather than dates ("21672" instead of "2016-07-20" etc)
The method below needs to distinguish numeric cells with datetimes vs those with numbers. Is there any way of doing this with NPOI? I'd rather not resort to parsing the string, especially since NPIOs cell.ToString() returns the horrible "2016-jan-20" format if it's a date.
I'm pretty much stuck here so help would be greatly appreciated! There must be some obvious best practice for this that I just can't find!
Turns out the DateUtil.IsCellDateFormatted() can be used as such:
case CellType.Numeric:
{
return DateUtil.IsCellDateFormatted(cell)
? cell.DateCellValue.ToString()
: cell.NumericCellValue.ToString();
}