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]>

2 files changed, 123 insertions, 2 deletions

diff --git a/kspread/extensions/reference.xml b/kspread/extensions/reference.xml
index 01715bf4..2b0af0bc 100644
--- a/kspread/extensions/reference.xml
+++ b/kspread/extensions/reference.xml
@@ -201,7 +201,59 @@
        <Example>LOOKUP(1.232; A1:A6; B1:B6) for A1 = 1, A2 = 2 returns the value of B1.</Example>
      </Help>
    </Function>
-                                                                                                                            
+
+   <Function>
+     <Name>HLOOKUP</Name>
+     <Type>String/Numeric</Type>
+     <Parameter>
+       <Comment>Lookup value</Comment>
+       <Type>String/Numeric</Type>
+     </Parameter>
+     <Parameter>
+       <Comment>Data source</Comment>
+       <Type>Array</Type>
+     </Parameter>
+     <Parameter>
+       <Comment>Row</Comment>
+       <Type>Int</Type>
+     </Parameter>
+     <Parameter>
+        <Comment>Sorted (optional)</Comment>
+        <Type>Boolean</Type>
+     </Parameter>
+     <Help>
+       <Text>Look for a matching value in the first row of the given table, and return the value of the indicated row.</Text>
+       <Text>Looks up the 'lookup value' in the first row of the 'data source'. If a value matches, the value in the 'row' and the column, the value was found in, is returned. If 'sorted' is true (default), the first row is assumed to be sorted. The search will end, if the 'lookup value' is lower than the value, currently compared to.</Text>
+       <Syntax>HLOOKUP(Lookup value; data source; Row; Sorted)</Syntax>
+     </Help>
+   </Function>
+
+   <Function>
+     <Name>VLOOKUP</Name>
+     <Type>String/Numeric</Type>
+     <Parameter>
+       <Comment>Lookup value</Comment>
+       <Type>String/Numeric</Type>
+     </Parameter>
+     <Parameter>
+       <Comment>Data source</Comment>
+       <Type>Array</Type>
+     </Parameter>
+     <Parameter>
+       <Comment>Column</Comment>
+       <Type>Int</Type>
+     </Parameter>
+     <Parameter>
+        <Comment>Sorted (optional)</Comment>
+        <Type>Boolean</Type>
+     </Parameter>
+     <Help>
+       <Text>Look for a matching value in the first column of the given table, and return the value of the indicated column.</Text>
+       <Text>Looks up the 'lookup value' in the first column of the 'data source'. If a value matches, the value in the 'column' and the row, the value was found in, is returned. If 'sorted' is true (default), the first column is assumed to be sorted. The search will end, if the 'lookup value' is lower than the value, currently compared to.</Text>
+       <Syntax>VLOOKUP(Lookup value; data source; Column; Sorted)</Syntax>
+     </Help>
+   </Function>
+
   </Group>
 
 </KSpreadFunctions>
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;
+}