NPOI number format

Question

I'm using NPOI library to create excel file, I have problem with formatting price.

                ISheet excelSheet = workbook.CreateSheet(sheetName);

                ICellStyle codeCellStyle = workbook.CreateCellStyle();
                ICellStyle priceCellStyle = workbook.CreateCellStyle();
                ICellStyle availabilityStyle = workbook.CreateCellStyle();

                excelSheet.SetColumnWidth(0, 10 * 256);
                excelSheet.SetColumnWidth(1, 12 * 256);
                excelSheet.SetColumnWidth(2, 15 * 256);

                List<string> columns = new List<string>() { "Code", "Price", "Availability" };
                IRow row = excelSheet.CreateRow(0);

                foreach (var columnData in columns.Select((v, i) => new { Column = v, Index = i }).ToList())
                {
                    row.CreateCell(columnData.Index).SetCellValue(columnData.Column);
                }

                codeCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("@");
                priceCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00");
                availabilityStyle.DataFormat = workbook.CreateDataFormat().GetFormat("@");

                int rowIndex = 1;

                foreach (var item in items)
                {
                    row = excelSheet.CreateRow(rowIndex);
                    row.CreateCell(0).SetCellValue(item.Code);
                    row.Cells[0].CellStyle = codeCellStyle;

                    if (item.HasData)
                    {
                        if (item.Price == "ON DEMAND")
                        {
                            row.CreateCell(1).SetCellValue(item.Price);
                        }
                        else
                        {
                            ICell priceCell = row.CreateCell(1);

                            row.Cells[1].SetCellType(CellType.Numeric);
                            row.Cells[1].CellStyle = priceCellStyle;

                            if (percentage != 0)
                                priceCell.SetCellValue(double.Parse(item.Price) + ((double)percentage / (double)100) * double.Parse(item.Price));
                            else
                                priceCell.SetCellValue(double.Parse(item.Price));
                        }
                        row.CreateCell(2).SetCellValue(item.Availability ? "True" : "False");
                    }
                    else
                    {
                        row.CreateCell(1).SetCellValue("");
                        row.CreateCell(2).SetCellValue("");
                    }
                    rowIndex++;
                }

When I have item with price "422,26" it produce excel file with price value 42226,00. It works fine when I have price "380,00", "4730,00", but problem occurs when I have decimal part in string number.

I tried other suggestions from stackoverflow but I have no luck nothing works on my example.

Answer 1

After struggling this format solved my problem

"#,#0.00"