Named Ranges, JavaScript Programming Example of Named Ranges in SpreadSheet

JavaScript Programming Example of Named Ranges in SpreadSheet

You can give a name to a particular range of cells and then use it in formulas. In this case the formula will be easier to understand and work with. Download a free programming example of Named Ranges.

JS Code

webix.ready(function(){

  var dialog = webix.ui({
    view: "window",
    id: "myDialog",
    move: true,
    close:true,
    head: "Range Name",
    autoheight: true,
    position: "center",
    body: {
      view: "form",
      elements: [
        {view: "text", name: "name", label: "Name", required:true},
        {view: "checkbox", name: "global", label: "Global"},
        {view: "button", value:"Save", click:function(){
          var form = this.getFormView();
          var range = $$("ssheet").getSelectedRange();

          if(!range)
            $$("ssheet").alert({text:"Please select a range."});
          else if(form.validate()){
            var values = form.getValues();
            //adds a range to the current sheet or global
            $$("ssheet").ranges.add(values.name, range, !!values.global);

            form.clear();
            dialog.hide();
          }
        }}
      ]
    }
  });

  function getRanges(){
    //gets ranges available for the current sheet
    var ranges = $$("ssheet").ranges.getRanges();
    var text = JSON.stringify(ranges, null," ");
    $$("txt").setValue(text);
  }

  function clearRanges(){
    //clears ranges from the current sheet
    $$("ssheet").ranges.clear();
    $$("txt").setValue("");
  }

  var buttons = {
    width:250, view: "form",
    rows:[
      { view:"button", value:"Add range", click: showDialog},
      { view:"button", value:"Get ranges", click:getRanges},
      { view:"button", value:"Clear ranges", click:clearRanges},
      { view:"textarea", id:"txt" }
    ]
  };
  webix.ui({
    cols:[
      buttons,
      {
        view:"spreadsheet",
        id: "ssheet",
        data: base_data,
        toolbar: "full"
      }
    ]

  });

  function showDialog(){
    if(!dialog.isVisible()){
      dialog.show();
      dialog.getBody().focus();
    }
  }
});

HTML Code

<script>
  var base_data = {
    "styles": [
      ["top","#FFEFEF;#6E6EFF;center;'PT Sans', Tahoma;17px;"],						
      ["subtop","#818181;#EAEAEA;center;'PT Sans', Tahoma;15px;;;bold;;;"],
      ["sales","#818181;;center;'PT Sans', Tahoma;15px;;;bold;;;"],
      ["total","#818181;;right;'PT Sans', Tahoma;15px;;;bold;;;"],
      ["count","#818181;#EAEAEA;center;'PT Sans', Tahoma;15px;;;;;;"],
      ["calc-top","#818181;#EAEAEA;;'PT Sans', Tahoma;15px;;;bold;;;"],
      ["calc-other","#818181;#EAEAEA;;'PT Sans', Tahoma;15px;;;bold;;;"],
      ["values","#000;#fff;right;'PT Sans', Tahoma;15px;;;;;;;price"]
    ],
    "sizes": [
      [0,1,125],
      [0,3,137],
      [0,4,137],
      [0,5,137]
    ],
    "data": [
      [1,1,"Report - July 2016","top"],    
      [2,1,"Region","subtop"],
      [2,2,"Country","subtop"],
      [2,3,"Sales - Group A","sales"],
      [2,4,"Sales - Group B","sales"],
      [2,5,"Total","total"],
      [3,1,"Europe","count"],
      [3,2,"Germany","count"],
      [3,3,"188400","values"],
      [3,4,"52000","values"],
      [3,5,"=C3+D3","values"],
      [4,1,"Europe","count"],
      [4,2,"France","count"],
      [4,3,"192200","values"],
      [4,4,"12000","values"],
      [4,5,"=C4+D4","values"],
      [5,1,"Europe","count"],
      [5,2,"Poland","count"],
      [5,3,"68900","values"],
      [5,4,"8000","values"],
      [5,5,"=C5+D5","values"],
      [6,1,"Asia","count"],
      [6,2,"Japan","count"],
      [6,3,"140000","values"],
      [6,4,"14000","values"],
      [6,5,"=C6+D6","values"],
      [7,1,"Asia","count"],
      [7,2,"China","count"],
      [7,3,"50000","values"],
      [7,4,"4800","values"],
      [7,5,"=C7+D7","values"]
  ],
    "spans": [
      [1,1,5,1]
    ]
  };  
</script>

Would you like to be able to name and rename ranges in your spreadsheet?