In my previous articles I talked about exporting GridView to excel. Although most of the time you will be exporting the content displayed on the screen but sometimes it might not be the case. In this article I will demonstrate how to export a DataSet to excel.
Introduction:
In my previous articles I talked about exporting GridView to excel. Although most of the time you will be exporting the content displayed on the screen but sometimes it might not be the case. In this article I will demonstrate how to export a DataSet to excel.
Why Exporting DataSets?
This is a valid question. The question becomes more interesting if you think of situations where you want to export the Data without actually showing it to the user. Recently I had to write some code at my work where I had to export the list of users without actually displaying it to the user. For these scenarios it is an ideal solution to simply export the DataSet instead of the GridView or any other control.
Populating the DataSet:
The first step is to populate the DataSet with some data. Check out the code below which is used to populate the DataSet.
private void BindData()
{
if (Cache["Categories"] == null)
{
string connectionString = "Server=localhost;Database=Northwind;Trusted_Connection=true";
SqlConnection myConnection = new SqlConnection(connectionString);
SqlDataAdapter ad = new SqlDataAdapter("SELECT CategoryID, CategoryName FROM Categories", myConnection);
DataSet ds = new DataSet();
ad.Fill(ds);
Cache.Insert("Categories", ds);
}
}
I have used Cache so that I don’t have to go to the database to fetch the data every time the BindData method is called.
Exporting the DataSet to Excel:
Now, check out the exportation code which is fired when a button is clicked.
protected void Button1_Click(object sender, EventArgs e)
{
ExportDataSetToExcel_Method(Cache["Categories"] as DataSet, new string[] {"CategoryID"});
}
The ExportDataSetToExcel method is used to export the contents. The method takes two parameters. First parameter is a DataSet and the second parameter is a string array which contains the names of the columns to be removed from the excel file. You might be wondering that why am I removing the columns. The reason is quite simple the columns might include the primary keys which, should not be exported as part of the excel file due to the security reasons.
private void ExportDataSetToExcel_Method(DataSet ds, string[] columns)
{
Response.Clear();
Response.ContentType = "application/vnd.excel";
Response.Charset = "";
Response.AddHeader("content-disposition", "attachment; FileName=MyFile.xls");
DataSet copyDataSet = ds.Copy();
if(columns != null && columns.Length > 0)
{
RemoveColumns(copyDataSet, columns);
}
GridView dg = new GridView();
dg.DataSource = copyDataSet;
dg.DataBind();
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
dg.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}
private void RemoveColumns(DataSet ds, string[] columns)
{
foreach (string columnName in columns)
ds.Tables[0].Columns.Remove(columnName);
}
The trick here is to use a dynamically generated GridView or DataGrid to export the contents. The RemoveColumns method is used to remove the extra columns from the exported file.
I hope you liked this article, happy coding!
