Post
Ask Question

How To Export Data In EXCEL, PDF, CSV, Word, JSON, XML And Text File In MVC Application

This article demonstrates how to export functionality with different types of file formats like excel, pdf, word, csv, json, xml and text files using a .net MVC application. I have implemented the most frequently used file format to export data in real time projects. 
 
Here I will explain step by step from scrach, an application to each file export option with an effective way using Ajax call in mvc. I have attached the source code for this demo for all export options provided with a code snippet.
 
Step 1
 
Create a new project and SQL scripts for product table which are used in this article (which I have already shared in my previous article). You can find all details related to SQL script and table binding at the below link.
Step 2
 
Here I have added two references which are required while working with Excel, Word and PDF generation. This reference is added from NuGet package manager.
 
The first reference is iTextSharp for pdf file to export data from data table in MVC.
 
How To Export Data In EXCEL, PDF, CSV, Word, JSON, XML And Text File In MVC Application
 
The second reference is used to generate an Excel file to export data from data table in MVC.
 
How To Export Data In EXCEL, PDF, CSV, Word, JSON, XML And Text File In MVC Application 
 
Step 3
 
Let's startwith the options, one by one. Here is the screen with all Export button options available.
 
How To Export Data In EXCEL, PDF, CSV, Word, JSON, XML And Text File In MVC Application 
 
Step 4 
 
I have created a new Controller, view and repositery to get product details from database.
  1. public class ProductRepository  
  2. {  
  3.     private const int pageSize = 20;  
  4.     public List<Product> GetProducts(int? pageNumber)  
  5.     {  
  6.         var numberOfRecordToskip = pageNumber * pageSize;  
  7.         using (var context = new Products())  
  8.         {  
  9.             return context.ProductsDetails.OrderBy(x=>x.ProductID).Skip(Convert.ToInt32(numberOfRecordToskip)).Take(pageSize).ToList<Product>();  
  10.         }  
  11.     }  
  12. }  
Common method to get product detail which is created by a private method in controller.
  1. public class ProductController : Controller  
  2. {  
  3.     private ProductRepository productRepository;  
  4.     // GET: Product  
  5.     public ActionResult Index()  
  6.     {  
  7.         return View();  
  8.     }  
  9.     [HttpGet]  
  10.     public ActionResult ProductList(int? pageNumber)  
  11.     {  
  12.         productRepository = new ProductRepository();  
  13.         var model = productRepository.GetProducts(pageNumber);  
  14.         return PartialView("~/Views/Product/ProductList.cshtml", model);  
  15.     }  
  16.     private DataTable GetProductsDetail(int? pageNumber)  
  17.     {  
  18.         productRepository = new ProductRepository();  
  19.         var products = productRepository.GetProducts(pageNumber);  
  20.   
  21.         DataTable dtProduct = new DataTable("ProductDetails");  
  22.         dtProduct.Columns.AddRange(new DataColumn[4] { new DataColumn("ProductID"),  
  23.                                         new DataColumn("ProductName"),  
  24.                                         new DataColumn("Price"),  
  25.                                         new DataColumn("ProductDescription") });  
  26.         foreach (var product in products)  
  27.         {  
  28.             dtProduct.Rows.Add(product.ProductID, product.ProductName, product.Price, product.ProductDescription);  
  29.         }  
  30.   
  31.         return dtProduct;  
  32.     }  
  33. }  
Step 5 
 
