5

I need some help with dynamically calculating an HTML table column using data from other columns and using a user-defined equation.

For example, if the user inputs the equation C1 + C2 * 0.5 + C3 * 0.8 into a input box the table would need to calculate the last column based on the data from the columns defined in the equation (C1 = column 1, C2 = column 2...).

My table data looks like this:

Student ID | Homework 1 | Homework 2 | Exam points | Final Grade
1            8.75         7.60         55.50         -
2            9.00         4.50         63.00         -
3            7.75         7.40         45.50         -

If the user typed in the equation C1 + C2 * 0.5 + C3 * 0.8 in the input the table should perform the operations and fill the column Final Grade based on that equation.

The result should look something like this.

Student ID | Homework 1 | Homework 2 | Exam points | Final Grade
1            8.75         7.60         55.50         56.95
2            9.00         4.50         63.00         61.65
3            7.75         7.40         45.50         47.85

The first row in final grade would be calcualted like this (8.75 + 7.60 * 0.5 + 55.50 * 0.8).

This is my body in HTML:

<div>
    <input id="equation">
</div>
<table>
    <tr>
        <th>Student ID</th>
        <th>Homework 1</th>
        <th>Homework 2</th>
        <th>Exam points</th>
        <th>Final grade</th>
    </tr>
    <tr>
        <td>1</td>
        <td>8.75</td>
        <td>7.60</td>
        <td>55.50</td>
        <td class="final-grade">-</td>
    </tr>
    <tr>
        <td>2</td>
        <td>9.00</td>
        <td>4.50</td>
        <td>63.00</td>
        <td class="final-grade">-</td>
    </tr>
    <tr>
        <td>3</td>
        <td>8.75</td>
        <td>7.60</td>
        <td>55.50</td>
        <td class="final-grade">-</td>
    </tr>
</table>

Any help would be greatly appreciated!

10
  • Please, can you give more examples of users input? Your table is dynamic created with dynamic values? Commented Aug 24, 2018 at 18:40
  • Another example of an input would be "C2 / 2 + C3 * 0.5" ( Column 2(Homework 2)/ 2 + Column 3(Exam points) * 0.5, any type of column sequence would work (excluding Column 4, because that is the result column), the only mathematical operators that would be used are +,-,/,*. For the time being the table is static but I would upgrade it so it can read values from a database in the future. Commented Aug 24, 2018 at 18:56
  • so, the user can input any kind of equation in the input? isn't it a little fragile? For example, if the user input something like "c1 * / c5" it wouldn't be valid and your code would probably fail. I'm trying to write an aswer for you, but this problem must be considered Commented Aug 24, 2018 at 18:59
  • Yes, that would be a problem, is it possible to check for such errors? Commented Aug 24, 2018 at 19:05
  • As @CalvinNunes suggests, this is going to be fragile if you start eval'ing user input like this. You will probably will need to use something to properly evaluate the expressions (I am not recommending this particular package, it was just the first google result) Commented Aug 24, 2018 at 19:06

3 Answers 3

4

Here's an example that uses the evil eval (!).

  • Works with any number of cells, uppercase or lowercase C identifier.
  • If the subject cell is invalid the result will be "NaN".
  • If the provided equation is invalid the result will be "-"

Basically it creates an object with references like {"c1":8.75, "c2":7.60, ...} , than it evals the input string replacing the c* occurrences with the respective object value.

function calcGrades() {

  const val = this.value.toLowerCase().trim();
  
  $("#grades tbody tr").each((i, TR) => {
    let res;
    const refs = $("td",TR).get().reduce((ob, TD, i) =>
        [ob["c"+i] = parseFloat(TD.textContent), ob][1], {});
        
    try { res = eval(val.replace(/c\d+/g, $1 => refs[$1])).toFixed(2) } 
    catch (err) { res = "-" }
    
    $(".final-grade",TR).text( res );
  });
}

$("#equation").on("input", calcGrades).trigger("input");
<input id="equation" type="text" value="C1 + c2 * 0.5 + C3 * 0.8">
<table id="grades">
  <thead>
    <tr>
        <th>ID</th><th>Homework 1</th><th>Homework 2</th><th>Exam pts</th><th>Final</th>
    </tr>
  </thead>
  <tbody>
    <tr><td>1</td><td>8.75</td><td>7.60</td><td>55.50</td><td class="final-grade">-</td></tr>
    <tr><td>2</td><td>9.00</td><td>4.50</td><td>63.00</td><td class="final-grade">-</td></tr>
    <tr><td>3</td><td>7.75</td><td>7.40</td><td>45.50</td><td class="final-grade">-</td></tr>
    <tr><td>4</td><td>0</td><td>0.0</td><td>0</td><td class="final-grade">-</td></tr>
    <tr><td>4</td><td>foo</td><td>bar</td><td>baz</td><td class="final-grade">-</td></tr>
  </tbody>
</table>
<script src="//ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>

Sign up to request clarification or add additional context in comments.

Comments

0

Well, the way you are doing is very fragile, the user would input a single different character in the input and then all calcuation can fail.

But besides that, there's a way of doing that keeping the way you want (I don't recommend), but let's go to the explanation:

  1. Add classes to your HTML table, in a way that you can identify each row and column.
  2. Create a function that get the values from each row (using a loop) and another function that you can call to calculate those values.
  3. Inside the function that calculates is where comes the solution and the problem at the same time. For now, it works only with 3 columns and considering that the user knows that only c1, c2 or c3 are valid column names.
  4. With the equation value, you replace the column names with the column values. Then you check if there's some other character remaining, if yes, then the equation is wrong and a message or an error should be displayed.
  5. If no other characters remain and just numbers and operation symbols are in the equation, then we use the BAD IDEA eval() method, which evaluates the equation as it is.
  6. I added the eval function inside a try...catch block to let it a little more secure (Please, read about why eval is not a good solution here: LINK)
  7. Well, if the equation was typed correct and no malicious code was introduced by eval, then you will have the final value to return.

