Exporting Data in to ASP.NET MVC.

Vivek.Ramapuram
Posted by in ASP.NET MVC category on for Intermediate level | Points: 250 | Views : 4216 red flag

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 

control action method 

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.
Page copy protected against web site content infringement by Copyscape

About the Author

Vivek.Ramapuram
Full Name: Vivek R
Member Level: Bronze
Member Status: Member,MVP
Member Since: 2/25/2014 12:46:59 PM
Country: India
RvGoud
http://www.dotnetfunda.com

Login to vote for this post.

Comments or Responses

Posted by: Usorovictor on: 8/7/2015 | Points: 25
thank you very much for your post, but please how will i make it retain the layout of the view while exporting to ms word?

Login to post response

Comment using Facebook(Author doesn't get notification)