Search code examples
javascriptsumrhandsontable

Sum from row value, "2 colums" the max handsontable

I have a hands-on table where I need to sum cols, the end col has total from all cols summed.

I can sum all cols, the problem is cols is dynamics sometimes one-col has a child, in this case, I need to sum the max of both, but I don't know how can I do it, !when is just one just sum one. if has a child sum just the max both cols.!

For example: work1 is the parent. the child is help 1, the arrays cols came from database

example http://jsfiddle.net/duxL728h/

enter image description here enter image description here

function hexRenderer(instance, td, row, col, prop, value, cellProperties) {
  Handsontable.TextCell.renderer.apply(this, arguments);
}

var hot = new Handsontable($("#exampleGrid")[0], {
  data: [
    [0, 0, 0, 0],
    [0, 0, 0, 0]
  ],
  fillHandle: false,
  minSpareCols: 0,
  minSpareRows: 0,
  columns: [{
      data: 'work.name',
      title: 'Name',
      readOnly: true,
      type: 'numeric',
      width: 100
    }, {
      data: 'work.1',
      parent: "1",
      title: 'work 1',
      type: 'numeric',
      width: 50
    }, {
      data: 'work.2',
      parent: "1|h",
      title: 'help 1',
      type: 'numeric',
      width: 50
    }, {
      data: 'work.3',
      parent: "3",
      title: 'work 2',
      type: 'numeric',
      width: 50
    }, {
      data: 'work.4',
      parent: "3|h",
      title: 'help 2',
      type: 'numeric',
      width: 50
    }, {
      data: 'work.5',
      parent: "5",
      title: 'work 3',
      type: 'numeric',
      width: 50
    }, {
      data: 'work.6',
      parent: "6",
      title: 'work 4',
      type: 'numeric',
      width: 50
    }, {
      data: 'work.7',
      parent: "6|h",
      title: 'help 4',
      type: 'numeric',
      width: 50
    }, {
      data: 'total',
      title: 'Total',
      readOnly: true,
      width: 100,
      type: {
        renderer: function(instance, td, row) {
          let cols = instance.countCols() - 1;
          let total = 0;

          for (let index = 1; index < cols; index++) {
            total += instance.getDataAtCell(row, index);
          }

          td.innerHTML = total;
        }
      }
    }


  ],
  afterChange: function(changes, source) {

    console.log("proces to save data")
  }
});
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" type="text/css" href="http://handsontable.com/demo/css/samples.css">
<script type="text/javascript" src="http://handsontable.com/dist/handsontable.full.js"></script>
<link rel="stylesheet" type="text/css" href="http://handsontable.com/dist/handsontable.full.css">

<div id="exampleGrid" class="dataTable"></div>

Solution

  • I fixed it by changing data keys, to easily recognize which col is parent and which col is it's child like below.

    function hexRenderer(instance, td, row, col, prop, value, cellProperties) {
  Handsontable.TextCell.renderer.apply(this, arguments);
}

var hot = new Handsontable($("#exampleGrid")[0], {
  data: [
    [0, 0, 0, 0],
    [0, 0, 0, 0]
  ],
  fillHandle: false,
  minSpareCols: 0,
  minSpareRows: 0,
  columns: [{
      data: 'work.name',
      title: 'Name',
      readOnly: true,
      type: 'numeric',
      width: 100
    }, {
      data: 'work.1',
      parent: "1",
      title: 'work 1',
      type: 'numeric',
      width: 50
    }, {
      data: 'help.1',
      parent: "1|h",
      title: 'help 1',
      type: 'numeric',
      width: 50
    }, {
      data: 'work.2',
      parent: "3",
      title: 'work 2',
      type: 'numeric',
      width: 50
    }, {
      data: 'help.2',
      parent: "3|h",
      title: 'help 2',
      type: 'numeric',
      width: 50
    }, {
      data: 'work.3',
      parent: "5",
      title: 'work 3',
      type: 'numeric',
      width: 50
    }, {
      data: 'work.4',
      parent: "6",
      title: 'work 4',
      type: 'numeric',
      width: 50
    }, {
      data: 'help.4',
      parent: "6|h",
      title: 'help 4',
      type: 'numeric',
      width: 50
    }, {
      data: 'total',
      title: 'Total',
      readOnly: true,
      width: 100,
      type: {
        renderer: function(instance, td, row) {
          const cols = instance.getCellMetaAtRow(row);
          cols.shift();
          const values = cols.reduce((acc, ele) => {
            const [type, ind] = ele.prop.split('.');
            const value = instance.getDataAtCell(row, ele.col);
            if (value && (!acc[ind] || value > acc[ind])) {
              acc[ind] = value;
            }
            return acc;
          }, {});


          td.innerHTML = Object.values(values).reduce((acc, e1) => acc + e1, 0);
        }
      }
    }


  ],
  afterChange: function(changes, source) {

    // console.log("proces to save data")
  }
});
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" type="text/css" href="http://handsontable.com/demo/css/samples.css">
<script type="text/javascript" src="http://handsontable.com/dist/handsontable.full.js"></script>
<link rel="stylesheet" type="text/css" href="http://handsontable.com/dist/handsontable.full.css">

<div id="exampleGrid" class="dataTable"></div>