jqgrid, export to excel (with current filter post data) in an asp.net-mvc site

Question

i have an asp.net-mvc web page and i am using jqgrid on the front end. i want to have an export to excel button that will export the current set of data (based on the current filter).

i already use the toolbar filter so i see that the filter settings are stored in post data but i can't figure out how to create a method that will pass along all of the filter settings / rules to the server from jqgrid.

i see a bunch of jqgrid "export to excel" example after googling but similar to this example, none of them seem to be passing the filter information to the serverside.

Plage

The usage of HtmlTextWriter to export data to HTML is quick & dirty solution. If you use numbers and not not only strings in the output, if you use currency, dates or custom formatters the HTML which you will use in Excel will be very rough. The user will have to make many type conversion to be able to sort the data for example. The list of disadvantages is very long. Really good data exporting to Excel you can make with respect of Open XML SDK 2.0 for Microsoft Office. The program will be longer, but the user will receive real XLSX file with correct data types. – Tunable

Answer 1

You could put hidden fields inside the Export to Excel form:

@using (Html.BeginForm(new { action = "ExportToExcel" }))
{
    @Html.Hidden("sidx")
    @Html.Hidden("sord")
    @Html.Hidden("page")
    @Html.Hidden("rows")
    <table id="list"></table>
    <input type="submit" value="Export to Excel" />
}

and populate them upon form submission based on the current values:

$('form').submit(function () {
    var grid = $('#list');
    var sortname = grid.getGridParam('sortname');
    var sortorder = grid.getGridParam('sortorder');
    var page = grid.getGridParam('page');
    var rows = grid.getGridParam('rowNum');
    $('#sidx').val(sortname);
    $('#sord').val(sortorder);
    $('#page').val(page);
    $('#rows').val(rows);
});

This way the ExportToExcel controller action will take those parameters and be able to filter the list.

Kilogram

I an getting Sidx value empty please help me – Inchworm

Answer 2

What I have done is put the gridstate into the cache each time data is requested, then I do the export to excel using the gridState. There are examples of this somewhere on the jqGrid site:

//this fragment in GetData method
Session["ExceptionGridState"] = gridModel.ExceptionGrid.GetState(true);

Then when the export is called:

public ActionResult ExportToExcel_CurrentData()
{
  var gridModel = new ExceptionJqGridModel();
  var grid = gridModel.ExceptionGrid;

  // call the ExportToExcel built-in method
  JQGridState gridState = Session["ExceptionGridState"] as JQGridState;
  gridState.CurrentPageOnly = false;
  grid.ExportToExcel(SourceQuery(),
        String.Format("SomeDatasetName_Filtered_{0:yyyymmddhhmm}.xls", 
            DateTime.Now), 
        gridState);

return View();

}

This works for me.

Wager

Thank you so much! I get inspiration by your solution using a session variable for each grid's parameter! – Denten

Answer 3

I succeed to make filtered export, taking inspiration by above @simpatric greg solution.

I set one session variable for each grid parameter, when data is requested and then passing again them to the excel export service. Greg's solution can work with asp.net MVC, which is ok for the main question. The following solution could be used with standard pure js jqgrid too:

CONTROLLER GRID ACTION

        ...
      Session["jqsidx"] = sidx; 
      Session["jqsord"] = sord; 
      Session["jqpage"] = page; 
      Session["jqrows"] = rows; 
      Session["jq_search"] = _search; 
      Session["jqfilters"] = filters; 
      ....

RECALLED INSIDE OF EXCEL EXPORT ACTION ^^

 string sidx = Session["jqsidx"] as String;
 string sord = Session["jqsord"] as String;
 int? page = Session["jqpage"] as Nullable<Int32>;
 int? rows = Session["jqrows"] as Nullable<Int32>;
 bool? _search = Session["jq_search"] as Nullable<bool>;
 string filters = Session["jqfilters"] as String;

var query = myqueryservice.getGridData(sidx, sord, (int)page, (int)rows, (bool)_search, filters, urlparams).ToList();
...

I hope this may help for other people having the same problem with standard jqgrid.

Denten