Top

Spreadsheet

In this guide you will learn how to develop a program to generate and use spreadsheets, giving you the possibility to create new ones, to load and to copy existing ones.

By referring to this guide, you will be able to:

  • Use filters to simulate a server to fecth data from an external xml file.
  • Use the submitJsonRequest to request data.
  • Use the aria templates asynch paradigm.
  • Use partial refresh mechanism with repeaters.
  • Use aria templates widget @aria:TextField.

Tutorial

Step 1

Create a file using your favourite editor and call it index.html. This will be the bootstrap for your app.

Save it inside the root of your project (e.g. /guides/spreadsheet/).

Step 2

Inside index.html you will have to load the aria templates framework, define the container div and load the template that you will create.

To load the framework:

<script type="text/javascript" src="/aria/ariatemplates-1.2.0.js"></script>
<script type="text/javascript" src="/aria/css/atskin-1.2.0.js"></script>

To define the div container:

<div id="root"></div>
To load the template:
<script type="text/javascript">
Aria.loadTemplate({
    classpath:'ariadoc.guides.spreadsheet.view.Main',
    div:"root",
    // In this sample, we associate a controller to our template
    moduleCtrl: {
      classpath : "ariadoc.guides.spreadsheet.Controller"
    }
  });
</script>
Here we are defining also the module controller.

Step 3

Create a file and call it Main.tpl. This is the template that will be used to display the spreadsheet interface.

Save it inside the view folder inside your project (/guides/spreadsheet/view/).

Step 4

Define the classpath of your template and declare that it has a script and a CSS template.
{Template {
    $classpath:'ariadoc.guides.spreadsheet.view.Main',
    $hasScript: true,
    $css: ['ariadoc.guides.spreadsheet.view.MainStyle']
}}
Define the macro main():
{macro main()}
    {call mainLinks()/}
    {call mainLayout()/}
{/macro}
Define inside the macro mainLinks() the links to load, save, copy, cancel and create a new dataset using the @aria:Link widgets:
<div class="mainLinks">
  {var links=[
     {name:"Load Dataset",cbfn:"loadDataset"},
     {name:"New Dataset",cbfn:"createNewDataset"},
     {name:"Copy Dataset",cbfn:"copyDataset"},
     {name:"Save",cbfn:"saveChanges"},
     {name:"Cancel",cbfn:"cancelChanges"}
   ]/}
  {foreach lnk in links}
     {separator}&nbsp;|&nbsp;{/separator}
     {@aria:Link {
        label:lnk.name,
        onclick:{
          fn:lnk.cbfn,
          scope:moduleCtrl
        }
     }/}
  {/foreach}
and a section to display the list of the dataset loaded:
{section {
    id:"dsList",
    bindRefreshTo: [
       {to:"datasets", inside:data, recursive:false},
       {to:"currentDs", inside:data}
    ]
}}
   {if data.datasets.length==0}
      No dataset loaded yet..
   {else/}
      // Display list of currently loaded datasets
      Datasets:&nbsp;
      {foreach ds in data.datasets}
         {separator} - {/separator}
         {if ds==data.currentDs}
            <b>${ds.name}</b>
         {else/}
            <a>
               ${ds.name}
            </a>
         {/if}
      {/foreach}
   {/if}
{/section}
Define inside the macro mainLayout() a section to display the spreadsheet inside a table.

