office js - Get input and combo box values and map values to new array, write to Excel -


i've got far can on this. i'm playing bit of javascript code using excel js api. have 2 controls: text box , combo box. text box takes user [number] input, , drop down lists actions take (multiply, divide, add, subtract). gist i'm taking current selection , performing specified action specified value, writing selection. in excel, same pastespecial/operator.

here javascript:

$('#mathbutton').click(function() {     callmathify()         .catch(officehelpers.logerror); });  function callmathify() {     return excel.run(function(context) {         var inputvalue = $('#input').val();         if (inputvalue == '') {             console.log('please enter value first!');         } else {             var mathtype = $('#mathaction').val();             var selection = context.workbook.getselectedrange();             selection.load('values');             console.log('input: ' + inputvalue + ', operator: ' + mathtype);             return context.sync()                 .then(function() {                     var newvalues = selection.values.map(row => {                         if (mathtype = 'multiply') {                             return row.map(val => {return val * number($('#input').val())});                         } else if (mathtype = 'divide') {                             return row.map(val => {return val / number(inputvalue)});                         } else if (mathtype = 'add') {                             return row.map(val => {return val + number(inputvalue)});                         } else if (mathtype = 'subtract') {                             return row.map(val => {return val - number(inputvalue)});                         }                     });                     selection.values = newvalues;                     //return context.sync();             });         }         return context.sync();     }); }

i have simple html end this:

<p class="ms-font-m">     math on cells in selection.     <br>     enter value, choose action, click button.     <br> </p>  <table>     <tr>         <td><p class="ms-font-m">value: </p><input type="text" id="input" /></td>         <td><p class="ms-font-m">action: </p><select id="mathaction">             <option value="multiply">multiply</option>             <option value="divide">divide</option>             <option value="add">add</option>             <option value="subtract">subtract</option>         </td>     </tr> </table>  <br>  <button id="mathbutton" class="ms-button">     <span class="ms-button-label">mathify</span> </button>

i getting error using 'map', i'm not getting error. see i'm doing wrong here?

edit: replaced text box references variable, forgot add prior posting.

edit: adjusted code according jakob's suggestions, , code works well. edited html id of option control match case sensitivity of drop down values (proper casing). code below works well.

$('#mathbutton').click(function() {     callmathify()         .catch(officehelpers.logerror); });  function callmathify() {         return excel.run(function(context) {             var inputvalue = $('#input').val();             if (inputvalue === '') {                 console.log('please enter value first!');                 return context.sync();             } else {                 var selection = context.workbook.getselectedrange();                 selection.load('values');                 return context.sync()                     .then(function() {                         var mathtype = $('#mathaction').val();                         switch (mathtype) {                             case "multiply":                                 var newvalues = selection.values.map(row => {                                     return row.map(val => {return number(val) * number(inputvalue)});                                 });                                 break;                             case "divide":                                 var newvalues = selection.values.map(row => {                                     return row.map(val => {return number(val) / number(inputvalue)});                                 });                                 break;                             case "add":                                 var newvalues = selection.values.map(row => {                                     return row.map(val => {return number(val) + number(inputvalue)});                                 });                                 break;                             case "subtract":                                 var newvalues = selection.values.map(row => {                                     return row.map(val => {return number(val) - number(inputvalue)});                                 });                                 break;                         }                         selection.values = newvalues;                         console.log('mathify operation complete! (' + mathtype + ')');                         return context.sync();                 });             }         });     }

glad asked zack!

there 3 changes need make code snippet work:

  • you need uncomment context.sync() have in innermost .then() block. ensures selection (range) object synchronized excel , new values applied selected cells.
  • when compare value of drop-down list string need use '===' operator.
  • the values in drop-down list control should match using in comparison since case sensitive.

i notice use inputvalue variable 3 of cases , $('#input').val() first case. modified code snippet use inputvalue cases.

      function callmathify() {         return excel.run(function(context) {             var inputvalue = $('#input').val();             if (inputvalue == '') {                 console.log('please enter value first!');             } else {                 var mathtype = $('#mathaction').val();                 var selection = context.workbook.getselectedrange();                 selection.load('values');                 console.log('input: ' + inputvalue + ', operator: ' + mathtype);                 return context.sync()                     .then(function() {                         var newvalues = selection.values.map(row => {                             if (mathtype === 'multiply') {                                 return row.map(val => {return val * number(inputvalue)});                             } else if (mathtype === 'divide') {                                 return row.map(val => {return val / number(inputvalue)});                             } else if (mathtype === 'add') {                                 return row.map(val => {return val + number(inputvalue)});                             } else if (mathtype === 'subtract') {                                 return row.map(val => {return val - number(inputvalue)});                             }                         });                         selection.values = newvalues;                         return context.sync();                 });             }                 });     }

there few changes can make code more optimized , cleaner. here couple of suggestions:

  • doing comparisons action outside of .map loop declaring function , using inside of loop.
  • using switch statement instead of chained if statements.

i leave try changes since goes beyond initial question.

jakob


Comments

Post a Comment

Popular posts from this blog

sql server - Cannot query correctly (MSSQL - PHP - JSON) -

i have little problem on project. , turns out query result returns nothing when value of first name or last name has 'ñ' in it. here's code config.php: <?php ini_set('mssql.charset', 'utf-8'); header('content-type: text/html; charset=utf-8'); $servername = "192.168.1.21"; /* ip add of db server */ $connection = array("database" => "db", "uid" => "?", "pwd" => "***"); $conn = sqlsrv_connect($servername, $connection); if(!$conn){ echo "connection not established."; die(print_r(sqlsrv_errors(), true)); } ?> myquery: $idnumber = $_post["idnum"]; $response = array(); $query2 = "select clname, cfname, cmname, cqualifier student cidno = '$idnumber'"; try{ $stmt2 = sqlsrv_query($conn, $query2); } catch(pdoexeption $ex){ $response["success"] = 0; $response["message"] = "d...
Read more

php - trouble displaying mysqli database results in correct order -

i have table in database stores events , date expire. want display events on website display current events first expired events after? i have tried following sql $sql = "select noticeid, notice, noticedesc, noticeimg, noticedate, expirydate tbl_notices group noticeid ,expired order expirydate asc"; but returns expired results first due expirydate being ordered asc in query. my tbl_notice structure looks this noticeid => int(4) notice => varchar(100) noticedesc => text noticedate => timestamp noticeimg => varchar(40) expirydate => datetime expired => int(1) 0 current or 1 expired my php code is $sql = "select noticeid, notice, noticedesc, noticeimg, noticedate, expirydate tbl_notices group noticeid ,expired order expirydate asc"; $result = mysqli_query($conn,$sql); if ($result->num_rows > 0) { // output data of each row while($row = $result->fetch_assoc()) { ...
Read more

C++ Linked List -

i trying create linked list download txt file , use linked list handle file line line. when handling downloaded linked list operations performed on such text editor would. encountering problems however. seems "node(string value)" section of code has wrong though original node() declaration no arguments passes. unable figure out quite is. node.h class node { public: node(); node(string value); void setnext(node *nextnode); // allows user set "next" pointer of node points friend class linkedlist; private: string data; // data box node* next; // pointer box }; node.cpp # include <string> # include "node.h" using namespace std; node::node() { data = ""; next = null; } node::node(string value) { data = value; next = null; } void node::setnext(node *nextnode) // allows user set "next" pointer of node points { this->next = nextnode; } your #include <string> ...
Read more