JqGrid DataBinding exception while exporting to excel file

Question

I am trying to export JqGrid to excel so i follow this instruction and i use it like at below.

        var grid = new JqGridModelParticipiant().JqGridParticipiant;

        var query = db.ReservationSet.Select(r => new
        {
            r.Id,
            Name = r.Doctor.Name,
            Identity = r.Doctor.Identity,
            Title = r.Doctor.Title.Name,
            Total = r.TotalTL,
            Organization = r.Organization.Name
        });

        grid.ExportToExcel(query,"file.xls");

And i get below exception on the line of " grid.ExportToExcel(query,"file.xls");"

Data binding directly to a store query (DbSet, DbQuery, DbSqlQuery) is not supported. Instead populate a DbSet with data, for example by calling Load on the DbSet, and then bind to local data. For WPF bind to DbSet.Local. For WinForms bind to DbSet.Local.ToBindingList().

As far as i understand that it expect to have ObservableCollection that is on DbSet.Local member. But i am working on projected query so i can't do that.

What is the solution for this problem.

Answer 1

In the answer I posted the demo which shows how to implement export to Excel (real *.XLSX file instead of HTML fragment renamed to *.XLS used here).

The method used for exported to the Excel in jqSuite (the demo) produce HTML fragment like

HTTP/1.1 200 OK
Cache-Control: private
Content-Type: application/excel; charset=utf-8
Server: Microsoft-IIS/7.0
X-AspNetMvc-Version: 2.0
content-disposition: attachment; filename=grid.xls
X-AspNet-Version: 2.0.50727
X-Powered-By: ASP.NET
Date: Fri, 29 Jun 2012 14:24:54 GMT
Connection: close

<table cellspacing="0" rules="all" border="1" id="_exportGrid" style="border-collapse:collapse;">
    <tr>
        <td>OrderID</td><td>CustomerID</td><td>OrderDate</td><td>Freight</td><td>ShipName</td>
    </tr><tr>
        <td>10248</td><td>VINET</td><td>1996/07/04</td><td>32.3800</td><td>Vins et alcools Chevalier</td>
    </tr><tr>
        <td>10249</td><td>TOMSP</td><td>1996/07/05</td><td>11.6100</td><td>Toms Spezialitäten</td>
    </tr><tr>
        <td>10250</td><td>HANAR</td><td>1996/07/08</td><td>65.8300</td><td>Hanari Carnes</td>
    </tr><tr>
    ...
</table>

instead of creating of real Excel file. The way is very unsafe because at the opening the "Standard" type of data will be always used. For example if you would export the data like

<td>10249</td><td>TOMSP</td><td>1996/07/05</td><td>11.02.12</td><td>Toms Spezialitäten</td>

the text "11.02.12" will be automatically converted to the date 11.02.2012 if German locale are used as default:

The name "Toms Spezialitäten" from will be wrong displayed as "Toms Spezialitäten".

It can be especially dangerous in case of large table where some small part of data in the middle of grid will be wrong converted. In one project I displayed information about Software and some software versions will be wrong converted to the Date type.

Because of such and other close problems I create real Excel file on the server using Open XML SDK 2.5 or Open XML SDK 2.0. In the way one have no problems described above. So I recommend you to follow the approach described in my old answer.