Excel Date column returning INT using EPPlus

Question

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

Provide a format string: `var range = sheet.Cells[dataLineStart, columnNumber, dataLineEnd, columnNumber]; range.Style.Numberformat.Format = "M/dd/yyyy"`. — Tim Schmelter, Jul 24 '14 at 12:46
@TimSchmelter when I write the file and it opens in Excel, all of the dates show correctly but when I need to read that excel file, the only dates that are incorrect are the ones the user has changed. — Tsukasa, Jul 24 '14 at 13:55

score 53 · Accepted Answer · answered Jul 24 '14 at 13:59

53

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

answered Jul 24 '14 at 13:59

Tim Schmelter

450,073
74
686
939

3

@Tim Schmelter what if I am looping through excel columns and I don't know which column has Date value and I need to convert it to `DateTime` usig `FromOADate`? – mmushtaq Dec 26 '17 at 09:06
If the field contains a datetime value then amend the code to use a double, e.g. double dateNum = double.Parse(worksheet.Cells[row, column].Value.ToString()); – SteveP Oct 10 '18 at 15:17
4

Or you can also do this `worksheet.cells[row,column].text` – Allen King Oct 15 '18 at 03:26

score 3 · Answer 2 · answered Jan 12 '22 at 07:20

You should try using

  string dateFromExcel = workSheet.Cells[row, col].Text.ToString();
  DateTime localdt;
  if (DateTime.TryParse(dateFromExcel, out localdt))
  {
    dateFromExcel = localdt.ToString("MM/dd/yyyy");
  };

the Value reads the value in the general formatting while Text reads the value as it is from the excel with applied formatting.

score 1 · Answer 3 · answered Apr 01 '19 at 08:08

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.

score 0 · Answer 4 · answered Nov 25 '20 at 17:11

you could check if the cell format is in date format, then parse it to date

var cell = worksheet.Cells[row, col];
  value = cell.Value.ToString();
  if (cell.Style.Numberformat.Format == "[$-409]d\\-mmm\\-yy;@")
  {
 string inputString = DateTime.FromOADate(long.Parse(value.ToString())).ToString("dd-MMM-yyyy");
 
 }

score -2 · Answer 5 · answered Nov 24 '15 at 13:07

You can also change the 'NumberFormatLocal' property. This worked for me. If you format the Excel file before improting it using EPPLUS.

The following basic example of code formats column A in a typical excel file.

Sub ChangeExcelColumnFormat()

Dim ExcelApp As Excel.Application
Dim ExcelWB As Excel.Workbook
Dim ExcelWS As Excel.Worksheet
Dim formatRange As Excel.Range

Dim strFile As String = "C:\Test.xlsx"
Dim strSheetname As String = "Sheet1"


ExcelApp = New Excel.Application
ExcelWB = ExcelApp.Workbooks.Open(strFile)

    strColSelect = "A:A"
    strFormat = "dd/mm/yyyy"

    formatRange = ExcelWS.Range(strColSelect)
    formatRange.NumberFormatLocal = strFormat

ExcelWB.Save()
ExcelWB.Close()
ExcelApp.Quit()

ExcelWS = Nothing
ExcelWB = Nothing
ExcelApp = Nothing

End Sub

Excel Date column returning INT using EPPlus

5 Answers5

Linked