Added Index.cshtml
  1. @model IEnumerable<LazyLoadingDemo.Product>  
  2.   
  3. <div class="jumbotron">  
  4.     <h2>Lazy Loading Example Using Ajax in MVC Application</h2>  
  5.     <p class="lead">  
  6.         <br />  
  7.         <h4>Export Data using different file format like Excel, CSV, PDF, WORD, JSON, XML, TEXT etc.</h4>  
  8.     </p>  
  9. </div>  
  10.   
  11. <div class="table-responsive col-md-12" id="divajaxCall">  
  12.     <form action="" method="post">  
  13.         <div class="row">  
  14.             <div style="text-align:center; padding:0 0 0 0">  
  15.                 <button type="submit" value="ExportToExcel" name="action">  
  16.                     <i class="far fa-file-excel"></i> Export To Excel  
  17.                 </button>  
  18.                 <button type="submit" value="ExportToCsv" name="action">  
  19.                     <i class="fas fa-file-csv"></i> Export To Csv  
  20.                 </button>  
  21.                 <button type="submit" value="ExportToPdf" name="action">  
  22.                     <i class="far fa-file-pdf"></i> Export To Pdf  
  23.                 </button>  
  24.                 <button type="submit" value="ExportToWord" name="action">  
  25.                     <i class="far fa-file-word"></i> Export To Word  
  26.                 </button>  
  27.                 <button type="submit" value="ExportToJson" name="action">  
  28.                     <i class="fab fa-js-square"></i> Export To Json  
  29.                 </button>  
  30.                 <button type="submit" value="ExportToXml" name="action">  
  31.                     <i class="far fa-file-code"></i> Export To XML  
  32.                 </button>  
  33.                 <button type="submit" value="ExportToText" name="action">  
  34.                     <i class="far fa-file-alt"></i> Export To Text  
  35.                 </button>  
  36.             </div>  
  37.         </div>  
  38.         <div class="row" style="text-align:center; padding : 5px 5px 5px 5px"></div>  
  39.         <div class="row">  
  40.             <table class="table table-striped table-bordered">  
  41.                 <thead>  
  42.                     <tr>  
  43.                         <th>  
  44.                             @Html.DisplayNameFor(model => model.ProductName)  
  45.                         </th>  
  46.                         <th>  
  47.                             @Html.DisplayNameFor(model => model.Price)  
  48.                         </th>  
  49.                         <th>  
  50.                             @Html.DisplayNameFor(model => model.ProductDescription)  
  51.                         </th>  
  52.   
  53.                     </tr>  
  54.                 </thead>  
  55.                 <tbody>  
  56.                     @if (Model != null)  
  57.                     {  
  58.                         @Html.Partial("~/Views/Product/ProductList", Model)  
  59.                     }  
  60.                 </tbody>  
  61.             </table>  
  62.         </div>  
  63.         <div class="row">  
  64.             <div id="loading" style="text-align:center; padding:0 0 0 0">  
  65.                 <img src='~/Content/progress-loader1.gif' />  
  66.             </div>  
  67.         </div>  
  68.   
  69.         <div class="divfooter">  
  70.         </div>  
  71.     </form>  
  72. </div>  
  73. <div id="divHide"></div>  
  74. @section scripts{  
  75.     <script src="~/Scripts/lazyLoading.js"></script>  
  76.     <script type="text/javascript">  
  77.         $(function () {  
  78.             $("div#loading").hide();  
  79.             $("#divajaxCall").css("border", "1px solid gray");  
  80.             $("#divajaxCall").css("padding", "10px 5px 5px 5px");  
  81.             $("#divajaxCall").css("border-radius", "10px");  
  82.         });  
  83.         var ajaxCallUrl = '@Url.RouteUrl("ProductDataList")';  
  84.   
  85.         $(window).scroll(scrollHandler);  
  86.     </script>  
  87. }  
  88.   
  89. <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">  
  90. <link rel="stylesheet" href="~/Content/CustomStyle.css">  
  91. <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>  
  92. <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>  
  93. <script src="https://kit.fontawesome.com/00267cf40d.js" crossorigin="anonymous"></script>  
Partial view ProductList.cshtml
  1. @model IEnumerable<LazyLoadingDemo.Product>  
  2. @foreach (var item in Model)  
  3. {  
  4.     <tr>  
  5.         <td>  
  6.             @Html.DisplayFor(modelItem => item.ProductName)  
  7.         </td>  
  8.         <td>@Html.DisplayFor(modelItem => item.Price)</td>  
  9.         <td> @Html.DisplayFor(modelItem => item.ProductDescription)</td>  
  10.     </tr>  
  11. }  
Step 6
 
I have created a custom attribute to handle multiple submit button clicks for single form in mvc.
  1. [AttributeUsage(AttributeTargets.Method, AllowMultiple = false, Inherited = true)]  
  2. public class AllowMultipleButtonAttribute : ActionNameSelectorAttribute  
  3. {  
  4.     public string Name { get; set; }  
  5.     public string Argument { get; set; }  
  6.   
  7.     public override bool IsValidName(ControllerContext controllerContext, string actionName, MethodInfo methodInfo)  
  8.     {  
  9.         var isValidName = false;  
  10.         isValidName = controllerContext.HttpContext.Request[Name] != null &&  
  11.             controllerContext.HttpContext.Request[Name] == Argument;  
  12.   
  13.         return isValidName;  
  14.     }  
  15. }  
Step 7
 
Let's start with Data export to Excel file. Here I have added a code snippet for exporting to Excel file post action method.
 
