EXPORT TO EXCEL USING OPEN XML IN MVC

In Your View

Create a Link: using anchor tag href = "'@Url.Action("ExportToExcel", "YourControllerName")'";

<strong><span style="text-decoration: underline;">Create a Model class to store the data:</span></strong>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using OpenXmlPackaging;

namespace ExportToExcel
{
public class ExcelModel
{
public string Column1{ get; set; }
public string Column2{ get; set; }
public string Column3{ get; set; }
public string Column4{ get; set; }}
}
}

<!--more-->

<span style="text-decoration: underline;"><strong>In Your Controller</strong></span>

<strong>public FileResult ExportToExcel()</strong>
<strong> {</strong>
List ListExcelModelObj = new List();

var data = (from row1 in DBEntity.Table1
join row2 in DBEntity.Table2 on row1.Id equals row2.id
select new {
row1.Col1,
row1.Col2,
row1.Col3,
row1.Col4
}).ToList();

ExcelModel ExcelModelObj = null;

foreach(var item in data)
{
ExcelModelObj = new ExcelModel ();
ExcelModelObj.Column1 = item.Col1;
ExcelModelObj.Column2 = item.Col2;
ExcelModelObj.Column3 = item.Col3;
ExcelModelObj.Column4 = item.Col4;
ListExcelModelObj.Add(ExcelModelObj);
}

<strong>DataTable table = ConvertToDataTable(ListExcelModelObj);</strong>

string tmpPath = Path.GetTempPath();
tmpPath = tmpPath + "ExcelName.xlsx";
<strong>ExportToExcelUsingOpenXMLPkg(table, tmpPath);</strong>

byte[] fileBytes = System.IO.File.ReadAllBytes(tmpPath);
string fileName = Path.GetFileName(tmpPath);
return File(fileBytes, System.Net.Mime.MediaTypeNames.Application.Octet, fileName);

<strong>}</strong>

<strong>public DataTable ConvertToDataTable(IList data)</strong>
<strong> {</strong>
PropertyDescriptorCollection properties =
TypeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
foreach (PropertyDescriptor prop in properties)
table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
foreach (T item in data)
{
DataRow row = table.NewRow();
foreach (PropertyDescriptor prop in properties)
row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
table.Rows.Add(row);
}
return table;
<strong>}</strong>

<strong>public void ExportToExcelUsingOpenXMLPkg(DataTable tbl, string excelFilePath = null)</strong>
<strong> {</strong>
using (var doc = new SpreadsheetDocument(excelFilePath))
{
Worksheet sheet1 = doc.Worksheets.Add("My Sheet");
//sheet1.Cells[1, 1].Value = "Test";
//sheet1.Cells["A1"].Value = 1;
//sheet1.Cells.CreateRange("B10:D5").MergeCells();
sheet1.ImportDataTable(tbl, "A1", true);
}
<strong>}</strong>

To use above code you need Open XML Package dll. you can install it using Nuget as: Install-Package DocumentFormat.OpenXml

References :
<ul>
	<li><a href="https://www.nuget.org/packages/DocumentFormat.OpenXml">Nuget Install OPEN XML PACKAGE</a></li>
	<li><a href="https://github.com/OfficeDev/Open-XML-SDK">https://github.com/OfficeDev/Open-XML-SDK</a></li>
	<li><a href="https://msdn.microsoft.com/en-us/library/documentformat.openxml.packaging.spreadsheetdocument(v=office.14).aspx">MSDN - Create Excel Document</a></li>
</ul>