jqgrid + EF + MVC: Is it possible to export in excel, using always the same controller action?

Question

I am using jqgrid (standard) with EF 4 + MVC3. I'd like to implement excel export and if possible using the same action controller used to populate the grid. I wonder if is it possible / logical to pass an additional parameter, for example. Which method you would suggest me? I ask this question because I am still approaching to implement excel export and I'd like to optimize / re-use code, if possible.

To generate excel, I'd like to use this library by Dr Stephen Walther, which has three types of output and allows to define headers too. Please tell me if you find it valid for my purpose.

About the jqgrid code, I found this interesting answer by Oleg, but I do not understand if could be applied to my needs.

Unfortunately, by now I only found parts of solutions for excel export with EF MVC, but no solution or complete examples...

Here's the _Index partial view containing my jqgrid

  <table id="mygrid"></table>
  <div id="pager2"></div>

  jQuery("#mygrid").jqGrid({
url:'controller/jqIndex',
datatype: "json",
colNames:['id','field1', ...],
colModel:[
    {name:'id',index:'id', width:55},
    {name:'field1',index:'field1', width:90},
            ...
],
rowNum:10,
rowList:[10,20,30],
pager: '#pager2',
sortname: 'id',
viewrecords: true,
sortorder: "desc",
caption:"modal jquery + jqgrid test"}); 
jQuery("#list2").jqGrid('navGrid','#pager2',{edit:false,add:false,del:false});

//TODO
???
...some code to call the controller action with the `excel` parameter set `true`

CONTROLLER (BASED ON OLEG'S IMPLEMENTATION)

     public ActionResult jqIndex(string sidx, string sord, int page, int rows, bool _search, string filters, bool excel) // note the excel parameter <<
       {
        var context = new TManagerContext();
        var objectContext = context.ObjectContext();

        var set = objectContext.CreateObjectSet<Ticket>();
        var serializer = new JavaScriptSerializer();

        Filters f = (!_search || string.IsNullOrEmpty(filters)) ? null : serializer.Deserialize<Filters>(filters);
        ObjectQuery<Ticket> filteredQuery = (f == null ? (set) : f.FilterObjectSet(set));

        filteredQuery.MergeOption = MergeOption.NoTracking; // we don't want to update the data


        int totalRecords = filteredQuery.Count();

        var pagedQuery = filteredQuery.Skip("it." + sidx + " " + sord, "@skip",
                                    new ObjectParameter("skip", (page - 1) * rows))
                             .Top("@limit", new ObjectParameter("limit", rows));

        int pageIndex = Convert.ToInt32(page) - 1;
        int pageSize = rows;

        int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);

        var queryDetails = (from e in pagedQuery
                            select new
                            {
                                e.TicketID,
                                e.field1,
                                ...
                            }).ToList();

        var result = new
        {
            total = totalPages,
            page = page,
            records = totalRecords,
            rows = (from e in queryDetails
                    select new
                    {
                        id = e.TicketID,
                        cell = new string[]
                        {
                            e.field1,
                            ...
                        }

                    }).ToArray()
        };

         if (excel) {
            ExportExcel(result); // if possible, pass filter parameters too, column order, etc...
         }

        return Json(result, JsonRequestBehavior.AllowGet);
    }

Please sorry if the question could be silly, I am just a (enthusiast) beginner.

Thanks for your precious help! Best Regards

Rumrunner

Answer 1

Larry - A few comments.

1. You shouldn't be doing that much logic in your controller. Move all of that business logic to another class/service. Then your action method would be just a few lines. A quick example

---

public JsonResult jqIndex(string sidx, string sord, int page, int rows, 
                          bool _search, string filters){
        return JSON(this.GridQueryService.GetJQGrid(sidx,sord,page,rows,_search,filters), JsosnRequestBehavior.AllowGet);
        }

2.I know you don't want to repeat code (which point 1 helps) but there are many parameters and things here that simply do not apply to Excel (page, rows).

3.Passing boolean parameters to change how things function can get messy fast. Lets assume that you now need to pass more/less data to the Excel file, now you have nested conditions all over the place and Unit Testing would just be crappy.

4.An excel action method will should have a FileResult return type, not a JSON result (I guess they are all action results, but this makes your intention all the more clear in your code. Your definition should be something like

---

public FileResult GetExcelFile(string sidx, string sord, bool _search, 
                               string filters){
              //do stuff to return Excel
        }

If you create your Service in point one in such a way that you have two methods that return different items, but share a common query/search base function, then you are really staying Dry while following the Single Responsibility Principle. An example of this service might be (very rough example, should give you some things to think about):

public class GridQueryService{
   public YourViewModel GetJQGrid(sidx, page, row, _search, filters){
      //Get the base data 
      var myData = this.GetGridData(sidx, _search, filters);
      //Create your view model and return it back to controller
} 
   public StreamWriter GetExcelFIle(sidx, _search, filters){
      //Get the base data 
      var myData = this.GetGridData(sidx, _search, filters);
      //Create your Excel file and return it to the controller
}

    private ObjectQuery<Ticket> GetGridData(string sidx, bool _search, string filters){
     //do your data grabbing here - you never return the raw data back to anything outside
     //of this service, so it should be ok to make private
}

}

Devoted

Tommy, thank you very much for your answer! I found it really focused on the main question. You suggested me the right way and the solution you proposed to me seems very sensible, rational and elegant. I am going to change the code following your valuable suggestions. As for how to export to excel, I am going to open a more specific question, the actual text of this went a little beyond the initial object. Thanks again! Best Regards, L – Rumrunner