ASP MVC Export/download Grid contents in different file formats csv,excel,pdf

 Source code here 

Introduction

Exporting data grid/table contents   is common functionality proposed in web applications. Even for reporting as a PDF document, exported as csv flat file or excel document. But each application has its own context and specific requirements that make a developer and a designer looking to rewrite existing resources or customize them.

Here I would like to expose  different ways to export html grid contents in ASP .NET MVC and try to make them generic and reusable. 

Background

 Recently I had to work with bootstrap table and export its contents to csv and pdf. I googled for that and I found plenty examples doing that but none of them doing exactly what I wanted. I had to customize them or write my own code. 

My needs were simple. I have an html button when clicked a table contents will be export in a specific file format. On the server just have a data list that I would like to convert to the requested format.

As requirements I have :

• Fastest method for the export: that if I have a large amount of data I will not spend most of time in data conversion 
• No I/o disk access
• Specify a file name for the downloaded document
• Downloaded document will be opend in Excel or PDF readers 

There are different solutions for this. And each solution has its drawback. Here, I would like to share with you what my work and let discuss later on the breaches in each solution. And I provide an example application that you can download and test. It's just a basic application using an employee dataset displayed in bootstrap grid and some buttons to export the contents.  

 In the code under there will be: 

csvFileResult generic class and how to use it to export to csv file 

pdfFileResult a class and how to use it to export to PDF file 

a different xslFileResult to export to Excel document . 

 

 

Using the code

1. Export grid to CSV 

Here is a csvFileResult class code that inherits from ASP.NET MVC fileResult and converts a collection of data to csv file format 

 

using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Web;
using System.Web.Mvc;

namespace MVCExport
{

    /// <summary>
    /// CSV file result impementation
    /// </summary>
    /// <typeparam name="TEntity">Entity list to transform to CSV</typeparam>
    public class CsvFileResult<TEntity> : FileResult where TEntity : class
    {
        #region Fields

        private const string DefaultContentType = "text/csv";
        private string _delimiter;
        private string _lineBreak;
        private Encoding _contentEncoding;
        private IEnumerable<string> _headers;
        private IEnumerable<PropertyInfo> _sourceProperties;
        private IEnumerable<TEntity> _dataSource;
        private Func<TEntity, IEnumerable<string>> _map;

        #endregion

        #region Properties

        public Func<TEntity, IEnumerable<string>> Map
        {
            get
            {

                return _map;
            }
            set { _map = value; }
        }
        public IEnumerable<TEntity> DataSource
        {
            get
            {
                return this._dataSource;
            }
        }
        /// <summary>
        /// CSV delimiter default ,
        /// </summary>
        public string Delimiter
        {
            get
            {
                if (string.IsNullOrEmpty(this._delimiter))
                {
                    this._delimiter = CultureInfo.CurrentCulture.TextInfo.ListSeparator;
                }

                return this._delimiter;
            }

            set { this._delimiter = value; }
        }

        /// <summary>
        /// Content Encoding (default is UTF8).
        /// </summary>
        public Encoding ContentEncoding
        {

            get
            {
                if (this._contentEncoding == null)
                {
                    this._contentEncoding = Encoding.Unicode;
                }

                return this._contentEncoding;
            }

            set { this._contentEncoding = value; }


        }

        /// <summary>
        /// the first line of the CSV file, column headers
        /// </summary>
        public IEnumerable<string> Headers
        {
            get
            {
                if (this._headers == null)
                {
                    this._headers = typeof(TEntity).GetProperties().Select(x => x.Name);
                }

                return this._headers;
            }

            set { this._headers = value; }
        }

        public IEnumerable<PropertyInfo> SourceProperties
        {
            get
            {
                if (this._sourceProperties == null)
                {
                    this._sourceProperties = typeof(TEntity).GetProperties();
                }

                return this._sourceProperties;
            }
        }



        /// <summary>
        ///  byte order mark (BOM)  .
        /// </summary>
        public bool HasPreamble { get; set; }

        /// <summary>
        /// Line  delimiter \n
        /// </summary>
        public string LineBreak
        {
            get
            {
                if (string.IsNullOrEmpty(this._lineBreak))
                {
                    this._lineBreak = Environment.NewLine;
                }

                return this._lineBreak;
            }

            set { this._lineBreak = value; }
        }



        /// <summary>
        /// Get or Set the response output buffer 
        /// </summary>
        public bool BufferOutput { get; set; }

        #endregion

