Sample Code

Text validation

In the method loadData of SheetAPI, it allows user add text validation to the sheet. .

Here comes the code example

    fileName: 'Validation Json File',
    sheets: [{
        name: 'First tab',
        color: 'orange',
        id: 1
        name: 'Second tab',
        id: 2
    floatings: [{
         json: "{"name":"vd","rng":[{"span":[1,2,2,4,4],"type":1}],"opt":{"dt":1,"op":8,"txt":"ok","hint":"ok","allow":true},"id":"validation-1"}"
    cells: [
         {sheet: 1, row: 2, col: 2, json: {data:\"ok\"}},
         {sheet: 1, row: 2, col: 3, json: {data:\"test\"}},
         {sheet: 1, row: 2, col: 4, json: {data:\"that is ok\"}},
         {sheet: 1, row: 3, col: 2, json: {data:\"ok123\"}},
         {sheet: 1, row: 3, col: 3, json: {data:\"fine\"}},
         {sheet: 1, row: 3, col: 4, json: {data:\"cool\"}},
         {sheet: 1, row: 4, col: 2, json: {data:\"me too\"}},
         {sheet: 1, row: 4, col: 3, json: {data:\"cool\"}},
         {sheet: 1, row: 4, col: 4, json: {data:\"too\"}}

The above code will insert a range of cells from B2:D4 by adding text validation.

For validation, loadData json object includes at least 4 names: fileName, sheets, floatings and cells.

NameProperty ExampleDescription
File namefileName fileName: 'Example Sheet' Define the file name for this sheet file.
Sheet objectsheets sheets: [{},{}] Set defined sheet tabs information for this file.
Floating objectfloatings floatings: [{},{}] Set defined floatings information for this file.
Cell objectcells cells: [{},{}] Set defined cell information for each tab.

Contain, Not Contain, Equal, Is valid email, Is valid URL validation

To add text validation, the following json object need to be added into floatings.

     sheet:1,              // the sheet id of added condition
     name:"validation1",   // name should be unique
     ftype:"cdt",          // define validation type
     json: "{name:\"vd\",rng:[{span:[1,2,2,4,4],type:1}],opt:{dt:1,op:8,text:\"ok\",hint:\"ok\",allow:true}},id:\"validation1\"}"

For each json object inside validation floating, it is a string with the following format.

  name       // vd
  span       // [sheetId, x1, y1, x2, y2] which define sheetId and a range of cells need to be added validation
      dt        // data type - should be set as 1 if it is text validation
      op        // 8 means Contain. 
                // 9 means Not Contain. 
                // 6 means Equal.
                // 10 means Is valid email.
                // 11 means Is valid URL.
      txt       // the text string to be compared  
      hint      // the display message for invalid data
      allow     // true or false. If it is true, it means allow enter invalid data, but show hint if it is invalid.
      ignoreBlank  // true - default is false if it is not set. If cell is blank, not apply validation if ignoreBlank is set true.
  id         // unique in this sheet file



Copyright © FeyaSoft Inc. All rights reserved.