Export List<T> to Excel using ClosedXML & .NET

_folder-structure.txt

  ├── Program.cs                        ← Project startup & dependency injection
  │
  ├── controllers /
  │   └── ExportToExcelController.cs    ← API entry point
  
  ├── services /
  │   ├── IExportToExcelService.cs      ← Export Excel interface
  │   └── ExportToExcelService.cs       ← Export Excel concrete class
  │
  ├── models /
  │       ├── Car.cs                    ← Car class definition and dummy data
  │       ├── Game.cs                   ← Game class definition and dummy data  
  │       └── Gamer.cs                  ← Gamer class definition and dummy data
  │
  └── extensions /
      ├── IEnumerableExtensions.cs      ← Extension methods for List<T>
      └── ReportFormatExtensions        ← Extension method for Data Table

car.cs

namespace DotNet8Starter.DL.Models
{
	public enum FuelType
	{
		Petrol,
		Diesel,
		Electric,
		Hybrid
	}


	public class Car
	{
		public Guid Id { get; set; }
		public string Name { get; set; }
		public string Manufacturer { get; set; }
		public int YearProduced { get; set; }
		public string Color { get; set; }
		public FuelType FuelType { get; set; }
		public int HorsePower { get; set; }
		public int NumberOfDoors { get; set; }
		public bool AutomaticTransmission { get; set; }
		public double AverageFuelConsumption { get; set; }
		public int MaxSpeed { get; set; }
		public decimal Price { get; set; }

		public static List<Car> GetCars()
		{
			return
			[
				new Car
				{
					Id = Guid.NewGuid(),
					Name = "Model S Plaid",
					Manufacturer = "Tesla",
					YearProduced = 2023,
					Color = "Matte Black",
					FuelType = FuelType.Electric,
					HorsePower = 1020,
					NumberOfDoors = 4,
					AutomaticTransmission = true,
					AverageFuelConsumption = 0,
					MaxSpeed = 322,
					Price = 129999
				},

				new Car
				{
					Id = Guid.NewGuid(),
					Name = "Golf GTI",
					Manufacturer = "Volkswagen",
					YearProduced = 2021,
					Color = "White",
					FuelType = FuelType.Petrol,
					HorsePower = 245,
					NumberOfDoors = 5,
					AutomaticTransmission = true,
					AverageFuelConsumption = 7.1,
					MaxSpeed = 250,
					Price = 38999
				},

				new Car
				{
					Id = Guid.NewGuid(),
					Name = "Civic Type R",
					Manufacturer = "Honda",
					YearProduced = 2022,
					Color = "Blue",
					FuelType = FuelType.Petrol,
					HorsePower = 315,
					NumberOfDoors = 4,
					AutomaticTransmission = false,
					AverageFuelConsumption = 8.2,
					MaxSpeed = 275,
					Price = 52999
				},

				new Car
				{
					Id = Guid.NewGuid(),
					Name = "A6",
					Manufacturer = "Audi",
					YearProduced = 2020,
					Color = "Gray",
					FuelType = FuelType.Diesel,
					HorsePower = 204,
					NumberOfDoors = 4,
					AutomaticTransmission = true,
					AverageFuelConsumption = 5.9,
					MaxSpeed = 246,
					Price = 61999
				},

				new Car
				{
					Id = Guid.NewGuid(),
					Name = "Corolla Hybrid",
					Manufacturer = "Toyota",
					YearProduced = 2021,
					Color = "Red",
					FuelType = FuelType.Hybrid,
					HorsePower = 140,
					NumberOfDoors = 4,
					AutomaticTransmission = true,
					AverageFuelConsumption = 4.3,
					MaxSpeed = 180,
					Price = 29999
				},

				new Car
				{
					Id = Guid.NewGuid(),
					Name = "Mustang GT",
					Manufacturer = "Ford",
					YearProduced = 2023,
					Color = "Yellow",
					FuelType = FuelType.Petrol,
					HorsePower = 450,
					NumberOfDoors = 2,
					AutomaticTransmission = false,
					AverageFuelConsumption = 12.4,
					MaxSpeed = 290,
					Price = 74999
				},

				new Car
				{
					Id = Guid.NewGuid(),
					Name = "M3 Competition",
					Manufacturer = "BMW",
					YearProduced = 2024,
					Color = "Dark Green",
					FuelType = FuelType.Petrol,
					HorsePower = 510,
					NumberOfDoors = 4,
					AutomaticTransmission = true,
					AverageFuelConsumption = 10.1,
					MaxSpeed = 290,
					Price = 98999
				},

				new Car
				{
					Id = Guid.NewGuid(),
					Name = "EQS 450+",
					Manufacturer = "Mercedes-Benz",
					YearProduced = 2023,
					Color = "Silver",
					FuelType = FuelType.Electric,
					HorsePower = 329,
					NumberOfDoors = 4,
					AutomaticTransmission = true,
					AverageFuelConsumption = 0,
					MaxSpeed = 210,
					Price = 119999
				},

				new Car
				{
					Id = Guid.NewGuid(),
					Name = "911 Turbo S",
					Manufacturer = "Porsche",
					YearProduced = 2024,
					Color = "Orange",
					FuelType = FuelType.Petrol,
					HorsePower = 650,
					NumberOfDoors = 2,
					AutomaticTransmission = true,
					AverageFuelConsumption = 11.5,
					MaxSpeed = 330,
					Price = 239999
				},

				new Car
				{
					Id = Guid.NewGuid(),
					Name = "Octavia RS",
					Manufacturer = "Skoda",
					YearProduced = 2022,
					Color = "Black",
					FuelType = FuelType.Diesel,
					HorsePower = 200,
					NumberOfDoors = 5,
					AutomaticTransmission = true,
					AverageFuelConsumption = 5.6,
					MaxSpeed = 249,
					Price = 42999
				},

				new Car
				{
					Id = Guid.NewGuid(),
					Name = "508 GT",
					Manufacturer = "Peugeot",
					YearProduced = 2022,
					Color = "Pearl White",
					FuelType = FuelType.Diesel,
					HorsePower = 225,
					NumberOfDoors = 4,
					AutomaticTransmission = true,
					AverageFuelConsumption = 5.4,
					MaxSpeed = 250,
					Price = 47999
				},

				new Car
				{
					Id = Guid.NewGuid(),
					Name = "i30 N",
					Manufacturer = "Hyundai",
					YearProduced = 2023,
					Color = "Performance Blue",
					FuelType = FuelType.Petrol,
					HorsePower = 280,
					NumberOfDoors = 5,
					AutomaticTransmission = false,
					AverageFuelConsumption = 8.0,
					MaxSpeed = 250,
					Price = 38999
				},

				new Car
				{
					Id = Guid.NewGuid(),
					Name = "GT-R Premium",
					Manufacturer = "Nissan",
					YearProduced = 2021,
					Color = "Gun Metallic",
					FuelType = FuelType.Petrol,
					HorsePower = 565,
					NumberOfDoors = 2,
					AutomaticTransmission = true,
					AverageFuelConsumption = 11.8,
					MaxSpeed = 315,
					Price = 115999
				},

				new Car
				{
					Id = Guid.NewGuid(),
					Name = "Megane RS",
					Manufacturer = "Renault",
					YearProduced = 2022,
					Color = "Liquid Yellow",
					FuelType = FuelType.Petrol,
					HorsePower = 300,
					NumberOfDoors = 5,
					AutomaticTransmission = false,
					AverageFuelConsumption = 7.5,
					MaxSpeed = 260,
					Price = 41999
				},

				new Car
				{
					Id = Guid.NewGuid(),
					Name = "SF90 Stradale",
					Manufacturer = "Ferrari",
					YearProduced = 2024,
					Color = "Rosso Corsa",
					FuelType = FuelType.Hybrid,
					HorsePower = 1000,
					NumberOfDoors = 2,
					AutomaticTransmission = true,
					AverageFuelConsumption = 6.1,
					MaxSpeed = 340,
					Price = 625000
				}
			];
		}
	}
}

