Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

ASP.NET MVC5 - AngularJS, Create XLS file using ExcelPackage

0.00/5 (No votes)
20 Dec 2015 1  
This article walks you through the steps for create a report in Excel using EPPlus (ExcelPackage)

Introduction

This article walks you through the steps for create a report in Excel using ExcelPackage.

ExcelPackage provides server-side generation of Excel 2007 spreadsheets.
It is a set of classes and wrappers around the .NET 3.0 System.IO.Packaging API and the new Office Open XML file format. It extracts away the complexity of dealing with the individual XML components making it real easy to create sophisticated spreadsheets on the server.

STEP 1 - Create ASP.NET Web Application

Check the link below, to see all the steps to create a Web Api wtih Entity Framework code first implementation.

http://social.technet.microsoft.com/wiki/contents/articles/26795.asp-net-webapi-entity-framework-code-first.aspx

 

STEP 2 - Install Nuget

Now in order to use ExcelPackage we need to install a Nuget package.

So on the Visual Studio 2015, select the follow menu option:

Tools-> Library Package manager -> Manage NuGet Packages for Solution

Search for EPPlus and select the option Install.

 

STEP 3 - Excel Generate Class

On ContactController add new method to call GenerateXLS

 

C#
using System; 
using System.Collections.Generic; 
using System.Data.Entity; 
using System.Data.Entity.Infrastructure; 
using System.Linq; 
using System.Net; 
using System.Net.Http; 
using System.Web.Http; 
using SampleEF6.Models; 
using System.Threading.Tasks; 
using System.Web; 
using System.IO; 
using System.Net.Http.Headers; 
 
namespace SampleEF6.Controllers 
{ 
    public class ReportController : ApiController 
    { 
        // GET api/<controller> 
        [HttpGet] 
        public async Task<httpresponsemessage> GetXLSReport() 
        { 
            string fileName = string.Concat("Contacts.xls"); 
            string filePath = HttpContext.Current.Server.MapPath("~/Report/" + fileName); 
 
            ContactController contact = new ContactController(); 
            List<contact> contacList = contact.Get().ToList(); 
 
            await SampleEF6.Report.ReportGenerator.GenerateXLS(contacList, filePath); 
 
            HttpResponseMessage result = null; 
            result = Request.CreateResponse(HttpStatusCode.OK); 
            result.Content = new StreamContent(new FileStream(filePath, FileMode.Open)); 
            result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment"); 
            result.Content.Headers.ContentDisposition.FileName = fileName; 
 
            return result; 
        } 
    } 
}
</contact>

 Create the GenerateXLS method like this:

 

C#
using OfficeOpenXml; 
using OfficeOpenXml.Style; 
using SampleEF6.Models; 
using System; 
using System.Collections.Generic; 
using System.Drawing; 
using System.IO; 
using System.Linq; 
using System.Reflection; 
using System.Threading.Tasks; 
using System.Web; 
 
namespace SampleEF6.Report 
{ 
    public class ReportGenerator 
    { 
        public static Task GenerateXLS(List<contact> datasource, string filePath) 
        { 
            return Task.Run(() => 
            { 
                using (ExcelPackage pck = new ExcelPackage()) 
                { 
                    //Create the worksheet 
                    ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Contacts"); 
 
                    ws.Cells[1, 1].Value = "Id"; 
                    ws.Cells[1, 2].Value = "Name"; 
                    ws.Cells[1, 3].Value = "Address"; 
                    ws.Cells[1, 4].Value = "City"; 
                    ws.Cells[1, 5].Value = "Country"; 
 
                    for (int i = 0; i < datasource.Count(); i++) 
                    { 
                        ws.Cells[i + 2, 1].Value = datasource.ElementAt(i).Id; 
                        ws.Cells[i + 2, 2].Value = datasource.ElementAt(i).Name; 
                        ws.Cells[i + 2, 3].Value = datasource.ElementAt(i).Address; 
                        ws.Cells[i + 2, 4].Value = datasource.ElementAt(i).City; 
                        ws.Cells[i + 2, 5].Value = datasource.ElementAt(i).Country; 
                    } 
 
                    using (ExcelRange rng = ws.Cells["A1:A5"]) 
                    { 
                        rng.Style.Font.Bold = true; 
                        rng.Style.Fill.PatternType = ExcelFillStyle.Solid;        //Set Pattern for the background to Solid 
                        rng.Style.Fill.BackgroundColor.SetColor(Color.DarkGray);  //Set color to DarkGray 
                        rng.Style.Font.Color.SetColor(Color.Black); 
                    } 
 
                    pck.SaveAs(new FileInfo(filePath)); 
                } 
            }); 
        } 
    } 
}
</contact>

STEP 4 - Run Application

 

 

Resources

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here