Learn how to export to excel using asp.net mvc. Here we shall try to export grid table to MS Excel in ASP.NET MVC. We shall also learn export to MS Word and XML.
Introduction
To export data from database to MS Excel, we can use GridView control of the System.Web.UI.WebControls namespace that is part of ASP.NET Web Form.
Export data to MS Excel using ASP.NET MVC
private TrainingMVCContext db = new TrainingMVCContext();
// GET: ExportData
public ActionResult ExportToExcel()
{
// Step 1 - get the data from database
var data = db.PersonalDetails.ToList();
// instantiate the GridView control from System.Web.UI.WebControls namespace
// set the data source
GridView gridview = new GridView();
gridview.DataSource = data;
gridview.DataBind();
// Clear all the content from the current response
Response.ClearContent();
Response.Buffer = true;
// set the header
Response.AddHeader("content-disposition", "attachment; filename=itfunda.xls"); Response.ContentType = "application/ms-excel";
Response.Charset = "";
// create HtmlTextWriter object with StringWriter
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// render the GridView to the HtmlTextWriter
gridview.RenderControl(htw);
// Output the GridView content saved into StringWriter Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
}
return View();
} In the above action method, most of the codes are documented and self understood however in short we are doing following
- First we are getting the data from the database and setting to the source of the gridview
- Clearing all the content of the response
- Setting the response header and ContentType
- Rendering the GridView conent using HtmlTextWriter and StringWriter
Calling the ExportToExcel action method will ask user to Open or Save the itfunda.xls file like below.
How to limit the number of columns appearing in the exported excel file?
To limit the number of columns appearing in the exported MS Excel file, set the data source of the GridView that has selected columns like below
PART OF CONTROLLER ACTION
METHOD
var data = db.PersonalDetails.ToList().Select(
p => new
{
p.Active,
p.FirstName,
p.LastName
}
);
// instantiate the GridView control from System.Web.UI.WebControls namespace
// set the data source
GridView gridview = new GridView();
gridview.DataSource = data;
gridview.DataBind();Now, the exported excel file will have only Active, FirstName and LastName columns data only
OutPut
Export data to MS Word in ASP.NET MVC
Exporting the data to MS Word is similar to exporting data into MS Excel, only the ContentType and the file name extension changes
// GET: ExportData public ActionResult ExportToWord()
{
// get the data from database
var data = db.PersonalDetails.ToList();
// instantiate the GridView control from System.Web.UI.WebControls namespace
// set the data source
GridView gridview = new GridView();
gridview.DataSource = data;
gridview.DataBind();
// Clear all the content from the current response
Response.ClearContent();
Response.Buffer = true;
// set the header
Response.AddHeader("content-disposition", "attachment; filename=itfunda.doc"); Response.ContentType = "application/ms-word";
Response.Charset = "";
// create HtmlTextWriter object with StringWriter
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// render the GridView to the HtmlTextWriter
gridview.RenderControl(htw);
// Output the GridView content saved into StringWriter Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
}
return View();
}
Notice the above code snippet and focus on the highlighted words. Note that Response.ContentType is “application/ms-word” and the file name extension is “.doc”.
Calling the above action method asks user to save or download the file like below
OutPut
Export data into XML in ASP.NET MVC
To export data into XML in ASP.NET, we can use following approach.
CONTROLLER ACTION METHOD
public void ExportToXML()
{
var data = db.PersonalDetails.ToList();
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment; filename=itfunda.xml"); Response.ContentType = "text/xml";
var serializer = new
System.Xml.Serialization.XmlSerializer(data.GetType()); serializer.Serialize(Response.OutputStream, data);
} In the above action method, we are getting the data from the database and setting almost same properties of the Response object that we have set into previous points.
The changes are following
- We are changing the name of the file to download
- Setting the ContentType to “text/xml”
- Using XmlSerializer to Serialize the data into XML and sending to the Response OutputStream.
The OutPut file looks like below
Conclusion
In this article, we have seen how to export to excel in ASP.NET MVC, how to export to MS Word in ASP.NET MVC and how to export to XML in ASP.NET MVC.