While working on my project I came across export to excel functionality from a ASP.NET gridview in which paging is enabled. This is something quite common and we require it almost frequently for reporting purpose.
There are many different methods in which you can implement this functionality , but what need to be kept in mind are the following point
- The code should work well even if the data bind ed to the grid view is quite bulky
- The excel sheet gets open quickly without any data lose.
- Data from all the grid view pages get exported to excel sheet, rather than just the page that user is currently viewing.
Its just for your knowledge that if you try to implement this functionality using Microsoft Inter op reference then , the functionality may work in your local environment but after deployment you may get error like this
Exception Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005.
- The problem is that by default Microsoft Excel as a COM object can only activated by the following accounts:
- Administrator
- System
- Interactive
When you are running your ASP.Net account on Windows XP your web application is running as the ASPNET account.
The way to resolve this issue is to edit the DCOM configuration settings for the Microsoft Excel Application object.
But even after resolving this issue the export to excel functionality wont be working , on clicking the export to excel button there wont be any error but the data wont be exported to excel sheet either.
But , Yes you can use the Microsoft Inter op Reference in windows based application and it will work properly.
So as a work around we can use the below code for export to excel functionality here you just have to pass the data table as parameter which contains your whole data you need to export to excel file(i.e it could be the same data table that you have used as data source for grid view).
private void ExportGridToExcel(DataTable dtData)
{
StringWriter strwriter = new StringWriter();
HtmlTextWriter htmlwriter = new HtmlTextWriter(strwriter);
BoundField extension = new BoundField();
BoundField CusName = new BoundField();
BoundField projectname = new BoundField();
BoundField phonetype = new BoundField();
BoundField listtype = new BoundField();
extension.HeaderText = "Phone no.";
CusName.HeaderText = "Contact Name";
projectname.HeaderText = "Project Name";
phonetype.HeaderText = "Phone Type";
listtype.HeaderText = "List Type";
extension.DataField = "Number";
CusName.DataField = "Name";
projectname.DataField = "ProjectName";
phonetype.DataField = "PhoneType";
listtype.DataField = "ListType";
GridView g = new GridView();
g.AutoGenerateColumns = false;
g.RowDataBound += new GridViewRowEventHandler(g_RowDataBound);
g.Columns.Insert(0, extension);
g.Columns.Insert(1, CusName);
g.Columns.Insert(2, projectname);
g.Columns.Insert(3, phonetype);
g.Columns.Insert(4, listtype);
g.DataSource = dtData;
g.DataBind();
g.RenderControl(htmlwriter);
string htmlmarkup = strwriter.ToString();
FileStream fs = new FileStream(Server.MapPath("excel_report.xls"), FileMode.Create);
try
{
Byte[] bContent = System.Text.Encoding.GetEncoding("utf-8").GetBytes(htmlmarkup);
fs.Write(bContent, 0, bContent.Length);
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
fs.Close();
fs.Dispose();
}
Response.AppendHeader("content-disposition", "attachment;filename=excel_report.xls");
Response.WriteFile("excel_report.xls");
Response.End();
}
protected void g_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.Cells[4].Attributes.Add("class", "text");
e.Row.Cells[3].Attributes.Add("class", "dateformat");
}
}
Note: The values used as Data Field are the column names of the data table that is passed as the parameter.
Here a grid view control is created dynamically and then this grid view is given the same datsource as the one present in the web page. After populating this dynamic grid view , the gridview data is rendered into the HtmlTextWriter object. After which all the data present in the HtmlTextWriter is written into a string variable, and thereafter the content in the string is written into the excel file.