KSpread: New lookup/reference functions

Adds reference functions HLOOKUP, VLOOKUP.

Import from Calligra revision 1134327.

Authors: Stefan Nikolaus <[email protected]>
         Sebastian Sauer <[email protected]>

Signed-off-by: Mavridis Philippe <[email protected]>

1 files changed, 70 insertions, 1 deletions

diff --git a/kspread/kspread_functions_reference.cpp b/kspread/kspread_functions_reference.cpp
index 7dacb054..1c9c5216 100644
--- a/kspread/kspread_functions_reference.cpp
+++ b/kspread/kspread_functions_reference.cpp
@@ -38,11 +38,13 @@ Value func_areas (valVector args, ValueCalc *calc, FuncExtra *);
 Value func_choose (valVector args, ValueCalc *calc, FuncExtra *);
 Value func_column (valVector args, ValueCalc *calc, FuncExtra *);
 Value func_columns (valVector args, ValueCalc *calc, FuncExtra *);
+Value func_hlookup (valVector args, ValueCalc *calc, FuncExtra *);
 Value func_index (valVector args, ValueCalc *calc, FuncExtra *);
 Value func_indirect (valVector args, ValueCalc *calc, FuncExtra *);
 Value func_lookup (valVector args, ValueCalc *calc, FuncExtra *);
 Value func_row (valVector args, ValueCalc *calc, FuncExtra *);
 Value func_rows (valVector args, ValueCalc *calc, FuncExtra *);
+Value func_vlookup (valVector args, ValueCalc *calc, FuncExtra *);
 
 // registers all reference functions
 void RegisterReferenceFunctions()
@@ -69,6 +71,10 @@ void RegisterReferenceFunctions()
   f->setAcceptArray ();
   f->setNeedsExtra (true);
   repo->add (f);
+  f = new Function ("HLOOKUP",  func_hlookup);
+  f->setParamCount (3, 4);
+  f->setAcceptArray ();
+  repo->add (f);
   f = new Function ("INDEX",   func_index);
   f->setParamCount (3);
   f->setAcceptArray ();
@@ -89,6 +95,10 @@ void RegisterReferenceFunctions()
   f->setAcceptArray ();
   f->setNeedsExtra (true);
   repo->add (f);
+  f = new Function ("VLOOKUP",  func_vlookup);
+  f->setParamCount (3, 4);
+  f->setAcceptArray ();
+  repo->add (f);
 }
 
 // Function: ADDRESS
@@ -225,6 +235,36 @@ Value func_choose (valVector args, ValueCalc *calc, FuncExtra *)
   return args[num];
 }
 
+// Function: HLOOKUP
+Value func_hlookup (valVector args, ValueCalc *calc, FuncExtra *)
+{
+  const Value key = args[0];
+  const Value data = args[1];
+  const int row = calc->conv()->asInteger( args[2] ).asInteger();
+  const int cols = data.columns();
+  const int rows = data.rows();
+  if ( row < 1 || row > rows )
+      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 col = 0; col < cols; ++col) {
+    // search in the first row
+    const Value le = data.element(col, 0);
+    if (calc->naturalEqual(key, le)) {
+      return data.element(col, row - 1);
+    }
+    // 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, row - 1);
+    }
+  }
+  return v;
+}
+
 // Function: INDEX
 Value func_index (valVector args, ValueCalc *calc, FuncExtra *)
 {
@@ -232,7 +272,7 @@ Value func_index (valVector args, ValueCalc *calc, FuncExtra *)
   // value, or a single cell containing an array - then we return the array
   // element. In any case, this function can assume that the given value
   // is the same. Because it is.
-  
+
   Value val = args[0];
   unsigned row = calc->conv()->asInteger (args[1]).asInteger() - 1;
   unsigned col = calc->conv()->asInteger (args[2]).asInteger() - 1;
@@ -340,3 +380,32 @@ Value func_indirect (valVector args, ValueCalc *calc, FuncExtra *e)
   return Value::errorVALUE();
 }
 
+// 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;
+}