Click here to Skip to main content
11,636,300 members (79,015 online)
Click here to Skip to main content

Export Data from jqGrid into a "real" Excel File

, 4 Nov 2014 CPOL 46.1K 4.2K 23
Rate this:
Please Sign up or sign in to vote.
Easy-to-use library, to export the data from your jqGrid into a real Excel file.

Download source - 1.8Mb

Introduction

This walkthrough takes the C# code which I documented in my CodeProject "Free Export to Excel C# class" article, and extends it to allow you to export your data directly from a jqGrid control, into a real Excel .xlsx file.

Last year, I wrote the "Export to Excel" C# class as I couldn't find any articles on the internet showing how to easily create a real Excel file in C#. Using that class, you can export any DataSet, DataTable or List<> into a real Excel file using the OpenXML libraries simply by calling one CreateExcelDocument function:

DataSet ds = CreateSampleData();
CreateExcelFile.CreateExcelDocument(ds, "C:\\Sample.xlsx");

I've been overwhelmed by the feedback (and number of downloads) that this CodeProject article has received.

The same goes for this jqGrid walkthrough. If you Google "jqGrid Export to Excel", you will get numerous hits, but none of them give an easy-to-use (and re-use !) way of doing this, without using third-party software or sending your data to a .php file. You'll also read lots of developers suggesting that this exporting can't be done.

Note that this article assumes you are using Visual Studio, as it uses a .ashx handler and the OpenXML libraries.

Our Goal

During this walkthough, we will add an "Export to Excel" button to our jqGrid, and when you click on it, we'll create a "real" Excel 2007 .xlsx file containing your jqGrid's data.

To keep the code maintainable and easy-to-use, this new button simply needs to call a JavaScript function, passing it the name of your <table> where your jqGrid is stored, and what you would like to call the Excel file:

ExportJQGridDataToExcel("#tblOrders", "CustomerOrders.xlsx");

If you have hidden columns in your jqGrid, these will not get exported to Excel.

The main problem with this scenario is that when the user clicks on the Export button, the data is currently held in a jqGrid on the user's browser. To use my "Export to Excel" C# class to create the Excel file, we somehow need to transfer a copy of this data back to the server before we can export it.

Big disclaimer: this control assumes that you are using a jqGrid with loadonce set to true.  If this isn't the case, then my library simply won't have access to your entire data set, and will just end up exporting the current page of data.

 

Getting Started

I am assuming that you already have a .NET web application set up, containing a jqGrid control in it.

To add the "Export to Excel" functionality to your app, first, you need to add 5 files to your project. The attached "jqGridExport.zip" file contains the current versions of these files.

  • A copy of my C# "Export to Excel" CreateExcelFile.cs file
  • The two .dlls needed to use Microsoft's OpenXML libraries (DocumentFormat.OpenXml.dll and WindowsBase.dll)
  • The ExportGridToExcel.ashx handler
  • The jqGridExportToExcel.js JavaScript file

Note that the jqGridExportToExcel.js expects to find the ExportGridToExcel.ashx file in a folder called "Handlers".  If you decide to save the .ashx file into a different folder, you will need to manually alter the final line in the ExportJQGridDataToExcel function in jqGridExportToExcel.js.

//  Now, we need to POST our Excel Data to our .ashx file *and* redirect to the .ashx file.
postAndRedirect("/Handlers/ExportGridToExcel.ashx?filename=" + excelFilename, { excelData: excelData });

 

Next, add References to the two .dll files. Your code should now build without errors.

In the following example, I will create a jqGrid control and its paging status bar, using these DOM elements:

<table id="tblOrders"></table>
<div id="pager"></div>

I want to fill my jqGrid control with Customer Order information from my iNorthwind JSON web service, whose data you can view by clicking on this link:

Here's the JavaScript which I have used to create my jqGrid and its pager bar:

