View unanswered posts | View active topics
It is currently Sun Nov 10, 2024 3:41 pm
|
Page 1 of 1
|
[ 3 posts ] |
|
Author |
Message |
jpaterson
Joined: Mon Jan 14, 2008 10:29 am Posts: 1
|
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 |
|
|
daniela
Joined: Fri Feb 03, 2006 12:23 pm Posts: 197 Location: Brasov, Romania
|
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 |
|
|
daniela
Joined: Fri Feb 03, 2006 12:23 pm Posts: 197 Location: Brasov, Romania
|
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 |
|
|
|
Page 1 of 1
|
[ 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
|
|