Thursday, April 20, 2006

ASP.NET : Export to Excel/Word from Nested GridViews


Exporting a GridView to an Excel/Word is one common requirement in ASP.NET applications. In case of simple GridViews this is a pretty easy and the code for the same can be found in my earlier article.

In case of Nested GridViews, when trying to export to Word/Excel, the output comes would always be rendered inverted in Word/Excel.

In this scenario, let us see how we can export the entire Nested GridView.

The first step is to insert a <div > that embeds the entire Nested GridView.
For Ex :

<div id="divNestedGrid" runat="server">
<asp:GridView id=.. >
...
<asp:GridView .... />
---
</asp:GridView>
</asp:GridView>
</div>


Next step is add a hidden variable in the same aspx form.

<input type="hidden" id="hdnInnerHtml" value="" runat="server" />

Now add a javascript function that in the aspx that fills the hidden variable with the inner html of the div.

function getInnerHtml()
{
var element = document.getElementById("divpreview");
var store = document.getElementById("hdnInnerHtml");
//add the css styles you have used inside the nested GridView
var css = "<style type=\"text/css\" id=\"style1\">.textbold {font-family: Arial, Helvetica, sans-serif;font-size: 11px;color: #000000;font-weight: bold;text-decoration: none;}.row1 {background-color: #FFFFFF; font-family: Arial, Helvetica, sans-serif;font-size: 11px;color: #000000;height: 18px;padding-left: 5px;}.;
store.value = css + element.innerHTML;
}

Now in the Code Behind, first add the javascript function to be triggered on the click on the Export button.

btnExport.Attributes.Add("OnClick", "getInnerHtml();");

Finally, write the following code in the Export Button click event, to Export to Word:

string html = hdnInnerHtml.Value;
Response.Cache.SetExpires(DateTime.Now.AddSeconds(1));
Response.Clear();
Response.AppendHeader("content-disposition", "attachment;filename=FileName.doc");
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.ms-word";
this.EnableViewState = false;
Response.Write("\r\n");
Response.Write(html);
Response.End();

In case of Exporting to Excel, you can change the code as follows:

string html = hdnInnerHtml.Value;
Response.Cache.SetExpires(DateTime.Now.AddSeconds(1));
Response.Clear();
Response.AppendHeader("content-disposition", "attachment;filename=FileName.xls");
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.ms-excel";
this.EnableViewState = false;
Response.Write("\r\n");
Response.Write(html);
Response.End();


In this article we discussed different ways of Exporting Data to Excel and Word when dealing with Nested Grid Views.

Thanks.

30 comments:

ರಾಜೇಶ ಹೆಗಡೆ / Rajesh Hegde / राजेश हेगडे said...

Hi I tried a similar sample. But output was html rather than word or excel. Anybody have hints how to export to real excel or word format ?

Lakshmi said...

Vikky,
Please refer to my previous article:
http://lakshmik.blogspot.com/2005/12/how-to-get-excel-data-into-net-dataset.html

Thanh An said...

Please help, I followed your help to export a web page with a lot of dynamic creating table, and I used a place holder to hold them and output them to print out. I'd like to export those table into ms word. I used yours, and succeeded to save, but the tables and info are not there. Would anyone please help? I has been searching for it for more than 2 weeks.

thank you very much,

Georgi Varbanov said...

Great.

This solution is working for me.
But I have a problem with cyrillic characters. They are not displaied correctly in the excel file. I tried to set Response.charset='utf-8' or Response.charset='win1251' without success.
Please help!
Thanks

Shrihari kale said...

hi,
it works fine for me but,
Currently In export document, the whole page exported.
but i want only the div content.
pls help me...

Shrihari...

alex said...

Nice article works fine when set
set ValidateRequest="false" at page attribute

sams said...

Hi, I am an entry level .net programmer. I used ExportToExcel1.Attributes.Add("OnClick", "getInnerHtml()"); in .cs file on ExportToExcel1_click() event. But the js function is not executing.Can any body please help me.I pasted js function (getInnerHtml())in .aspx

Unknown said...

hi
how export to word with the my template (some line string, table).
I haven't idea to do it
thanks

Milica said...

