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

Export Ajax datagrid to Excel File

, 4 Oct 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

License

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

Share

About the Author

kennysun
Software Developer
Canada Canada
No Biography provided

Comments and Discussions

 
QuestionOrder of performance Pinmemberperezk11-Oct-11 11:34 
AnswerRe: Order of performance Pinmemberkennysun13-Oct-11 8:55 
GeneralMy vote of 3 Pinmemberperezk11-Oct-11 11:31 
GeneralRe: My vote of 3 Pinmemberkennysun13-Oct-11 8:58 
QuestionFor real XLS or XLSX format PinmemberFilipKrnjic10-Oct-11 23:20 
GeneralMy vote of 1 PinmemberJepy10-Oct-11 23:04 
QuestionI wont vote but: PinmemberHaBiX4-Oct-11 21:03 

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.150414.1 | Last Updated 4 Oct 2011
Article Copyright 2011 by kennysun
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid