Introduction
This tip describes how to export data to an Excel file from a GridView
. We will create a sample database and see how the data can be shown in the GridView
and then export in Excel format.
Background
There are many scenarios where we would want to export data into Excel format. The data could be from a GridView
or from any other data bound control. In this article, we will see how we can export data from a GridView
to Excel.
Using the Code
In this tip, I'm trying to explain how to create or export GridView
to an MS-Excel file in ASP.NET using C#. I have also placed a button on the page for exporting data to a Microsoft Excel file. We have used a GridView
which is bound and a button which will be used to create an Excel file.
For this, I have taken a database named demodb in which there is a table with the following columns:
Now when we have the table structure ready, we have a function for binding the Gridview
for which I have used a Dataset
. So let us look at the code that fetches data from the database and then binds the data to the grid to display it on the webpage.
protected void fillGrid()
{
string str = "SELECT [UNo], [EmpName], [Age],
convert(char,[dob],103) dob FROM [tbl_EmpDetails]";
myConnection = new SqlConnection(conn);
myConnection.Open();
myCommand = new SqlCommand(str, myConnection);
SqlDataAdapter mySQLDataAdapter;
myDataSet = new DataTable();
mySQLDataAdapter = new SqlDataAdapter(myCommand);
mySQLDataAdapter.Fill(myDataSet);
GridView1.DataSource = myDataSet;
GridView1.DataBind();
ViewState["dtList"] = myDataSet;
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
fillGrid();
}
}
Now, after binding a Gridview
, data is ready to get exported to an Excel file. We click on a button named Export to Excel. I have used FileInfo
to get the information related to the file.
FileInfo FI = new FileInfo(Path);
StringWriter stringWriter = new StringWriter();
HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWriter);
DataGrid DataGrd = new DataGrid();
DataGrd.DataSource = dt1;
DataGrd.DataBind();
DataGrd.RenderControl(htmlWrite);
string directory = Path.Substring(0, Path.LastIndexOf("\\"));
if (!Directory.Exists(directory))
{
Directory.CreateDirectory(directory);
}
System.IO.StreamWriter vw = new System.IO.StreamWriter(Path, true);
stringWriter.ToString().Normalize();
vw.Write(stringWriter.ToString());
vw.Flush();
vw.Close();
WriteAttachment(FI.Name, "application/vnd.ms-excel", stringWriter.ToString());
The above code uses a WriteAttachment
function which pushes the attachment to the user in the Response
object. The following code shows the implementation of WriteAttachment
:
public static void WriteAttachment(string FileName, string FileType, string content)
{
HttpResponse Response = System.Web.HttpContext.Current.Response;
Response.ClearHeaders();
Response.AppendHeader("Content-Disposition", "attachment; filename=" + FileName);
Response.ContentType = FileType;
Response.Write(content);
Response.End();
}
Point of Interest
In this tip, we have seen how to export data to an Excel file in ASP.NET. It is rather easy, but many new developers struggle with it so I wrote this to help them.
History
- 16th October 2012: First version