I am trying to read a date value in Excel into a DateTime datatype in C#. I am not sure how to take the Cell value (which returns as a number) and convert it properly to the correct date.
In Excel I have a cell value as 1-Oct-2013 with the Cell format as Custom -> d-mmm-yyyy.
When I look at the Cell[row,col].Styles.NumberFormat.Format value in debugger I see "\\ d\\-mmm\\-yyyy", which is what I expected.
When I try to DateTime.Parse the Cell[row,col].Value.ToString() on any date value including the one above, I get the result 1/01/0001 12:00:00AM. I was expecting 10/1/2013 12:00:00AM ( the date as found in Excel).
Here is my code (I am looping through every cell from start to finish and putting each row's values in a List<string>):
using (ExcelPackage package = new ExcelPackage(_currentFile))
ExcelWorksheet worksheet = package.Workbook.Worksheets[worksheetIndex];
for (rowIndex = startAtRow; rowIndex < totalRows; rowIndex++)
List<string> record = new List<string>();
//read whole row
for (int columnIndex = 1; columnIndex <= totalColumns; columnIndex++)
string format = worksheet.Cells[rowIndex, columnIndex].Style.Numberformat.Format;
if ( format != "General" && !format.Contains("#") && !format.Contains(".") && !format.Contains("0"))
//it's a custom date in this file
DateTime.TryParse(worksheet.Cells[rowIndex, columnIndex].Value.ToString(),out result); //doesn't give the right answer
}else if (worksheet.Cells[rowIndex, columnIndex].Value != null)
record.Add(""); //add blank string for null cell value
Also, if there is a better way to determine when the format indicates a date type, please let me know, the way I have it now is not coded very well I'm sure.