Wijmo FlexSheet - custom sort

custom sort on columns containing formula strings using the CollectionView's sortConverter

by Joel Parks

HTML

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<link rel="stylesheet" href="http://cdn.wijmo.com/5.latest/styles/wijmo.min.css">
<script src="http://cdn.wijmo.com/5.latest/controls/wijmo.min.js"></script>
<script src="http://cdn.wijmo.com/5.latest/controls/wijmo.input.min.js"></script>
<script src="http://cdn.wijmo.com/5.latest/controls/wijmo.grid.min.js"></script>
<script src="http://cdn.wijmo.com/5.latest/controls/wijmo.grid.filter.min.js"></script>
<script src="http://cdn.wijmo.com/5.latest/controls/wijmo.grid.sheet.min.js"></script>
<div class="container">
<div class="col">
<div class="row">
<h1>FlexSheet</h1>
</div>
<h3>
Sorting with Formulas
</h3>
  <p>This FlexSheet sorts the Totals column by display value rather than the formula string bound to the cell.
  It also updates the CollectionView to ensure the value displayed corresponds to the sum total of the row</p>
</div>
 <div id="flexSheet" style="height:350px;"></div>
 </div>

CSS

h1{}

JavaScript

var countries = 'US,Germany,UK,Japan,Italy,Greece'.split(','),
    data = [];
for (var i = 0; i < countries.length+3; i++) {
    data.push({
        country: countries[i%countries.length],
        downloads: Math.round(Math.random() * 20000),
        sales: Math.round(Math.random() * 10000),
        expenses: Math.round(Math.random() * 5000),
        total: "=SUM(B"+(i+2) +":D"+(i+2)+")"
    });
}

var columns = [
		{'name': 'country', 'header': 'Country', 'binding': 'country' },
    {'name': 'downloads', 'header': 'Downloads', 'binding': 'downloads' },
    {'name': 'sales', 'header': 'Sales', 'binding': 'sales' },
    {'name': 'expenses', 'header': 'Expenses', 'binding': 'expenses' },
    {'name': 'total', 'header': 'Totals', 'binding': 'total'}
  ];

function getRowIndex(item){
	var row = -1;
	for(var i=0; i<cv.items.length; i++){
  	if( itemMatch(item, cv.items[i]) ){
    	row = i+1;
      break;
    }
  }
  return row;
}

function itemMatch(first, second){
  if(first.country == second.country){
  	//console.log('first.country: ' +first.country+'\t;\t'+'second.country: '+second.country);
  	if(first.downloads == second.downloads && first.sales == second.sales && first.expenses == second.expenses){
    	return true;
    }
  }
	return false;
}

var cv = new wijmo.collections.CollectionView(data, {
	groupDescriptions: ['country'],
	collectionChanged: function(s, e){
  	console.log(s); // collectionView
    
		// if sorted, filtered or grouped
		if( e.action == 3 && s.sortDescriptions.length != 0){
    	console.log(e); // NotifyEventChangedArgs
      for(var i=0; i< s.items.length; i++){
      	s.items[i].total = '=SUM(B'+ (i+2) +':D'+(i+2)+')';
      }
      
    }
  }
});
cv.sortConverter = function(sd, item, value){
	
  if(sd.property == 'total'){
    var currentRow = getRowIndex(item);
    var displayValue = flexSheet.getCellValue(currentRow ,4);
    value = displayValue;
  }
  return value;
}

var div = document.createElement('test-div');
var grid = new...