6

I have created dynamic kendo grid.In grid there is some columns which required formatting. I have one field "valueFormat" in database according that format I want to format other columns.

How to achieve above thing Please help.

link for sample grid

var data = [{
    "Dept": "Dev",
    "CalculateValue": 0 ,
    "AcualValue": 341.917,
    "ValueFormat": "#.###"
   }, 
   {
    "Dept": "Dev",
    "CalculateValue": 0,
    "AcualValue": 5333.083,
    "ValueFormat": "#.###"
   }, 
   {
    "Dept": "Dev",
    "CalculateValue":0 ,
    "AcualValue": 8735.666,
    "ValueFormat": "{0:c2}"
   }, 
   {
    "Dept": "Dev",
    "CalculateValue":0,
    "AcualValue": 126.000,
    "ValueFormat": "{0:c2}"
  }];

var dateFields = [];
generateGrid(data)

function generateGrid(gridData) {
        if ($(".k-header").length > 0) {
            grid = $("#grid").data("kendoGrid");
            grid.destroy();
            $("#grid").empty();
        }

        $("#grid").kendoGrid({
            toolbar: ["excel"],
            excel: {
                allPages: true,
                filterable: true,
                fileName: 'Aggregate Expenditure/Utilization Report_.xlsx'
            },
            dataSource: {

                pageSize: 100,
                schema: {
                    data: function () { return gridData; },
                    total: function () {
                        return gridData.length;
                    }
                }
            },
            sortable: true,
            filterable: true,
            groupable: true,
            pageable: true,
            columns: generateModel(gridData[0]),
            autoBind: true
        });
        $("#grid").data("kendoGrid").refresh();
    }

function generateModel(gridData) {
   var model = {};
   model.id = "ID";
   var fields = {};
   for (var property in gridData) {
     var propType = typeof gridData[property];

    if (propType == "number") {
       fields[property] = {
       type: "number",
       validation: {
          required: true
       }
    };
    } else if (propType == "boolean") {
      fields[property] = {
      type: "boolean",
      validation: {
        required: true
      }
    };
    } else if (propType == "string") {
      var parsedDate = kendo.parseDate(gridData[property]);
       if (parsedDate) {
         fields[property] = {
           type: "date",
           validation: {
             required: true
           }
         };
      dateFields.push(property);
     } else {
        fields[property] = {
          validation: {
             required: true
          }
        };
     }
   } else {
      fields[property] = {
        validation: {
          required: true
        }
      };
   }

  }
   model.fields = fields;

   return model;
}
Prajapati Vikas
  • 294
  • 1
  • 10

1 Answers1

3

The Kendo grid's column formatting option won't work for you, because it applies the same formatting to all cells within a column, but you are going for different formatting in every row.

In that case you should specify a custom template for the ActualValue column. Inside that template you can use a formatting function to process your value. Since you are already using Kendo, you can take advantage of kendo.format(), like this:

template: "#: kendo.format(ValueFormat, AcualValue)#"

However, that does not work with all of your data, because some of the format strings are already wrapped in brackets and have an index like "{0:c2}", and others are not - "#.###". So you should create a function that makes sure they are consistent, before you pass them to kendo.format(). I've done this with fixValueFormatString(ValueFormat) below.

Here's a link to a working sample: http://jsbin.com/jejixediga/edit?js,console,output

And here's the code:

var data = [{

  "Dept": "Dev",
  "CalculateValue": 0 ,
  "AcualValue": 341.917,
  "ValueFormat": "#.###"
}, {

  "Dept": "Dev",
  "CalculateValue": 0,
  "AcualValue": 5333.083,
  "ValueFormat": "#.###"
}, {

  "Dept": "Dev",
  "CalculateValue":0 ,
  "AcualValue": 8735.666,
  "ValueFormat": "{0:c2}"
}, {

  "Dept": "Dev",
  "CalculateValue":0,
  "AcualValue": 126.000,
  "ValueFormat": "{0:c2}"
}];

var dateFields = [];

// Make sure all format strings are consistent
function fixValueFormatString(ValueFormat){
  if(!ValueFormat.startsWith("{")){
      ValueFormat = "{0:" + ValueFormat + "}";
  }
  return ValueFormat;
}

generateGrid(data)

  function generateGrid(gridData) {
            if ($(".k-header").length > 0) {
                grid = $("#grid").data("kendoGrid");
                grid.destroy();
                $("#grid").empty();
            }

            $("#grid").kendoGrid({
                toolbar: ["excel"],
                excel: {
                    allPages: true,
                    filterable: true,
                    fileName: 'Aggregate Expenditure/Utilization Report_.xlsx'
                },
                dataSource: {

                    pageSize: 100,
                    schema: {
                        data: function () { return gridData; },
                        total: function () {
                            return gridData.length;
                        }
                    }
                },
                sortable: true,
                filterable: true,
                groupable: true,
                pageable: true,
                autoBind: true,
                columns: [{
                    field: "Dept"
                }, {
                    field: "CalculateValue",
                }, {
                    template: "#: kendo.format(fixValueFormatString(ValueFormat), AcualValue)#",
                    field: "AcualValue"
                }, {
                    field: "ValueFormat"
                }]
            });
            $("#grid").data("kendoGrid").refresh();
        }