ExportToExcelController.cs

using DotNet8Starter.BL.ServiceInterfaces;
using DotNet8Starter.DL.Models;
using Microsoft.AspNetCore.Mvc;

namespace DotNet8Starter.Controllers
{
	[Route("api/[controller]")]
	[ApiController]
	public class ExportToExcelController(IExportToExcelService exportToExcelService) : ControllerBase
	{
		private readonly List<Game> _games = Game.GetGames();
		private readonly List<Gamer> _gamersList = Gamer.GetGamers();
		private readonly List<Car> _cars = Car.GetCars();

		private const string EXCEL_MIME_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

		[HttpGet("CarsExcel")]
		public IActionResult GetCarsExcel()
		{
			try
			{
				var reportFormat = ReportFormat.ToReport("cars", _cars);
				var excel = exportToExcelService.GetExcel(reportFormat);

				return File(excel, EXCEL_MIME_TYPE, reportFormat.Name);
			}
			catch (Exception ex)
			{
				return StatusCode(500, ex.Message);
			}
		}

		[HttpGet("GamersListExcel")]
		public IActionResult GetGamersExcel()
		{
			try
			{
				var reportFormat = ReportFormat.ToReport("gamers-list", _gamersList);
				var excel = exportToExcelService.GetExcel(reportFormat);

				return File(excel, EXCEL_MIME_TYPE, reportFormat.Name);
			} 
			catch (Exception ex)
			{
				return StatusCode(500, ex.Message);
			}
		}

		[HttpGet("GamesExcel")]
		public IActionResult GetGamesExcel()
		{
			try
			{
				var reportFormat = ReportFormat.ToReport("games", _games);
				var excel = exportToExcelService.GetExcel(reportFormat);

				return File(excel, EXCEL_MIME_TYPE, reportFormat.Name);
			}
			catch (Exception ex)
			{
				return StatusCode(500, ex.Message);
			}
		}
	}
}

ExportToExcelService.cs

using ClosedXML.Excel;
using DotNet8Starter.BL.ServiceInterfaces;
using DotNet8Starter.DL.Extensions;
using DotNet8Starter.DL.Models;
using System.Data;

namespace DotNet8Starter.BL.Services
{
	public class ExportToExcelService : IExportToExcelService
	{
		public byte[] GetExcel(ReportFormat report)
		{
			DataTable dataTable = report.ToDataTable();

			using var xlWb = new XLWorkbook();
			xlWb.Worksheets.Add(dataTable, report.Name);

			using var stream = new MemoryStream();
			xlWb.SaveAs(stream);

			return stream.ToArray();
		}

	}
}

Game.cs

namespace DotNet8Starter.DL.Models
{
	public class Game
	{
		public string Title { get; set; }
		public string Publisher { get; set; }
		public Int16 ReleaseYear { get; set; }

		public static List<Game> GetGames()
		{
			return
			[
				new Game()
				{
					Title = "Call of Duty 4",
					Publisher = "Activision",
					ReleaseYear = 2007
				},
				new Game()
				{
					Title = "Counter Strike 1.6",
					Publisher = "Valve",
					ReleaseYear = 2003
				},
				new Game()
				{
					Title = "Escape From Tarkov",
					Publisher = "Battlestate",
					ReleaseYear = 2025
				},
			];
		}
	}
}

Gamer.cs

namespace DotNet8Starter.DL.Models
{
	public enum SkillRank
	{
		Noob,
		Pro
	}

	public enum Playstyle
	{
		Tactical,
		Rush,
		Ninja,
		Sniper,
		GoingLoud
	}

