// Function: REGEXP
Value func_regexp(valVector args, ValueCalc *calc, FuncExtra *)
{
// ensure that we got a valid regular expression
QRegExp exp(calc->conv()->asString(args[1]).asString());
if (!exp.isValid())
return Value::errorVALUE();
QString s = calc->conv()->asString(args[0]).asString();
QString defText;
if (args.count() > 2)
defText = calc->conv()->asString(args[2]).asString();
int bkref = 0;
if (args.count() == 4)
bkref = calc->conv()->asInteger(args[3]).asInteger();
if (bkref < 0) // strange back-reference
return Value::errorVALUE();
QString returnValue;
int pos = exp.indexIn(s);
if (pos == -1)
returnValue = defText;
else
returnValue = exp.cap(bkref);
return Value(returnValue);
}
//
// Function: MULTIPLE.OPERATIONS
//
Value func_multiple_operations(valVector args, ValueCalc *, FuncExtra *e)
{
if (args.count() != 3 && args.count() != 5)
return Value::errorVALUE(); // invalid number of parameters
for (int i = 0; i < args.count(); i++) {
if (e->ranges[i].col1 == -1 || e->ranges[i].row1 == -1)
return Value::errorVALUE();
}
CellStorage *s = e->sheet->cellStorage();
// get formula to evaluate
int formulaCol = e->ranges[0].col1;
int formulaRow = e->ranges[0].row1;
Formula formula = s->formula(formulaCol, formulaRow);
if (!formula.isValid())
return Value::errorVALUE();
CellIndirection cellIndirections;
cellIndirections.insert(Cell(e->sheet, e->ranges[1].col1, e->ranges[1].row1), Cell(e->sheet, e->ranges[2].col1, e->ranges[2].row1));
if (args.count() > 3) {
cellIndirections.insert(Cell(e->sheet, e->ranges[3].col1, e->ranges[3].row1), Cell(e->sheet, e->ranges[4].col1, e->ranges[4].row1));
}
return formula.eval(cellIndirections);
}
Value Function::exec(valVector args, ValueCalc *calc, FuncExtra *extra)
{
// check number of parameters
if (!paramCountOkay(args.count()))
return Value::errorVALUE();
if (extra)
extra->function = this;
// do we need to perform array expansion ?
bool mustExpandArray = false;
if (!d->acceptArray)
for (int i = 0; i < args.count(); ++i) {
if (args[i].isArray())
mustExpandArray = true;
}
if (!d->ptr) return Value::errorVALUE();
// perform the actual array expansion if need be
if (mustExpandArray) {
// compute number of rows/cols of the result
int rows = 0;
int cols = 0;
for (int i = 0; i < args.count(); ++i) {
int x = 1;
if (extra) x = extra->ranges[i].rows();
if (x > rows) rows = x;
if (extra) x = extra->ranges[i].columns();
if (x > cols) cols = x;
}
// allocate the resulting array
Value res(Value::Array);
// perform the actual computation for each element of the array
for (int row = 0; row < rows; ++row)
for (int col = 0; col < cols; ++col) {
// fill in the parameter vector
valVector vals(args.count());
FuncExtra extra2 = *extra;
for (int i = 0; i < args.count(); ++i) {
int r = extra->ranges[i].rows();
int c = extra->ranges[i].columns();
vals[i] = args[i].isArray() ?
args[i].element(col % c, row % r) : args[i];
// adjust the FuncExtra structure to refer to the correct cells
extra2.ranges[i].col1 += col;
extra2.ranges[i].row1 += row;
extra2.ranges[i].col2 = extra2.ranges[i].col1;
extra2.ranges[i].row2 = extra2.ranges[i].row1;
}
// execute the function on each element
res.setElement(col, row, exec(vals, calc, &extra2));
}
return res;
} else
// call the function
return (*d->ptr)(args, calc, extra);
}
//
// Function: BASE
//
Value func_base(valVector args, ValueCalc *calc, FuncExtra *)
{
int base = 10;
int minLength = 0;
if (args.count() > 1)
base = calc->conv()->asInteger(args[1]).asInteger();
if (args.count() == 3)
minLength = calc->conv()->asInteger(args[2]).asInteger();
if ((base < 2) || (base > 36))
return Value::errorVALUE();
if (minLength < 0) minLength = 2;
return calc->base(args[0], base, 0, minLength);
}
//
// Function: VLOOKUP
//
Value func_vlookup(valVector args, ValueCalc *calc, FuncExtra *)
{
const Value key = args[0];
const Value data = args[1];
const int col = calc->conv()->asInteger(args[2]).asInteger();
const int cols = data.columns();
const int rows = data.rows();
if (col < 1 || col > cols)
return Value::errorVALUE();
const bool rangeLookup = (args.count() > 3) ? calc->conv()->asBoolean(args[3]).asBoolean() : true;
// now traverse the array and perform comparison
Value r;
Value v = Value::errorNA();
for (int row = 0; row < rows; ++row) {
// search in the first column
const Value le = data.element(0, row);
if (calc->naturalEqual(key, le)) {
return data.element(col - 1, row);
}
// optionally look for the next largest value that is less than key
if (rangeLookup && calc->naturalLower(le, key) && calc->naturalLower(r, le)) {
r = le;
v = data.element(col - 1, row);
}
}
return v;
}
//
// Function: MATCH
//
Value func_match(valVector args, ValueCalc *calc, FuncExtra* e)
{
int matchType = 1;
if (args.count() == 3) {
bool ok = true;
matchType = calc->conv()->asInteger(args[2], &ok).asInteger();
if (!ok)
return Value::errorVALUE(); // invalid matchtype
}
const Value& searchValue = args[0];
const Value& searchArray = args[1];
if (e->ranges[1].rows() != 1 && e->ranges[1].columns() != 1)
return Value::errorNA();
int dr = 1, dc = 0;
if (searchArray.columns() != 1) {
dr = 0;
dc = 1;
}
int n = qMax(searchArray.rows(), searchArray.columns());
if (matchType == 0) {
// linear search
for (int r = 0, c = 0; r < n && c < n; r += dr, c += dc) {
if (calc->naturalEqual(searchValue, searchArray.element(c, r), false)) {
return Value(qMax(r, c) + 1);
}
}
return Value::errorNA();
} else if (matchType > 0) {
// binary search
int l = -1;
int h = n;
while (l+1 < h) {
int m = (l+h)/2;
if (calc->naturalLequal(searchArray.element(m*dc, m*dr), searchValue, false)) {
l = m;
} else {
h = m;
}
}
if (l == -1) return Value::errorNA();
return Value(l+1);
} else { /* matchType < 0 */
// binary search
int l = -1;
int h = n;
while (l+1 < h) {
int m = (l+h)/2;
if (calc->naturalGequal(searchArray.element(m*dc, m*dr), searchValue, false)) {
l = m;
} else {
h = m;
}
}
if (l == -1) return Value::errorNA();
return Value(l+1);
}
}
// Function: AsciiToChar
Value func_AsciiToChar(valVector args, ValueCalc *calc, FuncExtra *)
{
QString str;
for (int i = 0; i < args.count(); i++)
func_a2c_helper(calc, str, args[i]);
return Value(str);
}
// Function: SUBSTITUTE
Value func_substitute(valVector args, ValueCalc *calc, FuncExtra *)
{
int occurrence = 1;
bool all = true;
if (args.count() == 4) {
occurrence = calc->conv()->asInteger(args[3]).asInteger();
all = false;
}
QString text = calc->conv()->asString(args[0]).asString();
QString old_text = calc->conv()->asString(args[1]).asString();
QString new_text = calc->conv()->asString(args[2]).asString();
if (occurrence <= 0) return Value::errorVALUE();
if (old_text.length() == 0) return Value(text);
QString result = text;
if (all) {
result.replace(old_text, new_text); // case-sensitive
} else {
// We are only looking to modify a single value, by position.
int position = -1;
for (int i = 0; i < occurrence; ++i) {
position = result.indexOf(old_text, position + 1);
}
result.replace(position, old_text.size(), new_text);
}
return Value(result);
}
// Function: YEARFRAC
//
// | basis | descritption day-count
// -----------|--------|--------------------------------------------------------
// default: | 0 | US (NASD) system. 30 days/month, 360 days/year (30/360)
// | 1 | Actual/actual (Euro), also known as AFB
// | 2 | Actual/360
// | 3 | Actual/365
// | 4 | European 30/360
//
Value func_yearFrac(valVector args, ValueCalc *calc, FuncExtra *)
{
Value v1(calc->conv()->asDate(args[0]));
if (v1.isError()) return v1;
QDate date1 = v1.asDate(calc->settings());
if (!date1.isValid())
return Value::errorVALUE();
Value v2(calc->conv()->asDate(args[1]));
if (v2.isError()) return v2;
QDate date2 = v2.asDate(calc->settings());
if (!date2.isValid())
return Value::errorVALUE();
// check if basis is valid
int basis = 0;
if (args.count() > 2)
basis = calc->conv()->asInteger(args[2]).asInteger();
if (basis < 0 || basis > 4)
return Value::errorVALUE();
QDate date0 = calc->settings()->referenceDate(); // referenceDate
return Value(yearFrac(date0, date1, date2, basis));
}
// Function: WEEKNUM
//
// method startday name of day
// default: 1 0 sunday
// 2 -1 monday
//
// weeknum = (startday + 7 + dayOfWeek of New Year + difference in days) / 7
//
Value func_weekNum(valVector args, ValueCalc *calc, FuncExtra *)
{
Value v(calc->conv()->asDate(args[0]));
if (v.isError()) return v;
QDate date = v.asDate(calc->settings());
if (!date.isValid())
return Value::errorVALUE();
int method = 1;
if (args.count() > 1)
method = calc->conv()->asInteger(args[1]).asInteger();
if (method < 1 || method > 2)
return Value::errorVALUE();
QDate date1(date.year(), 1, 1);
int days = date1.daysTo(date);
int startday = 0;
if (method == 2)
startday = -1;
int res = (int)((startday + 7 + date1.dayOfWeek() + days) / 7);
if (date1.dayOfWeek() == 7 && method == 1)
res--;
//kDebug(36002) <<"weeknum = [startday(" << startday <<") + base(7) + New Year(" << date1.dayOfWeek() <<") + days(" << days <<")] / 7 =" << res;
return Value(res);
}
// Function: MID
Value func_mid(valVector args, ValueCalc *calc, FuncExtra *)
{
QString str = calc->conv()->asString(args[0]).asString();
int pos = calc->conv()->asInteger(args[1]).asInteger();
if (pos < 0) {
return Value::errorVALUE();
}
int len = 0x7fffffff;
if (args.count() == 3) {
len = (uint) calc->conv()->asInteger(args[2]).asInteger();
// the length cannot be less than zero
if (len < 0)
return Value::errorVALUE();
}
// Excel compatible
pos--;
// workaround for Qt bug
if (len > 0x7fffffff - pos) len = 0x7fffffff - pos;
return Value(str.mid(pos, len));
}
// Function: FIXED
Value func_fixed(valVector args, ValueCalc *calc, FuncExtra *)
{
// uses double, hence won't support big precision
int decimals = 2;
bool decimalsIsNegative = false;
bool no_commas = false;
double number = numToDouble(calc->conv()->toFloat(args[0]));
if (args.count() > 1) {
if (args[1].less(Value(0))) {
decimalsIsNegative = true;
decimals = -1 * ((calc->roundUp(args[1])).asInteger());
} else {
decimals = calc->conv()->asInteger(args[1]).asInteger();
}
}
if (args.count() == 3)
no_commas = calc->conv()->asBoolean(args[2]).asBoolean();
QString result;
const KLocale *locale = calc->settings()->locale();
// unfortunately, we can't just use KLocale::formatNumber because
// * if decimals < 0, number is rounded
// * if no_commas is true, thousand separators shouldn't show up
if (decimalsIsNegative) {
number = floor(number / pow(10.0, decimals) + 0.5) * pow(10.0, decimals);
decimals = 0;
}
bool neg = number < 0;
result = QString::number(neg ? -number : number, 'f', decimals);
int pos = result.indexOf('.');
if (pos == -1) pos = result.length();
else result.replace(pos, 1, locale->decimalSymbol());
if (!no_commas)
while (0 < (pos -= 3))
result.insert(pos, locale->thousandsSeparator());
result.prepend(neg ? locale->negativeSign() :
locale->positiveSign());
return Value(result);
}
// Function: CONCATENATE
Value func_concatenate(valVector args, ValueCalc *calc, FuncExtra *)
{
QString tmp;
for (int i = 0; i < args.count(); ++i)
func_concatenate_helper(args[i], calc, tmp);
return Value(tmp);
}
//
// Function: CHOOSE
//
Value func_choose(valVector args, ValueCalc *calc, FuncExtra *)
{
int cnt = args.count() - 1;
int num = calc->conv()->asInteger(args[0]).asInteger();
if ((num <= 0) || (num > cnt))
return Value::errorVALUE();
return args[num];
}
//
// Function: ROW
//
Value func_row(valVector args, ValueCalc *, FuncExtra *e)
{
int row = e ? e->myrow : 0;
if (e && args.count())
row = e->ranges[0].row1;
if (row > 0)
return Value(row);
return Value::errorVALUE();
}
//
// Function: COLUMN
//
Value func_column(valVector args, ValueCalc *, FuncExtra *e)
{
int col = e ? e->mycol : 0;
if (e && args.count())
col = e->ranges[0].col1;
if (col > 0)
return Value(col);
return Value::errorVALUE();
}
//
// Function: DELTA
//
Value func_delta(valVector args, ValueCalc *calc, FuncExtra *)
{
Value val1 = args[0];
Value val2 = Value(0.0);
if (args.count() == 2)
val2 = args[1];
return Value(calc->approxEqual(val1, val2) ? 1 : 0);
}
// Function: DAYS360
// algorithm adapted from gnumeric
Value func_days360(valVector args, ValueCalc *calc, FuncExtra *)
{
QDate date1 = calc->conv()->asDate(args[0]).asDate(calc->settings());
QDate date2 = calc->conv()->asDate(args[1]).asDate(calc->settings());
bool european = false;
if (args.count() == 3)
european = calc->conv()->asBoolean(args[2]).asBoolean();
return Value(func_days360_helper(date1, date2, european));
}
// Function: SEXDEC
Value func_sexdec(valVector args, ValueCalc *calc, FuncExtra *)
{
if (args.count() == 1) {
// convert given value to number
Value time = calc->conv()->asTime(args[0]);
return calc->mul(calc->conv()->asFloat(time), 24);
}
// convert h/m/s to number of hours
Value h = args[0];
Value m = args[1];
Value res = calc->add(h, calc->div(m, 60));
if (args.count() == 3) {
Value s = args[2];
res = calc->add(res, calc->div(s, 3600));
}
return res;
}
// Function: LEFT
Value func_left(valVector args, ValueCalc *calc, FuncExtra *)
{
QString str = calc->conv()->asString(args[0]).asString();
int nb = 1;
if (args.count() == 2)
nb = calc->conv()->asInteger(args[1]).asInteger();
if (nb < 0)
return Value::errorVALUE();
return Value(str.left(nb));
}
// Function: MINUTE
Value func_minute(valVector args, ValueCalc *calc, FuncExtra *)
{
QTime time;
if (args.count() == 1) {
Value v = calc->conv()->asTime(args[0]);
if (v.isError()) return v;
time = v.asTime();
} else
time = QTime::currentTime();
return Value(time.minute());
}
// Function: SECOND
Value func_second(valVector args, ValueCalc *calc, FuncExtra *)
{
QTime time;
if (args.count() == 1) {
Value v = calc->conv()->asTime(args[0]);
if (v.isError()) return v;
time = v.asTime(calc->settings());
} else
time = QTime::currentTime();
return Value(time.second() + qRound(time.msec() * 0.001));
}
//
// Function: IMSUB
//
Value func_imsub(valVector args, ValueCalc *calc, FuncExtra *)
{
Value result;
if (args.count() == 1)
awImSub(calc, result, args[0], Value(0));
else {
result = args[0];
valVector vector = args.mid(1);
calc->arrayWalk(vector, result, awImSub, Value(0));
}
return result;
}
// Function: ISOWEEKNUM
//
// method startday name of day
// default: 1 1 sunday
// 2 0 monday
//
Value func_isoWeekNum(valVector args, ValueCalc *calc, FuncExtra *)
{
QDate date = calc->conv()->asDate(args[0]).asDate(calc->settings());
if (!date.isValid())
return Value::errorVALUE();
int method = 2; // default method = 2
if (args.count() > 1)
method = calc->conv()->asInteger(args[1]).asInteger();
if (method < 1 || method > 2)
return Value::errorVALUE();
int startday = 1;
if (method != 1)
startday = 0;
int weeknum;
int day; // current date
int day4; // 4th of jan.
int day0; // offset to 4th of jan.
// date to find
day = date.toJulianDay();
// 4th of jan. of current year
day4 = QDate(date.year(), 1, 4).toJulianDay();
// difference in days to the 4th of jan including correction of startday
day0 = QDate::fromJulianDay(day4 - 1 + startday).dayOfWeek();
// do we need to count from last year?
if (day < day4 - day0) { // recalculate day4 and day0
day4 = QDate(date.year() - 1, 1, 4).toJulianDay(); // 4th of jan. last year
day0 = QDate::fromJulianDay(day4 - 1 + startday).dayOfWeek();
}
// calc weeeknum
weeknum = (day - (day4 - day0)) / 7 + 1;
// if weeknum is greater 51, we have to do some extra checks
if (weeknum >= 52) {
day4 = QDate(date.year() + 1, 1, 4).toJulianDay(); // 4th of jan. next year
day0 = QDate::fromJulianDay(day4 - 1 + startday).dayOfWeek();
if (day >= day4 - day0) { // recalculate weeknum
weeknum = (day - (day4 - day0)) / 7 + 1;
}
}
return Value(weeknum);
}
//
// Function: IMDIV
//
Value func_imdiv(valVector args, ValueCalc *calc, FuncExtra *)
{
Value result;
if (args.count() == 1) {
result = Value(complex<double>(1.0, 0.0));
awImDiv(calc, result, args[0], Value(0));
} else {
result = args[0];
valVector vector = args.mid(1);
calc->arrayWalk(vector, result, awImDiv, Value(0));
}
return result;
}
//
// Function: XOR
//
Value func_xor(valVector args, ValueCalc *calc, FuncExtra *)
{
// exclusive OR - odd number of values must be true
int cnt = args.count();
Value count(0);
for (int i = 0; i < cnt; ++i) {
if (args[i].isError())
return args[i];
}
for (int i = 0; i < cnt; ++i)
calc->arrayWalk(args[i], count, awXor, Value(0));
return Value((count.asInteger() & 1) == 1);
}
//
// Function: HEX2OCT
//
Value func_hex2oct(valVector args, ValueCalc *calc, FuncExtra *)
{
QRegExp rx("[0123456789ABCDEFabcdef]+");
int minLength = 0;
if (args.count() > 1)
// we have the optional "minimum length" argument
minLength = calc->conv()->asInteger(args[1]).asInteger();
if (rx.exactMatch(calc->conv()->asString(args[0]).asString())) {
// this only contains decimal digits.
return calc->base(calc->fromBase(args[0], 16), 8, 0, minLength);
} else {
return Value::errorVALUE();
}
}
//
// Function: BIN2HEX
//
Value func_bin2hex(valVector args, ValueCalc *calc, FuncExtra *)
{
QRegExp rx("[01]+");
int minLength = 0;
if (args.count() > 1)
// we have the optional "minimum length" argument
minLength = calc->conv()->asInteger(args[1]).asInteger();
if (rx.exactMatch(calc->conv()->asString(args[0]).asString())) {
// this only contains 0s and 1s.
return calc->base(calc->fromBase(args[0], 2), 16, 0, minLength);
} else {
return Value::errorVALUE();
}
}
//
// Function: GESTEP
//
Value func_gestep(valVector args, ValueCalc *calc, FuncExtra *)
{
Value x = args[0];
Value y = Value(0.0);
if (args.count() == 2)
y = args[1];
if (x.isString() || y.isString())
return Value::errorNUM();
int result = 0;
if (calc->greater(x, y) || calc->approxEqual(x, y))
result = 1;
return Value(result);
}
//
// Function: OR
//
Value func_or(valVector args, ValueCalc *calc, FuncExtra *)
{
Value result(false);
int cnt = args.count();
for (int i = 0; i < cnt; ++i) {
if (args[i].isError())
return args[i];
}
for (int i = 0; i < cnt; ++i) {
calc->arrayWalk(args[i], result, awOr, Value(0));
if (result.asBoolean())
// if any value is true, return true
return result;
}
// nothing is true -> return false
return result;
}