function LoadCustomerOrders() {

    //  Make sure IE allows us to load JSON data from the iNorthwind website
    jQuery.support.cors = true;

    //  Load our JSON data, and populate the jqGrid control with it
    $("#tblOrders").jqGrid({
        url: 'http://www.inorthwind.com/Service1.svc/getOrdersForCustomer/BERGS',
        contentType: "application/json",
        datatype: "json",
        data: "{}",
        jsonReader: {
            root: "GetOrdersForCustomerResult",     //arry containing actual data 
            id: "OrderID",                               //index of the column with the PK in it 
            repeatitems: false
        },
        mtype: "GET",
        colNames: ["ID", "Order Date", "Name", 
        "Address", "City", "Postcode", "Shipped Date"],
        colModel: [
            { name: "OrderID", width: 70, align: "center", search: false },
            { name: "OrderDate", search: true, width: 100 },
            { name: "ShipName", search: true, width: 120 },
            { name: "ShipAddress", search: true, hidden: true },
            { name: "ShipCity", search: true, width: 200 },
            { name: "ShipPostcode", search: true, width: 140 },
            { name: "ShippedDate", search: true, width: 80, align: "center" }
        ],
        pager: "#pager",
        width: 'auto',
        height: 'auto',
        rowNum: 10,
        rowList: [],
        loadonce: true,
        sortable: true,
        sortname: "OrderID",
        sortorder: "desc",
        viewrecords: true,
        gridview: true,
        autoencode: true,
        ignoreCase: true,   //  Make the "Search" popup search case-insensitive 
        caption: ""
    });

    $('#tblOrders').jqGrid('navGrid', '#pager', {
        search: true,
        searchtext: "Search",  //  Make the Search icon have a "Search" label next to it
        edit: false,
        add: false,
        del: false,
        refresh: false
    },
    {}, // default settings for edit
    {}, // default settings for add
    {}, // delete
    {
        closeOnEscape: true, closeAfterSearch: true, ignoreCase: true, 
        multipleSearch: false, multipleGroup: false, showQuery: false,
        sopt: ['cn', 'eq', 'ne'],
        defaultSearch: 'cn'
    });
}

There's really nothing new here.

If you already have a jqGrid control on your webpage, your JavaScript code should look something like that.

To add the new "Export to Excel" button, first, we need to add a navButtonAdd function to our pager:

    $('#tblOrders').jqGrid('navGrid', '#pager', {
        search: true,
        searchtext: "Search",  //  Make the Search icon have a "Search" label next to it
        edit: false,
        add: false,
        del: false,
        refresh: false
    },
    {}, // default settings for edit
    {}, // default settings for add
    {}, // delete
    {
        closeOnEscape: true, closeAfterSearch: true, ignoreCase: true, multipleSearch: false, multipleGroup: false, showQuery: false,
        sopt: ['cn', 'eq', 'ne'],
        defaultSearch: 'cn'
    }).navButtonAdd('#pager', {
        caption: "Export to Excel",
        buttonicon: "ui-icon-disk",
        onClickButton: function () {
            ExportDataToExcel("#tblOrders");
        },
        position: "last"
    });
}

When the user clicks on this new button, we will call an ExportDataToExcel function, passing it the control where our jqGrid is located.

Here's what that function looks like:

function ExportDataToExcel(tableCtrl) {
    //  Export the data from our jqGrid into a "real" Excel 2007 file
    ExportJQGridDataToExcel(tableCtrl, "CustomerOrders.xlsx");
}

This simply function calls the ExportJQGridDataToExcel function in the jqGridExportToExcel.js file, telling it where to find the jqGrid control, and the name of the Excel file we wish to create.

This function has one other nice trick up its sleeve.  

Supposing you had added a formatter to one of the columns in your jqGrid.

using code like this:
 