Below code as example:

let btn_calc = $("#btn_calc");
let equation = $("#equation");


btn_calc.on("click", calculate);

function calculate(){
  let rows = $(".values");
  for (var i = 0; i < rows.length; i++){
    let singleRow = $(rows[i]);
    let grade = singleRow.find(".final-grade");
    let n1 = singleRow.find(".c1").text(); 
    let n2 = singleRow.find(".c2").text();
    let n3 = singleRow.find(".c3").text();
    let total = returnValue(n1,n2,n3);
    if (total != false){
      grade.text(total)    
    } else {
      return;
    }
  }
}

function returnValue(n1,n2,n3){
  let calc = equation.val().trim();
  let result = 0;
  debugger;
  if (calc != null && calc != ""){
     calc = calc.replace(/c1/g,n1).replace(/c2/g,n2).replace(/c3/g,n3)

     var regx = new RegExp("[a-z]", "gi");
     debugger;
     if (calc.match(regx)){
      console.log(calc);
      alert("Your equation is invalid!");
      return false;
     }

     try{
      result = eval(calc);
      result.toFixed(2);
     } catch (ex){
      alert("Error in your equation! " + ex);
     }
  }
  return result;
}
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<div>
  <input type="text" id="equation"/> (Valid Column names: c1, c2, c3)
  <br>
  <input type="button" value="Calculate" id="btn_calc"/>
</div>
<table>
  <tr>
        <th>Student ID</th>
        <th >Homework 1</th>
        <th >Homework 2</th>
        <th>Exam points</th>
        <th>Final grade</th>
  </tr>
  <tr class="values">
        <td>1</td>                              
        <td class="c1">8.75</td>
        <td class="c2">7.60</td>
        <td class="c3">55.50</td>
        <td class="final-grade">-</td>
    </tr>
    <tr class="values">
        <td>2</td>
        <td class="c1">9.00</td>
        <td class="c2">4.50</td>
        <td class="c3">63.00</td>
        <td class="final-grade">-</td>
    </tr>
    <tr class="values">
        <td>3</td>
        <td class="c1">8.75</td>
        <td class="c2">7.60</td>
        <td class="c3">55.50</td>
        <td class="final-grade">-</td>
</tr>

You're lucky that I had some free time to code this for you... I should send the bill hahaha

Comments

0

As long as your html structure stays simple and you are okay with using eval (which is actually not recommended), defining the following JavaScript code and invoking it will do the trick for you. You will need to ensure that c1...c3 have valid values in them.

    function calcTotals() {
        const c1 = 'parseFloat(fg.previousElementSibling.previousElementSibling.previousElementSibling.textContent)'
        const c2 = 'parseFloat(fg.previousElementSibling.previousElementSibling.textContent)'
        const c3 = 'parseFloat(fg.previousElementSibling.textContent)'
        const equation = document.getElementById('equation').value
        try {
            const fg = document.querySelector('td.final-grade')
            eval(equation.replace('c1',c1)
                         .replace('c2',c2)
                         .replace('c3',c3))
        } catch(e) {
            alert('Please enter a valid equation')
        }
        document.querySelectorAll('td.final-grade').forEach(fg => {
            fg.textContent = eval(equation.replace('c1',c1)
                                          .replace('c2',c2)
                                          .replace('c3',c3))
        })
    }

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.