Export data to excel file 
  1. [HttpPost]  
  2. [AllowMultipleButton(Name = "action", Argument = "ExportToExcel")]  
  3. public ActionResult ExportToExcel(int? pageNumber)  
  4. {  
  5.     DataTable dtProduct = GetProductsDetail(pageNumber);  
  6.   
  7.     using (XLWorkbook woekBook = new XLWorkbook())  
  8.     {  
  9.         woekBook.Worksheets.Add(dtProduct);  
  10.         using (MemoryStream stream = new MemoryStream())  
  11.         {  
  12.             woekBook.SaveAs(stream);  
  13.             return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "ProductDetails.xlsx");  
  14.         }  
  15.     }  
  16.  }  
 Export data to CSV file 
  1. [HttpPost]  
  2. [AllowMultipleButton(Name = "action", Argument = "ExportToCsv")]  
  3. public ActionResult ExportToCsv(int? pageNumber)  
  4. {  
  5.     DataTable dtProduct = GetProductsDetail(pageNumber);  
  6.   
  7.     StringBuilder sb = new StringBuilder();  
  8.   
  9.     IEnumerable<string> columnNames = dtProduct.Columns.Cast<DataColumn>().  
  10.                                       Select(column => column.ColumnName);  
  11.     sb.AppendLine(string.Join(",", columnNames));  
  12.   
  13.     foreach (DataRow row in dtProduct.Rows)  
  14.     {  
  15.         IEnumerable<string> fields = row.ItemArray.Select(field =>  
  16.           string.Concat("\"", field.ToString().Replace("\"", "\"\""), "\""));  
  17.         sb.AppendLine(string.Join(",", fields));  
  18.     }  
  19.     Response.Clear();  
  20.     Response.Buffer = true;  
  21.     Response.AddHeader("content-disposition", "attachment;filename=ProductDetails.csv");  
  22.     Response.Charset = "";  
  23.     Response.ContentType = "application/text";  
  24.     Response.Output.Write(sb);  
  25.     Response.Flush();  
  26.     Response.End();  
  27.   
  28.     return View("Index");  
  29. }  
Export data to PDF file
  1. [HttpPost]  
  2. [AllowMultipleButton(Name = "action", Argument = "ExportToPdf")]  
  3. public ActionResult ExportToPdf(int? pageNumber)  
  4. {  
  5.     DataTable dtProduct = GetProductsDetail(pageNumber);  
  6.   
  7.     if (dtProduct.Rows.Count > 0)  
  8.     {  
  9.         int pdfRowIndex = 1;  
  10.   
  11.         string filename = "ProductDetails-" + DateTime.Now.ToString("dd-MM-yyyy hh_mm_s_tt");  
  12.         string filepath = Server.MapPath("\\") + "" + filename + ".pdf";  
  13.         Document document = new Document(PageSize.A4, 5f, 5f, 10f, 10f);  
  14.         FileStream fs = new FileStream(filepath, FileMode.Create);  
  15.         PdfWriter writer = PdfWriter.GetInstance(document, fs);  
  16.         document.Open();  
  17.   
  18.         Font font1 = FontFactory.GetFont(FontFactory.COURIER_BOLD, 10);  
  19.         Font font2 = FontFactory.GetFont(FontFactory.COURIER, 8);  
  20.   
  21.         float[] columnDefinitionSize = { 2F, 5F, 2F, 5F };  
  22.         PdfPTable table;  
  23.         PdfPCell cell;  
  24.   
  25.         table = new PdfPTable(columnDefinitionSize)  
  26.         {  
  27.             WidthPercentage = 100  
  28.         };  
  29.   
  30.         cell = new PdfPCell  
  31.         {  
  32.             BackgroundColor = new BaseColor(0xC0, 0xC0, 0xC0)  
  33.         };  
  34.   
  35.         table.AddCell(new Phrase("ProductId", font1));  
  36.         table.AddCell(new Phrase("ProductName", font1));  
  37.         table.AddCell(new Phrase("Price", font1));  
  38.         table.AddCell(new Phrase("ProductDescription", font1));  
  39.         table.HeaderRows = 1;  
  40.   
  41.         foreach (DataRow data in dtProduct.Rows)  
  42.         {  
  43.             table.AddCell(new Phrase(data["ProductId"].ToString(), font2));  
  44.             table.AddCell(new Phrase(data["ProductName"].ToString(), font2));  
  45.             table.AddCell(new Phrase(data["Price"].ToString(), font2));  
  46.             table.AddCell(new Phrase(data["ProductDescription"].ToString(), font2));  
  47.   
  48.             pdfRowIndex++;  
  49.         }  
  50.   
  51.         document.Add(table);  
  52.         document.Close();  
  53.         document.CloseDocument();  
  54.         document.Dispose();  
  55.         writer.Close();  
  56.         writer.Dispose();  
  57.         fs.Close();  
  58.         fs.Dispose();  
  59.   
  60.         FileStream sourceFile = new FileStream(filepath, FileMode.Open);  
  61.         float fileSize = 0;  
  62.         fileSize = sourceFile.Length;  
  63.         byte[] getContent = new byte[Convert.ToInt32(Math.Truncate(fileSize))];  
  64.         sourceFile.Read(getContent, 0, Convert.ToInt32(sourceFile.Length));  
  65.         sourceFile.Close();  
  66.         Response.ClearContent();  
  67.         Response.ClearHeaders();  
  68.         Response.Buffer = true;  
  69.         Response.ContentType = "application/pdf";  
  70.         Response.AddHeader("Content-Length", getContent.Length.ToString());  
  71.         Response.AddHeader("Content-Disposition", "attachment; filename=" + filename + ".pdf;");  
  72.         Response.BinaryWrite(getContent);  
  73.         Response.Flush();  
  74.         Response.End();  
  75.      }  
  76.      return View("Index");  
  77. }
