Create a Rails-based Excel Clone with jqGrid

In my previous article, Combine jqGrid and Rails for Beautified Business Applications, I began an article series explaining how to build a rich, data-driven Rails application by adding a jQuery grid (jqGrid) to it. (Thanks to a great Rails plugin that made integrating Rails with jqGrid much easier.) Specifically, the discussion in that first article was about how you could enable CRUD operations and create corresponding icons for jqGrid. In this article, I continue the series by explaining how you can enable the selection and editing of rows in the grid.

Selecting and Editing a Single Row of jqGrid

As discussed in the previous article, users can call a rich jqGrid table in the browser by using the JavaScript code below, within the tags.

         var mygrid = jQuery("#fact").jqGrid({ url:'/facts?q=1', editurl:'/facts/update', datatype: "json", colNames:['ID','NAME'], colModel:[{name:'id', index:'id',resizable:false,width:135, editable:false },{name:'NAME', index:'NAME',edittype:'text',editable:true}], pager: '#fact_pager', rowNum:$row_num, rowList:$no_elem, imgpath: '/images/jqgrid', sortname: '', viewrecords: true, height:$table_height, width: $table_width, sortorder: '', gridview: false, scrollrows: true, autoheight: false, autowidth: false, rownumbers: false, multiselect: false, subGrid: true, ondblClickRow: function(id) { if (id) { var grid = jQuery('#fact');     if(id != prev) { grid.saveRow(prev);                     grid.editRow(id, true);                        prev = id; }     else { prev = -1;            grid.saveRow(prev); } } }, onKeyup: function(id){ grid.saveRow(id); }, caption: "Features" }).navGrid('#fact_pager', {edit:true,add:true,del:true,search:true,refresh:true, go:true} )
mygrid.filterToolbar();mygrid[0].toggleToolbar() });

And the HTML table that gets rendered will be.

       

Now note the colModel field “id” and “NAME”, respectively. You can clearly see that “id” is not editable but “NAME” is.

jqGrid and Rails Business Applications

Also note the icons at the bottom of the table. If the user wants to edit the third row (i.e. AVG COST), he or she can choose that particular row and click the pencil (edit) icon beneath. On choosing the row, it gets highlighted as shown below.

jqGrid and Rails Business Applications

Users can edit the value and change the contents to say “AVERAGE COST”. However, users can also do inline editing of the values by adding a simple jQuery code. In the script shown below, the user has declared a variable by name, prev. If the user double clicks on a different row (that is other than the one that has been chosen presently), the old value gets saved (grid.saveRow(prev)) and the new value gets highlighted for editing as shown in Figure 3.

     ondblClickRow: function(id) { if (id) { var grid = jQuery('#fact');     if(id != prev) { grid.saveRow(prev);                     grid.editRow(id, true);                        prev = id; }     else { prev = -1;            grid.saveRow(prev); } } },

jqGrid and Rails Business Applications

The corresponding backend code for handling CRUD operations is shown in the listing below. Note that “Fact” is the Model Class.

def post_data          if params[:oper] == "del"            Fact.find(params[:id]).destroy          else            @fact = { :id => params[:id], :NAME => params[:NAME]                             }            if params[:id] == "_empty"              Fact.create(@fact)            else              Fact.find(params[:id]).update_attributes(@fact)            end          end          render :nothing => trueenddef create    @fact = Fact.new(params[:fact])    respond_to do |format|      if @fact.save        flash[:notice] = 'Fact was successfully created.'        format.html { redirect_to(@fact) }        format.xml  { render :xml => @fact, :status => :created, :location => @fact }      else        format.html { render :action => "new" }        format.xml  { render :xml => @fact.errors, :status => :unprocessable_entity }      end    endenddef update          if params[:oper] == "del"            Fact.find(params[:id]).destroy          else            @fact = { :id => params[:id], :NAME => params[:NAME]                             }            if params[:id] == "_empty"              Fact.create(@fact)            else              Fact.find(params[:id]).update_attributes(@fact)            end          end          render :nothing => trueenddef destroy    ids = params[:id].split(",");    @facts = Fact.find(ids)    if(@facts.nil?)        puts "**********************"        puts "No item found for deletion"        puts "**********************"    end    @facts.each do |fact|        fact.destroy    end    respond_to do |format|      format.html { redirect_to(facts_url) }      format.xml  { head :ok }    endenddef index      if(!params[:page].nil?)                params[:page] = params[:page]      end      if(!params[:rows].nil?)                params[:rows] = params[:rows]      end      if(!params[:searchField].nil? && !params[:searchField].blank?)        if(!params[:searchOper].nil?)                if(!params[:searchString].nil?)                        if(params[:searchOper] == "eq")                                cond = "#{params[:searchField]} = #{params[:searchString]}"                                @facts = Fact.find(:all, :conditions => ["#{params[:searchField]} = ?", "#{params[:searchString]}"])                        else                                @facts = Fact.find(:all, :conditions => ["#{params[:searchField]} like ?", "%#{params[:searchString]}%"])                        end                        params[:records] = @facts.size()                end                if([email protected]?)                        respond_to do |format|                            format.html                            format.json { render :json => @facts.to_jqgrid_json([:id,:NAME],
params[:page], params[:rows], params[:records]) } end else puts "Gungu" end end else params[:records] = Fact.count(:all) # This will work only on Rails 3 Fact.order("id").limit(params[:rows]).offset((params[:page].to_i - 1)*params[:rows].to_i); respond_to do |format| format.html format.json { render :json => @facts.to_jqgrid_json([:id,:NAME],
params[:page], params[:rows], params[:records]) } end end end

