This project is read-only.

How to treat DateTime fields as string fields?

Jun 22, 2010 at 9:34 PM
I have a field that contains what NPOI or Excel is treating as a DateTime type. However, I want to simply access that field as the string that is represented. Is this possible?
Jun 23, 2010 at 4:25 AM
I'm not following you, are you saying you have a worksheet with a column containing DateTime values that you want to convert to a string values? If you know you have a DateTime column in your source data, you can do a DateTime.Parse() in c# and then use the built-in/custom ToString() methods to convert the field to a text string (e.g. value.ToShortDate(), value.ToString("yyyyMMdd"), etc...).
Jun 23, 2010 at 2:32 PM
Almost, yes. The column doesn't actually have DateTime values though. They are running times, such as 01:23:45 (hours, minutes, seconds), that are being interpreted as DateTime. What I want to do is get the value as it is in the Excel column.
Jun 24, 2010 at 8:43 PM
Edited Jun 24, 2010 at 8:43 PM

CM,

If you have an Excel spread sheet with "HH:MM:SS" and you want to read/write the values , you should read in cell as DateTime and use the properties of the TimeOfDay to pull your values and write your cell using using Numeric with the value of TotalDays from the TimeOfDay properties.

Example:

Time = 01:30:05

TimeOfDay
    Days: 0
    Hours: 1
    Milliseconds: 0
    Minutes: 30
    Seconds: 5
    Ticks: 54050000000
    TotalDays: 0.062557870370370361
    TotalHours: 1.5013888888888889
    TotalMilliseconds: 5405000.0
    TotalMinutes: 90.083333333333329
    TotalSeconds: 5405.0

** The default date on a time cell in Excel 2007 is 12/31/1899, you should use this same date when you create a new DatTime object.

Here is a example of doing a read & write

HSSFSheet sheet = workbook.GetSheet(Sheets.SelectedValue);
HSSFRow detailRow1 = sheet.GetRow(1);

Double read = detailRow1.GetCell(0).NumericCellValue;

DateTime write = new DateTime(1899, 12, 31, 12, 00, 00); //New Time of 12:00:00

detailRow1.GetCell(0).SetCellValue(write.TimeOfDay.TotalDays);


 

Jun 24, 2010 at 8:51 PM

Just one last comment, if all you want is the value out of Excel in the exact same format of "HH:MM;SS", then you can call ToString() on the cell.

Time = 01:30:05

String read = detailRow1.GetCell(0).ToString(); // returns 01:30:05

Jun 24, 2010 at 9:06 PM
Knowing the default date is 12/31/1899 makes a ton of sense now. I have a value of "28:26:00" in a cell, and doing .ToString() on that cell returns ":26:00". However, looking at the DateTime value it shows 1/1/1900 4:26:00 AM. I can deduce the text-based value by subtracting the default date from that. Thank you very much.