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.

How to Create SQL script and load data in table using ajax call in mvc application

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.

Step 3

Let's startwith the options, one by one. Here is the screen with all Export button options available.

Step 4

I have created a new Controller, view and repositery to get product details from database.

public class ProductRepository
{
private const int pageSize = 20;
public List<Product> GetProducts(int? pageNumber)
{
var numberOfRecordToskip = pageNumber * pageSize;
using (var context = new Products())
{
return context.ProductsDetails.OrderBy(x=>x.ProductID).Skip(Convert.ToInt32(numberOfRecordToskip)).Take(pageSize).ToList<Product>();
}
}
}

Common method to get product detail which is created by a private method in controller.

public class ProductController : Controller
{
private ProductRepository productRepository;
// GET: Product
public ActionResult Index()
{
return View();
}
[HttpGet]
public ActionResult ProductList(int? pageNumber)
{
productRepository = new ProductRepository();
var model = productRepository.GetProducts(pageNumber);
return PartialView("~/Views/Product/ProductList.cshtml", model);
}
private DataTable GetProductsDetail(int? pageNumber)
{
productRepository = new ProductRepository();
var products = productRepository.GetProducts(pageNumber);
DataTable dtProduct = new DataTable("ProductDetails");
dtProduct.Columns.AddRange(new DataColumn[4] { new DataColumn("ProductID"),
new DataColumn("ProductName"),
new DataColumn("Price"),
new DataColumn("ProductDescription") });
foreach (var product in products)
{
dtProduct.Rows.Add(product.ProductID, product.ProductName, product.Price, product.ProductDescription);
}
return dtProduct;
}
}

Step 5

Added Index.cshtml

@model IEnumerable<LazyLoadingDemo.Product>
<div class="jumbotron">
<h2>Lazy Loading Example Using Ajax in MVC Application</h2>
<p class="lead">
<br />
<h4>Export Data using different file format like Excel, CSV, PDF, WORD, JSON, XML, TEXT etc.</h4>
</p>
</div>
<div class="table-responsive col-md-12" id="divajaxCall">
<form action="" method="post">
<div class="row">
<div style="text-align:center; padding:0 0 0 0">
<button type="submit" value="ExportToExcel" name="action">
<i class="far fa-file-excel"></i> Export To Excel
</button>
<button type="submit" value="ExportToCsv" name="action">
<i class="fas fa-file-csv"></i> Export To Csv
</button>
<button type="submit" value="ExportToPdf" name="action">
<i class="far fa-file-pdf"></i> Export To Pdf
</button>
<button type="submit" value="ExportToWord" name="action">
<i class="far fa-file-word"></i> Export To Word
</button>
<button type="submit" value="ExportToJson" name="action">
<i class="fab fa-js-square"></i> Export To Json
</button>
<button type="submit" value="ExportToXml" name="action">
<i class="far fa-file-code"></i> Export To XML
</button>
<button type="submit" value="ExportToText" name="action">
<i class="far fa-file-alt"></i> Export To Text
</button>
</div>
</div>
<div class="row" style="text-align:center; padding : 5px 5px 5px 5px"></div>
<div class="row">
<table class="table table-striped table-bordered">
<thead>
<tr>
<th>
@Html.DisplayNameFor(model => model.ProductName)
</th>
<th>
@Html.DisplayNameFor(model => model.Price)
</th>
<th>
@Html.DisplayNameFor(model => model.ProductDescription)
</th>
</tr>
</thead>
<tbody>
@if (Model != null)
{
@Html.Partial("~/Views/Product/ProductList", Model)
}
</tbody>
</table>
</div>
<div class="row">
<div id="loading" style="text-align:center; padding:0 0 0 0">
<img src='~/Content/progress-loader1.gif' />
</div>
</div>
<div class="divfooter">
</div>
</form>
</div>
<div id="divHide"></div>
@section scripts{
<script src="~/Scripts/lazyLoading.js"></script>
<script type="text/javascript">
$(function () {
$("div#loading").hide();
$("#divajaxCall").css("border", "1px solid gray");
$("#divajaxCall").css("padding", "10px 5px 5px 5px");
$("#divajaxCall").css("border-radius", "10px");
});
var ajaxCallUrl = '@Url.RouteUrl("ProductDataList")';
$(window).scroll(scrollHandler);
</script>
}
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<link rel="stylesheet" href="~/Content/CustomStyle.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<script src="https://kit.fontawesome.com/00267cf40d.js" crossorigin="anonymous"></script>

