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 <script>..</script> 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() }); </script>

And the HTML table that gets rendered will be.

 <table id="fact" class="scroll" cellpadding="0" cellspacing="0"></table>
<div id="fact_pager" class="scroll" style="text-align:center;"></div>

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

Click here for larger image
Figure 1. "id" Not Editable, "NAME" Editable

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.

Click here for larger image
Figure 2. User Edits Row

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); } } },

Click here for larger image
Figure 3. New Value Gets Highlighted For Editing

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 => true
end
def 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
end
end
def 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 => true
end
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
respond_to do |format|
format.html { redirect_to(facts_url) }
format.xml { head :ok }
end
end
def 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(!@facts.nil?)
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