Export to Excel in Asp.Net MVC and .Net Core 3.1

The ClosedXML is a library that enables web developers to work with Excel files. Using ClosedXML, developers can create, read, and manage Excel files.

In this article we will cover how to Export data to Excel file from the Asp.Net MVC and .Net Core 3.1 application Live Example

Prerequisites:

Understanding of C#, Asp.Net Core, MVC

Let's Start:

The first step Let's create a fresh Asp.Net MVC or .Net Core project and add below mentioned dll as reference from Nuget Package.

ClosedXML.dll

Model

Create Model named "Country.cs"and add below mentioned Properties.

public class Country
    {
        public int Country_Id { get; set; }
        public string Country_Title { get; set; }
        public string Country_Code { get; set; }
        public bool Country_Active { get; set; }
    }

Controller

Create Controller named "CountryController" and add below namespace reference to use the ClosedXML.

ClosedXML.Excel

Copy below mentioned [HttpGet] DownloadExcelDocument() method and add into CountryController

Download Countries.json file from here Countries.json

[HttpGet]
        public ActionResult DownloadExcelDocument()
        {
            string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            string fileName = "Countries.xlsx";
 
             // Taken List of data from json file which we want to export to excel.
            List<Country> countries = new List<Country>();
            using (StreamReader sr = new StreamReader(Server.MapPath("~/countries.json")))
            {
                countries = JsonConvert.DeserializeObject<List<Country>>(sr.ReadToEnd());
            }
 
             using (var workbook = new XLWorkbook())
            {
                IXLWorksheet worksheet =
                workbook.Worksheets.Add("Countries");
                worksheet.Cell(1, 1).Value = "Country ID";
                worksheet.Cell(1, 2).Value = "Title";
                worksheet.Cell(1, 3).Value = "Code";
                worksheet.Cell(1, 4).Value = "Status";
 
                for (int index = 1; index <= 4; index++)
                {
                    worksheet.Cell(1, index).Style.Font.Bold = true;
                }
 
                for (int index = 1; index <= countries.Count(); index++)
                {
                    worksheet.Cell(index + 1, 1).Value = countries[index - 1].Country_Id;
                    worksheet.Cell(index + 1, 2).Value = countries[index - 1].Country_Title;
                    worksheet.Cell(index + 1, 3).Value = countries[index - 1].Country_Code;
                    string _active = countries[index - 1].Country_Active ? "Active" : "Inactive";
                    worksheet.Cell(index + 1, 4).Value = _active;
                }
                using (var stream = new MemoryStream())
                {
                    workbook.SaveAs(stream);
                    var content = stream.ToArray();
                    return File(content, contentType, fileName);
                }
            }
        }

Now, Let's create a View page with anchor tag as shown below

<a href="/Country/DownloadExcelDocument">Export To Excel</a>

Run the project. Click on the anchor, The excel file will get downloaded with the given name. Live Example

Additional References:

1. https://github.com/ClosedXML/ClosedXML