Partial view ProductList.cshtml

@model IEnumerable<LazyLoadingDemo.Product>
@foreach (var item in Model)
{
<tr>
<td>
@Html.DisplayFor(modelItem => item.ProductName)
</td>
<td>@Html.DisplayFor(modelItem => item.Price)</td>
<td> @Html.DisplayFor(modelItem => item.ProductDescription)</td>
</tr>
}

Step 6

I have created a custom attribute to handle multiple submit button clicks for single form in mvc.

[AttributeUsage(AttributeTargets.Method, AllowMultiple = false, Inherited = true)]
public class AllowMultipleButtonAttribute : ActionNameSelectorAttribute
{
public string Name { get; set; }
public string Argument { get; set; }
public override bool IsValidName(ControllerContext controllerContext, string actionName, MethodInfo methodInfo)
{
var isValidName = false;
isValidName = controllerContext.HttpContext.Request[Name] != null &&
controllerContext.HttpContext.Request[Name] == Argument;
return isValidName;
}
}

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

[HttpPost]
[AllowMultipleButton(Name = "action", Argument = "ExportToExcel")]
public ActionResult ExportToExcel(int? pageNumber)
{
DataTable dtProduct = GetProductsDetail(pageNumber);
using (XLWorkbook woekBook = new XLWorkbook())
{
woekBook.Worksheets.Add(dtProduct);
using (MemoryStream stream = new MemoryStream())
{
woekBook.SaveAs(stream);
return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "ProductDetails.xlsx");
}
}
}

Export data to CSV file

[HttpPost]
[AllowMultipleButton(Name = "action", Argument = "ExportToCsv")]
public ActionResult ExportToCsv(int? pageNumber)
{
DataTable dtProduct = GetProductsDetail(pageNumber);
StringBuilder sb = new StringBuilder();
IEnumerable<string> columnNames = dtProduct.Columns.Cast<DataColumn>().
Select(column => column.ColumnName);
sb.AppendLine(string.Join(",", columnNames));
foreach (DataRow row in dtProduct.Rows)
{
IEnumerable<string> fields = row.ItemArray.Select(field =>
string.Concat("\"", field.ToString().Replace("\"", "\"\""), "\""));
sb.AppendLine(string.Join(",", fields));
}
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=ProductDetails.csv");
Response.Charset = "";
Response.ContentType = "application/text";
Response.Output.Write(sb);
Response.Flush();
Response.End();
return View("Index");
}

Export data to PDF file

[HttpPost]
[AllowMultipleButton(Name = "action", Argument = "ExportToPdf")]
public ActionResult ExportToPdf(int? pageNumber)
{
DataTable dtProduct = GetProductsDetail(pageNumber);
if (dtProduct.Rows.Count > 0)
{
int pdfRowIndex = 1;
string filename = "ProductDetails-" + DateTime.Now.ToString("dd-MM-yyyy hh_mm_s_tt");
string filepath = Server.MapPath("\\") + "" + filename + ".pdf";
Document document = new Document(PageSize.A4, 5f, 5f, 10f, 10f);
FileStream fs = new FileStream(filepath, FileMode.Create);
PdfWriter writer = PdfWriter.GetInstance(document, fs);
document.Open();
Font font1 = FontFactory.GetFont(FontFactory.COURIER_BOLD, 10);
Font font2 = FontFactory.GetFont(FontFactory.COURIER, 8);
float[] columnDefinitionSize = { 2F, 5F, 2F, 5F };
PdfPTable table;
PdfPCell cell;
table = new PdfPTable(columnDefinitionSize)
{
WidthPercentage = 100
};
cell = new PdfPCell
{
BackgroundColor = new BaseColor(0xC0, 0xC0, 0xC0)
};
table.AddCell(new Phrase("ProductId", font1));
table.AddCell(new Phrase("ProductName", font1));
table.AddCell(new Phrase("Price", font1));
table.AddCell(new Phrase("ProductDescription", font1));
table.HeaderRows = 1;
foreach (DataRow data in dtProduct.Rows)
{
table.AddCell(new Phrase(data["ProductId"].ToString(), font2));
table.AddCell(new Phrase(data["ProductName"].ToString(), font2));
table.AddCell(new Phrase(data["Price"].ToString(), font2));
table.AddCell(new Phrase(data["ProductDescription"].ToString(), font2));
pdfRowIndex++;
}
document.Add(table);
document.Close();
document.CloseDocument();
document.Dispose();
writer.Close();
writer.Dispose();
fs.Close();
fs.Dispose();
FileStream sourceFile = new FileStream(filepath, FileMode.Open);
float fileSize = 0;
fileSize = sourceFile.Length;
byte[] getContent = new byte[Convert.ToInt32(Math.Truncate(fileSize))];
sourceFile.Read(getContent, 0, Convert.ToInt32(sourceFile.Length));
sourceFile.Close();
Response.ClearContent();
Response.ClearHeaders();
Response.Buffer = true;
Response.ContentType = "application/pdf";
Response.AddHeader("Content-Length", getContent.Length.ToString());
Response.AddHeader("Content-Disposition", "attachment; filename=" + filename + ".pdf;");
Response.BinaryWrite(getContent);
Response.Flush();
Response.End();
}
return View("Index");
}