The table will have this structure:

  • for the first row call the dataDiv() macro to generate a div containing the data specified by the dataCat attribute;
  • for the second row call the scrollbarDiv() macro to generate a div that will handle the scrollbar;
  • for the third row call the dataDiv() macro to generate a div containing the data specified by the dataCat attribute;
  • for the fourth row call the scrollbarDiv() macro to generate a div that will handle the second scrollbar;
{section {
    id:"mainLayout",
    bindRefreshTo: [{to:"currentDs", inside:data}]
}}
   // process table width to have fixed column sizes
   // otherwise column size might change when content is updated
   {if data.currentDs!=null}
      {var borderWidth=1/}
      {var tblWidthFixed=borderWidth/}
      {var tblWidthScroll=borderWidth/}
      {foreach col in data.currentDs["fixedColumns"]}
          {set tblWidthFixed+=borderWidth+col.width/}
      {/foreach}
      {foreach col in data.currentDs["scrollableColumns"]}
          {set tblWidthScroll+=borderWidth+col.width/}
      {/foreach}
      <table class="mainLayout">
        <tbody>
          // query row
          <tr>
            <td class="dataBlock">{call dataDiv("fixedColumns","filter1","filter",tblWidthFixed)/}</td>
            <td class="dataBlock">{call dataDiv("scrollableColumns","filter2","filter",tblWidthScroll)/}</td>
            <td class="vscroll">&nbsp;</td>
          </tr>
          // scrollbar row
          <tr>
            <td class="hscroll">&nbsp;</td>
            <td class="hscroll">{call scrollbarDiv("hscroll1","hscrollbar","width:"+tblWidthScroll+"px")/}</td>
            <td class="hscroll">&nbsp;</td>
          </tr>
          // data row
          <tr>
            <td class="dataBlock">{call dataDiv("fixedColumns","part1","data",tblWidthFixed)/}</td>
            <td class="dataBlock">{call dataDiv("scrollableColumns","part2","data",tblWidthScroll)/}</td>
            <td class="vscroll">{call scrollbarDiv("vscroll","vscrollbar","height:2400px")/}</td>
          </tr>
          // 2nd scrollbar row
          <tr>
            <td class="hscroll">&nbsp;</td>
            <td class="hscroll">{call scrollbarDiv("hscroll2","hscrollbar","width:"+tblWidthScroll+"px")/}</td>
            <td class="hscroll">&nbsp;</td>
          </tr>
        </tbody>
      <table>
   {/if}
{/section}
Define the macro scrollbarDiv() to generate a div that will habdle the scrollbar using the onScroll event:
{macro scrollbarDiv(id,cssClass,contentStyle)}
 <div {id id/} class="${cssClass}" {on scroll {fn:"onScroll",args:id}/}>
   <div {id id+"Content"/} class="${cssClass}Content" {if contentStyle} style="${contentStyle}" {/if}>
     &nbsp;
   </div>
 </div>
{/macro}
Define the macro dataDiv() to generate a div containing the data specified by the dataCat attribute:
{macro dataDiv(colType,cssClass,type,tblWidth)}
 <div class="${colType} ${cssClass}">
  <div class="dataDiv">
    {if !data.currentDs || !data.currentDs.items || data.currentDs.items.length==0}
      &nbsp;
    {else/}
      <table class="datasheet">
        <thead>
          <tr>
            {foreach col in data.currentDs[colType]}
              <th>${col.title}</th>
            {/foreach}
          </tr>
        </thead>
        {repeater {                  
            loopType: "array",
            content: data.currentDs.items,
            type: "TBODY",
            childSections : {
                 id: "dsrow_"+colType,
                 bindRefreshTo: {fn:"getRowBindRefresh", scope:this},
                 macro: {
                     name: "dsRow",
                     args: [data.currentDs[colType]],
                     scope:this
                 },
                 type: "TR"
            }
        }/}
      </table>
    {/if}
  </div>
 </div>
{/macro}
Define the dsRow() macro to display a row of the dataset table:
{macro dsRow(cols, itm)}
  {var item=itm.item/}
  {var cellItm=null/}
  {var cls="cell"/}
  {if item.editMode}
    {set cls="selected"/}
  {/if}
  {foreach col in cols}
    {set cellItm=item[col.colId]/}
    <td class="${cls}">
      {if item.editMode}
       {@aria:TextField {
          autoselect:true,
          width: col.width-2,
          bind:{
            value:{to:"editValue",inside:cellItm}            
          }
        }/}
      {else/}
         ${cellItm.value}&nbsp;
      {/if}
    </td>
  {/foreach}
{/macro}
To switch the cell from read only state to editable state it is used the partial refresh, this means that when the user clicks to change the cell's value and when the user saves the change only the cell clicked is refresh instead of all the widgets or all the page.

