Friday, December 14, 2007

Export datagrid to Excel in asp.net (Revised)

Previously I had Post for Export Datagrid Data to Excel

It was perfectly alright but following Article has added Advantage over the Previous Post.

  • You can able to Hide Few Fields and Display desired fields in Exported Excel Sheet, bydefault all the fields which are displayed in datagrid was displayed in Excel, but let say we want to hide "Edit" and "Delete" field while exporting datagrid data to excel. (Note: You can also use datagrid1.Columns[4].Visible = false;)
  • Format Datagrid Data. Full control over formatting of data as compare to previous method.
  • Replace Control before exporting to excel. eg: Removing Hyperlink, dropdownlist, checkbox and other controls before exporting data to excel.
  • Can Export All data @ once, bydefault you can only export page full of data.

Lets understand by example, consider following datagrid, which contain "Employee Name" and "Email" as Hyperlink, but while exporting data i want Hyperlink from email should be removed.




Utility Code:
It is self explanatory

protected void btnExportToExcel_Click(object sender, EventArgs e)
{
DataTable dtOriginal = new DataTable();
dtOriginal = ReturnTable(); //Return Table consisting data

//Create Tempory Table
DataTable dtTemp = new DataTable();

//Creating Header Row
dtTemp.Columns.Add("<b>Employee Name</b>");
dtTemp.Columns.Add("<b>Email</b>");
dtTemp.Columns.Add("<b>Join Date</b>");
dtTemp.Columns.Add("<b>Salary</b>");
double dSalary;
DateTime dtDate;
DataRow drAddItem;
for (int i = 0; i < dtOriginal.Rows.Count; i++)
{
drAddItem = dtTemp.NewRow();
drAddItem[0] = dtOriginal.Rows[i][0].ToString();//Name
drAddItem[1] = dtOriginal.Rows[i][1].ToString();//Email

//Join Date
dtDate = Convert.ToDateTime(dtOriginal.Rows[i][2].ToString());
drAddItem[2] = dtDate.ToShortDateString();

//Salary
dSalary = Convert.ToDouble(dtOriginal.Rows[i][3].ToString());
drAddItem[3] = dSalary.ToString("C");

dtTemp.Rows.Add(drAddItem);
}

//Temp Grid
DataGrid dg = new DataGrid();
dg.DataSource = dtTemp;
dg.DataBind();
ExportToExcel("BudgeReport.xls", dg);
dg = null;
dg.Dispose();
}

private void ExportToExcel(string strFileName, DataGrid dg)
{
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);
Response.ContentType = "application/excel";
System.IO.StringWriter sw = new System.IO.StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
dg.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}

19 comments:

Unknown said...

Hi,
Well this code works perfectly, however you should note that the file created is not a "real" XLS (BIFF) file.

If you want to work with "real" Excel files (XLS, XLSX or CSV) you could try some third party components (http://www.gemboxsoftware.com/GBSpreadsheet.htm).

Also if you want to build import/export to XLSX (Excel 2007) files yourself, look at this article:
http://www.gemboxsoftware.com/WebDemo/Index.aspx

Sharad said...

thanks
it is working perfectly in my asp.net application
thanks a lot

Anonymous said...

This is great - works well - I searched for ages trying to find this solution...

Amit said...

Hi,

This code works fine for exporting but after exporting the grid to excel events on page are not firing.

I have a button for generating report within some scope.On clicking this button,datagrid gets filled with report and on clicking Export the filled datagrid is exported to excel.

But once export is done,this generate button stops firing.

Any ideas why this happens,as this happens only after clicking export button.

Regards,
Amit Tuteja

DotNetGuts said...

Hi Amit,

Export button is independent of code you might be executing, so such a behavior is unexpected. Have you tried debugging? I am 100% sure that it cannot be because of this code. Do reply me if you found the root cause of problem.

Dan said...

Amit-

I am having the same issue. After I export to excel none of my button work. If I debug a post-back in not happening when I click. Did you ever resolve this issue and if so how?

Anonymous said...

I have the same issue, when debugging it seems no code is executed after the Response.End()

Unknown said...

Hi, In my case it says, ReturnTable() does not exist in the current context?

DotNetGuts said...

@Prerita,

You are right, ReturnTable is actual dataset which might be DATATABLE, this is your actual data.

wassim said...

Hi, thank you for code it was very useful for me and saved a lot of time, however when I try to open the generated file, I get a windows message: “the file your are trying to open…….xls is in different format than specified by the file extension verify that the file is not corrupted and is from trusted source befor opening the file, do you want to open the file now ?“
Any ideas why this happens

yangibam said...

hi wassim,
I have same error, have you solved this issue?

thanks and regards

R.S.Bains said...

where is the ReturnTable()

DotNetGuts said...

@Sarbjeet

It Return DataTable consisting data. Here i have asume that this function would return DataTable.

VENKATESH said...

thanks for providing such nice posts of coding with good explanations. But one thing I really need is that i want to deploy my own web site in my local iis 5.1 or 6.0. since i'm some what good to design my own asp.net website but don't know the deploymeny. I beleive you could help me in acheiving this.One request is that plaese give me a good step by step procedure will do good.For Your Information: I'm having the projects both in vs2005 and 2008.

Zeeshan Umar said...

Great article, thanks for sharing. Also have a look at this post, it shows some nice features of excel 2007 based reports:-

Export to Excel

Jagadeesh Arasoor said...

Hi,

Its useful code,but

after Exporting it is showing Excel Sheet is not in Correct Format.

Rajesh said...

Hi

Thanks for your post. Like the same what they discussed above ,after exporting the buttons are not fire. Can you help me on this issue.

Sheekha Khetan said...

It says return table does not exist in the context. How do I resolve this issue?

Also, when I export to excel, it does not populate table data. It only lists the table header. Any clue?

Unknown said...

Well this is a good example of exporting grid view data to Excel. but If I have more than 20 columns in a grid fewer are visible but rest are hide. and want to export all data then what should I do, this problem I am facing actually this is my requirement/my Application Requirement, so do you have any solution for that....I want to export 20 columns gridview data to Excel file.... with 500 records.... with different data types. looking forward for reply....

Most Recent Post

Subscribe Blog via Email

Enter your email address:



Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape