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"
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
- Create the excel file
- Create a list of header row using List
- Select the Header range using "Range"
- Get the Worksheet
- 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
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
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.
Buy us a coffee
Become a Patron