Today I will show you how to generate an excel file from a list of objects in C#. To do this, several plugins allow you to do this work in a global way. In this work, I will try to create a very simple and generic method to generate an excel file from a list of objects whatever their nature.
Step 1
Create a new ASP.NET MVC project.
Give a name to this project. for example "ExcelGeneratingApp".
Choose the MVC type.
Step 2
Create a class "ExcelLib.cs" in the models folder of the application.
Step 3
Add "ClosedXML" library from Nuget Package Manager.
Add class named "Employee.cs" for example.
using System.ComponentModel;
namespace ExcelGeneratingApp.Models
{
public class Employee
{
[DisplayName("Identity number")]
public int ID { get; set; }
[DisplayName("Full name")]
public string Name { get; set; }
[DisplayName("Age")]
public int Age { get; set; }
[DisplayName("Salary")]
public float Salary { get; set; }
[DisplayName("Department name")]
public string Department { get; set; }
}
}
Add an "addHeader" method to "ExcelLib.cs" class. This method allows to add and style header of the table in excel file.
using ClosedXML.Excel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
namespace ExcelGeneratingApp.Models
{
public class ExcelLib
{
// Default Constructor.
public ExcelLib() { }
// Method for adding Header and Title of the table containing List of object values.
public IXLWorksheet addHeader(XLWorkbook wb, List<Object> objs, string title, string fontFamily = "Sakkal Majalla", string color = "#3498DB")
{
// Sheet initialisation.
var ws = wb.Worksheets.Add("nomDeLaListe").SetTabColor(XLColor.UaBlue);
// font choice.
ws.Style.Font.FontName = fontFamily;
ws.Style.Font.SetFontSize(13);
ws.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Style.Alignment.WrapText = true;
Object obj = objs.FirstOrDefault();
// Add the model fields to the header of the excel file.
int totalOfFields = obj.GetType().GetProperties().Length; // number of fields in the object.
int numberOfFields = 0;
// Adding the title of table in excel file.
ws.Range(ws.Cell(4, 4), ws.Cell(4, totalOfFields + 3)).Merge().Value = title;
ws.Range(ws.Cell(4, 4), ws.Cell(4, totalOfFields + 3)).Merge().Style.Fill.BackgroundColor = XLColor.FromHtml(color); ;
ws.Range(ws.Cell(4, 4), ws.Cell(4, totalOfFields + 3)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Range(ws.Cell(4, 4), ws.Cell(4, totalOfFields + 3)).Style.Font.Bold = true;
ws.Range(ws.Cell(4, 4), ws.Cell(4, totalOfFields + 3)).Style.Font.FontColor = XLColor.WhiteSmoke;
ws.Range(ws.Cell(4, 4), ws.Cell(4, totalOfFields + 3)).Style.Font.FontSize = 18;
//Looping all propeties of the object.
foreach (var prop in obj.GetType().GetProperties())
{
var displayNameAttribute = prop.GetCustomAttributes(typeof(DisplayNameAttribute), false);
string displayName = prop.Name;
if (displayNameAttribute.Count() != 0)
{
displayName = (displayNameAttribute[0] as DisplayNameAttribute).DisplayName;
}
numberOfFields++;
ws.Cell(5, totalOfFields - numberOfFields + 4).Value = displayName;
ws.Cell(5, totalOfFields - numberOfFields + 4).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
ws.Cell(5, totalOfFields - numberOfFields + 4).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
ws.Cell(5, totalOfFields - numberOfFields + 4).Style.Border.RightBorder = XLBorderStyleValues.Thin;
ws.Cell(5, totalOfFields - numberOfFields + 4).Style.Border.TopBorder = XLBorderStyleValues.Thin;
ws.Column(totalOfFields - numberOfFields + 4).Width = 30;
ws.Column(totalOfFields - numberOfFields + 4).Style.Font.Bold = true;
}
ws.Range(ws.Cell(5, 4), ws.Cell(5, totalOfFields + 3)).SetAutoFilter();
return ws;
}
}
}
Then add "addBody" method to the "ExcelLib.cs" class.
This method allows to add the list of objects "objs" content to the table in excel file.
It merges the cells of the first column containing the same values.
public IXLWorksheet addBody(IXLWorksheet ws, List<Object> objs)
{
int numberOfFields = 0;
int numberOfRecords = 0;
Object obj = objs.FirstOrDefault();
int totalOfFields = obj.GetType().GetProperties().Length;
string previousValue = "";
int indexOfPreviousValue = 0;
foreach (var item in objs.ToList())
{
numberOfFields = 0;
Type myType = item.GetType();
IList<PropertyInfo> props = new List<PropertyInfo>(myType.GetProperties());
foreach (PropertyInfo prop in props)
{
object propValue = prop.GetValue(item, null);
numberOfFields++;
ws.Cell(6 + numberOfRecords, totalOfFields - numberOfFields + 4).Value = propValue;
ws.Cell(6 + numberOfRecords, totalOfFields - numberOfFields + 4).Style.Font.Bold = true;
ws.Cell(6 + numberOfRecords, totalOfFields - numberOfFields + 4).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
ws.Cell(6 + numberOfRecords, totalOfFields - numberOfFields + 4).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
ws.Cell(6 + numberOfRecords, totalOfFields - numberOfFields + 4).Style.Border.RightBorder = XLBorderStyleValues.Thin;
ws.Cell(6 + numberOfRecords, totalOfFields - numberOfFields + 4).Style.Border.TopBorder = XLBorderStyleValues.Thin;
if (numberOfFields == 1 && numberOfRecords == 0)
{
previousValue = propValue.ToString();
}
else
{
if (numberOfFields == 1)
{
if (previousValue == propValue.ToString())
{
ws.Range(ws.Cell(6 + numberOfRecords - (1 + indexOfPreviousValue), totalOfFields - numberOfFields + 4), ws.Cell(6 + numberOfRecords, totalOfFields - numberOfFields + 4)).Merge().Value = propValue.ToString();
ws.Range(ws.Cell(6 + numberOfRecords - (1 + indexOfPreviousValue), totalOfFields - numberOfFields + 4), ws.Cell(6 + numberOfRecords, totalOfFields - numberOfFields + 4)).Merge().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Range(ws.Cell(6 + numberOfRecords - (1 + indexOfPreviousValue), totalOfFields - numberOfFields + 4), ws.Cell(6 + numberOfRecords, totalOfFields - numberOfFields + 4)).Merge().Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
indexOfPreviousValue++;
}
else
{
previousValue = propValue.ToString();
indexOfPreviousValue = 0;
}
}
}
}
numberOfRecords++;
}
return ws;
}
Then add a "Generate" method for generating the excel file.
public void Generate(List<Object> objs, string title, string fontFamily = "Sakkal Majalla", string color = "#3498DB")
{
// Workbook creation.
using (XLWorkbook wb = new XLWorkbook())
{
var ws = addHeader(wb, objs, title);
ws = addBody(ws, objs);
wb.SaveAs("C://TestExcelGen.xlsx");
}
}
Step 4
To test this method, we will add some code to the Home controller.
public ActionResult Index()
{
List<Employee> employees = new List<Employee>();
employees.Add(new Employee() { ID = 100 ,Name="PETER", Age=32,Salary=12000,Department="INFO"}) ;
employees.Add(new Employee() { ID = 200 ,Name="SCOTT", Age=24,Salary=10000,Department="CIVIL"}) ;
employees.Add(new Employee() { ID = 300 ,Name="ADAM", Age=20,Salary=11000,Department="INDUS"}) ;
employees.Add(new Employee() { ID = 400 ,Name="ETHAN", Age=21,Salary=9000,Department="INFO"}) ;
ExcelLib excel = new ExcelLib();
excel.Generate(employees.Cast<object>().ToList(), "List of employees");
return View();
}
The full ExcelLib.cs content:
using ClosedXML.Excel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.IO;
using System.Linq;
using System.Reflection;
namespace ExcelGeneratingApp.Models
{
public class ExcelLib
{
// Default Constructor.
public ExcelLib() { }
// Method for adding Header and Title of the table containing List of object values.
public IXLWorksheet addHeader(XLWorkbook wb, List<Object> objs, string title, string fontFamily = "Sakkal Majalla", string color = "#3498DB")
{
// Sheet initialisation.
var ws = wb.Worksheets.Add("nomDeLaListe").SetTabColor(XLColor.UaBlue);
// font choice.
ws.Style.Font.FontName = fontFamily;
ws.Style.Font.SetFontSize(13);
ws.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Style.Alignment.WrapText = true;
Object obj = objs.FirstOrDefault();
// Add the model fields to the header of the excel file.
int totalOfFields = obj.GetType().GetProperties().Length; // number of fields in the object.
int numberOfFields = 0;
// Adding the title of table in excel file.
ws.Range(ws.Cell(4, 4), ws.Cell(4, totalOfFields + 3)).Merge().Value = title;
ws.Range(ws.Cell(4, 4), ws.Cell(4, totalOfFields + 3)).Merge().Style.Fill.BackgroundColor = XLColor.FromHtml(color); ;
ws.Range(ws.Cell(4, 4), ws.Cell(4, totalOfFields + 3)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Range(ws.Cell(4, 4), ws.Cell(4, totalOfFields + 3)).Style.Font.Bold = true;
ws.Range(ws.Cell(4, 4), ws.Cell(4, totalOfFields + 3)).Style.Font.FontColor = XLColor.WhiteSmoke;
ws.Range(ws.Cell(4, 4), ws.Cell(4, totalOfFields + 3)).Style.Font.FontSize = 18;
//Looping all propeties of the object.
foreach (var prop in obj.GetType().GetProperties())
{
var displayNameAttribute = prop.GetCustomAttributes(typeof(DisplayNameAttribute), false);
string displayName = prop.Name;
if (displayNameAttribute.Count() != 0)
{
displayName = (displayNameAttribute[0] as DisplayNameAttribute).DisplayName;
}
numberOfFields++;
ws.Cell(5, totalOfFields - numberOfFields + 4).Value = displayName;
ws.Cell(5, totalOfFields - numberOfFields + 4).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
ws.Cell(5, totalOfFields - numberOfFields + 4).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
ws.Cell(5, totalOfFields - numberOfFields + 4).Style.Border.RightBorder = XLBorderStyleValues.Thin;
ws.Cell(5, totalOfFields - numberOfFields + 4).Style.Border.TopBorder = XLBorderStyleValues.Thin;
ws.Column(totalOfFields - numberOfFields + 4).Width = 30;
ws.Column(totalOfFields - numberOfFields + 4).Style.Font.Bold = true;
}
ws.Range(ws.Cell(5, 4), ws.Cell(5, totalOfFields + 3)).SetAutoFilter();
return ws;
}
public IXLWorksheet addBody(IXLWorksheet ws, List<Object> objs)
{
int numberOfFields = 0;
int numberOfRecords = 0;
Object obj = objs.FirstOrDefault();
int totalOfFields = obj.GetType().GetProperties().Length;
string previousValue = "";
int indexOfPreviousValue = 0;
foreach (var item in objs.ToList())
{
numberOfFields = 0;
Type myType = item.GetType();
IList<PropertyInfo> props = new List<PropertyInfo>(myType.GetProperties());
foreach (PropertyInfo prop in props)
{
object propValue = prop.GetValue(item, null);
numberOfFields++;
ws.Cell(6 + numberOfRecords, totalOfFields - numberOfFields + 4).Value = propValue;
ws.Cell(6 + numberOfRecords, totalOfFields - numberOfFields + 4).Style.Font.Bold = true;
ws.Cell(6 + numberOfRecords, totalOfFields - numberOfFields + 4).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
ws.Cell(6 + numberOfRecords, totalOfFields - numberOfFields + 4).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
ws.Cell(6 + numberOfRecords, totalOfFields - numberOfFields + 4).Style.Border.RightBorder = XLBorderStyleValues.Thin;
ws.Cell(6 + numberOfRecords, totalOfFields - numberOfFields + 4).Style.Border.TopBorder = XLBorderStyleValues.Thin;
if (numberOfFields == 1 && numberOfRecords == 0)
{
previousValue = propValue.ToString();
}
else
{
if (numberOfFields == 1)
{
if (previousValue == propValue.ToString())
{
ws.Range(ws.Cell(6 + numberOfRecords - (1 + indexOfPreviousValue), totalOfFields - numberOfFields + 4), ws.Cell(6 + numberOfRecords, totalOfFields - numberOfFields + 4)).Merge().Value = propValue.ToString();
ws.Range(ws.Cell(6 + numberOfRecords - (1 + indexOfPreviousValue), totalOfFields - numberOfFields + 4), ws.Cell(6 + numberOfRecords, totalOfFields - numberOfFields + 4)).Merge().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Range(ws.Cell(6 + numberOfRecords - (1 + indexOfPreviousValue), totalOfFields - numberOfFields + 4), ws.Cell(6 + numberOfRecords, totalOfFields - numberOfFields + 4)).Merge().Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
indexOfPreviousValue++;
}
else
{
previousValue = propValue.ToString();
indexOfPreviousValue = 0;
}
}
}
}
numberOfRecords++;
}
return ws;
}
public void Generate(List<Object> objs, string title, string fontFamily = "Sakkal Majalla", string color = "#3498DB")
{
// Workbook creation.
using (XLWorkbook wb = new XLWorkbook())
{
var ws = addHeader(wb, objs, title);
ws = addBody(ws, objs);
wb.SaveAs("C://TestExcelGen.xlsx");
}
}
}
}
NB: You can download all project source code from my github page.
Conclusion
I think the procedure is very clear with snapshots. If you have found any mistake in concept, please do comment. Your comments will make me perfect in the future.
Thanks for reading.