codedecoder

breaking into the unknown…

cascading dropdown or autocomplete with handsontable excel

8 Comments

Recently for one of our project, we need to support excel editing online i,e user should able to enter his assets and liabilities details in browser itself and able to submit it online. we used handsontable library for this. It is great with detailed documentation and many events and call back.

My financial sheet created with handsontable look as below.

financial sheet

online financial sheet

So, its look great. The only problem is that, while filling real state detail user need to select state, suburb, and postal code from the dropdown. Now for any country there can be thousands of suburb and postal code. Loading such big data in the dropdown with script hang the page. What if we load suburb only within a selected state and postal code within the selected suburb. Well it will reduce the suburb to 50 and postal code to 100-200.

This call for cascading dropdown list implementation , where value of next dropdown based on value of its parent. Whether you are implementing cascading dropdown in pure HTML or jquery based handsontable here. The logic and flow will remain same :

STEP 1: when user try to select suburb without selecting state, alert message should be given “Select a State first”, same if he try to select postal code without selecting a suburb “Select a Suburb first”

STEP 2: When a state is selected, you should trigger a ajax call which will update the suburb drop down. Similarly when a Suburb is selected it should update the post code dropdown.

I am assuming, you have all the required handsontable js file in place. Also create a css and js file to write cutom js and css code related to the grid. Let us call it handsontable_custom.css and handsontable_custom.js

Below is the code which implement the cascading dropdown.

=> Controller Code :

class BrowserBasedExcelController < ApplicationController   
  require 'carmen'
  include Carmen

  def handsontable
    @real_state = [["Select", 3, 20, "Select", "Select", "Park Avenu", "Select", 0 ]]
  end
  # state corresponding to country passed in param is fetched with carmen gem
  def state_list
    us = Country.named(params[:country])
    subregions = us.subregions.map{|subregion| subregion.name}
    render :json => {"data" => subregions}
  end

  // we will fetch suburb corresponding to state passed in params. if
  // no state is present in params we will return back only" Select" in the list
  def suburb_list
    suburb = []
    if params[:state].present?  && (params[:state] != "Select")
      if params[:state] == "Alaska"
        suburb = ["Dedham", "Concord", "Chanhassen", "Minneapolis"]
      else
        suburb = ["Patna", "Gaya", "Dhanbad", "Ranchi"]
      end
    else
      suburb = ["Select"]
    end
    render :json => {"data" => suburb}
  end

  def postal_code
    post_code = []
    if params[:suburb].present? && (params[:suburb] != "Select")
      if params[:suburb] == "Dedham"
        post_code = ["55416", "92008", "20850", "07663"]
      else
        post_code = ["5555", "9299", "2222", "33333"]
      end
    else
      post_code = ["Select"]
    end
    render :json => {"data" => post_code}
  end

end

So my controller have 4 action, the first is the action which represent the financial page(for simplicity I have removed other code and kept the code which is needed to demo cascading dropdown). The other three are actions to which we will make the ajax call the get the list of suburb and post code.

NOTE:
The suburb and postal code is fetching dummy value from the ajax call for now. Iam using Carmen gem for listing different location.But it provide only the listing of all the country of the world and states within a specific country. It do not provide suburb details within a state or the postal codes within a suburb.try to find out some other gem which provides all the details of a location. If no such gem is available you have to create your own database containing all these data.

=> Create the routes :
We need routes corresponding to these action of controller.

  match 'handsontable'=> 'browser_based_excel#handsontable', :as => :handsontable
  match 'state_list'=> 'browser_based_excel#state_list', :as => :state_list
  match 'suburb_list'=> 'browser_based_excel#suburb_list', :as => :suburb_list
  match 'postal_code'=> 'browser_based_excel#postal_code', :as => :postal_code

=> customized handsontable css code

We will keep it in handsontable_custom.css file. We will add below line to it for now

.handsonbottom{
    padding-bottom: 10px;
    vertical-align: top
}

.wrapWords{
    overflow: hidden !important;
    text-overflow: ellipsis !important;
    white-space: nowrap !important;
}

=> customized handsontable js

We will keep it in handsontable_custom.js file. We will add below line to it for now.
NOTE : the below custom code is based on two other js file spin.min.js and jquery.blockUI.js , so download them and to your project

