Support for Evaluation of Functions in Formulas

Aspose.Tasks for Java API supports evaluating functions defined as formula expression in Extended Attribute. These include the calculation of Math, General, Text, and DateTime functions.

Calculation of Math Expressions

1. Abs( number )
2. Atn( number )
3. Cos( number )
4. Exp( number )
5. Fix( number )
6. Int( number )
7. Log( number )
8. Rnd( number )
9. Sgn( number )
10. Sin( number )
11. Sqr( number )
12. Tan( number )

Project project = CreateTestProjectWithCustomField();
        
// Set formula Sin(pi/2)
project.getExtendedAttributes().get(0).setFormula("Sin(3.1415926/2)");

// Print Calculated value
Task task = project.getRootTask().getChildren().getById(1);
System.out.println("Sin(pi/2): {0}" + task.getExtendedAttributes().get(0).getNumericValue());

Calculation of General Functions

The following General functions can be calculated by the API.

1. Choose( index, choice-1, choice-2, … , choice-n|, choice-n)
2. IIf( expr, truepart, falsepart )
3. IsNumeric( expression)
4. IsNull( expression )
5. Switch( expr-1, value-1, expr-2, value-2, … , expr-n,value-n|, expr-n,value-n )

public static void EvaluateChoose()
{    
    Project project = CreateTestProjectWithCustomField();
    
    // Set Formula
    project.getExtendedAttributes().get(0).setFormula("Choose(3, \"This is a\", \"right\", \"choice\")");
    
    // Print extended attribute value
    Task task = project.getRootTask().getChildren().getById(1);
    System.out.println(task.getExtendedAttributes().get(0).getTextValue());
}

public static void EvaluateIsNumeric()
{
    String[] numericFormulas = { "IsNumeric('AAA')", "IsNUmeric(1)", "IsNumeric(1<0)", "IsNumeric(\"1.1\")", "IsNumeric(Choose((2 + Sgn(2^-3)), 123, \"one two three\"))" };
    
    Project project = CreateTestProjectWithCustomField();
    
    for (String numericFormula : numericFormulas)    
    {
        // Set Formula
        project.getExtendedAttributes().get(0).setFormula(numericFormula);

        // Print extended attribute value
        Task task = project.getRootTask().getChildren().getById(1);
        System.out.println(task.getExtendedAttributes().get(0).getTextValue());
    }           
}

public static void EvaluateSwitch()
{    
    Project project = CreateTestProjectWithCustomField();

    // Set Formula
    project.getExtendedAttributes().get(0).setFormula("Switch( 0 < 1, \"0 is lesser than 1\", 0 > 1, \"0 is greater than 1\")");

    // Print extended attribute value
    Task task = project.getRootTask().getChildren().getById(1);
    System.out.println(task.getExtendedAttributes().get(0).getTextValue());
}    

Calculation of Text Functions

1. Asc( string )
2. Chr( charcode )
3. Format( expression, format, firstdayofweek, firstweekofyear)
4. Instr( start,string1, string2, compare )
5. LCase( string )
6. Left( string, length )
7. Len( string )
8. LTrim( string )
9. Mid( string, start, length )
10. Right( string, length )
11. RTrim( string )
12. Space( number )
13. StrComp( string1, string2, compare )
14. StrConv( string, conversion, LCID )
15. String( number, character )
16. Trim( string )
17. UCase( string )

Project project = CreateTestProjectWithCustomField();
Task task = project.getRootTask().getChildren().getById(1);

// EvaluateStrConv
// Set formulas and print extended attribute value
project.getExtendedAttributes().get(0).setFormula("StrConv(\"sTring and sTRINg\",3)");
System.out.println(task.getExtendedAttributes().get(0).getTextValue());
        
project.getExtendedAttributes().get(0).setFormula("StrConv(\"sTring and sTRINg\",1)");
System.out.println(task.getExtendedAttributes().get(0).getTextValue());
        
project.getExtendedAttributes().get(0).setFormula("StrConv(\"sTring and sTRINg\",2)");
System.out.println(task.getExtendedAttributes().get(0).getTextValue());
   
// EvaluateStringFunction
// Set formulas and print extended attribute value
project.getExtendedAttributes().get(0).setFormula("String(5, 40)");
System.out.println(task.getExtendedAttributes().get(0).getTextValue());
        
project.getExtendedAttributes().get(0).setFormula("String(5, \"A\")");
System.out.println(task.getExtendedAttributes().get(0).getTextValue());
        
project.getExtendedAttributes().get(0).setFormula("String(-5, \"A\")");
// #Error
System.out.println(task.getExtendedAttributes().get(0).getTextValue());

