NPOI GetFormat or GetBuiltinFormat

Question

While applying CellStyle to a cell using GetFormat or GetBuiltinFormat for "#,#0" , the same is not getting rendered in the cell. Am I missing something?

For example the number rendered on cell should show up as 10,000 while on click the same the formula bar should show up 10000

var sheetLabelCellStyle = hssfworkbook.CreateCellStyle();  
sheetLabelCellStyle.Alignment = HorizontalAlignment.Right;  
sheetLabelCellStyle.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("#,##0");  
.  
.  
ICell cell = row.CreateCell(j,CellType.Numeric);  
.  
.  
System.Decimal objVal = Convert.ToDecimal(dt.Rows[i][columnName].ToString());  
columnValue = objVal.ToString("#,#0", CultureInfo.InvariantCulture);  
cell.SetCellValue(columnValue);  
cell.CellStyle = sheetLabelCellStyle;

Presently it is rendering as 10000 and in formula bar also its showing the same. While the desired cell content is 10,000 and in formula bar it should be 10000

If I remove the objVal part converting it to invariant culture, then it shows up as 10000.00 in both cell and formula bar

score 2 · Answer 1 · answered Jun 08 '16 at 05:22

Converting the cellvalue to double type rather than a string before assigning using SetCellValue resolved the problem.

ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = hssfworkbook.GetCreationHelper().CreateDataFormat().GetFormat("#,##0");
cellStyle.Alignment = HorizontalAlignment.Right;  
.  
.  
System.Decimal objVal = Convert.ToDecimal(dt.Rows[i][columnName].ToString());
.  
.  
cell.SetCellValue((Double)objVal);
cell.SetCellType(CellType.Numeric);
cell.CellStyle = cellStyle;

Assigning it as a string was causing the desired format to be lost.

NPOI GetFormat or GetBuiltinFormat

1 Answers1