EasyXLS
http://forum.easyxls.com/

Returning Date Values
http://forum.easyxls.com/viewtopic.php?f=5&t=137		 Page 1 of 1
Author:  jpaterson [ Mon Jan 14, 2008 10:36 am ]
Post subject:  Returning Date Values
I am relatively new to EasyXLS and would like to see a code sample of returning a value from a formula cell that is a date. I am coding in VS2005 using C#. This is probably simple. When I call the getFormulaResultValue() function, I get back a value like "39203" (string). I need to convert this to a .NET DateTime value.
Author:  daniela [ Tue Jan 15, 2008 1:09 pm ]
Post subject: 
Microsoft Excel always considers the dates as numbers. The difference between numbers and dates is given by the number/date format applied to the cell. Like:
xlsTable.easy_getCell("A1").setFormat("MM/dd/yyyy")

You can also format a formula using the TEXT function:
xlsTable.easy_getCell("A1").setValue("=TEXT(A2, \"MM/dd/yyyy\")")
where instead of A2 you can have any formula or cell reference.
Author:  daniela [ Tue Jan 15, 2008 1:23 pm ]
Post subject: 
This functions transforms the date number into a datetime:

public DateTime GetDate(double date)
{
try
{
int nTotalDays = (int)date;
int nMiliseconds = (int)Math.Round((date - nTotalDays)*86400000);
int nHour = nMiliseconds/( 60/*minutes*/*60/*seconds*/*1000 );
nMiliseconds = nMiliseconds - nHour*60/*minutes*/*60/*seconds*/*1000;
int nMin = nMiliseconds/( 60/*seconds*/*1000 );
nMiliseconds = nMiliseconds - nMin*60/*seconds*/*1000;
int nSec = nMiliseconds/1000;
int nYear = 1900;
while (nTotalDays > ( (( (nYear%4 == 0 && nYear%100 != 0) || nYear%400 == 0 ) || nYear==1900)?366:365))
{
if ( ((nYear%4 == 0 && nYear%100 != 0) || nYear%400 == 0) || nYear==1900)//bisect year
{
nTotalDays -= 366;
}
else
{
nTotalDays -= 365;
}
nYear++;
}

//because Excel consider 1900 as a leap year 60 = 02/29/1900; ther is no such a dat in java or C# so we should consider this day as 02/28/1900
if(nTotalDays == 60 && nYear == 1900)
{
nTotalDays = nTotalDays - 1;
}

bool isBisectYear = ((nYear%4 == 0 && nYear%100 != 0) || nYear%400 == 0) || nYear==1900;
int nMonth = 0;
bool bMonthFound = false;
for (nMonth=1; nMonth<12> 31) nTotalDays -= 31;//January else bMonthFound = true;
break;
case 2: if (nTotalDays > (isBisectYear?29:28)) nTotalDays -= isBisectYear?29:28;//February else bMonthFound = true;
break;
case 3: if (nTotalDays > 31) nTotalDays -= 31;//March
else bMonthFound = true;
break;
case 4: if (nTotalDays > 30)
nTotalDays -= 30;//April
else bMonthFound = true;
break;
case 5: if (nTotalDays > 31)
nTotalDays -= 31;//May
else bMonthFound = true;
break;
case 6: if (nTotalDays > 30) nTotalDays -= 30;//June
else bMonthFound = true;
break;
case 7: if (nTotalDays > 31)
nTotalDays -= 31;//July
else bMonthFound = true;
break;
case 8: if (nTotalDays > 31)
nTotalDays -= 31;//August
else bMonthFound = true;
break;
case 9: if (nTotalDays > 30)
nTotalDays -= 30;//September
else bMonthFound = true;
break;
case 10: if (nTotalDays > 31)
nTotalDays -= 31;//October
else bMonthFound = true;
break;
case 11: if (nTotalDays > 30)
nTotalDays -= 30;//November
else bMonthFound = true;
break;
case 12: if (nTotalDays > 31)
nTotalDays -= 31;//December
else bMonthFound = true;
break;
}
}
nMonth = nMonth - 1;
int nDay = nTotalDays;
if (nDay == 0)//if it is the last day of the year
{
nDay = 31;
nMonth = 1;
nYear = nYear - 1;
}

return new DateTime( nYear, nMonth, nDay, nHour, nMin, nSec);
}
catch (Exception x)
{
Console.WriteLine(x.Message);
return new DateTime(0,0,0);
}
}
Page 1 of 1 All times are UTC - 4 hours
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/