Export data to Word file

[HttpPost]
[AllowMultipleButton(Name = "action", Argument = "ExportToWord")]
public ActionResult ExportToWord(int? pageNumber)
{
DataTable dtProduct = GetProductsDetail(pageNumber);
if (dtProduct.Rows.Count > 0)
{
StringBuilder sbDocumentBody = new StringBuilder();
sbDocumentBody.Append("<table width=\"100%\" style=\"background-color:#ffffff;\">");
//
if (dtProduct.Rows.Count > 0)
{
sbDocumentBody.Append("<tr><td>");
sbDocumentBody.Append("<table width=\"600\" cellpadding=0 cellspacing=0 style=\"border: 1px solid gray;\">");
// Add Column Headers dynamically from datatable
sbDocumentBody.Append("<tr>");
for (int i = 0; i < dtProduct.Columns.Count; i++)
{
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>");
}
sbDocumentBody.Append("</tr>");
// Add Data Rows dynamically from datatable
for (int i = 0; i < dtProduct.Rows.Count; i++)
{
sbDocumentBody.Append("<tr>");
for (int j = 0; j < dtProduct.Columns.Count; j++)
{
sbDocumentBody.Append("<td class=\"Content\"style=\"border: 1px solid gray;\">" + dtProduct.Rows[i][j].ToString() + "</td>");
}
sbDocumentBody.Append("</tr>");
}
sbDocumentBody.Append("</table>");
sbDocumentBody.Append("</td></tr></table>");
}
Response.Clear();
Response.Buffer = true;
Response.AppendHeader("Content-Type", "application/msword");
Response.AppendHeader("Content-disposition", "attachment; filename=ProductDetails.doc");
Response.Write(sbDocumentBody.ToString());
Response.End();
}
return View("Index");
}

Export data to Json file

[HttpPost]
[AllowMultipleButton(Name = "action", Argument = "ExportToJson")]
public ActionResult ExportToJson(int? pageNumber)
{
DataTable dtProduct = GetProductsDetail(pageNumber);
var listProduct = (from DataRow row in dtProduct.Rows
select new Product()
{
ProductID = row["ProductID"] != null ? Convert.ToInt32(row["ProductID"]) : 0,
ProductName = Convert.ToString(row["ProductName"]),
Price = row["Price"] != null ? Convert.ToInt32(row["Price"]) : 0,
ProductDescription = Convert.ToString(row["ProductDescription"])
}).ToList();
string jsonProductList = new JavaScriptSerializer().Serialize(listProduct);
Response.ClearContent();
Response.ClearHeaders();
Response.Buffer = true;
Response.ContentType = "application/json";
Response.AddHeader("Content-Length", jsonProductList.Length.ToString());
Response.AddHeader("Content-Disposition", "attachment; filename=ProductDetails.json;");
Response.Output.Write(jsonProductList);
Response.Flush();
Response.End();
return View("Index");
}

Export to XML file

[HttpPost]
[AllowMultipleButton(Name = "action", Argument = "ExportToXml")]
public ActionResult ExportToXML(int? pageNumber)
{
productRepository = new ProductRepository();
var products = productRepository.GetProducts(pageNumber);
XmlDocument xml = new XmlDocument();
XmlElement root = xml.CreateElement("Products");
xml.AppendChild(root);
foreach (var product in products)
{
XmlElement child = xml.CreateElement("Product");
child.SetAttribute("ProductID", product.ProductID.ToString());
child.SetAttribute("ProductName", product.ProductName);
child.SetAttribute("Price", product.Price.ToString());
child.SetAttribute("ProductDescription", product.ProductDescription);
root.AppendChild(child);
}
Response.ClearContent();
Response.ClearHeaders();
Response.Buffer = true;
Response.ContentType = "application/xml";
Response.AddHeader("Content-Disposition", "attachment; filename=ProductDetails.xml;");
Response.Output.Write(xml.OuterXml.ToString());
Response.Flush();
Response.End();
return View("Index");
}

Export data to Text file

[HttpPost]
[AllowMultipleButton(Name = "action", Argument = "ExportToText")]
public ActionResult ExportToText(int? pageNumber)
{
var delimeter = ",";
var lineEndDelimeter = ";";
DataTable dtProduct = GetProductsDetail(pageNumber);
StringBuilder sb = new StringBuilder();
string Columns = string.Empty;
foreach (DataColumn column in dtProduct.Columns)
{
Columns += column.ColumnName + delimeter;
}
sb.Append(Columns.Remove(Columns.Length - 1, 1) + lineEndDelimeter);
foreach (DataRow datarow in dtProduct.Rows)
{
string row = string.Empty;
foreach (object items in datarow.ItemArray)
{
row += items.ToString() + delimeter;
}
sb.Append(row.Remove(row.Length - 1, 1) + lineEndDelimeter);
}
Response.ClearContent();
Response.ClearHeaders();
Response.Buffer = true;
Response.ContentType = "application/Text";
Response.AddHeader("Content-Disposition", "attachment; filename=ProductDetails.txt;");
Response.Output.Write(sb.ToString());
Response.Flush();
Response.End();
return View("Index");
}

Below out ut files are generated from each button click.

Let's check some of the resulting generated files.

Excel File

Csv file

Pdf File

Json File

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!!!

14 Comments

Join the conversation! Your thoughts help the community grow.

Anandu G Nath2y ago
Nice Article
richard cruz4y ago
Export data un txt file using async task handler rather for exporting database data to txtfile by the way can we use core 5 api for this.... thanks sir
richard cruz4y ago
Can u make a async method for this to txt file.... looking forward to it sir kumar...
Burak Can4y ago
How can i implement with multiple entities?
- Jignesh Kumar4y ago
  You can create multiple table and add to dataset then export that dataset to excel. Please follow this one, https://www.c-sharpcorner.com/UploadFile/rahul4_saxena/how-to-export-multiple-data-tables-to-multiple-worksheets-in/
Rohit Singh5y ago
It can be possible with the help of third-party software.
- Jignesh Kumar5y ago
  Rohit Singh Yes its possible to achieve via third party software
arun sahani6y ago
Plz also suggest for asp.net core 3.1 environment
- Jignesh Kumar5y ago
  I wrote article on this Please refer https://www.c-sharpcorner.com/article/export-data-in-excel-pdf-csv-word-json-xml-and-text-file-in-mvc-application/
- Jignesh Kumar5y ago
  I am planning to write article on this very soon. I will update you on this.
shiva ksk6y ago
I have one dou
- Jignesh Kumar6y ago
  shiva ksk Yes, Please let me know. I will help on that.
sankeerth m6y ago
Var delimeter = ","; var lineEndDelimeter = ";"; ? is it going to replace any delimeter?. Can excel and pdf support around 1000 columns and 50k records. using this 3rd party tool
- Jignesh Kumar6y ago
  sankeerth m I have tested with 2000 rows. You can create data using script which was provided previous article. Create number of records and try to load in excel and pdf.