Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Issue with writing a date to Excel file using NPOI

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.

enter image description here

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...

like image 750
AndreiC Avatar asked Oct 23 '14 13:10

AndreiC


People also ask

How do I format a date in Excel in Java?

Creating A Simple Date FormatString pattern = "yyyy-MM-dd" ; SimpleDateFormat simpleDateFormat = new SimpleDateFormat(pattern); The specified parameter “pattern” is the pattern used for formatting and parsing dates.

How do I change the date format in Excel poi?

To create date cell which display current date to the cell, we can use Apache POI's style feature to format the cell according to the date. The setDateformat() method is used to set date format. Lets see an example in which we are creating a cell containing current date.

How do I set cell value to date and apply default Excel date format?

In an Excel sheet, select the cells you want to format. Press Ctrl+1 to open the Format Cells dialog. On the Number tab, select Custom from the Category list and type the date format you want in the Type box. Click OK to save the changes.

What is Npoi in Excel?

NPOI is an open source project which can help you read/write XLS, DOC, PPT file extensions. This tool is the .NET version of POI Java project (http://poi.apache.org/).


1 Answers

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;
        }
like image 145
Gaston Paolo Avatar answered Oct 22 '22 13:10

Gaston Paolo