NPOI Excel number format not showing in Excel sheet in asp.net

Question

I am trying to create double and number format cells in excel using NPOI library. I used code like

Dim cell As HSSFCell = row.CreateCell(j)
cell.SetCellValue(Double.Parse(dr(col).ToString))

In excel numbers are aligning right but when I check format it is showing in "General"

then I changed my code to like below

 Dim cell As HSSFCell = row.CreateCell(j)
 cell.SetCellValue(Double.Parse(dr(col).ToString))
 Dim cellStyle As HSSFCellStyle = hssfworkbook.CreateCellStyle
 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,#0.0")
 cell.CellStyle = cellStyle

Then While opening file it is giving error and also taking so long to open. But Excel format showing in "Number"

error showing is like below.

How to fix this?

Answer 1

Take a look at this, are you creating a cellStyle object for each cell? If so don't. Try creating just a couple of styles before creating your cells and then apply these pre-defined styles to the cells you create.

Answer 2

Take a look at this post comment to see how to create a CellStyle using NPOI:

http://www.leniel.net/2009/07/creating-excel-spreadsheets-xls-xlsx-c.html#c5492160556746101054