var create_spinner = function(target) {
    var opts = {
        lines : 13, // The number of lines to draw
        length : 20, // The length of each line
        width : 10, // The line thickness
        radius : 50, // The radius of the inner circle
        corners : 1, // Corner roundness (0..1)
        rotate : 0, // The rotation offset
        direction : 1, // 1: clockwise, -1: counterclockwise
        color : '#FFFF00', // #rgb or #rrggbb
        speed : 1, // Rounds per second
        trail : 60, // Afterglow percentage
        shadow : false, // Whether to render a shadow
        hwaccel : false, // Whether to use hardware acceleration
        className : 'spinner', // The CSS class to assign to the spinner
        zIndex : 2e9, // The z-index (defaults to 2000000000)
        top : 'auto', // Top position relative to parent in px
        left : 'auto' // Left position relative to parent in px
    };
    var spinner = new Spinner(opts);
    spinner.spin(document.getElementById(target));
    return spinner
}

=> View file

We will write code in view file to create online excel, for simplicity , I have removed other code used for the complete excel displayed above and kept only those which is needed to demonstrate cascading dropdown . Below code will create this excel

cascading drop down

cascading drop down

The handsontable action render our excel view so let us create  handsontable.html.erb file for this. Its content is as below.

<script>

  $(document).ready(function () {
    var realestate = <%= raw @real_state %>;

    function get_store_data(search_string){
      return $('#handsontable_block').data(search_string)
    }
    function set_store_data(search_string , data){
      $('#handsontable_block').data(search_string,data)
    }

    var wrapstring = function (instance, td, row, col, prop, value, cellProperties) {
      Handsontable.renderers.TextRenderer.apply(this, arguments);
      $(td).addClass("wrapWords")
    };
    function reale_state(){
      $('#realestate').handsontable({
        data: realestate,
        selectState: true,
        minSpareRows: 5,
        colWidths: [100, 100, 200, 70,100],
        colHeaders: ['State','Suburb', 'Street', 'Postcode'],
        columns: [
          {
            data: 0,
            type: 'autocomplete',
            source: function(query,process) {
              if(get_store_data('state'))
              {
                process(get_store_data('state'));
              }
              else
              {
                $("#handsontable_block").block({
                  message : null
                });
                spinner =  create_spinner('handsontable_block');

                $.ajax({
                  url: '/state_list',
                  data: {
                    country: "United States"
                  },
                  dataType: "json",
                  success: function (response) {
                    process(response.data);
                    set_store_data('state',response.data)
                    spinner.spin(false);
                    $('#handsontable_block').unblock();
                  },
                  error: function(response){
                    spinner.spin(false);
                    $('#handsontable_block').unblock();
                  }
                });
              }
            },
            strict: true

          },
          {
            data: 1,
            type: 'autocomplete',
            source: function(query,process) {
              var selected_state = $("#realestate").handsontable("getDataAtCell",this.row,0);
              if(!selected_state){
                selected_state = "Select"
              }

              if(get_store_data(selected_state) )
              {
                process(get_store_data(selected_state))
              }
              else
              {
                $("#handsontable_block").block({
                  message : null
                });
                spinner =  create_spinner('handsontable_block');

                $.ajax({
                  url: '/suburb_list',
                  data: {
                    state: selected_state
                  },
                  dataType: "json",
                  success: function (response) {
                    process(response.data);
                    set_store_data(selected_state , response.data)
                    spinner.spin(false);
                    $('#handsontable_block').unblock();
                  }
                });
              }
            },
            strict: true
          },
          {
            data: 2,
            renderer: wrapstring
          },
          {
            data: 3,
            type: 'dropdown',
            source: function(query,process) {
              var selected_suburb = $("#realestate").handsontable("getDataAtCell",this.row,1);
              if(!selected_suburb)
              {
                selected_suburb = 'Select'
              }

              if(get_store_data(selected_suburb) )
              {
                process(get_store_data(selected_suburb))
              }
              else
              {
                $("#handsontable_block").block({
                  message : null
                });
                spinner =  create_spinner('handsontable_block');

                $.ajax({
                  url: '/postal_code',
                  data: {
                    suburb: selected_suburb
                  },
                  dataType: "json",
                  success: function (response) {
                    process(response.data);
                    set_store_data(selected_suburb , response.data);
                    spinner.spin(false);
                    $('#handsontable_block').unblock();
                  }
                });
              }
            }
          }
        ],
        contextMenu: true
      });
    }

    // I have added afterSelection event on the setting selectState. any handsontable instance have
    // with its value set to true will fire the below function  
    Handsontable.PluginHooks.add('afterSelectionEnd', function() {
      if(this.getSettings().selectState){
        var selected_cell = this.getSelected()
        var selected_state = this.getDataAtCell(this.getSelected()[0], 0)
        var selected_suburb = this.getDataAtCell(this.getSelected()[0], 1)
        var selected_post_code = this.getDataAtCell(this.getSelected()[0], 3)
        if((selected_cell[1]== 1) && (selected_suburb ==null || selected_suburb =="Select") ){
          if (selected_state == null || selected_state == "Select"){
            alert("Select a State first.")
          }
        }
        if((selected_cell[1]== 3) && (selected_post_code == null || selected_post_code =="Select") ){
          if (selected_suburb == null || selected_suburb == "Select"){
            alert("Select a Suburb first.")
          }
        }
      }
    });

</script>
<div id ="handsontable_block">
<table border="1" width="100%">
   <tr  valign="top">
      <td colspan="7" width="100%">
            <div id="realestate"  class="handsontable handsonbottom"></div>
      </td>
   </tr>
</table>
</div>

See that we have minimal HTML above, the excel is created just by calling handsontable function on the div object with ID realestate.

Now we have a functional cascading dropdown working in online excel sheet.

The above implementation demonstrate below feature

=> Cascading dropdown through ajax call.

suburb will be populated based on state and postal code will be populated based on suburb

=> Blocking GRID when ajax call in progress

when user click a state or suburb dropdown it will fire a ajax call, which will take 2 to 3 second to complete. The Grid will show a spinner indicating the ongoing ajax call in progress and also block the UI so that user do not click any other dropdown. The UI will be unblocked on completion of the ajax call

=> caching of data

say we have retrieved all the state in India, so if user again select country as India we do not need to fire the ajax call to fetch its state. It is cached with data method of jQuery

=> Cell based css

The street cell may have long text. The above code apply truncation to the string in street cell if it increases the specified size of the cell, here the extra text will be displayed with dots and when user clicks the cell the complete text will be displayed.

Reference :

http://handsontable.com/index.html
https://github.com/warpech/jquery-handsontable/wiki/Events #handsontable event
https://github.com/warpech/jquery-handsontable/wiki/Options # options
https://github.com/warpech/jquery-handsontable/wiki/Methods #methods

Author: arunyadav4u

over 7 years experience in web development with Ruby on Rails.Involved in all stage of development lifecycle : requirement gathering, planing, coding, deployment & Knowledge transfer. I can adept to any situation, mixup very easily with people & can be a great friend.

8 thoughts on “cascading dropdown or autocomplete with handsontable excel

  1. Hi, Thanks for the approach.

    wat is called handsontable_block…. ?

    is again separate invisible handsontable?

    actually m trying to reduce the no.of ajax calls. so trying to “get_store_data” , set_store_data method.

    Can u post the simple working code.

    Many Thanks
    Prakie

    • trying to understand the “get_store_data” , set_store_data method’s usage.!

      • get_store_data , set_store_data are custom method defined to store the data(see the script under view part above)

            function set_store_data(search_string , data){
              $('#handsontable_block').data(search_string,data)
            }
            
           function get_store_data(search_string){
              return $('#handsontable_block').data(search_string)
            }
            

        The above function simply call the data method of jquery, which link or set any arbitrery data with any element you want.It need the key value pair.while reading you just need to pass the key

    • handsontable_block is simply the id of the div within which basic structure for the handson table is created, you can give it any name. see below line under view part above .

      <div id="handsontable_block">
      <table border="1" width="100%">
         <tr valign="top">
            <td colspan="7" width="100%">
                  <div id="realestate" class="handsontable handsonbottom"></div>
            </td>
         </tr>
      </table>
      </div>
      

      Note that, only above html is required to create the grid, remaining work will be done by the script placed in the same file.

  2. I wonder how this fares in terms of performance. I haven’t tried it but the idea is to have records or rows each having cascading dropdowns in like three columns. Eg:

    Row1 > Product Category > Product Group (cascaded value from Product Category) > Product (cascaded value from Product Category)

    • It do have performance overhead. Handson table refresh the whole table for any change made to any of its cell. Infact, its this feature is used to create the cascading dropdown. say user change the state, then handson script will try to refresh the whole table and at this point, the ajax call for suburb will again executed, fetching the suburb within the current state.

      To make handson table to work fast, try to minimize external call as far as possible.But sometime you do not have any option, but to compromise on performance. As far as this specific case is concerned, handson table work faster with this approach, just imagine implementing post code dropdown without above approach. There is 1000 of post code within a country. Handson table will hang if it try to loop over all of them to create the post code options.

  3. If a person entered all 4 values for a row and then decided that they selected the wrong state (Arkansas instead of Alaska, for example) and they changed their selection in column 0, would that clear out everything for columns 1-3?

  4. Thanks a lot @arunyadav4u, this is what I was looking for, I tried and it works.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s