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.
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() {
jQuery.support.cors = true;
$("#tblOrders").jqGrid({
url: 'http://www.inorthwind.com/Service1.svc/getOrdersForCustomer/BERGS',
contentType: "application/json",
datatype: "json",
data: "{}",
jsonReader: {
root: "GetOrdersForCustomerResult", id: "OrderID", 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, caption: ""
});
$('#tblOrders').jqGrid('navGrid', '#pager', {
search: true,
searchtext: "Search", edit: false,
add: false,
del: false,
refresh: false
},
{}, {}, {}, {
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", edit: false,
add: false,
del: false,
refresh: false
},
{}, {}, {}, {
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) {
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='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.