function generateModel(gridData) {
  var model = {};
  model.id = "ID";
  var fields = {};
  for (var property in gridData) {
    var propType = typeof gridData[property];

    if (propType == "number") {
      fields[property] = {
        type: "number",
        validation: {
          required: true
        }
      };
    } else if (propType == "boolean") {
      fields[property] = {
        type: "boolean",
        validation: {
          required: true
        }
      };
    } else if (propType == "string") {
      var parsedDate = kendo.parseDate(gridData[property]);
      if (parsedDate) {
        fields[property] = {
          type: "date",
          validation: {
            required: true
          }
        };
        dateFields.push(property);
      } else {
        fields[property] = {
          validation: {
            required: true
          }
        };
      }
    } else {
      fields[property] = {
        validation: {
          required: true
        }
      };
    }

  }
  model.fields = fields;

  return model;
}

UPDATE - ASSIGN TEMPLATE TO DYNAMICALLY GENERATED COLUMNS

In response to OPs request for a solution that works with dynamic columns, here's the revised code (http://jsbin.com/jinowamosa/edit?js,console,output):

var data = [{

  "Dept": "Dev",
  "CalculateValue": 0 ,
  "AcualValue": 341.917,
  "ValueFormat": "#.###"
}, {

  "Dept": "Dev",
  "CalculateValue": 0,
  "AcualValue": 5333.083,
  "ValueFormat": "#.###"
}, {

  "Dept": "Dev",
  "CalculateValue":0 ,
  "AcualValue": 8735.666,
  "ValueFormat": "{0:c2}"
}, {

  "Dept": "Dev",
  "CalculateValue":0,
  "AcualValue": 126.000,
  "ValueFormat": "{0:c2}"
}];

var dateFields = [];

function fixValueFormatString(ValueFormat){
  if(!ValueFormat.startsWith("{")){
      ValueFormat = "{0:" + ValueFormat + "}";
  }
  return ValueFormat;
}

generateGrid(data)

  function generateGrid(gridData) {
            if ($(".k-header").length > 0) {
                grid = $("#grid").data("kendoGrid");
                grid.destroy();
                $("#grid").empty();
            }

            $("#grid").kendoGrid({
                toolbar: ["excel"],
                excel: {
                    allPages: true,
                    filterable: true,
                    fileName: 'Aggregate Expenditure/Utilization Report_.xlsx'
                },
                dataSource: {
                    pageSize: 100,
                    schema: {
                        data: function () { return gridData; },
                        total: function () {
                            return gridData.length;
                        }
                    }
                },
                sortable: true,
                filterable: true,
                groupable: true,
                pageable: true,
                autoBind: true,
                columns: generateColumns(gridData[0])
            });
            $("#grid").data("kendoGrid").refresh();
        }

function generateColumns(gridData) {
  var fields = {};
  var columns = [];

  for (var property in gridData) {
    var propType = typeof gridData[property];

    if (propType == "number") {
      fields[property] = {
        type: "number",
        validation: {
          required: true
        }
      };
    } else if (propType == "boolean") {
      fields[property] = {
        type: "boolean",
        validation: {
          required: true
        }
      };
    } else if (propType == "string") {
      var parsedDate = kendo.parseDate(gridData[property]);
      if (parsedDate) {
        fields[property] = {
          type: "date",
          validation: {
            required: true
          }
        };
        dateFields.push(property);
      } else {
        fields[property] = {
          validation: {
            required: true
          }
        };
      }
    } else {
      fields[property] = {
        validation: {
          required: true
        }
      };
    }

  }

  for (var field in fields) { 
        if(field == 'AcualValue'){
          columns.push({ field: field, template: "#: kendo.format(fixValueFormatString(ValueFormat), AcualValue)#" });
        } else {
          columns.push({ field: field });
        }
    }

  return columns;
}

Hope this helps!

Oggy
  • 1,516
  • 1
  • 16
  • 22
  • you did very well but at my side columns are being generated dynamically, and there will be more column like "CalculateValue" and "ActualValue" so your solution will not work in my case. – Prajapati Vikas Apr 12 '17 at 10:38
  • I've updated the answer to make it work with dynamic columns too. – Oggy Apr 12 '17 at 11:39
  • see, you are passing ValueFormat into function "fixValueFormatString" but as per your code written how ValueFormat variable will get its value.I am always getting "undefined" error. – Prajapati Vikas Apr 13 '17 at 06:25
  • Take a look at the JS Bin: http://jsbin.com/jinowamosa/edit?js,console,output. I call fixValueFormatString(ValueFormat) inside the column template, where ValueFormat is the field in your data model that contains the format string. Not sure what you are getting "undefined" on, but check the spelling on your field names. In your example you have ActualValue missing a letter. – Oggy Apr 13 '17 at 07:15