Sometimes you may need to create excel file in C# or in your ASP.NET MVC project, one way of creating it using Microsoft's Office, but in that case you would have to install Microsoft Office on your server, so in this article I will explain to you how you can create excel file without using Office on server using EPPlus.

EPPlus is a library to manage Excel spreadsheets. EPPlus is a .NET library, which reads and writes Excel 2007/2010 or higher files, using Open Office XML format.  Epplus is a free tool.

Let's get started I will provide you step by step example to create .xlsx file using C# & EPPLus

Step 1: Create a new project in your Visual Studio, open your Visual Studio IDE and navigate to File->New->Project-> ASP.NET Empty Web Application.Name the Project as you like, I named it "CreateExcelUsingMVC", click "OK" & then select MVC template in the new screen.

Step 2: Now, we will install EPPlus in our project, so navigate to Tools-> NuGet Package Manager-> Select "Manage Nuget package for this solution...".

Select the "Browse" tab and search for "EPPlus"

epplus-to-create-excel-using-csharp-mvc-min.png

After selecting the Epplus, select the projects in which you want to install the API, and click "Install".

Step 3: Navigate to your project's HomeController and create a new ActionResult method to generate/create excel file using Epplus.

First of all, you would have to create an EPPlus object in our method using which we will create a worksheet

// Create the package and make sure you wrap it in a using statement
using (ExcelPackage excel = new ExcelPackage())
{
     // add a new worksheet to the empty workbook
     ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("TestSheet1");

    // --------- Creating file, Data and styling goes here -------------- //

}

Reference file you would need

using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Web.Mvc;

Now, let's create the file and header row in the file

//Create Excel file in Uploads folder of your project
                FileInfo excelFile = new FileInfo(Server.MapPath("~/ExcelFile/test.xlsx"));

                //Add header row columns name in string list array
                var headerRow = new List<string[]>()
                  {
                    new string[] { "Full Name","Email" }
                  };

                // Get the header range
                string Range = "A1:" + Char.ConvertFromUtf32(headerRow[0].Length + 64) + "1";

                // get the workSheet in which you want to create header
                var worksheet = excel.Workbook.Worksheets["TestSheet1"];

                // Popular header row data
                worksheet.Cells[Range].LoadFromArrays(headerRow);

I have explained each line of code using comment's in the above code, basically, here is what we are doing

  1. Create the excel file
  2. Create a list of header row using List
  3. Select the Header range using "Range" 
  4. Get the Worksheet
  5. Load header in the excel sheet "TestSheet1" using HeaderRow created in the second step.

Complete code till now will be as below

using (ExcelPackage excel = new ExcelPackage())
            {

                //Add Worksheets in Excel file
                excel.Workbook.Worksheets.Add("TestSheet1");

               
                //Create Excel file in Uploads folder of your project
                FileInfo excelFile = new FileInfo(Server.MapPath("~/ExcelFile/test.xlsx"));

                //Add header row columns name in string list array
                var headerRow = new List<string[]>()
                  {
                    new string[] { "Full Name","Email" }
                  };

                // Get the header range
                string Range = "A1:" + Char.ConvertFromUtf32(headerRow[0].Length + 64) + "1";

                // get the workSheet in which you want to create header
                var worksheet = excel.Workbook.Worksheets["TestSheet1"];

                // Popular header row data
                worksheet.Cells[Range].LoadFromArrays(headerRow);

               

                //Save Excel file
                excel.SaveAs(excelFile);
            }
            byte[] fileBytes = System.IO.File.ReadAllBytes(Server.MapPath("~/ExcelFile/test.xlsx"));
            string fileName = "test.xlsx";

            return File(fileBytes, System.Net.Mime.MediaTypeNames.Application.Octet, fileName);

Building project and executing it, gives me excel file as below

excel-file-header-using-epplus-min.png

For styling header rows we can write C# code as below

worksheet.Cells[Range].Style.Font.Bold = true;
worksheet.Cells[Range].Style.Font.Size = 16;
worksheet.Cells[Range].Style.Font.Color.SetColor(System.Drawing.Color.DarkBlue);

To load the data into our excel file we can load data from the database as DataSet or we can create custom array List Object and then add the data

              //Now add some data in rows for each column
                var Data = new List<object[]>()
                    {
                      new object[] {"Test","test@gmail.com"},
                      new object[] {"Test2","test2@gmail.com"},
                      new object[] {"Test3","test3@gmail.com"},

                    };
        //add the data in worksheet, here .Cells[2,1] 2 is rowNumber while 1 is column number
                worksheet.Cells[2, 1].LoadFromArrays(Data);

So, the complete code of your ActionMethod in the controller will be as below

 public ActionResult CreateExcel()
        {
            using (ExcelPackage excel = new ExcelPackage())
            {

                //Add Worksheets in Excel file
                excel.Workbook.Worksheets.Add("TestSheet1");

               
                //Create Excel file in Uploads folder of your project
                FileInfo excelFile = new FileInfo(Server.MapPath("~/ExcelFile/test.xlsx"));

                //Add header row columns name in string list array
                var headerRow = new List<string[]>()
                  {
                    new string[] { "Full Name","Email" }
                  };

                // Get the header range
                string Range = "A1:" + Char.ConvertFromUtf32(headerRow[0].Length + 64) + "1";

                // get the workSheet in which you want to create header
                var worksheet = excel.Workbook.Worksheets["TestSheet1"];

                // Popular header row data
                worksheet.Cells[Range].LoadFromArrays(headerRow);

                //show header cells with different style
                worksheet.Cells[Range].Style.Font.Bold = true;
                worksheet.Cells[Range].Style.Font.Size = 16;
                worksheet.Cells[Range].Style.Font.Color.SetColor(System.Drawing.Color.DarkBlue);

                //Now add some data in rows for each column
                var Data = new List<object[]>()
                    {
                      new object[] {"Test","test@gmail.com"},
                      new object[] {"Test2","test2@gmail.com"},
                      new object[] {"Test3","test3@gmail.com"},

                    };

                //add the data in worksheet, here .Cells[2,1] 2 is rowNumber while 1 is column number
                worksheet.Cells[2, 1].LoadFromArrays(Data);

                //Save Excel file
                excel.SaveAs(excelFile);
            }
            byte[] fileBytes = System.IO.File.ReadAllBytes(Server.MapPath("~/ExcelFile/test.xlsx"));
            string fileName = "test.xlsx";

            return File(fileBytes, System.Net.Mime.MediaTypeNames.Application.Octet, fileName);
        }

Navigate to Index.cshtml in View->Home-> Index.cshtml and add the Razor code to download the file

@{
    ViewBag.Title = "Home Page";
}

<br />

@Html.ActionLink("Test.xlsx", "CreateExcel", "Home") 

Build your MVC project and try to download the "Test.xlsx" file, here is my local project's output in Gif image

create-excel-without-using-interop-office-c-sharp-min.gif

You can also load data from database using an Epplus method for that you can read the Export data to excel (.xlsx & .xls) file using ASP.NET MVC C# article.


If you have any questions, feel free to ask it in the below comment's section or in question's section.