Sams, you need to add this line of code ExportToExcel1.Attributes.Add("OnClick", "getInnerHtml()"); before you call ExportToExcel1_click() event. If you add it in the click event procedure itself, of course it won't work. Put it in the Page_Load() and try again.

S said...

I dont see any data in theg excel sheet , exactly implemented in teh way specified and also added validaterequest= false .
Please help me...
Your help will be very much appreciated

S said...

Hi Lakshmi,
This code doesnt seem to work for me.

Its populating a empty excel sheet. Can you please help?

Regrds,
Madhuri

Vincy said...

Madhuri,

if your excel sheet is empty that means JS function is not able to find the DIV object properly. keep in mind that if you use runat='server' tag at the div and hidden text box declaration then the compiler is changing id while compiling. In my case what I did is, for the JS func i added two arguments and on the page load I passed both div and hidden text box as he parameter as shown below:

Here is the JS function:
function getInnerHtml(oDiv, oHdn) {
oHdn.value = oDiv.innerHTML;
}

and on the pageload like this:
btnExpPage.Attributes.Add("OnClick", string.Format("getInnerHtml({0},{1});",divExp.ClientID, hdnExp.ClientID));

Hope, this helps if it doesn't then ping me at vd133@att.com.

Alex House said...

Hi, very useful article. Does any one knows if I need an MS-Office license installed in my server to be able to export to doc?. I know there is no technical need for it, I just wanna know if I need a license since I will be running a commercial application on my server.

Regards

Vincy said...

You neither need licence nor office installed on the server because its not using any of the office component to generate export. However, the user must have either Office or Excel/Word viewer in his/her machine in order to view the exported file.

Hope, this helps. :)

DotNetTech said...

Hi,
Can you tell me how much maximum rows i can export and also
there is image in my grid.
But it is not showing in Exported excel sheet?
PlEASE HELP ME

Vincy said...

Maximum rows are depending on the version of excel you are using. Meaning it really does not care at the Server. Its all happening at the client when the request is made, the server exports and and sends the file to client. But if the client has excell 2003 or earlier then they are limited to 65536 rows (remember this limitation is from excel) but if the client has excel 2007 then its million (http://blogs.msdn.com/excel/archive/2005/09/26/474258.aspx).

for the image if it is showing up on the web then it should show on the export as well unless you mess up the path as you export it. Check back your export code and see whether its using the right path for it.

Hope, this helps

MOF said...

Thank you for this great articals
put i am facing a problem where to put this
btnExport.Attributes.Add("OnClick", "getInnerHtml();");
i tried to put it in btnExport.click but still i get an emmpty hidden field
please help me

Vincy said...

in the page_load event

Unknown said...

Hi,
I want to export all the web page data to word document. This web page also containing chart.

Could you please suggest me how to export these charts to the word.

Thanks...
Pankaj

Unknown said...

Hi Lakshmi,
I tried the same sample with the steps that you have mentioned, I got a filename.xls file with empty rows. Can you please help me out?

Thanks,
Raji

Unknown said...

I have used the same sample with all the steps that u have mentioned above. but im getting a excel sheet with empty rows. Can u pls help me out?

testset said...

Hi Lakshmi,
Very Nice Article,BUt I had a problem here.I want to export data in different worksheet,i mean from one dataset if there are 100 rows ten for each customer then there should be 10 worksheets.I want to avoid use of COM or third party components.
Can you please help me out?

Unknown said...

hello.. it is very nice article.

but i want more than on2e html page to export into single word file..

give me solution.

Thank in advance

Unknown said...

HI laxmi

very nice article. but i want to export more than one html pages to single word file. how can i do this,

thanks in advance

Abhijit Aitwade said...

nice post! how we make colors and font formatting?

Unknown said...

nice article exactly the same for which i was looking

Marco Diaz said...

Hi.
It work fine.
I have problem when i click in OPEN (when i have 3 option : OPEM|SAVE|CANCEL)
whe system need user and password, finally open excel with page content.
Any have same problem???
Please i need help.

Uday said...

Many Thanks for posting this article Lakshmi !
It worked as expected for me.

S.K.Gupta said...

It's a very very good article...

S.K.Gupta said...

This is a very very Helpful Article...