Calculation of Date/Time Functions

1. CDate( expression )
2. Date ()
3. DateAdd( interval, number, date )
4. DateDiff( interval, date1, date2, firstdayofweek, firstweekofyear )
5. DatePart( interval, date, firstdayofweek, firstweekofyear)
6. DateSerial( year, month, day )
7. DateValue( date)
8. Day( date)
9. Hour( time )
10. IsDate( expression )
11. Minute( time)
12. Month( date)
13. Now ()
14. ProjDateAdd( date, duration, calendar )
15. ProjDateConv( expression, dateformat )
16. ProjDateDiff( date1, date2, calendar )
17. ProjDateSub( date, duration, calendar )
18. ProjDateValue( expression )
19. ProjDurConv( expression, durationunits )
20. ProjDurValue( duration )
21. Second( time )
22. Time ()
23. Timer ()
24. TimeSerial( hour, minute, second)
25. TimeValue( time)
26. Weekday( date, firstdayofweek )
27. Year( date)

Project project = CreateTestProjectWithCustomField();
Task task = project.getRootTask().getChildren().getById(1);

ExtendedAttributeDefinition numberDefinition = ExtendedAttributeDefinition.createTaskDefinition(ExtendedAttributeTask.Number1, null);
project.getExtendedAttributes().add(numberDefinition);

ExtendedAttribute numberAttribute = numberDefinition.createExtendedAttribute();
task.getExtendedAttributes().add(numberAttribute);

// Set ProjDateDiff formula and print extended attribute value
numberDefinition.setFormula("ProjDateDiff(\"03/23/2015\",\"03/18/2015\")");
System.out.println(task.getExtendedAttributes().get(0).getNumericValue());
        
numberDefinition.setFormula("ProjDateDiff(\"03/23/2015\",\"03/25/2015\")");
System.out.println(task.getExtendedAttributes().get(0).getNumericValue());

ExtendedAttributeDefinition dateDefinition = ExtendedAttributeDefinition.createTaskDefinition(ExtendedAttributeTask.Date1, null);
project.getExtendedAttributes().add(dateDefinition);
ExtendedAttribute dateAttribute = dateDefinition.createExtendedAttribute();
task.getExtendedAttributes().add(dateAttribute);

ExtendedAttributeDefinition durationDefinition = ExtendedAttributeDefinition.createTaskDefinition(ExtendedAttributeTask.Duration4, "Custom duration field");
project.getExtendedAttributes().add(durationDefinition);
ExtendedAttribute durationAttribute = durationDefinition.createExtendedAttribute();
task.getExtendedAttributes().add(durationAttribute);

ExtendedAttributeDefinition textDefinition = ExtendedAttributeDefinition.createTaskDefinition(ExtendedAttributeTask.Text5, "Custom text field");
project.getExtendedAttributes().add(textDefinition);
ExtendedAttribute textAttribute = textDefinition.createExtendedAttribute();
task.getExtendedAttributes().add(textAttribute);

// Set ProjDateSub formula and print extended attribute value
dateDefinition.setFormula("ProjDateSub(\"3/19/2015\", \"1d\")");
System.out.println(dateAttribute.getDateValue());

// We can set ProjDurConv formula to duration-valued attribute as well as to text-valued attribute.

// Set ProjDurConv formula to duration-valued extended attribute and print its value.
durationDefinition.setFormula("ProjDurConv([Duration], pjHours)");
System.out.println(durationAttribute.getDurationValue());

// Set ProjDurConv formula to text-valued extended attribute and print its value.
textDefinition.setFormula("ProjDurConv([Duration], pjHours)");
System.out.println(task.getExtendedAttributes().get(0).getTextValue());

textDefinition.setFormula("ProjDurConv([Duration], pjWeeks)");
System.out.println(task.getExtendedAttributes().get(0).getTextValue());

// Set Second formula and print entended attribute value
numberDefinition.setFormula("Second(\"4/21/2015 2:53:41 AM\")");
System.out.println(task.getExtendedAttributes().get(0).getNumericValue());

// Set Weekday formula and print entended attribute value
numberDefinition.setFormula("Weekday(\"24/3/2015\", 1)");
System.out.println(task.getExtendedAttributes().get(0).getNumericValue());
        
numberDefinition.setFormula("Weekday(\"24/3/2015\", 2)");
System.out.println(task.getExtendedAttributes().get(0).getNumericValue());
        
numberDefinition.setFormula("Weekday(\"24/3/2015\", 3)");
System.out.println(task.getExtendedAttributes().get(0).getNumericValue());