Click here to Skip to main content
Click here to Skip to main content

Export Data from jqGrid into a "real" Excel File

, 4 Nov 2014 CPOL
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.
Follow on   Google+   LinkedIn

Comments and Discussions

 
QuestionHow safe it is to post data in a Post request ? Pinmemberabhineetz17-Dec-14 21:39 
QuestionJQ Grid Export Option Not Working Pinmembersankaran_kmm5-Dec-14 0:30 
QuestionIs there any way to capture all the jq grid data records to excel Pinmemberparkway13-Oct-14 10:37 
AnswerRe: Is there any way to capture all the jq grid data records to excel PinmemberMichael Gledhill4-Nov-14 0:01 
QuestionHow to keep formatted data PinmemberMember 950967729-Sep-14 15:04 
QuestionOnly headers show Pinmemberjp569912-Sep-14 9:57 
QuestionThe solution does not check Checkbox column [modified] Pinmembernirmal.k.26-Aug-14 19:31 
AnswerRe: The solution does not check Checkbox column PinmemberMichael Gledhill4-Nov-14 0:16 
Questionthe data row missing PinmemberDali G31-Jul-14 12:14 
AnswerRe: the data row missing PinmemberMember 1104561129-Aug-14 5:46 
Questiongetting data from multiple pages PinmemberMember 1095521322-Jul-14 4:44 
AnswerRe: getting data from multiple pages PinmemberMichael Gledhill3-Nov-14 23:38 
QuestionAlternative solution PinmemberVahid_N6-Jul-14 22:07 
AnswerRe: Alternative solution PinmemberCiprian Beldi5-Nov-14 0:07 
GeneralRe: Alternative solution PinmemberMichael Gledhill5-Nov-14 0:14 
QuestionOn MVC2 PinmemberMember 109268355-Jul-14 19:51 
SuggestionColgroups and multiselect Pinmemberkritochin8528-Jun-14 20:02 
Suggestion1 Problem: multi-paged data PinmemberVadK13-Jun-14 15:50 
GeneralRe: 1 Problem: multi-paged data PinmemberMikeGledhill13-Jun-14 19: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
Web03 | 2.8.141216.1 | Last Updated 4 Nov 2014
Article Copyright 2014 by Michael Gledhill
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid