Export Data In EXCEL, PDF, CSV, Word, JSON, XML And Text File In .NET Core 3.1 Using MVC Core

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 MVC core and .net core 3.1 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 in mvc core. Source code is attached so if you wish to download full flaged functionlity then you can download from .zip file attached with this article.

 

Tchknology tools and version used 

• SQL Server management studio 2017
• Visual stuio 2019
• .net core 3.1 installed

Lets start with create new table in SQL server and insert 5k data in table using below script, 

-- ==================== Create Table ============================
CREATE TABLE [dbo].[Products](
    [ProductID] [int] IDENTITY(1,1) NOT NULL,
    [ProductName] [nvarchar](max) NULL,
    [Price] [int] NOT NULL,
    [ProductDescription] [nvarchar](max) NULL,
 CONSTRAINT [PK_dbo.Products] PRIMARY KEY CLUSTERED
(
    [ProductID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- ===============================================
declare @productId int
select @productId = 1
declare @ProductName varchar(100)
declare @Price int
declare @Productdescription varchar(500)

-- ================================================
--Loop through 1000 records and insert into table
-- ================================================

while @productId >=1 and @productId <= 5000
begin
set @ProductName = 'PENT - '+ CAST(@productId as nvarchar(10))
    set @Price = 1000
    set @Productdescription = 'PENT - Description - '+ CAST(@productId as nvarchar(10))

if (@productId%2 = 0)
begin
    set @ProductName = 'PENT - '+ CAST(@productId as nvarchar(10))
    set @Price = 2000
    set @Productdescription = 'PENT - Description - '+ CAST(@productId as nvarchar(10))
end
if(@productId%3 = 0)
begin
    set @ProductName = 'Color T-SHIRT - '+ CAST(@productId as nvarchar(10))
    set @Price = 3000
    set @Productdescription = 'Color T-SHIRT - Description - '+ CAST(@productId as nvarchar(10))
end
if(@productId%5=0)
begin
    set @ProductName = 'Designed Shirt - '+ CAST(@productId as nvarchar(10))
    set @Price = 5000
    set @Productdescription = 'Designed Shirt - Description - '+ CAST(@productId as nvarchar(10))
end
    insert into products(ProductName, Price,ProductDescription)
                values( @ProductName ,@Price, @Productdescription)
    select @productId = @productId + 1
end
---====================================================

Once table created and data inserted in table we can create new project in visual studio.

 

If you do not have installed .net core 3.1 yet then you can download .net core different version from here Click Here

 

Lets start step by step with new project in visual studio. 

• Start Visual Studio and click on click on create new project

•  Click on Asp .Net Core Web application and click on next.

• Give project name as per your requirement, Here I have given "ExportData"

•  Select Web application Model-View-Controller and click on Create.

• Once you click on create you are ready with your new project with default files and folders.

 

• Click on project solution and check Target framework and confirm that its .net core 3.1. Lets run application and check its up and running without error. 

• Lets start with require nuget pakages for export data in different file format. Here I am going to use two different nuget packages 

1. ItextSharp
2. XMLClosed

• Lest install both packege from nuget package manager.

 

  

• Now lets check under installed package we should be able to see both packages as below.

 

Lets create Model class, Service Interface and service class which implement service interface to get data from database using entity framework core. 

public class Product
    {
        public int ProductID { get; set; }

        public string ProductName { get; set; }

        public int Price { get; set; }

        public string ProductDescription { get; set; }
    }

Add connection string value in Appsetting.json file

{
  "ConnectionStrings": {
    "ExportDataContext": "Data Source=.;Initial Catalog=MyTestDV;Persist Security Info=True;User ID=sa;Password=password"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*"
}

Create DBContext class with name ExportContext

public class ExportContext : DbContext
   {
       protected readonly IConfiguration _configuration;

       public ExportContext(IConfiguration configuration)
       : base()
       {
           _configuration = configuration;
       }


       public virtual DbSet<Product> ProductsDetails { get; set; }

       protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
       {
           if (!optionsBuilder.IsConfigured)
           {
               var connectionString = _configuration.GetConnectionString("ExportDataContext");
               optionsBuilder.UseSqlServer(connectionString, options => options.EnableRetryOnFailure());
           }
       }

       protected override void OnModelCreating(ModelBuilder modelBuilder)
       {
           base.OnModelCreating(modelBuilder);
           modelBuilder.HasDefaultSchema("dbo");
           modelBuilder.Entity<Product>().ToTable("Products");
       }
   }

Create Interface for repository and Product Repository 

public interface IProductRepository
    {
        List<Product> GetProducts();
    }

public class ProductRepository : IProductRepository
   {
       public readonly ExportContext _context;
       public ProductRepository(ExportContext exportContext)
       {
           _context = exportContext;
       }

       public List<Product> GetProducts()
       {
           return _context.ProductsDetails.ToList();
       }
   }

Create Service interface and service class

public interface IProductService
   {
       List<Product> GetProductData();
   }

public class ProductService : IProductService
   {
       public IProductRepository _productRepository;
       public ProductService(IProductRepository productRepository)
       {
           _productRepository = productRepository;
       }
       List<Product> IProductService.GetProductData()
       {
           return _productRepository.GetProducts();
       }
   }

Create new controller with ExportDataController

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Xml;
using ClosedXML.Excel;
using CsvHelper;
using ExportData.Common;
using ExportData.IService;
using ExportData.Models;
using Grpc.Core;
using iTextSharp.text;
using iTextSharp.text.pdf;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Nancy.Json;

namespace ExportData.Controllers
{
    public class ExportDataController : Controller
    {
        private IProductService _productService;
        public ExportDataController(IProductService productService)
        {
            _productService = productService;
        }
        public IActionResult Index()
        {
            return View();
        }

        [HttpPost]
        public IActionResult ExporDataToFile()
        {
            var dictioneryexportType = Request.Form.ToDictionary(x => x.Key, x => x.Value.ToString());
            var exportType = dictioneryexportType["Export"];
            var products = GetProductsDetail();
            switch (exportType)
            {
                case "Excel":
                    ExportToExcel(products);
                    break;
                case "Csv":
                    ExportToCsv(products);
                    break;
                case "Pdf":
                     ExportToPdf(products);
                    break;
                case "Word":
                     ExportToWord(products);
                    break;
                case "Json":
                     ExportToJson(products);
                    break;
                case "Xml":
                     ExportToXML(products);
                    break;
                case "Text":
                     ExportToText(products);
                    break;
            }
            return null;
        }
        private void ExportToExcel(DataTable products)
        {
            using (var workbook = new XLWorkbook())
            {
                var worksheet = workbook.Worksheets.Add("Products");
                var currentRow = 1;
                worksheet.Cell(currentRow, 1).Value = "ProductID";
                worksheet.Cell(currentRow, 2).Value = "ProductName";
                worksheet.Cell(currentRow, 3).Value = "Price";
                worksheet.Cell(currentRow, 4).Value = "ProductDescription";

                for (int i = 0; i < products.Rows.Count; i++)
                {
                    {
                        currentRow++;
                        worksheet.Cell(currentRow, 1).Value = products.Rows[i]["ProductID"];
                        worksheet.Cell(currentRow, 2).Value = products.Rows[i]["ProductName"];
                        worksheet.Cell(currentRow, 3).Value = products.Rows[i]["Price"];
                        worksheet.Cell(currentRow, 4).Value = products.Rows[i]["ProductDescription"];

                    }
                }
                using var stream = new MemoryStream();
                workbook.SaveAs(stream);
                var content = stream.ToArray();
                Response.Clear();
                Response.Headers.Add("content-disposition", "attachment;filename=ProductDetails.xls");
                Response.ContentType = "application/xls";
                Response.Body.WriteAsync(content);
                Response.Body.Flush();
            }
        }
        private void ExportToCsv(DataTable products)
        {
            StringBuilder sb = new StringBuilder();

            IEnumerable<string> columnNames = products.Columns.Cast<DataColumn>().
                                              Select(column => column.ColumnName);
            sb.AppendLine(string.Join(",", columnNames));

            foreach (DataRow row in products.Rows)
            {
                IEnumerable<string> fields = row.ItemArray.Select(field =>
                  string.Concat("\"", field.ToString().Replace("\"", "\"\""), "\""));
                sb.AppendLine(string.Join(",", fields));
            }
            byte[] byteArray = ASCIIEncoding.ASCII.GetBytes(sb.ToString());
            Response.Clear();
            Response.Headers.Add("content-disposition", "attachment;filename=ProductDetails.csv");
            Response.ContentType = "application/text";
            Response.Body.WriteAsync(byteArray);
            Response.Body.Flush();
        }
        private void ExportToPdf(DataTable products)
        {

            if (products.Rows.Count > 0)
            {
                int pdfRowIndex = 1;
                string filename = "ProductDetails-" + DateTime.Now.ToString("dd-MM-yyyy hh_mm_s_tt");
                string filepath = MyServer.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 products.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.Clear();
                Response.Headers.Clear();
                Response.ContentType = "application/pdf";
                Response.Headers.Add("Content-Length", getContent.Length.ToString());
                Response.Headers.Add("Content-Disposition", "attachment; filename=" + filename + ".pdf;");
                Response.Body.WriteAsync(getContent);
                Response.Body.Flush();
            }
        }
        private void ExportToWord(DataTable products)
        {
            DataTable dtProduct = GetProductsDetail();

            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>");
                }
                byte[] byteArray = ASCIIEncoding.ASCII.GetBytes(sbDocumentBody.ToString());

                Response.Clear();
                Response.Headers.Add("Content-Type", "application/msword");
                Response.Headers.Add("Content-disposition", "attachment; filename=ProductDetails.doc");
                Response.Body.WriteAsync(byteArray);
                Response.Body.FlushAsync();
            }
        }
        private void ExportToJson(DataTable products)
        {
            var listProduct = (from DataRow row in products.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);
            byte[] byteArray = ASCIIEncoding.ASCII.GetBytes(jsonProductList);

            Response.Clear();
            Response.Headers.Clear();
            Response.ContentType = "application/json";
            Response.Headers.Add("Content-Length", jsonProductList.Length.ToString());
            Response.Headers.Add("Content-Disposition", "attachment; filename=ProductDetails.json;");
            Response.Body.WriteAsync(byteArray);
            Response.Body.FlushAsync();
        }
        private void ExportToXML(DataTable products)
        {
            var listProduct = (from DataRow row in products.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();
            XmlDocument xml = new XmlDocument();
            XmlElement root = xml.CreateElement("Products");
            xml.AppendChild(root);
            foreach (var product in listProduct)
            {
                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);
            }
            byte[] byteArray = ASCIIEncoding.ASCII.GetBytes(xml.OuterXml.ToString());

            Response.Clear();
            Response.Headers.Clear();
            Response.ContentType = "application/xml";
            Response.Headers.Add("Content-Disposition", "attachment; filename=ProductDetails.xml;");
            Response.Body.WriteAsync(byteArray);
            Response.Body.Flush();
        }
        private void ExportToText(DataTable products)
        {
            var delimeter = ",";
            var lineEndDelimeter = ";";


            StringBuilder sb = new StringBuilder();
            string Columns = string.Empty;

            foreach (DataColumn column in products.Columns)
            {
                Columns += column.ColumnName + delimeter;
            }
            sb.Append(Columns.Remove(Columns.Length - 1, 1) + lineEndDelimeter);
            foreach (DataRow datarow in products.Rows)
            {
                string row = string.Empty;
                foreach (object items in datarow.ItemArray)
                {
                    row += items.ToString() + delimeter;
                }

                sb.Append(row.Remove(row.Length - 1, 1) + lineEndDelimeter);
            }
            byte[] byteArray = ASCIIEncoding.ASCII.GetBytes(sb.ToString());

            Response.Clear();
            Response.Headers.Clear();
            Response.ContentType = "application/Text";
            Response.Headers.Add("Content-Disposition", "attachment; filename=ProductDetails.txt;");
            Response.Body.WriteAsync(byteArray);
            Response.Body.FlushAsync();
        }
        private DataTable GetProductsDetail()
        {
            var products = _productService.GetProductData().ToList();

            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;
        }
    }
}

Add Index.cshtml for for ExportDataController

@{
    ViewData["Title"] = "Index";
}
<div class="text-center">
    <h1 class="display-4">Welcome To Demo</h1>
    <h1 class="display-4">Export Data In Differnt File Format</h1>
</div>
<div class="jumbotron">
    <div class="lead">
        <div class="table-responsive col-md-12" id="divajaxCall">
                @using (Html.BeginForm("ExporDataToFile", "ExportData", FormMethod.Post))
                {
                    <div class="row">
                        <div class="col-md-3">
                            <label>
                                <input checked="checked" id="ExportExcel" name="Export"
                                       type="radio" value="Excel" />
                                <i class="far fa-file-excel">  Export To Excel</i>
                            </label>
                        </div>
                        <div class="col-md-3">
                            <label>
                                <input id="ExportCSV" name="Export" type="radio"
                                       value="Csv" />
                                <i class="fas fa-file-csv">  Export To Csv</i>
                            </label>
                        </div>
                        <div class="col-md-3">
                            <label>
                                <input id="ExportPdf" name="Export" type="radio"
                                       value="Pdf" />
                                <i class="far fa-file-pdf">  Export To Pdf</i>
                            </label>
                        </div>
                        <div class="col-md-3">
                            <label>
                                <input id="ExportToWord" name="Export" type="radio"
                                       value="Word" />
                                <i class="far fa-file-word">  Export To Word</i>
                            </label>
                        </div>
                    </div>
                    <div class="row">
                        <div class="col-md-3">
                            <label>
                                <input id="ExportToJson" name="Export" type="radio"
                                       value="Json" />
                                <i class="fab fa-js-square">  Export To Json</i>
                            </label>
                        </div>
                        <div class="col-md-3">
                            <label>
                                <input id="ExportToXml" name="Export" type="radio"
                                       value="Xml" />
                                <i class="far fa-file-code">  Export To XML</i>
                            </label>
                        </div>
                        <div class="col-md-3">
                            <label>
                                <input id="ExportToText" name="Export" type="radio"
                                       value="Text" />
                                <i class="far fa-file-alt">  Export To Text</i>
                            </label>
                        </div>
                    </div>
                    <div class="row">
                        <div class="col-md-12" style="text-align:center; padding : 5px 5px 5px 5px">
                            <button id="btnExportData" type="submit" value="ExportData" name="action">
                                <i class="fas fa-file-download"> Export Data </i>
                            </button>
                        </div>
                    </div>
                    <div class="row" style="text-align:center; padding : 5px 5px 5px 5px"></div>
                    <div class="divfooter">
                    </div>
                }
            </div>
        </div>
</div>

<script src="https://kit.fontawesome.com/00267cf40d.js" crossorigin="anonymous"></script>

Add dependency for Dbcontext, ProductRepository and ProductServive as below in startup.cs file

public void ConfigureServices(IServiceCollection services)
{
            services.AddControllersWithViews();
            services.AddDbContext<ExportContext>();
            services.AddTransient<IProductRepository, ProductRepository>();
            services.AddTransient<IProductService, ProductService>();
}

Add Default EndPoint which you want set as dafault route. Here I have put ExportData controller and Index method as default endpoint.

app.UseEndpoints(endpoints =>
           {
               endpoints.MapControllerRoute(
                   name: "default",
                   pattern: "{controller=ExportData}/{action=Index}/{id?}");
           });

Now we are ready with all require set up. Lets press F5 and run our application and it should display with all option for different type of file as below screen

• Now lets select desire option and click on Export Data button. File will generate with desire output with selected option. Here I have tried all option one by one so you will be able to see all files downloaded at down in browser.

 

• As per above snippet you able to see downloaded files.

In this article, I have demonstrated how to export data in different file formats using MVC core applications and .net core 3.1 which are most commonly used in real time projects. IAs reporting pupose we need to generate files as per need of client requirement. 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. Here I have attached source code as well so it might be useful for beginer for reference.

 

Happy Coding !!! I hope this article is useful.