Step 5

Create a file and call it MainScript.js.

Save it inside the view folder inside your project (/guides/spreadsheet/view/).

Step 6

Define the classpath and the constructor:
Aria.tplScriptDefinition({
  $classpath : 'ariadoc.guides.spreadsheet.view.MainScript',
  $constructor : function () {

  }
Define the onModuleEvent to refresh the dataset table:
onModuleEvent:function(evt) {
  return;
  if (evt.name=="methodCallBack") {alert(123);}
  if (evt.name=="methodCallEnd") {
    if (evt.method=="xloadDataset"
        || evt.method=="createNewDataset"
        || evt.method=="copyDataset"
        || evt.method=="selectDataset") {
          this.$refresh();
    }
  }
}

Note: To get more info about the onModuleEvent take a look at this page.

Define the onScroll() function to manage the vertical and horizontal scroll inside the dataset table:
 /**
  * Event Handler called when a scrollbar is used
  * Will change the content scroll and synchronize all scrollbars
  */
  onScroll: function(evt,id) {
    if (id=="vscroll") {
      // vertical scroll
      var vs=this.$getElementById("vscroll");
      if (vs) {
        var sTop=vs.getScroll().scrollTop;
        vs.$dispose();
        var arr=["dataCont_fixedColumns_data","dataCont_scrollableColumns_data"];
        for (sid in arr) {
          div=this.$getElementById(arr[sid]);
          if (div) {
            div.setScroll({scrollTop:sTop});
            div.$dispose();
          }
        }
      }
    } else {
      // horizontal scroll
      var hIdA=id, hIdB=(id=="hscroll1")? "hscroll2":"hscroll1";
      var hA=this.$getElementById(hIdA), div;
      if (hA) {
        var sLeft=hA.getScroll().scrollLeft;
        hA.$dispose();
        var arr=["dataCont_scrollableColumns_data","dataCont_scrollableColumns_filter",hIdB];
        for (sid in arr) {
          div=this.$getElementById(arr[sid]);
          if (div) {
            div.setScroll({scrollLeft:sLeft});
            div.$dispose();
          }
        }
      }
    }
  }
Define the onDsDataCellClick() function used when a data cell is clicked to allow the user to edit the cell content:
/**
 * Event Handler called when a data cell is clicked
 */
 onDsDataCellClick:function(evt) {
   var id=evt.target.getExpando("id",true);
   if (id=="TABLE") return; // event did not hapen in a cell
   var colId=evt.target.getExpando("colId",true);
   this.moduleCtrl.editCell(parseInt(id,10),colId);
 }
Define the getRowBindRefresh() function to return the bindRefreshTo for each data table row section and the selectDataset() function to call the controller method to select a different dataset:
/**
  * Return the bindRefreshTo value for each data table row section
  * @param {Object} itm the item passed as argument of each Repeater childSection callback
  * cf. Repeater documentation
  */
  getRowBindRefresh:function(itm) {
    var item=itm.item;
    return [{to:"editMode", inside:item}];
  },

/**
  * Event Handler called when a link/tab is clicked to change the current dataset
  * @param {Integer} idx the dataset index that should become the current one
  */
  selectDataset:function(evt,idx) {
    this.moduleCtrl.selectDataset(idx);
  }
}

Step 7

Create a file and call it ControllerInterface.js.

Save it inside the root folder inside your project (/guides/spreadsheet/).

Step 8

Define the classpath, which class the controller interface extends, that is always aria.templates.IModuleCtrl and write the signature of all the methods of your controller:
Aria.interfaceDefinition({
  $classpath : 'ariadoc.guides.spreadsheet.ControllerInterface',
  $extends : 'aria.templates.IModuleCtrl',
  $interface : {
    createNewDataset : {$type : "Function"},
    copyDataset : {$type : "Function"},
    loadDataset : {$type : "Function",$callbackParam: 1},
    editCell : {$type : "Function"},
    selectDataset : {$type : "Function"},
    saveChanges : {$type : "Function"},
    cancelChanges : {$type : "Function"}
  }
});

Step 9

Create a file and call it Controller.js.

Save it inside the root folder inside your project (/guides/spreadsheet/).

Step 10

Define the classpath, which class the controller extends, that is always aria.templates.ModuleCtrl, which controller interface implements, in our case ariadoc.guides.spreadsheet.ControllerInterface (that's the controller interface defined in the previous step), define the constructor and the destructor. Remember to add the filter to fetch the data from the xml file in order to simulate the server requests:
Aria.classDefinition({
  $classpath : 'ariadoc.guides.spreadsheet.Controller',
  $extends : 'aria.templates.ModuleCtrl',
  $implements : ['ariadoc.guides.spreadsheet.ControllerInterface'],
  $constructor : function () {
    this.$ModuleCtrl.constructor.call(this);
    // add test filter for response mocks
    aria.core.IOFiltersMgr.addFilter('ariadoc.guides.spreadsheet.mocks.TestMsgHandler');
    this._data = {
       currentDs:null, // current dataset - cf. createNewDataset for the structure
       datasets:[]   // list of loaded datasets
    };
  },
  $destructor : function () {
    this.$ModuleCtrl.$destructor.call(this);
  }
Define the copyDataset() function to copy an existing dataset:
/**
  * Copy the current dataset into a new dataset
  * and set is as current dataset
  */
  copyDataset:function() {
    if (!this._data.currentDs) return;
    var ds=this.json.copy(this._data.currentDs,true);
    // change name
    nm=ds.name.replace(/\(.*\)$/,'');
    ds.name=nm+"("+this._data.datasets.length+")";
    // add dataset to the list and select it
    this.json.add(this._data.datasets,ds);
    this.json.setValue(this._data,"currentDs",ds);
  }
Define the createNewDataset() function to create a new dataset:
/**
  * Create a new dataset and add it to the dataset list (but will not be selected by default)
  */
  createNewDataset:function(nm,createData,setAsCurrentDataset) {
    if (typeof(nm)!='string') nm="New "+this._data.datasets.length;
    if (typeof(createData)!='boolean') createData=true;
    if (typeof(setAsCurrentDataset)!='boolean') setAsCurrentDataset=true;

    var ds={
      name:nm,
      fixedColumns:[],
      scrollableColumns:[],
      items:[]
    }
    this.json.add(this._data.datasets,ds);
    if (createData) {
      // create Columns
      var nbrOfColumns=20, nbrOfFixedColumns=3, nbrOfLines=100;
      var colIds=[], d, colId;
      for (var i=0;nbrOfColumns>i;i++) {
        colId="c"+i;
        colIds.push(colId);
        d={
          colId:colId,
          title:"Col "+i,
          width:90
        }
        if (ii;i++) {
        d={editMode:false};
        for (var j=0, maxJ=colIds.length;maxJ>j;j++) {
          d[colIds[j]]={
            id:i,
            value: "", //+i+":"+j,
            editMode:false
          };
        }
        ds.items.push(d);
      }
    }
    if (setAsCurrentDataset) this.json.setValue(this._data,"currentDs",ds);
    return ds;
  }
Define the selectDataset() function to set a dataset as current dataset:
/**
  * Set a dataset as current dataset - i.e. referenced by 'currentDs'
  * in the data model
  * @param {Integer} dsIdx the dataset index in the datasets array
  */
  selectDataset : function (dsIdx) {
    if (dsIdx>-1 && this._data.datasets.length>dsIdx) {
      this.json.setValue(this._data,"currentDs",this._data.datasets[dsIdx]);
    }
  }
}
Define the cancelChanges() function to cancel the changes made in the current dataset:
/**
  * Cancel the changes made in the current dataset
  */
  cancelChanges: function () {
    var ds=this._data.currentDs;
    var cols=ds.fixedColumns.concat(ds.scrollableColumns);
    var items=ds.items, itm, colId;
    for (var i=0, iMax=items.length;iMax>i;i++) {
      itm=items[i];
      if (itm.editMode) {
        for (var k in cols) {
          colId=cols[k].colId;
          this.json.setValue(itm[colId],"editMode",false);
        }
        this.json.setValue(itm,"editMode",false);
      }
    }
    // TODO submit changes
  }
Define the saveChanges() function to save the changes made in the current dataset:
/**
  * Save the changes made in the current dataset
  */
  saveChanges: function () {
    var ds=this._data.currentDs;
    var cols=ds.fixedColumns.concat(ds.scrollableColumns);
    var items=ds.items, itm, colId;
    for (var i=0, iMax=items.length;iMax>i;i++) {
      itm=items[i];
      if (itm.editMode) {
        for (var k in cols) {
          colId=cols[k].colId;
          this.json.setValue(itm[colId],"editMode",false);
          this.json.setValue(itm[colId],"value",itm[colId].editValue);
        }
        this.json.setValue(itm,"editMode",false);
      }
    }
  }
Define the editCell() function to set a cell and its associated row to editable mode:
 /**
   * Set a cell and its associated row to editable mode
   * @param {Integer} itemId line id
   * @param {String} colId column id
   */
  editCell : function (itemId,colId) {
    // set the cell in edit mode
    // itemId is not necessarily the index in the array
    var ds=this._data.currentDs;
    var cols=ds.fixedColumns.concat(ds.scrollableColumns);
    var items=ds.items, itm, tmpId;
    for (var i=0, iMax=items.length;iMax>i;i++) {
      itm=items[i];
      if (itm[colId].id==itemId) {
        if (!itm.editMode) {
          // set all editValues (used to not change the value until save is called
          for (var k in cols) {
            tmpId=cols[k].colId;
            this.json.setValue(itm[tmpId],"editMode",true);
            this.json.setValue(itm[tmpId],"editValue",itm[tmpId].value);
          }
          //this.json.setValue(itm[colId],"hasFocus",true);
          this.json.setValue(itm,"editMode",true);
        }
        break;
      }
    }
  }
Define the loadDataset() function to make a Json request to load a dataset inside the data model and the _loadDataset() function to manage the answer from the server:
 /**
   * Load a dataset in the data model
   */
   loadDataset : function (ref,cb) {
     var requestData = {datasetRef:ref};
     this.submitJsonRequest("getDataset", requestData, {
       fn : "_loadDataset",
       scope : this,
       args : cb
     });
},

 /**
   * loadDataset callback - called when server response is received
   */
   _loadDataset: function (res, args) {
      var cb=args;
      if (res.error) {
        this.$callback(cb);
        return;
      }
      var ds = res.response;
      // modify the cell values to use a structure instead of a string
      var item, tmp;
      for (var i=0, iMax=ds.items.length;iMax>i;i++) {
        item=ds.items[i];
        for (var k in item) {
          if (k=="id") continue;
          tmp=item[k];
          item[k]={
            id:i,
            value:tmp
          }
        }
      }
      // add dataset to the list and select it
      this.json.add(this._data.datasets,ds);
      this.json.setValue(this._data,"currentDs",ds);
      this.$callback(cb);
  }

Step 11

Create a file and call it MainStyle.tpl.css.

Save it inside the view folder inside your project (/guides/spreadsheet/view/).

Step 12

Give some style to your todo app.

Step 13

That's it! Open the index.html with your favourite browser (e.g. Chrome) and enjoy.