void TestTextFunctions::testCONCATENATE()
{
    CHECK_EVAL("CONCATENATE(\"Hi \"; \"there\")", Value("Hi there"));
    CHECK_EVAL("CONCATENATE(\"A\"; \"B\"; \"C\")", Value("ABC"));
    CHECK_EVAL("CONCATENATE(2;3)", Value("23"));
    CHECK_EVAL("CONCATENATE(23)", Value("23"));
}
void TestTextFunctions::testBAHTTEXT()
{
    Value r;
    r = evaluate("BAHTTEXT(23)", r);
    CHECK_EVAL("BAHTTEXT(23)", r);
    CHECK_EVAL("COM.MICROSOFT.BAHTTEXT(23)", r);
}
void TestInformationFunctions::testISERR()
{
    CHECK_EVAL("ISERR(1/0)",      Value(true));      // Error values other than NA() return true.
    CHECK_EVAL("ISERR(NA())",     Value(false));     // NA() does NOT return True.
    CHECK_EVAL("ISERR(\"#N/A\")", Value(false));     // Text is not an error.
    CHECK_EVAL("ISERR(1)",        Value(false));     // Numbers are not an error.
}
void TestInformationFunctions::testISBLANK()
{
    CHECK_EVAL("ISBLANK(1)",    Value(false));     // Numbers return false.
    CHECK_EVAL("ISBLANK(\"\")", Value(false));     // Text, even empty string, returns false.
    CHECK_EVAL("ISBLANK(B8)",   Value(true));      // Blank cell is true.
    CHECK_EVAL("ISBLANK(B7)",   Value(false));     // Non-blank cell is false.
}
Exemple #5
0
void TestDatetimeFunctions::testSECOND()
{
  //
  CHECK_EVAL( "SECOND(1/(24*60*60))",   Value( 1 ) );    // This is one second into today.
  CHECK_EVAL( "SECOND(1/(24*60*60*2))", Value( 1 ) );    // Rounds.
  CHECK_EVAL( "SECOND(1/(24*60*60*4))", Value( 0 ) );    // Rounds.
}
void TestInformationFunctions::testISLOGICAL()
{
    CHECK_EVAL("ISLOGICAL(TRUE())",   Value(true));      // Logical values return true.
    CHECK_EVAL("ISLOGICAL(FALSE())",  Value(true));      // Logical values return true.
    CHECK_EVAL("ISLOGICAL(\"TRUE\")", Value(false));     // Text values are not logicals,
    // even if they can be converted.
}
Exemple #7
0
void TestDatetimeFunctions::testMINUTE()
{
  //
  CHECK_EVAL( "MINUTE(1/(24*60))",         Value( 1 ) ); // 1 minute is 1/(24*60) of a day.
  CHECK_EVAL( "MINUTE(TODAY()+1/(24*60))", Value( 1 ) ); // If you start with today, and add a minute, you get a minute.
  CHECK_EVAL( "MINUTE(1/24)",              Value( 0 ) ); // At the beginning of the hour, we have 0 minutes.
}
void TestInformationFunctions::testNA()
{
    CHECK_EVAL("ISERROR(NA())", Value(true));     // NA is an error.
    CHECK_EVAL("ISNA(NA())",    Value(true));     // Obviously, if this doesn't work, NA() or ISNA() is broken.
    CHECK_EVAL("ISNA(5+NA())",  Value(true));     // NA propagates through various functions
    // and operators, just like any other error type.
}
void TestInformationFunctions::testSHEET()
{
    CHECK_EVAL("SHEET(B7)", Value(1));
    CHECK_EVAL("SHEET(Sheet2!B7)", Value(2));
    CHECK_EVAL("SHEET(Sheet3!B7)", Value(3));
    CHECK_EVAL("SHEET()", Value(1));
}
void TestTextFunctions::testROT13()
{
    CHECK_EVAL("ROT13(\"KSpread\")", Value("XFcernq"));
    CHECK_EVAL("ROT13(\"XFcernq\")", Value("KSpread"));
    CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.DATEFUNCTIONS.GETROT13(\"KSpread\")", Value("XFcernq"));
    CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.DATEFUNCTIONS.GETROT13(\"XFcernq\")", Value("KSpread"));
}
void TestInformationFunctions::testAREAS()
{
    CHECK_EVAL("AREAS(B3)",          Value(1));     // A reference to a single cell has one area
    CHECK_EVAL("AREAS(B3:C4)",       Value(1));     // A reference to a single range has one area
// concatenation is not supported yet
//    CHECK_EVAL( "AREAS(B3:C4~D5:D6)", Value( 2 ) ); // Cell concatenation creates multiple areas
//    CHECK_EVAL( "AREAS(B3:C4~B3)",    Value( 2 ) ); // Cell concatenation counts, even if the cells are duplicated
}
Exemple #12
0
void TestDatetimeFunctions::testWORKDAY()
{
  // 2001 JAN 01 02 03 04 05 06 07 08
  //          MO TU WE TH FR SA SU MO
  //          01 02 -- --  
  CHECK_EVAL( "WORKDAY(DATE(2001;01;01);2;2)=DATE(2001;01;05)", Value( true ) );
  CHECK_EVAL( "WORKDAY(DATE(2001;01;01);2;3)=DATE(2001;01;08)", Value( true ) );
}
void TestInformationFunctions::testCOUNTIF()
{
    CHECK_EVAL("COUNTIF(B4:B5;\">2.5\")", Value(1));           // B4 is 2 and B5 is 3, so there is one cell in the range with a value greater than 2.5.
    CHECK_EVAL("COUNTIF(B3:B5;B4)",       Value(1));           // Test if a cell equals the value in [.B4].
    CHECK_EVAL("COUNTIF(\"\";B4)",        Value::errorNA());   // Constant values are not allowed for the range.
    CHECK_EVAL("COUNTIF(B3:B10;\"7\")",   Value(1));           // [.B3] is the string "7".
    CHECK_EVAL("COUNTIF(B3:B10;1+1)",     Value(1));           // The criteria can be an expression.
}
void TestInformationFunctions::testISNA()
{
    CHECK_EVAL("ISNA(1/0)",      Value(false));     // Error values other than NA() return False - the error does not propagate.
    CHECK_EVAL("ISNA(NA())",     Value(true));      // By definition
    // CHECK_EVAL( "ISNA(#N/A)",     Value( true  ) ); // By definition
    CHECK_EVAL("ISNA(\"#N/A\")", Value(false));     // Text is not NA
    CHECK_EVAL("ISNA(1)",        Value(false));     // Numbers are not NA
}
void TestInformationFunctions::testISREF()
{
    CHECK_EVAL("ISREF(B3)",     Value(true));        //
    CHECK_EVAL("ISREF(B3:C4)",  Value(true));        // The range operator produces references
    CHECK_EVAL("ISREF(1)",      Value(false));       // Numbers are not references
    CHECK_EVAL("ISREF(\"A1\")", Value(false));       // Text is not a reference, even if it looks a little like one
    CHECK_EVAL("ISREF(NA())",   Value::errorNA());   // Errors propagate through this function
}
void TestInformationFunctions::testFORMULA()
{
    CHECK_EVAL( "FORMULA(B1)", Value( "=SUM(A19:A31)" ) ); // B1 contains a simple SUM formula
    CHECK_EVAL( "FORMULA(B2)", Value::errorNA() ); // Empty formula means no formula
    CHECK_EVAL( "FORMULA(B3)", Value::errorNA() ); // Cell constants are not formulas

    CHECK_EVAL( "LEN(FORMULA(B1))>0", Value( true ) ); // B7 is a formula, so this is fine and will produce a text value
}
Exemple #17
0
void TestDatetimeFunctions::testDAYS360()
{
  // TODO Note: Lotus 1-2-3v9.8 has a function named DAYS but with different semantics.  It supports an optional "Basis" parameter
  // with many different options.  Without the optional parameter, it defaults to a 30/360 basis, not calendar days; thus, in Lotus 1-2-3v9.8,
  // DAYS(DATE(1993;4;16);  DATE(1993;9;25)) computes -159, not -162. 

  CHECK_EVAL( "DAYS360(DATE(1993;4;16);DATE(1993;9;25); FALSE)", Value( 159 ) ); // specs. -162 but OOo and KSpread calculate 159
  CHECK_EVAL( "DAYS360(\"2002-02-22\"; \"2002-04-21\" ; FALSE)", Value(  59 ) ); // ref. docs
}
void TestTextFunctions::testUNICODE()
{
    QChar euro(8364);

    CHECK_EVAL("UNICODE(\"A\")", Value(65));
    CHECK_EVAL("UNICODE(\"AB€C\")", Value(65));
    CHECK_EVAL(QString("UNICODE(\"%1\")").arg(euro), Value(8364));
    CHECK_EVAL(QString("UNICODE(\"%1F\")").arg(euro), Value(8364));
}
void TestInformationFunctions::testISERROR()
{
    CHECK_EVAL("ISERROR(1/0)",      Value(true));      // Error values return true.
    CHECK_EVAL("ISERROR(NA())",     Value(true));      // Even NA().
    CHECK_EVAL("ISERROR(\"#N/A\")", Value(false));     // Text is not an error.
    CHECK_EVAL("ISERROR(1)",        Value(false));     // Numbers are not an error.
    CHECK_EVAL("ISERROR(CHOOSE(0; \"Apple\"; \"Orange\";"
               " \"Grape\"; \"Perry\"))", Value(true));    // If CHOOSE given
    // out-of-range value, ISERROR needs to capture it.
}
void TestInformationFunctions::testCOLUMN()
{
    CHECK_EVAL("COLUMN(B7)",       Value(2));     // Column "B" is column number 2.
//    CHECK_EVAL( "COLUMN()",         Value( 5 ) ); // Column of current cell is default, here formula in column E.

    Value res(Value::Array);
    res.setElement(0, 0, Value(2));
    res.setElement(1, 0, Value(3));
    res.setElement(2, 0, Value(4));
    CHECK_EVAL("COLUMN(B2:D2)", res);   // Array with column numbers.
}
// TODO row not working here
void TestInformationFunctions::testROW()
{
    CHECK_EVAL("ROW(B7)", Value(7));     // The second value of a cell reference is the row number.
//     CHECK_EVAL( "ROW()",   Value( 5 ) ); // Row of current cell is default, here formula in row 5.

    Value res(Value::Array);
    res.setElement(0, 0, Value(2));
    res.setElement(0, 1, Value(3));
    res.setElement(0, 2, Value(4));

    CHECK_EVAL("ROW(B2:B4)", res);      // Array with row numbers.
}
void TestInformationFunctions::testSHEETS()
{
    CHECK_EVAL( "SHEETS(B7)",  Value( 1 ) ); // If given, the sheet number of the reference is used.
    CHECK_EVAL( "SHEETS(Sheet1!B7:C9)",  Value( 1 ) );
    CHECK_EVAL( "SHEETS(Sheet1!A7:Sheet1!C9)",  Value( 1 ) );

    //TODO this should not fail! :-(
    //CHECK_EVAL( "SHEETS(Sheet1!B7:Sheet2!C9)",  Value( 2 ) );
    //CHECK_EVAL( "SHEETS(Sheet1!B7:Sheet3!C9)",  Value( 2 ) );
    //CHECK_EVAL( "SHEETS(Sheet1!A7:Sheet3!C9)",  Value( 3 ) );

    CHECK_EVAL( "SHEETS()", Value( 3 ) ); // Count all sheets
}
void TestInformationFunctions::testTYPE()
{
    //  Value's Type | Type return
    // --------------+-------------
    //     Number    |     1
    //     Text      |     2
    //     Logical   |     4
    //     Error     |    16
    //     Array     |    64

    CHECK_EVAL("TYPE(1+2)",              Value(1));      // Number has TYPE code of 1
    CHECK_EVAL("TYPE(\"Hi\"&\"there\")", Value(2));      // Text has TYPE 2
    CHECK_EVAL("TYPE(NA())",             Value(16));     // Errors have TYPE 16.
}
void TestTextFunctions::testEXACT()
{
    CHECK_EVAL("EXACT(\"A\";\"A\")",  Value(true));
    CHECK_EVAL("EXACT(\"A\";\"a\")",  Value(false));
    CHECK_EVAL("EXACT(1;1)",  Value(true));
    CHECK_EVAL("EXACT((1/3)*3;1)",  Value(true));
    CHECK_EVAL("EXACT(TRUE();TRUE())",  Value(true));
    CHECK_EVAL("EXACT(\"1\";2)",  Value(false));
    CHECK_EVAL("EXACT(\"h\";1)",  Value(false));
    CHECK_EVAL("EXACT(\"1\";1)",  Value(true));
    CHECK_EVAL("EXACT(\" 1\";1)",  Value(false));
}
void TestTextFunctions::testRIGHT()
{
    CHECK_EVAL("RIGHT(\"Hello\";2)", Value("lo"));
    CHECK_EVAL("RIGHT(\"Hello\")", Value("o"));
    CHECK_EVAL("RIGHT(\"Hello\";20)", Value("Hello"));
    CHECK_EVAL("RIGHT(\"Hello\";0)", Value(""));
    CHECK_EVAL("RIGHT(\"\";4)", Value(""));
    CHECK_EVAL("RIGHT(\"xxx\";-1)", Value::errorVALUE());
    CHECK_EVAL("RIGHT(\"xxx\";-0.1)", Value::errorVALUE());
    CHECK_EVAL("RIGHT(\"Hello\";2^15-1)", Value("Hello"));
    CHECK_EVAL("RIGHT(\"Hello\";2.9)", Value("lo"));
}
Exemple #26
0
void TestDatetimeFunctions::testWEEKDAY()
{
  //    | type 1 |  type 2 | type 3
  // ---+--------+---------+--------
  // 01 |  SU    |   MO    |  TU
  // 02 |  MO    |   TU    |  WE
  // 03 |  TU    |   WE    |  TH
  // 04 |  WE    |   TH    |  FR
  // 05 |  TH    |   FR    |  SA
  // 06 |  FR    |   SA    |  SU
  // 07 |  SA    |   SU    |  MO

  CHECK_EVAL( "WEEKDAY(DATE(2006;05;21))",   Value( 1 ) ); // Year-month-date format
  CHECK_EVAL( "WEEKDAY(DATE(2005;01;01))",   Value( 7 ) ); // Saturday 
  CHECK_EVAL( "WEEKDAY(DATE(2005;01;01);1)", Value( 7 ) ); // Saturday
  CHECK_EVAL( "WEEKDAY(DATE(2005;01;01);2)", Value( 6 ) ); // Saturday
  CHECK_EVAL( "WEEKDAY(DATE(2005;01;01);3)", Value( 5 ) ); // Saturday
}
void TestTextFunctions::testFIND()
{
    CHECK_EVAL("FIND(\"b\";\"abcabc\")", Value(2));
    CHECK_EVAL("FIND(\"b\";\"abcabcabc\"; 3)", Value(5));
    CHECK_EVAL("FIND(\"b\";\"ABC\";1)", Value::errorVALUE());
    CHECK_EVAL("FIND(\"b\";\"bbbb\")", Value(1));
    CHECK_EVAL("FIND(\"b\";\"bbbb\";2)", Value(2));
    CHECK_EVAL("FIND(\"b\";\"bbbb\";2.9)", Value(2));
    CHECK_EVAL("FIND(\"b\";\"bbbb\";0)", Value::errorVALUE());
    CHECK_EVAL("FIND(\"b\";\"bbbb\";0.9)", Value::errorVALUE());
}
void TestTextFunctions::testREPT()
{
    CHECK_EVAL("REPT(\"X\";3)",  Value("XXX"));
    CHECK_EVAL("REPT(\"XY\";2)",  Value("XYXY"));
    CHECK_EVAL("REPT(\"X\";2.9)",  Value("XX"));
    CHECK_EVAL("REPT(\"XY\";2.9)",  Value("XYXY"));
    CHECK_EVAL("REPT(\"X\";0)",  Value(""));
    CHECK_EVAL("REPT(\"XYZ\";0)",  Value(""));
    CHECK_EVAL("REPT(\"X\";-1)",  Value::errorVALUE());
    CHECK_EVAL("REPT(\"XYZ\";-0.1)",  Value::errorVALUE());
}
Exemple #29
0
void TestDatetimeFunctions::testHOUR()
{
  //
  CHECK_EVAL( "HOUR(5/24)",              Value(  5 ) ); // 5/24ths of a day is 5 hours, aka 5AM.
  CHECK_EVAL( "HOUR(5/24-1/(24*60*60))", Value(  4 ) ); // A second before 5AM, it's 4AM.
  CHECK_EVAL( "HOUR(\"14:00\")",         Value( 14 ) ); // TimeParam accepts text
  CHECK_EVAL( "HOUR(\"9:00\")",          Value(  9 ) );
  CHECK_EVAL( "HOUR(\"09:00\")",         Value(  9 ) );
  CHECK_EVAL( "HOUR(\"23:00\")",         Value( 23 ) );
  CHECK_EVAL( "HOUR(\"11:00 PM\")",      Value( 23 ) );
  CHECK_EVAL( "HOUR(\"11:00 AM\")",      Value( 11 ) );
}
Exemple #30
0
void TestDatetimeFunctions::testNETWORKDAY()
{
  // 2001 JAN 01 02 03 04 05 06 07 08 09
  //          MO TU WE TH FR SA SU MO TU
  //             01 02 03 04 05 05 05 06 ... networkdays
  CHECK_EVAL( "NETWORKDAY(DATE(2001;01;01);DATE(2001;01;08))", Value( 5 ) );
  CHECK_EVAL( "NETWORKDAY(DATE(2001;01;01);DATE(2001;01;07))", Value( 5 ) );
  CHECK_EVAL( "NETWORKDAY(DATE(2001;01;01);DATE(2001;01;06))", Value( 5 ) );
  CHECK_EVAL( "NETWORKDAY(DATE(2001;01;01);DATE(2001;01;05))", Value( 4 ) );

  // 2008 FEB 25 26 27 28 29 01 02 03 04
  //          MO TU WE TH FR SA SU MO TU
  //             01 02 03 04 05 05 05 06 ... networkdays
  CHECK_EVAL( "NETWORKDAY(DATE(2008;02;25);DATE(2008;02;28))", Value( 3 ) );
  CHECK_EVAL( "NETWORKDAY(DATE(2008;02;25);DATE(2008;02;29))", Value( 4 ) );
  CHECK_EVAL( "NETWORKDAY(DATE(2008;02;25);DATE(2008;03;01))", Value( 5 ) );
  CHECK_EVAL( "NETWORKDAY(DATE(2008;02;25);DATE(2008;03;02))", Value( 5 ) );
  CHECK_EVAL( "NETWORKDAY(DATE(2008;02;25);DATE(2008;03;03))", Value( 5 ) );
  CHECK_EVAL( "NETWORKDAY(DATE(2008;02;25);DATE(2008;03;04))", Value( 6 ) );
}