How to build a reusable Excel export service in ASP.NET Core

_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 & dummy data
  │       ├── ExcelResponse.cs          ← Wrapper class for excel file name and data
  │       └── ExportExcelRequest.cs     ← Request class for that accepts any kind of list that will be exported
  │
  └── extensions /
      └── IEnumerableExtensions.cs      ← Extension methods for List<T> and List<Dictionary>

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
				}
			];
		}
	}
}

ExcelResponse.cs

namespace DotNet8Starter.DL.Models
{
	public class ExcelResponse
	{
		public string Name { get; set; }
		public byte[] Data { get; set; }

		public static ExcelResponse ToResponse(string name, byte[] data)
		{
			return new ExcelResponse()
			{
				Name = $"{name}.xlsx",
				Data = data
			};
		}
	}
}

ExportExcelRequest.cs

namespace DotNet8Starter.DL.Models
{
	public class ExportExcelRequest
	{
		public string FileName { get; set; }
		public List<Dictionary<string, object>> Data { get; set; }
	}
}

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<Car> _cars = Car.GetCars();
		private const string EXCEL_MIME_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";


		/// <summary>
		/// Demonstrates how to export list in memory (from DB query, HTTP response, etc.) into Excel.
		/// </summary>
		/// <param name="fileName">Name of the file to be exported.</param>
		/// <returns>File</returns>
		[HttpGet("DataInMemory/{fileName}")]
		public IActionResult ExportExcelFromDataInMemory([FromRoute] string fileName = "cars")
		{
			try
			{
				var excel = exportToExcelService.GetExcelFromEnumerable(fileName, _cars);
				return File(excel.Data, EXCEL_MIME_TYPE, excel.Name);
			}
			catch (Exception ex)
			{
				return StatusCode(500, ex.Message);
			}
		}

		/// <summary>
		/// Demonstrates how to export custom data into Excel.
		/// </summary>
		/// <param name="request">Name of the file and data to be exported.</param>
		/// <returns>File</returns>
		[HttpPost("CustomData")]
		public IActionResult ExportExcelFromCustomData([FromBody] ExportExcelRequest request)
		{
			try
			{
				var report = exportToExcelService.GetExcelFromDictionaryList(request.FileName, request.Data);
				return File(report.Data, EXCEL_MIME_TYPE, report.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 ExcelResponse GetExcelFromEnumerable<T>(string fileName, IEnumerable<T> genericList)
		{
			// Mapping List<T> into the table using the extension method
			DataTable dataTable = genericList.ToDataTable(fileName);

			// Converting DataTable into Excel
			var dataStream = CreateMemoryStream(fileName, dataTable);

			return ExcelResponse.ToResponse(fileName, dataStream.ToArray());
		}

		public ExcelResponse GetExcelFromDictionaryList(string fileName, List<Dictionary<string, object>> dictionaryList)
		{
			// Mapping List<Dictionary> into the table using the extension method
			DataTable dataTable = dictionaryList.ToDataTable(fileName);

			// Converting DataTable into Excel
			var dataStream = CreateMemoryStream(fileName, dataTable);

			return ExcelResponse.ToResponse(fileName, dataStream.ToArray());
		}

		/// <summary>
		/// Converts a DataTable into an Excel workbook stored inside a MemoryStream.
		/// </summary>
		/// <param name="worksheetName">Name of the sheet inside the Excel file.</param>
		/// <param name="dataTable">Tabular data to be written into the Excel sheet.</param>
		/// <returns>MemoryStream containing the serialized .xlsx file.</returns>
		private static MemoryStream CreateMemoryStream(string worksheetName, DataTable dataTable)
		{
			var stream = new MemoryStream();

			// Create in-memory Excel workbook
			using var xlWb = new XLWorkbook();

			// ClosedXML adds DataTable into the workbook
			xlWb.Worksheets.Add(dataTable, worksheetName);

			// Serialize the workbook into the MemoryStream (writes XLSX bytes)
			xlWb.SaveAs(stream);

			// Reset position so the caller reads from the beginning
			stream.Position = 0;

			return stream;
		}
	}
}

IEnumerableExtensions.cs

using System.Data;

namespace DotNet8Starter.DL.Extensions
{
	public static class IEnumerableExtensions
	{
		public static DataTable ToDataTable<T>(this IEnumerable<T> genericList, string fileName)
		{
			var table = new DataTable(fileName);

			// # Column headers
			// Extract property names from the dynamic list (using Reflection)
			var props = typeof(T).GetProperties();

			// Loop through the list of props and insert the name of each as DataTable column
			foreach (var p in props)
			{
				table.Columns.Add(p.Name, typeof(string));
			}

			// # Rows data
			foreach (var item in genericList)
			{
				// Create a new DataRow for the DataTable
				var row = table.NewRow();
				foreach (var p in props)
				{
					// Retrieve the value from the list (for each property) and add it to the row
					var value = p.GetValue(item);
					row[p.Name] = value?.ToString() ?? string.Empty;
				}
				table.Rows.Add(row);
			}

			return table;
		}

		public static DataTable ToDataTable(this List<Dictionary<string, object>> dictionaryList, string fileName)
		{
			var table = new DataTable(fileName);

			// # Columns
			// Read dictionary keys
			if (dictionaryList.Count > 0)
			{
				foreach (var col in dictionaryList[0].Keys)
				{
					// Insert each key as column to data table
					table.Columns.Add(col, typeof(string));
				}
			}

			// # Rows data
			foreach (var row in dictionaryList)
			{
				// Create a new DataRow for the DataTable
				var dtRow = table.NewRow();

				foreach (var col in row.Keys)
				{
					// Retrieve the value for each key and add it to the row (belonging to that key)
					dtRow[col] = row[col]?.ToString() ?? string.Empty;
				}

				table.Rows.Add(dtRow);
			}

			return table;
		}
	}
}

IExportToExcelService.cs

using DotNet8Starter.DL.Models;

namespace DotNet8Starter.BL.ServiceInterfaces
{
	public interface IExportToExcelService
	{
		ExcelResponse GetExcelFromEnumerable<T>(string fileName, IEnumerable<T> genericList);

		ExcelResponse GetExcelFromDictionaryList(string fileName, List<Dictionary<string, object>> dictionaryList);
	}
}

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();