Export data to Word file
  1. [HttpPost]  
  2. [AllowMultipleButton(Name = "action", Argument = "ExportToWord")]  
  3. public ActionResult ExportToWord(int? pageNumber)  
  4. {  
  5.     DataTable dtProduct = GetProductsDetail(pageNumber);  
  6.   
  7.     if (dtProduct.Rows.Count > 0)  
  8.     {  
  9.         StringBuilder sbDocumentBody = new StringBuilder();  
  10.   
  11.         sbDocumentBody.Append("<table width=\"100%\" style=\"background-color:#ffffff;\">");  
  12.         //  
  13.         if (dtProduct.Rows.Count > 0)  
  14.         {  
  15.             sbDocumentBody.Append("<tr><td>");  
  16.             sbDocumentBody.Append("<table width=\"600\" cellpadding=0 cellspacing=0 style=\"border: 1px solid gray;\">");  
  17.   
  18.             // Add Column Headers dynamically from datatable  
  19.             sbDocumentBody.Append("<tr>");  
  20.             for (int i = 0; i < dtProduct.Columns.Count; i++)  
  21.             {  
  22.                 sbDocumentBody.Append("<td class=\"Header\" width=\"120\" style=\"border: 1px solid gray; text-align:center; font-family:Verdana; font-size:12px; font-weight:bold;\">" + dtProduct.Columns[i].ToString().Replace(".", "<br>") + "</td>");  
  23.             }  
  24.             sbDocumentBody.Append("</tr>");  
  25.   
  26.             // Add Data Rows dynamically from datatable  
  27.             for (int i = 0; i < dtProduct.Rows.Count; i++)  
  28.             {  
  29.                 sbDocumentBody.Append("<tr>");  
  30.                 for (int j = 0; j < dtProduct.Columns.Count; j++)  
  31.                 {  
  32.                     sbDocumentBody.Append("<td class=\"Content\"style=\"border: 1px solid gray;\">" + dtProduct.Rows[i][j].ToString() + "</td>");  
  33.                 }  
  34.                 sbDocumentBody.Append("</tr>");  
  35.             }  
  36.             sbDocumentBody.Append("</table>");  
  37.             sbDocumentBody.Append("</td></tr></table>");  
  38.         }  
  39.         Response.Clear();  
  40.         Response.Buffer = true;  
  41.         Response.AppendHeader("Content-Type", "application/msword");  
  42.         Response.AppendHeader("Content-disposition", "attachment; filename=ProductDetails.doc");  
  43.         Response.Write(sbDocumentBody.ToString());  
  44.         Response.End();  
  45.     }  
  46.     return View("Index");  
  47. }  
Export data to Json file
  1. [HttpPost]  
  2. [AllowMultipleButton(Name = "action", Argument = "ExportToJson")]  
  3. public ActionResult ExportToJson(int? pageNumber)  
  4. {  
  5.     DataTable dtProduct = GetProductsDetail(pageNumber);  
  6.     var listProduct = (from DataRow row in dtProduct.Rows  
  7.   
  8.                        select new Product()  
  9.                        {  
  10.                            ProductID = row["ProductID"] != null ? Convert.ToInt32(row["ProductID"]) : 0,  
  11.                            ProductName = Convert.ToString(row["ProductName"]),  
  12.                            Price = row["Price"] != null ? Convert.ToInt32(row["Price"]) : 0,  
  13.                            ProductDescription = Convert.ToString(row["ProductDescription"])  
  14.                        }).ToList();  
  15.     string jsonProductList = new JavaScriptSerializer().Serialize(listProduct);  
  16.   
  17.     Response.ClearContent();  
  18.     Response.ClearHeaders();  
  19.     Response.Buffer = true;  
  20.     Response.ContentType = "application/json";  
  21.     Response.AddHeader("Content-Length", jsonProductList.Length.ToString());  
  22.     Response.AddHeader("Content-Disposition", "attachment; filename=ProductDetails.json;");  
  23.     Response.Output.Write(jsonProductList);  
  24.     Response.Flush();  
  25.     Response.End();  
  26.   
  27.     return View("Index");  
  28. } 
     