function formatURL(cellValue, options, rowdata, action) {
    return "<a href="/KB/aspnet/Somepage.aspx"?id=" + options.rowId + "' >" + cellValue + " </a>";
}

 $("#tblOrders").jqGrid({
       ...  
       colModel: [
            { name: "OrderID", width: 70, align: "center", search: false },
            { name: "OrderDate", search: true, width: 100 },
            { name: "ShipName", search: true, width: 120, formatter: formatURL },
            { name: "ShipAddress", search: true, hidden: true },
            { name: "ShipCity", search: true, width: 200 },
            { name: "ShipPostcode", search: true, width: 140 },
            { name: "ShippedDate", search: true, width: 80, align: "center" }
        ],

The ExportJQGridDataToExcel function will automatically strip out any <a href> elements and just export the text element. 

And that's it.

That's actually all you need to know, to use this class.

How It Works

If you have a look at the ExportJQGridDataToExcel function, you'll find that it iterates through the rows of data in your jqGrid, and builds one (large ?) tab-separated variable containing all of your jqGrid's data, plus a header row.

It then calls the ExportGridToExcel.ashx handler, but as a "POST" call, so it can pass this (potentially large) set of data to the handler.

Once the data arrives at the handler, we're back on the server-side, and can easily convert this into a DataTable, and call my "Export to Excel" class to create the Excel file.

Release notes

November 2014

Thanks to everyone who's left comments.  You were right, this library didn't quite work properly when multiselect was set to true.   My library would attempt to export the first column of data, which actually contained some JavaScript code from jqGrid (to tick/untick the checkbox on that row), and it'd result in a nasty error appearing.

A potentially dangerous Request.Form value was detected from the client (excelData="<input role=").

I have now updated the .js file to avoid this exception.

Final Thoughts

I've learned (and had to learn) a lot whilst writing this code.

I'm relatively new to using jqGrid, and I'm sure that there's a cleaner/safer/more environmentally friendly way of doing this. But, as I said, I couldn't find any articles offering a .NET-friendly way of exporting from jqGrid to Excel, so I hope this fills a gap.

Please leave some feedback if you find any issues using this library, and don't forget to leave a rating if you find this code useful.

You can also find some other useful walkthroughs on my blog:

Thanks for reading.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Michael Gledhill
Software Developer Partner Reinsurance
Switzerland Switzerland
I'm a software developer, working with Visual Studio and XCode, working for a financial company.

Author of the PartnerReSearch iPad app, winner of a "Business Insurance Innovation Award" in 2013, and a TechAward2014 "Innovation of the year" award in 2014.

Living near Zurich, Switzerland, with my Swiss-English family.

You may also be interested in...

Comments and Discussions

 
Questionjq grid export excel Pin
Bhagwat Prasad Sharma15-Jul-15 5:07
groupBhagwat Prasad Sharma15-Jul-15 5:07 
AnswerRe: jq grid export excel Pin
Michael Gledhill15-Jul-15 5:14
memberMichael Gledhill15-Jul-15 5:14 
Questioni m getting exported only headrs of the table in asp.net forms with loadonce=false Pin
kanthasamy27-Feb-15 1:00
memberkanthasamy27-Feb-15 1:00 
AnswerRe: i m getting exported only headrs of the table in asp.net forms with loadonce=false Pin
Mike Gledhill27-Feb-15 2:19
memberMike Gledhill27-Feb-15 2:19 
GeneralRe: i m getting exported only headrs of the table in asp.net forms with loadonce=false Pin
kanthasamy20-Apr-15 20:45
memberkanthasamy20-Apr-15 20:45 
Questionhow to exclude hidden rows in export Please help me soon? Pin
kanthasamy22-Feb-15 18:49
memberkanthasamy22-Feb-15 18:49 
QuestionExport to excel with formatted column Pin
joseph.scott.garza10-Feb-15 8:59
memberjoseph.scott.garza10-Feb-15 8:59 
QuestionAwesome! Pin
joseph.scott.garza9-Feb-15 13:39
memberjoseph.scott.garza9-Feb-15 13:39 
AnswerRe: Awesome! Pin
Michael Gledhill11-Feb-15 20:47
memberMichael Gledhill11-Feb-15 20:47 
GeneralRe: Awesome! Pin
joseph.scott.garza12-Feb-15 13:14
memberjoseph.scott.garza12-Feb-15 13:14 
QuestionAwesomee Pin
Member 113138154-Feb-15 2:49
memberMember 113138154-Feb-15 2:49 
AnswerRe: Awesomee Pin
Michael Gledhill11-Feb-15 20:47
memberMichael Gledhill11-Feb-15 20:47 
QuestionExport to Excel navigates to url instead of downloading file Pin
De Shan3-Feb-15 11:16
memberDe Shan3-Feb-15 11:16 
AnswerRe: Export to Excel navigates to url instead of downloading file Pin
Gayathribe9-Jun-15 4:03
memberGayathribe9-Jun-15 4:03 
Questionjqgridexporttoexcel.js and updated file to accomidate multi select Pin
Member 1137614615-Jan-15 2:47
memberMember 1137614615-Jan-15 2:47 
AnswerRe: jqgridexporttoexcel.js and updated file to accomidate multi select Pin
Michael Gledhill15-Jan-15 21:52
memberMichael Gledhill15-Jan-15 21:52 
QuestionHow safe it is to post data in a Post request ? Pin
abhineetz17-Dec-14 20:39
memberabhineetz17-Dec-14 20:39 
QuestionJQ Grid Export Option Not Working Pin
sankaran_kmm4-Dec-14 23:30
membersankaran_kmm4-Dec-14 23:30 
QuestionIs there any way to capture all the jq grid data records to excel Pin
parkway13-Oct-14 9:37
memberparkway13-Oct-14 9:37 
AnswerRe: Is there any way to capture all the jq grid data records to excel Pin
Michael Gledhill3-Nov-14 23:01
memberMichael Gledhill3-Nov-14 23:01 
QuestionHow to keep formatted data Pin
Member 950967729-Sep-14 14:04
memberMember 950967729-Sep-14 14:04 
QuestionOnly headers show Pin
jp569912-Sep-14 8:57
memberjp569912-Sep-14 8:57 
QuestionThe solution does not check Checkbox column Pin
nirmal.k.26-Aug-14 18:31
membernirmal.k.26-Aug-14 18:31 
AnswerRe: The solution does not check Checkbox column Pin
Michael Gledhill3-Nov-14 23:16
memberMichael Gledhill3-Nov-14 23:16 
Questionthe data row missing Pin
Dali G31-Jul-14 11:14
memberDali G31-Jul-14 11:14 
AnswerRe: the data row missing Pin
Member 1104561129-Aug-14 4:46
memberMember 1104561129-Aug-14 4:46 
Questiongetting data from multiple pages Pin
Member 1095521322-Jul-14 3:44
memberMember 1095521322-Jul-14 3:44 
AnswerRe: getting data from multiple pages Pin
Michael Gledhill3-Nov-14 22:38
memberMichael Gledhill3-Nov-14 22:38 
QuestionAlternative solution Pin
Vahid_N6-Jul-14 21:07
memberVahid_N6-Jul-14 21:07 
AnswerRe: Alternative solution Pin
Ciprian Beldi4-Nov-14 23:07
memberCiprian Beldi4-Nov-14 23:07 
GeneralRe: Alternative solution Pin
Michael Gledhill4-Nov-14 23:14
memberMichael Gledhill4-Nov-14 23:14 
QuestionOn MVC2 Pin
Member 109268355-Jul-14 18:51
memberMember 109268355-Jul-14 18:51 
SuggestionColgroups and multiselect Pin
kritochin8528-Jun-14 19:02
memberkritochin8528-Jun-14 19:02 
Suggestion1 Problem: multi-paged data Pin
VadK13-Jun-14 14:50
memberVadK13-Jun-14 14:50 
GeneralRe: 1 Problem: multi-paged data Pin
MikeGledhill13-Jun-14 18:39
memberMikeGledhill13-Jun-14 18:39 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150728.1 | Last Updated 4 Nov 2014
Article Copyright 2014 by Michael Gledhill
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid