Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel Date column returning INT using EPPlus

Tags:

c#

excel

epplus

So i'm using EPPlus to read and write excel documents.

Workflow

  • User generates populated excel document
  • Opens document and adds a row
  • Uploaded and read

The dates that are generated when I create the document using EPPlus show correctly when I'm reading the value back but the row the user changes the date one or adds is showing as an INT value not something I can use as a real date.

When I enter the date 1/01/2014 and write it, the output when I open the file up shows 41640

I'm reading it as follows

sheet.Cells[i, "AE".ConvertExcelColumnIndex()].Value != null      ? sheet.Cells[i, "AE".ConvertExcelColumnIndex()].Value.ToString().Trim()          : string.Empty 

Update

When exporting the file I have added the following

DateTime testDate;  if (DateTime.TryParse(split[i], out testDate)) {     sheet.Cells[row, i + 1].Style.Numberformat.Format = "MM/dd/yyyy";     sheet.Cells[row, i + 1].Value = testDate.ToString("MM/dd/yyyy"); } 

Also when reading the value back I have tried

sheet.Cells[i, "AE".ConvertExcelColumnIndex()].Style.Numberformat.Format = "MM/dd/yyy"; 

I still get an INT back

like image 850
Tsukasa Avatar asked Jul 24 '14 12:07

Tsukasa


People also ask

How do I change the date format on EPPlus?

Format = "yyyy-mm-dd"; ws. Cells[3, 1]. Value = new DateTime(2014,10,5); ws.

Does EPPlus work with XLS?

EPPlus does not work with the XLS format. Only XLSX. You'll need to find a new library.

What is the use of EPPlus?

EPPlus is a very helpful open-source 3rd party DLL for writing data to excel. EPPlus supports multiple properties of spreadsheets like cell ranges, cell styling, charts, pictures, shapes, comments, tables, protection, encryption, pivot tables, data validation, conditional formatting, formula calculation, etc.


2 Answers

...when I need to read that excel file, the only dates that are incorrect are the ones the user has changed

So when you read the modified excel-sheet, the modified dates are numbers whereas the unchanged values are strings in your date-format?

You could get the DateTime via DateTime.FromOADate:

long dateNum = long.Parse(worksheet.Cells[row, column].Value.ToString()); DateTime result = DateTime.FromOADate(dateNum); 

With your sample-number:

Console.Write(DateTime.FromOADate(41640)); // ->  01/01/2014  
like image 134
Tim Schmelter Avatar answered Sep 25 '22 08:09

Tim Schmelter


I stumbled upon this issue today when trying to generate some Excel documents from some ASP.NET DataTables: I had no problem with strings, but ran into few issues with numeric types (int, doubles, decimals) and DataTables, which were formatted as string or as numeric representations (OADate).

Here's the solution I eventually managed to pull off:

if (dc.DataType == typeof(DateTime)) {     if (!r.IsNull(dc))     {         ws.SetValue(row, col, (DateTime)r[dc]);         // Change the following line if you need a different DateTime format         var dtFormat = "dd/MM/yyyy";         ws.Cells[row, col].Style.Numberformat.Format = dtFormat;     }     else ws.SetValue(row, col, null); } 

Apparently, the trick was to set the value as DateTime and then configure the proper Style.Numberformat.Formataccordingly.

I published the full code sample (DataTable to Excel file with EPPlus) in this post on my blog.

like image 37
Darkseal Avatar answered Sep 22 '22 08:09

Darkseal