Cells.LoadFromCollection not putting in correct dates

May 21, 2015 at 9:34 PM
Edited May 21, 2015 at 9:35 PM
Hi,
I am trying to put a collection of EmployeeBenefits in an Excel Sheet like this:
using (var pck = new ExcelPackage())
{
var fixedBenefitQuery = from fb in fullRequest.EmployeeFixedBenefits ?? new Collection<EmployeeFixedBenefit>() orderby fb.AdoptionAgreementEffectiveFromDate ascending, fb.AdoptionAgreementSequenceNumber ascending
                                        select new 
                                        {
                                            CalculationId = calculationId,
                                            Ssn = fb.Ssn,
                                            fb.CoopNumber,
                                            fb.EmployerNumber,
                                            fb.PlanCode,
                                            fb.AdoptionAgreementEffectiveFromDate,                                            
                                            fb.AdoptionAgreementSequenceNumber,
                                            fb.AdoptionAgreementStartDate,                                            
                                            fb.FixedBenefitFormulaNumber,
                                            fb.FixedBenefitPercentage,
                                            fb.PredecessorPlanNormalFormFixedBenefitAmount,
                                            fb.RSNormalFormFixedBenefitAmount
                                            
                                        };
                var employeeFixedBenefits = fixedBenefitQuery.ToArray();
var employeeFixedBenefitWs = pck.Workbook.Worksheets.Add(WorksheetName.EmployeeFixedBenefits);
employeeFixedBenefitWs.Cells["A1"].LoadFromCollection(employeeFixedBenefits, true);
                    
}
The dates in AdoptionAgreementEffectiveFromDate and AdoptionAgreementStartDate is '1/1/1900 12:00:00 AM', but in Excel, I get both dates as '2'.


After formatting into Number-format (did not find DateTime format) as below, my Excel sheet showed a date, but this time as '01/02/1900' - which is 1 day ahead of my input date.
 ws.Cells[1, head.Start.Column, ws.Cells.End.Row, head.Start.Column].Style.Numberformat.Format = "mm/dd/yyyy";
Any suggestions on why this is happening and remedy?
May 22, 2015 at 1:20 PM
It is my understanding that there are two issues affecting 01/01/1900 dates. The first is that EPPLUS returns dates as a string if a standard Date format is not used. The second is the old LOTUS 1900 Leap Year shortcut that reports an incorrect date offset for any dates preceding 3/1/1900. I have used the following logic to solve these two issues:
                                    try
                                    {
                                        // For 01/01/1900, EPPLUS returns "12/31/1899 12:00:00 AM" as a string instead of the Date Offset as a Double
                                        strvalue = namedRange.Worksheet.Cells[rowIndex, 84].Value.ToString( );

                                        // Non-Standard Dates will come out as text; Standard dates will come out as Double
                                        if (strvalue.Contains("AM") || strvalue.Contains("PM"))
                                        {
                                            if (strvalue == "12/31/1899 12:00:00 AM")
                                                dtevalue = new DateTime(1900, 1, 1);
                                            else
                                                dtevalue = DateTime.Parse(strvalue);
                                        }
                                        else
                                            dtevalue = FromExcelSerialDate(double.Parse(strvalue));
                                        ....
                                    }
                                    catch (Exception ex)
                                    {
                                        .... report error;
                                    }


Here is the code for "FromExcelSerialDate"

        public static DateTime FromExcelSerialDate(double SerialDate)
        {
            if (SerialDate > 59)
                SerialDate -= 1;   // Excel/Lotus 2/29/1900 bug
            return new DateTime(1899, 12, 31).AddDays(SerialDate);
        }