Using NPOI, how do I return a cell value as it has been formatted by Excel?
10,766
Found the NPOI built in possibility. However some formats like "Sunday, September 18, 1983" are evaluated like "EEEE, September 18, 1983".
using NPOI.SS.UserModel;
DataFormatter dataFormatter = new DataFormatter(CultureInfo.CurrentCulture);
ICell cell = workbook.GetSheet("table1").GetRow(0).GetCell(0);
string value = dataFormatter.FormatCellValue(cell);
Author by
Samuel
Updated on June 08, 2022Comments
-
Samuel almost 2 years
Using NPOI, is there any buildin possibility to format a cell value (especially numeric and date values) as it has been formatted by Excel?
If not what would be the best way to implement it? I thought of a formatstring converter from Excel-formatstrings to C#-formatstrings?
The following example assumes the Excel-formatstring and the C#-formatstring are the same. So it works for some basic formatstrings like: "#,##0.00"
using NPOI.SS.UserModel; ICell cell = workbook.GetSheet("table1").GetRow(0).GetCell(0); string value = null; if(cell.CellType == CellType.String) { value = cell.StringCellValue; } else if(cell.CellType == CellType.Numeric) { string formatString = cell.CellStyle.GetDataFormatString(); if(DateUtil.IsCellDateFormatted(cell)) { value = cell.DateCellValue.ToString(formatString); } else { value = cell.NumericCellValue.ToString(formatString); } } else [...]