        #region Ctor
        /// <summary>
        /// Creats new instance of CsvFileResult{TEntity}
        /// </summary>
        /// <param name="source">List of data to be transformed to csv</param>
        /// <param name="fileDonwloadName">CSV file name</param>
        /// <param name="contentType">Http response content type</param>
        public CsvFileResult(IEnumerable<TEntity> source, string fileDonwloadName, string contentType)
            : base(contentType)
        {
            if (source == null)
                throw new ArgumentNullException("source");
            this._dataSource = source;

            if (string.IsNullOrEmpty(fileDonwloadName))
                throw new ArgumentNullException("fileDonwloadName");
            this.FileDownloadName = fileDonwloadName;

            this.BufferOutput = true;

        }

        /// <summary>
        /// Creats new instance of CsvFileResult{TEntity}
        /// </summary>
        /// <param name="source">List of data to be transformed to csv</param>
        /// <param name="fileDonwloadName">CSV file name</param>
        public CsvFileResult(IEnumerable<TEntity> source, string fileDonwloadName)
            : this(source, fileDonwloadName, DefaultContentType)
        {

        }

        /// <summary>
        /// Creats new instance of CsvFileResult{TEntity}
        /// </summary>
        /// <param name="source">List of data to be transformed to csv</param>
        /// <param name="fileDonwloadName">CSV file name</param>
        /// <param name="map">Custom transformation delegate</param>
        /// <param name="headers">Columns headers</param>
        public CsvFileResult(IEnumerable<TEntity> source, string fileDonwloadName, Func<TEntity, IEnumerable<string>> map, IEnumerable<string> headers)
            : this(source, fileDonwloadName, DefaultContentType)
        {
            this._headers = headers;
            this._map = map;
        }

        #endregion

        #region override

        protected override void WriteFile(HttpResponseBase response)
        {
            response.ContentEncoding = this.ContentEncoding;
            response.BufferOutput = this.BufferOutput;
            var streambuffer = ContentEncoding.GetBytes(this.GetCSVData());
            if (HasPreamble)
            {
                var preamble = this.ContentEncoding.GetPreamble();
                response.OutputStream.Write(preamble, 0, preamble.Length);
            }

            response.OutputStream.Write(streambuffer, 0, streambuffer.Length);
        }

        #endregion

        #region local routines

        private string GetCSVHeader()
        {
            string csv = "";
            csv = String.Join(this.Delimiter, this.Headers.Select(x => this.FormatCSV(x)));

            return csv;
        }


        private string GetCSVData()
        {
            string csv = GetCSVHeader();
            Func<TEntity, string> expr = x => this.Map == null ? this.FormatPropertiesCSV(x) : this.FormatMapCSV(x);
            csv += this.LineBreak + String.Join(this.LineBreak, this.DataSource.Select(expr));
            return csv;
        }

        private string FormatCSV(string str)
        {
            str = (str ?? "").Replace(this.Delimiter, "\"" + this.Delimiter + "\"");
            str = str.Replace(this.LineBreak, "\"" + this.LineBreak + "\"");
            str = str.Replace("\"", "\"\"");

            return String.Format("\"{0}\"", str);
        }

        private string FormatPropertiesCSV(TEntity obj)
        {
            string csv = "";

            foreach (var pi in this.SourceProperties)
            {
                string val = GetPropertyValue(pi, obj);
                csv += FormatCSV(val) + this.Delimiter;
            }

            csv = csv.TrimEnd(this.Delimiter.ToCharArray());
            return csv;
        }


        private string GetPropertyValue(PropertyInfo pi, object source)
        {
            try
            {
                var result = pi.GetValue(source, null);
                return (result == null) ? "" : result.ToString();
            }
            catch (Exception)
            {
                return "Can not obtain the value";
            }
        }

        private string FormatMapCSV(TEntity obj)
        {
            return String.Join(this.Delimiter, this.Map(obj).Select(x => FormatCSV(x)));
        }


        #endregion

    }
}

how to use it 

CsvFileResult defines different properties for line break, Delimiter, encoding, file name, data source, header columns and a map for how to transform the data source to specific csv data format . There are also two constructors to initialize those properties 

Here are some examples on how to use CsvFileResult in controller action: 

 public ActionResult MyExportCSV()
        {
            IEnumerable<Employee> dataList = _dataSource.GetAll();
            return new CsvFileResult<Employee>(dataList, "toto.csv");
        }

