Export Ajax datagrid to Excel File

Introduction

In this part, I will demo how to export Ajax datagrid to Excel.

Background

I posted another article "Generic Method to Export Data to Excel File" 2 years ago. In that article, I discuss how to use a generic method to export data to Excel when the datagrid is generated by Ajax call. It provides a generic method which does object mapping with the result and creates Excel to send back to client. Compare with what people usually do in normal ASP.NET datagrid or gridview, that they generate the control again or post the grid control back to generate the report. This way will save the network traffic, but it will bother the server to do the query again.

Now I have an issue, if some query is heavy, we should avoid hitting the database again. Then how to post the Ajax grid back to server without viewstate and create report is what I will show below, and it also uses part of the technique that I show in Part 1.

Step by Step

Step 1 (in Client Side JS File)

Convert the grid to JSON format array and post back to server.

Why not just send the table back to server? Because HTML table is heavy, it includes too many HTML tags and styling and also includes other things that we don't want to export.
Here is the code need in the client JS file:

//an array contains prototype method
Array.prototype.contains=function(val){for(i in this)
{if(this[i]==val)return true;}return false;}

//convert table to JSON array
function getTb(){
var hideIdx=[];
//filter those columns won't be exported, all those columns are defined 
//in the table column header by class or id. TB is table id.
$('#'+TB+' tr:eq(0) .hideCol,#checkCol,#imgCol').each(function()
	{hideIdx.push($(this)[0].cellIndex);});
var tbA=trA=[];
$('#'+TB+' tr').each(function(i,tr){
trA=[];
$('td',tr).each(function(i,td){if(!hideIdx.contains
	($(td)[0].cellIndex))trA.push($(td).text());});
tbA.push(trA);
});
//create a object which has JSON array convert from table
return{tb:JSON.stringify(tbA)};
}

//post the JSON array back to page
function post(url,params){
var F=$(document.createElement('form')).attr
	({'method':'POST','action':url}).hide().appendTo('body');
for(var x in params){$(document.createElement('textarea')).attr
	({'name':x,'value':params[x]}).appendTo(F);}
F.submit();
}

//an event fire post table back and invoke 'exportExcel' by name
function ExcelExport(){post(ajaxPath()+'exportExcel',getTb());}

//code below are in the Jpage that demo in part 1.
function ajaxPath(){return (pgUrl+
	((-1<pgUrl.indexOf('?'))?'&':'?')+'Rand='+new Date().getTime()+'&Ajax=');}
var pgUrl=document.location.href;

Step 2 (in Server Side C#)

Convert JSON array back to HTML format return.
In the server side, it will be handled as Ajax call and invoke exportExcel like I demo in Part 1, the exportExcel converts the array to HTML format table with MIME type "application/vnd.ms-excel" directly sent back to client.

protect void exportExcel() {
	string tb=Param("tb");
	if (null == tb) {
		...handle exception here...
	}
	//convert JSON Array back to html format
	byte[] ByteArray = System.Text.Encoding.Unicode.GetBytes
		(serializer.Deserialize<List<List<string>>>(tb).ToHtmlTable());
	Response.Clear();
	Response.ClearContent();
	Response.ClearHeaders();
	Response.ContentType = "application/vnd.ms-excel";
	Response.AddHeader("Content-disposition", 
		"attachment; filename=" + "YourExcelReportName.xls");
	//encode international characters
	Page.Response.BinaryWrite(new byte[] { 0xFF, 0xFE });
	Response.BinaryWrite(ByteArray);
	Response.Flush();
	Response.End();
}

Extension method to convert JSON array to plain HTML table:

public static string ToHtmlTable(this List<List<string>> lists) {
string td = "<td align=\"right\">{0}</td>";
StringBuilder sb = new StringBuilder();
sb.Append("<table border=\"1\">");
foreach (List<string> list in lists) {
sb.Append("<tr>");
foreach (string i in list) {
sb.Append(string.Format(td, i));
}
sb.Append("</tr>");
}
sb.Append("</table>");
return sb.ToString();
}

Thanks for reading.