New to Kendo UI for jQuery? Download free 30-day trial

Get Flagged Spreadsheet Cells Containing Invalid Values

Environment

Product Progress Kendo UI Spreadsheet for jQuery
Operating System Windows 10 64bit
Visual Studio version Visual Studio 2017
Preferred Language JavaScript

Description

How can I get all the cells in a Kendo UI for jQuery Spreadsheet that contain invalid values?

Solution

The following example demonstrates how to get all flagged cells in the Spreadsheet which contain invalid values.

<style>html { font-size: 14px; font-family: Arial, Helvetica, sans-serif; }</style>
<div id="example">
  <div id="spreadsheet" style="width: 100%;"></div>
  <script>
    function getFlaggedCells(e) {
      var spreadsheet = e.sender;
      var sheet = spreadsheet.activeSheet();
      var range = sheet.range('A3:E8');

      range.forEachCell(function (row, column, cell) {
          if(cell.validation && !cell.validation.value) {
            console.log("Row: " + row + " Col: " + column);
          }
      });
    }

    $(function() {
      $("#spreadsheet").kendoSpreadsheet({
        change: getFlaggedCells,
        render: getFlaggedCells,
        columns: 26,
        rows: 30,
        sheetsbar: false,
        excel: {
          // Required to enable Excel Export in some browsers
          proxyURL: "//demos.telerik.com/kendo-ui/service/export"
        },
        sheets: [
          {
            name: "ContactsForm",
            mergedCells: [
              "A1:E1"
            ],
            rows: [
              {
                height: 70,
                cells: [
                  {
                    index: 0, value: "CONTACTS FORM", fontSize: 32, background: "rgb(96,181,255)", enable: false,
                    textAlign: "center", color: "white"
                  }
                ]
              },
              {
                height: 25,
                cells: [
                  {
                    value: "Full Name", background: "rgb(167,214,255)", textAlign: "center", color: "rgb(0,62,117)", enable: false
                  },
                  {
                    value: "Email", background: "rgb(167,214,255)", textAlign: "center", color: "rgb(0,62,117)", enable: false
                  },
                  {
                    value: "Date of Birth", background: "rgb(167,214,255)", textAlign: "center", color: "rgb(0,62,117)", enable: false
                  },
                  {
                    value: "Phone", background: "rgb(167,214,255)", textAlign: "center", color: "rgb(0,62,117)", enable: false
                  },
                  {
                    value: "Confirmed", background: "rgb(167,214,255)", textAlign: "center", color: "rgb(0,62,117)", enable: false
                  }
                ]
              },
              {
                height: 25,
                cells: [
                  {
                    value: "Maria Anders",
                    validation: {
                      dataType: "custom",
                      from: "AND(LEN(A3)>3, LEN(A3)200)",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Full Name validation error",
                      messageTemplate: "The full name should be longer than 3 letters and shorter than 200."
                    }

                  },
                  {
                    value: "maria.anders@mail.com",
                    validation: {
                      dataType: "custom",
                      from: "AND(NOT(ISERROR(FIND(\"@\", B3))), NOT(ISERROR(FIND(\".\", B3))), ISERROR(FIND(\" \", J1)), LEN(B3)>5)",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Email validation error",
                      messageTemplate: "The value entered is not an valid email address."
                    }
                  },
                  {
                    value: 31232,
                    format: "m/d/yyyy",
                    validation: {
                      dataType: "date",
                      comparerType: "between",
                      from: "DATEVALUE(\"1/1/1900\")",
                      to: "DATEVALUE(\"1/1/1998\")",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Birth Date validaiton error",
                      messageTemplate: "Birth Date should be between 1899 and 1998 year."
                    }
                  },
                  {
                    value: 0921123465,
                    validation: {
                      dataType: "custom",
                      from: "AND(ISNUMBER(D3),LEN(D3)<14)",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Phone validation error",
                      messageTemplate: "The value entered is not an valid phone number. Please enter numeric value with less than 14 digits."
                    }
                  },
                  {
                    value: true,
                    validation: {
                      dataType: "list",
                      from: "ListValues!A1:B1",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Invalid value",
                      messageTemplate: "Valid values are 'true' and 'false'."
                    }
                  }
                ]
              },

              {
                height: 25,
                cells: [
                  {
                    value: "Ana Trujillo",
                    validation: {
                      dataType: "custom",
                      from: "AND(LEN(A4)>3, LEN(A4)200)",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Full Name validation error",
                      messageTemplate: "The full name should be longer than 3 letters and shorter than 200."
                    }

                  },
                  {
                    value: "ana.trujillo@mail.com",
                    validation: {
                      dataType: "custom",
                      from: "AND(NOT(ISERROR(FIND(\"@\", B4))), NOT(ISERROR(FIND(\".\", B4))), ISERROR(FIND(\" \", J1)), LEN(B4)>5)",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Email validation error",
                      messageTemplate: "The value entered is not an valid email address."
                    }
                  },
                  {
                    value: 31222,
                    format: "m/d/yyyy",
                    validation: {
                      dataType: "date",
                      comparerType: "between",
                      from: "DATEVALUE(\"1/1/1900\")",
                      to: "DATEVALUE(\"1/1/1998\")",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Birth Date validaiton error",
                      messageTemplate: "Birth Date should be between 1899 and 1998 year."
                    }
                  },
                  {
                    value: 55554729,
                    validation: {
                      dataType: "custom",
                      from: "AND(ISNUMBER(D4),LEN(D4)<14)",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Phone validation error",
                      messageTemplate: "The value entered is not an valid phone number. Please enter numeric value with less than 14 digits."
                    }
                  },
                  {
                    value: true,
                    validation: {
                      dataType: "list",
                      from: "ListValues!A1:B1",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Invalid value",
                      messageTemplate: "Valid values are 'true' and 'false'."
                    }
                  }
                ]
              },

              {
                height: 25,
                cells: [
                  {
                    value: "Antonio Moreno",
                    validation: {
                      dataType: "custom",
                      from: "AND(LEN(A5)>3, LEN(A5)200)",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Full Name validation error",
                      messageTemplate: "The full name should be longer than 3 letters and shorter than 200."
                    }

                  },
                  {
                    value: "antonio.moreno@mail.com",
                    validation: {
                      dataType: "custom",
                      from: "AND(NOT(ISERROR(FIND(\"@\", B5))), NOT(ISERROR(FIND(\".\", B5))), ISERROR(FIND(\" \", J1)), LEN(B5)>5)",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Email validation error",
                      messageTemplate: "The value entered is not an valid email address."
                    }
                  },
                  {
                    value: 32232,
                    format: "m/d/yyyy",
                    validation: {
                      dataType: "date",
                      comparerType: "between",
                      from: "DATEVALUE(\"1/1/1900\")",
                      to: "DATEVALUE(\"1/1/1998\")",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Birth Date validaiton error",
                      messageTemplate: "Birth Date should be between 1899 and 1998 year."
                    }
                  },
                  {
                    value: "(5) 555-3932",
                    validation: {
                      dataType: "custom",
                      from: "AND(ISNUMBER(D5),LEN(D5)<14)",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Phone validation error",
                      messageTemplate: "The value entered is not an valid phone number. Please enter numeric value with less than 14 digits."
                    }
                  },
                  {
                    value: true,
                    validation: {
                      dataType: "list",
                      from: "ListValues!A1:B1",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Invalid value",
                      messageTemplate: "Valid values are 'true' and 'false'."
                    }
                  }
                ]
              },

              {
                height: 25,
                cells: [
                  {
                    value: "Thomas Hardy",
                    validation: {
                      dataType: "custom",
                      from: "AND(LEN(A6)>3, LEN(A6)200)",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Full Name validation error",
                      messageTemplate: "The full name should be longer than 3 letters and shorter than 200."
                    }

                  },
                  {
                    value: "thomas.hardy@mail.com",
                    validation: {
                      dataType: "custom",
                      from: "AND(NOT(ISERROR(FIND(\"@\", B6))), NOT(ISERROR(FIND(\".\", B6))), ISERROR(FIND(\" \", J1)), LEN(B6)>5)",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Email validation error",
                      messageTemplate: "The value entered is not an valid email address."
                    }
                  },
                  {
                    value: 21232,
                    format: "m/d/yyyy",
                    validation: {
                      dataType: "date",
                      comparerType: "between",
                      from: "DATEVALUE(\"1/1/1900\")",
                      to: "DATEVALUE(\"1/1/1998\")",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Birth Date validaiton error",
                      messageTemplate: "Birth Date should be between 1899 and 1998 year."
                    }
                  },
                  {
                    value: 1715557788,
                    validation: {
                      dataType: "custom",
                      from: "AND(ISNUMBER(D6),LEN(D6)<14)",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Phone validation error",
                      messageTemplate: "The value entered is not an valid phone number. Please enter numeric value with less than 14 digits."
                    }
                  },
                  {
                    value: true,
                    validation: {
                      dataType: "list",
                      from: "ListValues!A1:B1",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Invalid value",
                      messageTemplate: "Valid values are 'true' and 'false'."
                    }
                  }
                ]
              },

              {
                height: 25,
                cells: [
                  {
                    value: "Christina Toms",
                    validation: {
                      dataType: "custom",
                      from: "AND(LEN(A7)>3, LEN(A7)200)",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Full Name validation error",
                      messageTemplate: "The full name should be longer than 3 letters and shorter than 200."
                    }

                  },
                  {
                    value: "christina.toms",
                    validation: {
                      dataType: "custom",
                      from: "AND(NOT(ISERROR(FIND(\"@\", B7))), NOT(ISERROR(FIND(\".\", B7))), ISERROR(FIND(\" \", J1)), LEN(B7)>5)",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Email validation error",
                      messageTemplate: "The value entered is not an valid email address."
                    }
                  },
                  {
                    value: 30102,
                    format: "m/d/yyyy",
                    validation: {
                      dataType: "date",
                      comparerType: "between",
                      from: "DATEVALUE(\"1/1/1900\")",
                      to: "DATEVALUE(\"1/1/1998\")",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Birth Date validaiton error",
                      messageTemplate: "Birth Date should be between 1899 and 1998 year."
                    }
                  },
                  {
                    value: 0921123465,
                    validation: {
                      dataType: "custom",
                      from: "AND(ISNUMBER(D7),LEN(D7)<14)",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Phone validation error",
                      messageTemplate: "The value entered is not an valid phone number. Please enter numeric value with less than 14 digits."
                    }
                  },
                  {
                    value: true,
                    validation: {
                      dataType: "list",
                      from: "ListValues!A1:B1",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Invalid value",
                      messageTemplate: "Valid values are 'true' and 'false'."
                    }
                  }
                ]
              },

              {
                height: 25,
                cells: [
                  {
                    value: "Hanna Moos",
                    validation: {
                      dataType: "custom",
                      from: "AND(LEN(A8)>3, LEN(A8)200)",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Full Name validation error",
                      messageTemplate: "The full name should be longer than 3 letters and shorter than 200."
                    }

                  },
                  {
                    value: "hanna.moos@mail.com",
                    validation: {
                      dataType: "custom",
                      from: "AND(NOT(ISERROR(FIND(\"@\", B8))), NOT(ISERROR(FIND(\".\", B8))), ISERROR(FIND(\" \", J1)), LEN(B8)>5)",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Email validation error",
                      messageTemplate: "The value entered is not an valid email address."
                    }
                  },
                  {
                    value: 0,
                    format: "m/d/yyyy",
                    validation: {
                      dataType: "date",
                      comparerType: "between",
                      from: "DATEVALUE(\"1/1/1900\")",
                      to: "DATEVALUE(\"1/1/1998\")",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Birth Date validaiton error",
                      messageTemplate: "Birth Date should be between 1900 and 1998 year."
                    }
                  },
                  {
                    value: 062108460,
                    validation: {
                      dataType: "custom",
                      from: "AND(ISNUMBER(D8),LEN(D8)<14)",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Phone validation error",
                      messageTemplate: "The value entered is not an valid phone number. Please enter numeric value with less than 14 digits."
                    }
                  },
                  {
                    value: true,
                    validation: {
                      dataType: "list",
                      from: "ListValues!A1:B1",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Invalid value",
                      messageTemplate: "Valid values are 'true' and 'false'."
                    }
                  }
                ]
              },
              {
                height: 25,
                cells: [
                  {
                    value: "",
                    validation: {
                      dataType: "custom",
                      from: "AND(LEN(A9)>3, LEN(A9)200)",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Full Name validation error",
                      messageTemplate: "The full name should be longer than 3 letters and shorter than 200."
                    }

                  },
                  {
                    value: "",
                    validation: {
                      dataType: "custom",
                      from: "AND(NOT(ISERROR(FIND(\"@\", B9))), NOT(ISERROR(FIND(\".\", B9))), ISERROR(FIND(\" \", J1)), LEN(B9)>5)",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Email validation error",
                      messageTemplate: "The value entered is not an valid email address."
                    }
                  },
                  {
                    value: "",
                    format: "m/d/yyyy",
                    validation: {
                      dataType: "date",
                      comparerType: "between",
                      from: "DATEVALUE(\"1/1/1900\")",
                      to: "DATEVALUE(\"1/1/1998\")",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Birth Date validaiton error",
                      messageTemplate: "Birth Date should be between 1899 and 1998 year."
                    }
                  },
                  {
                    value: "",
                    validation: {
                      dataType: "custom",
                      from: "AND(ISNUMBER(D9),LEN(D9)<14)",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Phone validation error",
                      messageTemplate: "The value entered is not an valid phone number. Please enter numeric value with less than 14 digits."
                    }
                  },
                  {
                    value: "",
                    validation: {
                      dataType: "list",
                      comparerType: "greaterThan",
                      from: "ListValues!A1:B1",
                      allowNulls: true,
                      type: "reject",
                      titleTemplate: "Invalid value",
                      messageTemplate: "Valid values are 'true' and 'false'."
                    }
                  }
                ]
              }
            ],
            columns: [
              {
                width: 100
              },
              {
                width: 215
              },
              {
                width: 115
              },
              {
                width: 115
              },
              {
                width: 115
              }
            ]
          },
          {
            name: "ListValues",
            rows: [ //A1:B1
              {
                cells: [
                  {
                    value: true
                  },
                  {
                    value: false
                  }
                ]
              }
            ]
          }
        ]
      }).data('kendoSpreadsheet').unbind('render', getFlaggedCells);
    });
  </script>
</div>

See Also

In this article