In this example we get the data List as an IEnumerable of Employee and call the CsvFileResult to convert it to csv by specifying the file name here toto.csv and the data source as data list 

Here the csv headers will be all properties names of the Employee class and the rows will be the values of each item in the datalist.

Let’s see another example: 

 

 public ActionResult MyCustomExportCSV()
        {

            IEnumerable<string> headers = new[] { 
                "FullName" , 
                "Title" ,
                "PhoneNumber" ,
                "Address" 
            };

            IEnumerable<Employee> dataList = _dataSource.GetAll();
            Func<Employee, IEnumerable<string>> map = x => new[] { x.TitleOfCourtesy + " " + x.LastName + " " + x.FirstName, x.Title, x.HomePhone, x.Address + ", " + x.PostalCode + "  " + x.City + "  " + x.Region };
            return new CsvFileResult<Employee>(dataList, "employees.csv", map, headers);
        }

Here we specify the columns we would like export in headers  and we make transformation of the data List by doing a special map . 

    2. Export To PDF 

 Use the known library iTextSharp to transform a razor view to PDF. So first we create a Razor view for the grid and fill it with a data source then transform the result to PDF by the PdfFileResult.

Here is the code of the PdfFileResult:

using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.tool.xml;
using System;
using System.IO;
using System.Text;
using System.Web;
using System.Web.Mvc;

namespace MVCExport
{
    public class PdfFileResult<TEntity> : FileResult where TEntity : class
    {
        #region Fields

        private const string DefaultContentType = "application/pdf";
        private Encoding _contentEncoding;
        private object _viewModel;
        private ControllerContext _context;
        private string _viewName;

        #endregion

        #region Properties

        /// <summary>
        /// 
        /// </summary>
        public string ViewName
        {
            get { return _viewName; }
            set { _viewName = value; }
        }

        /// <summary>
        /// 
        /// </summary>
        public ControllerContext Context
        {
            get { return _context; }
            set { _context = value; }
        }

        

        /// <summary>
        /// Data list to be transformed to Excel
        /// </summary>
        public object ViewModel
        {
            get
            {
                return this._viewModel;
            }
             set { _viewModel = value; }
        }
        
        /// <summary>
        /// Content Encoding (default is UTF8).
        /// </summary>
        public Encoding ContentEncoding
        {

            get
            {
                if (this._contentEncoding == null)
                {
                    this._contentEncoding = Encoding.UTF8;
                }

                return this._contentEncoding;
            }

            set { this._contentEncoding = value; }


        }

        /// <summary>
        ///  byte order mark (BOM)  .
        /// </summary>
        public bool HasPreamble { get; set; }

        /// <summary>
        /// Get or Set the response output buffer 
        /// </summary>
        public bool BufferOutput { get; set; }

        #endregion