Selecting and Editing Multiple Rows in jqGrid

As discussed earlier, users can produce a rich jqGrid table in the browser with the JavaScript code below, within the tags.

         var mygrid = jQuery("#fact").jqGrid({ url:'/facts?q=1', editurl:'/facts/update', datatype: "json", colNames:['ID','NAME'], colModel:[{name:'id', index:'id',resizable:false,width:135, editable:false },{name:'NAME', index:'NAME',edittype:'text',editable:true}], pager: '#fact_pager', rowNum:$row_num, rowList:$no_elem, imgpath: '/images/jqgrid', sortname: '', viewrecords: true, height:$table_height, width: $table_width, sortorder: '', gridview: false, scrollrows: true, autoheight: false, autowidth: false, rownumbers: false, multiselect: true, subGrid: true, ........

Note the multiselect field is highlighted and set to true. This field creates (enables) the checkboxes as shown in the Figure 4 below.

jqGrid and Rails Business Applications

The user can select the rows by either clicking on that particular row or by clicking the checkbox. JqGrid takes care of the editing of the fields. By clicking the “edit” [pencil] icon at the bottom of the table, users can traverse through the fields of each row and edit the values. This is very similar to what was discussed in the previous section. In Figure 5 below, the row containing the value “COST” has been selected.

jqGrid and Rails Business Applications

One of the important features that a user typically expects with multiselect is deletion. For any deletion to happen, users have to fetch the ID or the rows that need to be passed to the backend for deletion.

     ondblClickRow: function(id) { if (id) { var grid = jQuery('#fact');     if(id != prev) { grid.saveRow(prev);                     grid.editRow(id, true);                        prev = id; }     else { prev = -1;            grid.saveRow(prev); } } },      onSelectRow: function(id) {                        var gr = jQuery("#fact").getGridParam('selarrrow');              },     onKeyup: function(id){                      grid.saveRow(id);              },

JqGrid allows users to fetch the selected rows and the contents using the onSelectRow function. The values within the grid can be fetched using getGridParam. Note the selarrrow parameter to getGridParam. That is not misspelled; it is indeed ‘sel’ + ‘arr’ + ‘row’. Selarrrow returns an array of IDs of all the fetched IDs.

The backend controller code includes the code for deletion. In the destroy method of the controller class, all the IDs that were marked for deletion are fetched from the params[:id] parameter.

  def post_data          if params[:oper] == "del"            destroy          else            @fact = { :id => params[:id], :NAME => params[:NAME], :TYPE => params[:TYPE] }            if params[:id] == "_empty"              create            else              Fact.find(params[:id]).update_attributes(@fact)            end          end          render :nothing => true  end  # DELETE /facts/1  # DELETE /facts/1.xml  def destroy    ids = params[:id].split(",");    @facts = Fact.find(ids)    if(@facts.nil?)        puts "**********************"        puts "No item found for deletion"        puts "**********************"    end    @facts.each do |fact|        fact.destroy    end    @facts = Fact.find(:all, :limit => params[:rows],                                                :offset => (params[:page].to_i - 1) * params[:rows].to_i);  endend

Selarrrow may sound a bit weird and at times users may want to select the ID of just one row. For that, getGridParam('selrow') returns the ID of the presently selected row. You can look into jqGrid’s getGridParam documentation for more details on the other methods that are available.

In upcoming articles in this series, the focus will be on selecting and using multiple rows in jqGrid.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: