Issue with writing a date to Excel file using NPOI

Question

I am currently using NPOI to generate Excel files based on a database from my asp.net mvc app. I'm almost done with the code, except one small issue which I keep getting when when trying to write Dates to Excel.

In the database (MS SQL Server) I have the date saved as 41883, 41913 etc ... which in C# I can convert to a DataTime object using

DateTime dt = DateTime.FromOADate(Convert.ToDouble(41883));

The code that I use to write the Date to Excel looks lie this:

var cell = excelRow.CreateCell(columnIndex);
IDataFormat format = workbook.CreateDataFormat();
short dateFormat = format.GetFormat("dd/MM/yyyy");
cell.SetCellValue(DateTime.FromOADate(Convert.ToDouble(dbDateValue)));
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.DataFormat = dateFormat;
cell.CellStyle = cellStyle;

this is just a sample of my code (the piece which is doing the date writing part). The issue with this piece of code is that only part of the date cells are actually formatted as a date, for all the others I still see the values as in the database 41883, 41913 etc which of course I can select and apply Short Date/Date formatting from Excel (but I don't want that).

Could anyone let me know why such a behavior could appear (formatting works only for part of the cells)... I even tried to use the HSSFDataFormat.GetBuiltinFormat("Date") but none of the cells were formatted in that case.

The image above explains better my issue... when I select the first cells in the first column I see the cell is formatted as "Custom"... for all the other values which are not formatted it's General. Once I select the cells I can format it as date from Excel without any problem. This is weird as the same code is executed for all the date cells, but only some get the proper formatting...

Answer 1

No need to convert anything. You have to create a style and apply it to your cell

        var newDataFormat = workbook.CreateDataFormat();
        var style = workbook.CreateCellStyle();
        style.BorderBottom = BorderStyle.Thin;
        style.BorderLeft = BorderStyle.Thin;
        style.BorderTop = BorderStyle.Thin;
        style.BorderRight = BorderStyle.Thin;
        style.DataFormat = newDataFormat.GetFormat("MM/dd/yyyy HH:mm:ss");

        foreach (var objArticles in tempArticles)
        {
            //Create a new Row
            var row = sheet.CreateRow(rowNumber++);

            //Set the Values for Cells
            row.CreateCell(0).SetCellValue(objArticles.ProjectId);
            row.CreateCell(1).SetCellValue(objArticles.ProjectName);
            row.CreateCell(2).SetCellValue(objArticles.MetricDescription);
            row.CreateCell(3).SetCellValue(objArticles.MetricValue);             
            var cell = row.CreateCell(4);
            cell.SetCellValue(objArticles.BuildDate);
            cell.CellStyle = style; 
            var cell5 = row.CreateCell(5);
            cell5.SetCellValue(objArticles.CreateDate);
            cell5.CellStyle = style;
        }