How to read in DateTime in Excel Interop C#
There yo go bud. I modified the code for you a little to reflect the usage of the helper conversion function. The conversion happens in this line: DateTime dt = DateTime.Parse(ConvertToDateTime(dtString)); Feel free to modify the helper function as needed to return a DateTime variable instead of a string, but this is essentially the same thing. Hope this helps!
public void addTime(Microsoft.Office.Interop.Excel.Workbook workbook)
{
Excel.Worksheet ws = (Excel.Worksheet)workbook.Worksheets.get_Item("Time Series");
Excel.Range range = ws.UsedRange;
int num = 0;
for (int row = 1; row <= range.Rows.Count; row++ )
{
String dtString = ((Excel.Range)ws.Cells[row, "C"]).Value2.ToString();
DateTime dt = DateTime.Parse(ConvertToDateTime(dtString));
this.addEdgeInstance(dt);
}
}
public static string ConvertToDateTime(string strExcelDate)
{
double excelDate;
try
{
excelDate = Convert.ToDouble(strExcelDate);
}
catch
{
return strExcelDate;
}
if (excelDate < 1)
{
throw new ArgumentException("Excel dates cannot be smaller than 0.");
}
DateTime dateOfReference = new DateTime(1900, 1, 1);
if (excelDate > 60d)
{
excelDate = excelDate - 2;
}
else
{
excelDate = excelDate - 1;
}
return dateOfReference.AddDays(excelDate).ToShortDateString();
}
WildBill
Updated on June 13, 2022Comments
-
WildBill almost 2 years
Possible Duplicate:
Reading Datetime value From Excel sheetI'm trying to read in a DateTime value from an Excel spreadsheet using Interop in C#. I have all times in the 'C' column of my sheet. My code is as follows:
public void addTime(Microsoft.Office.Interop.Excel.Workbook workbook) { Excel.Worksheet ws = (Excel.Worksheet)workbook.Worksheets.get_Item("Time Series"); Excel.Range range = ws.UsedRange; int num = 0; for (int row = 1; row <= range.Rows.Count; row++ ) { String dtString = ((Excel.Range)ws.Cells[row, "C"]).Value2.ToString(); DateTime dt = Convert.ToDateTime(dtString); this.addEdgeInstance(dt); } }
Yet this doesn't read in the time. Reading of the string works, but the Convert function does not work. Do I have read in values a different way? Can the values vary? (IE: can I enter '11/11' or '11/11/2011' as Excel recognizes both of these entries as valie DateTime values when working in Excel?)