Reply to topic  [ 3 posts ] 
Returning Date Values 
Author Message

Joined: Mon Jan 14, 2008 10:29 am
Posts: 1
Reply with quote
Post 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.


Mon Jan 14, 2008 10:36 am
Profile

Joined: Fri Feb 03, 2006 12:23 pm
Posts: 197
Location: Brasov, Romania
Reply with quote
Post 
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.


Tue Jan 15, 2008 1:09 pm
Profile WWW

Joined: Fri Feb 03, 2006 12:23 pm
Posts: 197
Location: Brasov, Romania
Reply with quote
Post 
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);
}
}


Tue Jan 15, 2008 1:23 pm
Profile WWW
Display posts from previous:  Sort by  
Reply to topic   [ 3 posts ] 

Who is online

Users browsing this forum: No registered users and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group.
Designed by STSoftware for PTF.