Importing Excel data into SQL Server using EPPlus

Question

I am writing a C# code in .NET 6 to read data from Excel and save it into the SQL Server database.

When reading the column with date / date time value, I realized that if the cell contains a time portion, EPPlus will return the cell value as 9/1/2000 8:00:00 AM, but if the cell value only contains date, EPPlus will return the value as an integer, eg: 39580.

I would like to know if it is possible to standardize the return of both cell values to return in integer/double?

My code for reading the value from Excel:

for (int row = 2; row <= sheet.Dimension.Rows; row++) 
{
    var obj = Activator.CreateInstance(typeof (T)); //generic object

    foreach(var prop in typeof (T).GetProperties()) 
    {
        if (!string.IsNullOrWhiteSpace(prop.Name)) 
        {
            var column = columnInfo.SingleOrDefault(c => !string.IsNullOrWhiteSpace(c.ColumnName) && c.ColumnName.Trim() == prop.Name.Trim());

            if (column != null) 
            {
                int col = column.Index;
                var val = sheet.Cells[row, col].Value;
                var propType = prop.PropertyType;
                string ? strVal = "";

                if (val != null) 
                {
                    strVal = val.ToString();

                    if (!string.IsNullOrWhiteSpace(strVal)) 
                    {
                        strVal = strVal.Trim();
                    }
                }

                prop.SetValue(obj, Convert.ChangeType(strVal, propType));
            }
        }
    }

    if (obj != null) 
    {
        list.Add((T) obj);
    }
}

Answer 1

Yes, using the EP Plus library and C#, it is possible to standardize the return of both cell values to an integer or a double. A DateTime object can be converted to an OLE Automation date, which is a double value that indicates the number of days from midnight on December 30, 1899, by using the DateTime.ToOADate method. Here's a better way:

using (var package = new ExcelPackage(file))
{
    var worksheet = package.Workbook.Worksheets[1];
    for (int row = 1; row <= worksheet.Dimension.End.Row; row++)
    {
        for (int col = 1; col <= worksheet.Dimension.End.Column; col++)
        {
            var cell = worksheet.Cells[row, col];
            if (cell.Value != null)
            {
                if (cell.Value is DateTime)
                {
                    var dateValue = (DateTime)cell.Value;
                    var doubleValue = dateValue.ToOADate();
                }
                else
                {
                    var intValue = (int)cell.Value;
                }
            }
        }
    }
}

overall<< DateTime, will be converted to a double using ToOADate, and if the cell value is an integer, it will be cast to an int.