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.

38 comments:

Harish said...

Nice Article !!!

Late Night Coder 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 ?

vikky said...

Hi,

I want to create an excel file using ASP.NET. The document has 2 workbooks [sheet1 and sheet2] so i tried using XML. But the users dont want XML as when they try to save its saves as .XML extention.
I want to avoid use of COM or third party components.
Can you please help in this regard?
Thanks
Vikky.

Lakshmi said...

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

stevo said...

I tried using the example for excel, and can successfully "save" the spreadsheet to my machine, but the "open" option fails with this error: "... \Temporary Internet Files\content.IE5\L37FT(GA\FileName[1].xls' could not be found. Check the spelling... If you are trying to open the file from your list of most recently used files on the file menu, make sur that the file has not been renamed, moved, or deleted."
help, please???

Karin Jooste said...

Cool! This also works for nested DataGrids in asp.net 1.x...

BTW, I replaced the 1st line in your Javascript function by:
var element = document.getElementById("divNestedGrid");
and then it worked.

mahesh said...

Hi,
I have different requirement to export the data from oracle into word document using ASP. Once the word doc is generated I am getting the 'File Download' option but that should not come instead a Word document should be opened directly.

Also any idea how to place the headers and footers into the word document using ASP.

Bye
Mahesh.

harsha said...

Im Trying to Export Resume to ms word in asp.net but css file missing....

Hi all,
Trying to Export Resume to ms word in asp.net, but css file missing when opening resume doc after saving it.

im writing as...

aspx file...
im calling resume preview control here

html
head
title Resume Preview /title
link
style type="text/css" title="currentStyle"
@import url(style/Master_Import.css);
@import url(style/JobDetails.css);
@import url(style/ResumePreview.css);
@import url(style/ResPreview.css);
/style
/head


.cs file....


private void Resumedownload_Click(object sender, System.EventArgs e)
{
Response.ContentType = "application/msword";
ResumePreview.CandidateId = Convert.ToInt32(Request.QueryString["CandId"]);
}

getting window to open or save, when im opening its ok, but whn im saving it & reopening
css files are missing

thanks

NinjaShadow said...

Nice article.

One question, it seems that .net 2.0 has a new sercurity page attribute "validateRequest" that checks for potentially dangerous Request data. Is there a way the extract the HTML information without sending it in the request object. If i set this new attribute to "false" your example works perfectly.

Thanks

lip said...

how to export the layout format also ? e.g. line , color ...etc.

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 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 vino raj 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

damkien 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.

Alejandro 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. :)

Ankurj 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

no 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

Pankaj 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

Raji 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

Raji 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?

nirav 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?

jignesh 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

jignesh 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?

ps 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...