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.
}
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.
}
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
}
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
}
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"));
}
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());
}
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 ) );
}
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 ) );
}