Spreadsheet like Data Bars

Any arbitrary Excel formula can be executed in context of grid to get its result i.e.,

var result = grid.Formulas().exec("MAX(C:C)")

Using the above concept, in this example Excel formulas are executed in

• column.render callback to generate Excel like data bars by using MIN and MAX spreadsheet formulas

  render: function (ui) {
      var F = this.Formulas(),
          col = pq.toLetter(ui.colIndx), //get column letter i.e., "C", "D", etc of the current column.
          range = col + ":" + col,
          min = F.exec("MIN(" + range + ")"),
          max = F.exec("MAX(" + range + ")"),
          val = ((ui.cellData - min) / (max - min)) * 100;
  
      return {
          style: {
              background: "linear-gradient(to right, salmon " + val + "%, #fff 0%)"
          }
      };
  }
  

• rowTemplate to compute rank property of each row based on revenues value by using RANK spreadsheet formula

  rowTemplate: {
      get rank() {
          var F = grid.Formulas(),
              ri = this.pq_ri, //get rowIndx of current row.
              col = pq.toLetter(grid.getColIndx({ dataIndx: 'revenues' }));
  
          return F.exec("RANK(" + col + (ri + 1) + ", " + col + ":" + col + ")"); //i.e., RANK(C1, C:C)
      }
  },