        #region Ctor
         /// <summary>
         /// 
         /// </summary>
         /// <param name="vieModel"></param>
         /// <param name="context"></param>
         /// <param name="viewName"></param>
         /// <param name="fileDonwloadName"></param>
         /// <param name="contentType"></param>
        public PdfFileResult(object viewModel, ControllerContext context, string viewName, string fileDonwloadName, string contentType)
            : base(contentType)
        {
            if (viewModel == null)
                throw new ArgumentNullException("viewModel");
            this._viewModel = viewModel;

            if (string.IsNullOrEmpty(fileDonwloadName))
                throw new ArgumentNullException("fileDonwloadName");
            this.FileDownloadName = fileDonwloadName;

            if (string.IsNullOrEmpty(viewName))
                throw new ArgumentNullException("viewName");
            this._viewName = viewName;

            if (context==null)
                throw new ArgumentNullException("context");
            this._context = context;
            this.BufferOutput = true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="viewModel"></param>
        /// <param name="context"></param>
        /// <param name="viewName"></param>
        /// <param name="fileDonwloadName"></param>
        public PdfFileResult(object viewModel, ControllerContext context, string viewName, string fileDonwloadName)
            : this(viewModel, context, viewName, fileDonwloadName,DefaultContentType)
        {

        }
        
        #endregion

        protected override void WriteFile(HttpResponseBase response)
        {
            response.ContentEncoding = this.ContentEncoding;
            response.BufferOutput = this.BufferOutput;
           
            if (HasPreamble)
            {
                var preamble = this.ContentEncoding.GetPreamble();
                response.OutputStream.Write(preamble, 0, preamble.Length);
            }

            this.RenderPDFView(response);
        }

        private void RenderPDFView(HttpResponseBase response)
        {
            string htmlView = this.RenderViewToString(this.Context, this.ViewName, this.ViewModel);
            byte[] streambuffer;
            using (var document = new Document())
            {
                using (var workStream = new MemoryStream())
                {
                    PdfWriter writer = PdfWriter.GetInstance(document, workStream);
                    writer.CloseStream = false;
                    document.Open();
                    using (var reader = new StringReader(htmlView))
                    {
                        XMLWorkerHelper.GetInstance().ParseXHtml(writer, document, reader);
                        document.Close();
                        streambuffer = workStream.ToArray() ;
                        response.OutputStream.Write(streambuffer, 0, streambuffer.Length);
                    }
                }
            }
        }

        private string RenderViewToString(ControllerContext context, String viewPath, object model = null)
        {
            context.Controller.ViewData.Model = model;
            using (var sw = new StringWriter())
            {
                var viewResult = ViewEngines.Engines.FindView(context, viewPath, null);
                var viewContext = new ViewContext(context, viewResult.View, context.Controller.ViewData, context.Controller.TempData, sw);
                viewResult.View.Render(viewContext, sw);
                viewResult.ViewEngine.ReleaseView(context, viewResult.View);
                return sw.GetStringBuilder().ToString();
            }
        }
    }
}

how to use it 

First let's create a razor view for the grid to display in the PDF:

@model MVCExport.Models.EmployeeListViewModel
@{
    Layout = null;
}

<html>
<head>
    <title> Employees </title>
    <style>
        thead th{
            background:red;
            color:white
        }
        caption{
           background:blue;
            color:white;
            font-weight:bold
        }
    </style> 
</head>
<body>
    <table>
        <caption>
             Employees 
        </caption>
        <thead valign="top" >
            <tr>
                @foreach (var head in @Model.Headers)
                {
                    <th >@head</th>
                }
            </tr>
        </thead>
        <tbody>
            @foreach (var items in @Model.Data)
            {
               <tr> 
              @foreach (var item in @items)
              {
                <th>@item</th>
              }
               </tr>
            }
        </tbody>
        <tfoot>
            <tr>
                <td colspan="4" align="right"><strong> Total : </strong>@Model.Total</td>
            </tr>
        </tfoot>
    </table>
</body>
</html>

And now in any controller action let's create an Export action:

 

 public ActionResult PdfExport()
 {
            IEnumerable<string> headers = new[] { 
                "FullName" , 
                "Title" ,
                "PhoneNumber" ,
                "Address" 
            };

            IEnumerable<Employee> dataList = _dataSource.GetAll();
      Func<Employee, IEnumerable<string>> map = x => new[] { x.TitleOfCourtesy + " " + x.LastName + " " + x.FirstName, x.Title, x.HomePhone, x.Address + ", " + x.PostalCode + "  " + x.City + "  " + x.Region };
            EmployeeListViewModel vm = new EmployeeListViewModel()
            {
                Total = dataList.Count(),
                Headers = headers,
                Data = dataList.Select(x => map(x))
            };

       return new PdfFileResult<Employee>(vm, this.ControllerContext, @"PDFGridExport", "employees.pdf");
}

In this example calling PdfFileResult by passing an EmployeeListViewModel as data source, the controllerContext to compile the Razor view, the Razor view name here PDFGridExport   for PDFGridExport.cshtml and finally the exported file name employees.pdf

3. Export To Excel 

 In the attached source code you can find three different ways to export a grid view as Excel document using Ole connection, using ASP GridView, and using  Razor view. We can also use Native Excel libraries in MS Office or using OpenXML library to produce Xlsx documents but those methods are out of scope this example.

Let's see the first one using the Ole connection. I like this method but its slow comparing to others.

Here is XslFileResult class code: 

using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Web;
using System.Web.Hosting;
using System.Web.Mvc;

namespace MVCExport
{
    public class XlsFileResult<TEntity> : FileResult where TEntity : class
    {
        #region Fields

        private const string DefaultContentType = "application/vnd.ms-excel";
        private string _tempPath;
        private string _tableName;



        private Encoding _contentEncoding;
        private IEnumerable<string> _headers;
        private IEnumerable<PropertyInfo> _sourceProperties;
        private IEnumerable<TEntity> _dataSource;
        private Func<TEntity, IEnumerable<string>> _map;

        #endregion

        #region Properties