	public class Gamer
	{
		public int Id { get; set; }
		public string Username { get; set; }
		public Playstyle Playstyle { get; set; }
		public SkillRank Rank { get; set; }

		public static List<Gamer> GetGamers()
		{
			return
			[
				new Gamer()
				{
					Id = 1,
					Username = "Mirzly",
					Playstyle = Playstyle.Sniper,
					Rank = SkillRank.Pro
				},
				new Gamer()
				{
					Id = 2,
					Username = "ZoP3",
					Playstyle = Playstyle.Tactical,
					Rank = SkillRank.Pro
				},
				new Gamer()
				{
					Id = 3,
					Username = "yoSe",
					Playstyle = Playstyle.GoingLoud,
					Rank = SkillRank.Noob
				},
				new Gamer()
				{
					Id = 4,
					Username = "Tazzon",
					Playstyle = Playstyle.Ninja,
					Rank = SkillRank.Pro
				},
				new Gamer()
				{
					Id = 5,
					Username = "F4rE",
					Playstyle = Playstyle.Rush,
					Rank = SkillRank.Pro
				},
			];
		}

	}
}

IEnumerableExtensions.cs

using System.Text.Json;
using System.Text.Json.Serialization;

namespace DotNet8Starter.DL.Extensions
{
	public static class IEnumerableExtensions
	{
		public static List<string> GetObjectProperies<T>(this IEnumerable<T> _)
		{
			return typeof(T)
				.GetProperties()
				.Select(p => p.Name)
				.ToList();
		}

		public static List<JsonElement> ToJsonelementList<T>(this IEnumerable<T> enumerable)
		{
			var options = new JsonSerializerOptions
			{
				Converters = { new JsonStringEnumConverter() }
			};

			return enumerable
				.Select(x => JsonSerializer.SerializeToElement(x, options))
				.ToList();
		}
	}
}

IExportToExcelService.cs

using DotNet8Starter.DL.Models;

namespace DotNet8Starter.BL.ServiceInterfaces
{
	public interface IExportToExcelService
	{
		byte[] GetExcel(ReportFormat reportFormat);
	}
}

Program.cs

var builder = WebApplication.CreateBuilder(args);

// Dependency Injection
builder.Services.AddScoped<IExportToExcelService, ExportToExcelService>();

var app = builder.Build();

app.UseSwaggerExtension();

app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();

app.Run();

ReportFormat.cs

using DotNet8Starter.DL.Extensions;
using System.Text.Json;

namespace DotNet8Starter.DL.Models
{
	public class ReportFormat
	{
		public string Name { get; set; }
		public List<string> Columns { get; set; }
		public List<JsonElement> Rows { get; set; }

		public static ReportFormat ToReport<T>(string reportName, IEnumerable<T> list)
		{
			List<string> columns = list.GetObjectProperies();
			List<JsonElement> rows = list.ToJsonelementList();
			string name = $"{reportName}.xlsx";

			return new ReportFormat
			{
				Name = name,
				Columns = columns,
				Rows = rows
			};
		}
	}
}

ReportFormatExtensions.cs

using DotNet8Starter.DL.Models;
using System.Data;

namespace DotNet8Starter.DL.Extensions
{
	public static class ReportFormatExtensions
	{
		public static DataTable ToDataTable(this ReportFormat report)
		{
			var table = new DataTable(report.Name);

			// Add columns in the exact order provided
			foreach (var col in report.Columns)
			{
				table.Columns.Add(col, typeof(string));
			}

			// Add rows
			foreach (var json in report.Rows)
			{
				var row = table.NewRow();

				foreach (var col in report.Columns)
				{
					if (json.TryGetProperty(col, out var value))
						row[col] = value.ToString();
					else
						row[col] = DBNull.Value;
				}

				table.Rows.Add(row);
			}

			return table;
		}
	}
}