CODEWITHSUNDEEP
.netexcelepplus
Carl Decks
Carl Decks

Reputation: 415

EPPlus returns number instead of date/string

I'm trying to read a .xlsx file using EPPlus.

I can load the worksheet and read all cells except for the cells holding dates which returns a decimal number instead of the value in the cell.

FileInfo fileInfo = new FileInfo(filePath);

using (ExcelPackage excel = new ExcelPackage(fileInfo))
{
    var worksheet = excel.Workbook.Worksheets.First();

    string cellName = worksheet.Cells[i, colDate].ToString(); // = "A2"- correct cell
    string cellValue= worksheet.Cells[i, colDate].Value.ToString(); //  = "42874.6666666667"
}

Expected returned value for Cell["A2"] of that sheet is "5/19/2017 4:00:00 PM" but I recieve "42874.6666666667".

Getting the number value in the cell next to that one works fine so I load the right file and sheet.

How can I get the date of that cell (or at least the string and parse it)?

Upvotes: 0

Views: 691

Answers (3)

user1390375
user1390375

Reputation: 736

apply one of the named styles to the cell with the value in it.

Excel really has only 2 (2.5?) types of cell data:

  1. Strings
    • 0-255 chars long
    • more than 255 chars long (aka "memo")
  2. Floating point numbers ("integers" are a style...)

So Dates are really a special case of floating point numbers, with a style applied to them ("ShortDate", "mmm dd yyyy", or whatever).

applying a style to a cell determines how it's presented, and yes, EPPlus has the Excel built-in styles already set up to use in its style dictionary.

The EPPlus way of applying styles to a cell is very similar to how you'd do it in VBA:

'''VBA example, using built-in styles''' If Worksheets("Sheet1").Range("B4").Style = "Normal" Then Worksheets("Sheet1").Range("B4").Style = "Percent" End If

Somewhere in MSDN there's a list of all the enums/names/definitions of the built-in Excel styles for C#/VBA programmers. These styles are also set up in the EPPlus style dictionary.

You can also add your own style definitions to the style dictionary in your EPPlus code. Any custom styles previously defined in a .XLSX file will be available when you open the .XLSX file in EPPlus, too.

Upvotes: 0

Demir Karic
Demir Karic

Reputation: 147

You need to use MemoryStream

  byte[] bin = File.ReadAllBytes(file);
            using (MemoryStream stream = new MemoryStream(bin))
            {
                using (ExcelPackage excel = new ExcelPackage(stream))
                {
                    var worksheet = excel.Workbook.Worksheets.First();
                    string cellValue = worksheet.Cells[2, 1].Value.ToString(); // A2 Cell value
                }
            }

Upvotes: 0

user4913118
user4913118

Reputation: 105

Excel stores dates as the number of days since 1st January 1900 (or maybe 1904 on Mac), Using some sort of time library in your programming language, create the a date 1st January 1900 and add whatever value you have in each cell - in your example - 42874.6666666667 days. This should, in theory, give you the date in the cell.

Upvotes: 1

Related Questions