        public string TableName
        {
            get
            {

                if (string.IsNullOrEmpty(_tableName))
                {
                    _tableName = typeof(TEntity).Name;
                }

                _tableName = _tableName.Trim().Replace(" ", "_");
                if (_tableName.Length > 30)
                {
                    _tableName = _tableName.Substring(0, 30);
                }

                return _tableName;
            }
            set { _tableName = value; }
        }

        public string TempPath
        {
            get
            {
                if (string.IsNullOrEmpty(_tempPath))
                {
                    _tempPath = HostingEnvironment.MapPath(Path.Combine(@"~/App_Data", this.FileDownloadName));
                }
                return _tempPath;
            }
            set
            {
                _tempPath = Path.Combine(value, this.FileDownloadName);
            }
        }

        /// <summary>
        /// Custom properties transformation
        /// </summary>
        public Func<TEntity, IEnumerable<string>> Map
        {
            get
            {
                return _map;
            }

            set { _map = value; }
        }

        /// <summary>
        /// Data list to be transformed to Excel
        /// </summary>
        public IEnumerable<TEntity> DataSource
        {
            get
            {
                return this._dataSource;
            }
        }

        /// <summary>
        /// Content Encoding (default is UTF8).
        /// </summary>
        public Encoding ContentEncoding
        {

            get
            {
                if (this._contentEncoding == null)
                {
                    this._contentEncoding = Encoding.UTF8;
                }

                return this._contentEncoding;
            }

            set { this._contentEncoding = value; }


        }

        /// <summary>
        /// the first line of the CSV file, column headers
        /// </summary>
        public IEnumerable<string> Headers
        {
            get
            {
                if (this._headers == null)
                {
                    this._headers = typeof(TEntity).GetProperties().Select(x => x.Name);
                }

                return this._headers;
            }

            set { this._headers = value; }
        }

        /// <summary>
        /// Object's properties to convert to excel  
        /// </summary>
        public IEnumerable<PropertyInfo> SourceProperties
        {
            get
            {
                if (this._sourceProperties == null)
                {
                    this._sourceProperties = typeof(TEntity).GetProperties();
                }

                return this._sourceProperties;
            }
        }

        /// <summary>
        ///  byte order mark (BOM)  .
        /// </summary>
        public bool HasPreamble { get; set; }

        /// <summary>
        /// Get or Set the response output buffer 
        /// </summary>
        public bool BufferOutput { get; set; }

        #endregion

        #region Ctor
        /// <summary>
        /// Creats new instance of CsvFileResult{TEntity}
        /// </summary>
        /// <param name="source">List of data to be transformed to csv</param>
        /// <param name="fileDonwloadName">CSV file name</param>
        /// <param name="contentType">Http response content type</param>
        public XlsFileResult(IEnumerable<TEntity> source, string fileDonwloadName, string contentType)
            : base(contentType)
        {
            if (source == null)
                throw new ArgumentNullException("source");
            this._dataSource = source;

            if (string.IsNullOrEmpty(fileDonwloadName))
                throw new ArgumentNullException("fileDonwloadName");
            this.FileDownloadName = fileDonwloadName;

            this.BufferOutput = true;
        }

        /// <summary>
        /// Creats new instance of CsvFileResult{TEntity}
        /// </summary>
        /// <param name="source">List of data to be transformed to csv</param>
        /// <param name="fileDonwloadName">CSV file name</param>
        public XlsFileResult(IEnumerable<TEntity> source, string fileDonwloadName)
            : this(source, fileDonwloadName, DefaultContentType)
        {

        }

        /// <summary>
        /// Creats new instance of CsvFileResult{TEntity}
        /// </summary>
        /// <param name="source">List of data to be transformed to csv</param>
        /// <param name="fileDonwloadName">CSV file name</param>
        /// <param name="map">Custom transformation delegate</param>
        /// <param name="headers">Columns headers</param>
        public XlsFileResult(IEnumerable<TEntity> source, Func<TEntity, IEnumerable<string>> map, IEnumerable<string> headers, string fileDonwloadName)
            : this(source, fileDonwloadName, DefaultContentType)
        {
            this._headers = headers;
            this._map = map;
        }

        #endregion

        protected override void WriteFile(HttpResponseBase response)
        {
            response.ContentEncoding = this.ContentEncoding;
            response.BufferOutput = this.BufferOutput;

            if (HasPreamble)
            {
                var preamble = this.ContentEncoding.GetPreamble();
                response.OutputStream.Write(preamble, 0, preamble.Length);
            }

            this.RenderResponse(response);
        }

