Export Data from 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:

• http://www.inorthwind.com/Service1.svc/getOrdersForCustomer/BERGS

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.

function formatURL(cellValue, options, rowdata, action) {
    return "<a href='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" }
        ],

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:

• http://www.mikesknowledgebase.com

Thanks for reading.