Export to XML file
  1. [HttpPost]  
  2. [AllowMultipleButton(Name = "action", Argument = "ExportToXml")]  
  3. public ActionResult ExportToXML(int? pageNumber)  
  4. {  
  5.     productRepository = new ProductRepository();  
  6.     var products = productRepository.GetProducts(pageNumber);  
  7.   
  8.     XmlDocument xml = new XmlDocument();  
  9.     XmlElement root = xml.CreateElement("Products");  
  10.     xml.AppendChild(root);  
  11.     foreach (var product in products)  
  12.     {  
  13.         XmlElement child = xml.CreateElement("Product");  
  14.         child.SetAttribute("ProductID", product.ProductID.ToString());  
  15.         child.SetAttribute("ProductName", product.ProductName);  
  16.         child.SetAttribute("Price", product.Price.ToString());  
  17.         child.SetAttribute("ProductDescription", product.ProductDescription);  
  18.         root.AppendChild(child);  
  19.      }
  20.      Response.ClearContent();  
  21.      Response.ClearHeaders();  
  22.      Response.Buffer = true;  
  23.      Response.ContentType = "application/xml";  
  24.      Response.AddHeader("Content-Disposition", "attachment; filename=ProductDetails.xml;");  
  25.      Response.Output.Write(xml.OuterXml.ToString());  
  26.      Response.Flush();  
  27.      Response.End();  
  28.   
  29.      return View("Index");  
  30. }  
Export data to Text file
  1. [HttpPost]  
  2. [AllowMultipleButton(Name = "action", Argument = "ExportToText")]  
  3. public ActionResult ExportToText(int? pageNumber)  
  4. {  
  5.      var delimeter = ",";  
  6.      var lineEndDelimeter = ";";  
  7.      DataTable dtProduct = GetProductsDetail(pageNumber);  
  8.   
  9.      StringBuilder sb = new StringBuilder();  
  10.      string Columns = string.Empty;  
  11.   
  12.      foreach (DataColumn column in dtProduct.Columns)  
  13.      {  
  14.          Columns += column.ColumnName + delimeter;  
  15.      }  
  16.      sb.Append(Columns.Remove(Columns.Length - 1, 1) + lineEndDelimeter);   
  17.      foreach (DataRow datarow in dtProduct.Rows)  
  18.      {  
  19.          string row = string.Empty;  
  20.          foreach (object items in datarow.ItemArray)  
  21.          {  
  22.              row += items.ToString() + delimeter;  
  23.          }  
  24.                   
  25.          sb.Append(row.Remove(row.Length - 1, 1) + lineEndDelimeter);  
  26.      }  
  27.   
  28.      Response.ClearContent();  
  29.      Response.ClearHeaders();  
  30.      Response.Buffer = true;  
  31.      Response.ContentType = "application/Text";  
  32.      Response.AddHeader("Content-Disposition", "attachment; filename=ProductDetails.txt;");  
  33.      Response.Output.Write(sb.ToString());  
  34.      Response.Flush();  
  35.      Response.End();  
  36.   
  37.      return View("Index");  
  38. }  
Below out ut files are generated from each button click.
 
How To Export Data In EXCEL, PDF, CSV, Word, JSON, XML And Text File In MVC Application
 
Let's check some of the resulting generated files. 
 
Excel File
 
How To Export Data In EXCEL, PDF, CSV, Word, JSON, XML And Text File In MVC Application 
 
Csv file
 
How To Export Data In EXCEL, PDF, CSV, Word, JSON, XML And Text File In MVC Application 
 
Pdf File
 
How To Export Data In EXCEL, PDF, CSV, Word, JSON, XML And Text File In MVC Application 
 
Json File
 
How To Export Data In EXCEL, PDF, CSV, Word, JSON, XML And Text File In MVC Application 
 

Conclusion

 
In this article, I have demonstrated how to export data in different file formats using MVC applications which are most commonly used in real time projects. In the past, many times I have seen that a user needs different types of files that need to export data. I thought to write this article which helps to get all solutions in a single article which will help users who are beginners or who need to export data in different types of file formats. I have attached source code in case you want to use it for your reference.
 
Thanks for reading. Happy coding!!! 


Similar Articles