        private void RenderResponse(HttpResponseBase response)
        {
            if (File.Exists(this.TempPath))
            {
                File.Delete(this.TempPath);
            }
            string sexcelconnectionstring = GetConnectionString(this.TempPath);
            using (System.Data.OleDb.OleDbConnection oledbconn = new System.Data.OleDb.OleDbConnection(sexcelconnectionstring))
            {
                oledbconn.Open();
                this.createTable(oledbconn);
                this.InsertData(oledbconn);
            }

            var streambuffer = this.ContentEncoding.GetBytes(File.ReadAllText(this.TempPath));

            response.OutputStream.Write(streambuffer, 0, streambuffer.Length);
        }

        private IEnumerable<string> GetEntityValues(TEntity obj)
        {
            IEnumerable<string> ds = null;
            if (this.Map != null)
            {
                ds = this.Map(obj);
            }
            else
            {
                ds = this.SourceProperties.Select(x => this.GetPropertyValue(x, obj));

            }

            return ds;
        }

        private string GetPropertyValue(PropertyInfo pi, object source)
        {
            try
            {
                var result = pi.GetValue(source, null);
                return (result == null) ? "" : result.ToString();
            }
            catch (Exception)
            {
                return "Can not obtain the value";
            }
        }

        private string GetConnectionString(string FileName, bool hasHeaders = true)
        {
            string HDR = hasHeaders ? "Yes" : "No";
            return Path.GetExtension(FileName).Equals(".xlsx") ?
                "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"" :
                "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
        }

        private void createTable(OleDbConnection con)
        {
            string tyed = string.Join(",", this.Headers.Select(x => x + " " + "VARCHAR"));
            string commandText = string.Format("CREATE TABLE [{0}]({1});", this.TableName, tyed);
            OleDbCommand oledbcmd = new OleDbCommand(commandText,con);
            oledbcmd.ExecuteNonQuery();
        }

        private void InsertData(OleDbConnection con)
        {
            OleDbDataAdapter oleAdap = new OleDbDataAdapter("SELECT * FROM [" + this.TableName + "]", con);
            OleDbCommandBuilder oleCmdBuilder = new OleDbCommandBuilder(oleAdap);
            oleCmdBuilder.QuotePrefix = "[";
            oleCmdBuilder.QuoteSuffix = "]";
            OleDbCommand cmd = oleCmdBuilder.GetInsertCommand();
            foreach (TEntity row in this.DataSource)
            {
                var pVals = GetEntityValues(row);
                int index = 0;
                foreach (OleDbParameter param in cmd.Parameters)
                {
                    param.Value = pVals.ElementAt(index);
                    index++;
                }

                cmd.ExecuteNonQuery();
            }
        }

        private void InsertDataQuery(OleDbConnection cn)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Length = 0;
            sbSql.Insert(0, "INSERT INTO [" + this.TableName + "]");
            sbSql.Append(" (");
            sbSql.Append(string.Join(",", this.Headers));
            sbSql.Append(")");
            sbSql.Append(string.Join(" UNION ALL ", this.DataSource.Select(x => "  SELECT  " + string.Join(",", GetEntityValues(x)) + " ")));
            sbSql.Append(";");
            OleDbCommand cmd = new OleDbCommand(sbSql.ToString(), cn);
            cmd.ExecuteNonQuery();
        }
    }
}

How to use it 

An example on how to use XslFileResult in controller action :

public ActionResult ExcelExport()
        {
            IEnumerable<Employee> dataList = _dataSource.GetAll();
             

            return new XlsFileResult<Employee>(dataList, "employees.xls");
        }

Just get a data list and pass it to XslFileResult  and specify a file name here employees.xls.

In the attached source code  you can find other examples on how to export as Excel document  including using the RDLC reports. 

Discussion :

Exporting a grid/table to csv is the simplest and the fastest way. When it's not necessary to export to native Excel format, use csv as you can open it in MS Excel and in many other spreadsheet applications and as a plat file document. 

Using Razor view and iTextSharp   to export to PDF is excellent and you can produce stylish reports.

When exporting to native Excel document you have choice to use native office libraries or OpenXML for Xslx or use the OleDb Connection to fill a spread sheet as data table and finally you can use Html table directly in the document. 

Finally, you can use the RDLC reports to export to all file formats. 

 Points of Interest

Here is a reusable code that you can include directly in your applications when working with exporting data grids. You can also use the attached source code for learning purpose