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
- 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
{
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;
}
}
public string Delimiter
{
get
{
if (string.IsNullOrEmpty(this._delimiter))
{
this._delimiter = CultureInfo.CurrentCulture.TextInfo.ListSeparator;
}
return this._delimiter;
}
set { this._delimiter = value; }
}
public Encoding ContentEncoding
{
get
{
if (this._contentEncoding == null)
{
this._contentEncoding = Encoding.Unicode;
}
return this._contentEncoding;
}
set { this._contentEncoding = value; }
}
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;
}
}
public bool HasPreamble { get; set; }
public string LineBreak
{
get
{
if (string.IsNullOrEmpty(this._lineBreak))
{
this._lineBreak = Environment.NewLine;
}
return this._lineBreak;
}
set { this._lineBreak = value; }
}
public bool BufferOutput { get; set; }
#endregion
#region Ctor
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;
}
public CsvFileResult(IEnumerable<TEntity> source, string fileDonwloadName)
: this(source, fileDonwloadName, DefaultContentType)
{
}
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
public string ViewName
{
get { return _viewName; }
set { _viewName = value; }
}
public ControllerContext Context
{
get { return _context; }
set { _context = value; }
}
public object ViewModel
{
get
{
return this._viewModel;
}
set { _viewModel = value; }
}
public Encoding ContentEncoding
{
get
{
if (this._contentEncoding == null)
{
this._contentEncoding = Encoding.UTF8;
}
return this._contentEncoding;
}
set { this._contentEncoding = value; }
}
public bool HasPreamble { get; set; }
public bool BufferOutput { get; set; }
#endregion
#region Ctor
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;
}
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);
}
}
public Func<TEntity, IEnumerable<string>> Map
{
get
{
return _map;
}
set { _map = value; }
}
public IEnumerable<TEntity> DataSource
{
get
{
return this._dataSource;
}
}
public Encoding ContentEncoding
{
get
{
if (this._contentEncoding == null)
{
this._contentEncoding = Encoding.UTF8;
}
return this._contentEncoding;
}
set { this._contentEncoding = value; }
}
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;
}
}
public bool HasPreamble { get; set; }
public bool BufferOutput { get; set; }
#endregion
#region Ctor
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;
}
public XlsFileResult(IEnumerable<TEntity> source, string fileDonwloadName)
: this(source, fileDonwloadName, DefaultContentType)
{
}
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
