This article will teach you how to export any list into Excel in C# using the ClosedXML library.
Steps to complete
- Create the data model with dummy data that we'll export into Excel.
- Create
ExportExcelinterface methods that accept any type of List (usingIEnumerable<T>) and a Dictionary List and export a byte array. - Create extension methods and convert the provided data into rows and columns (using
DataTable). - Create a service class that implements the interface methods and export the data table into a Memory Stream (byte array) using ClosedXML.
- Create one API endpoint that exports data in memory into Excel. Create another endpoint that exports incoming (custom) request data into Excel.
- Wire up dependencies.
Project structure
├── 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>
1️⃣ Data model
I've created the dummy data model to demonstrate the dynamic implementation.
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() { ... }
}
The model has a static method GetCars() that returns hardcoded data that will be exported to Excel. You can find full data in the example Gist.
2️⃣ Create ExportExcel Service Interface
This is a very brief contract that takes the name of an Excel file and a generic list (e.g., Cars) and returns an ExcelResponse.
The second method works the same for the most part, but also allows for processing incoming data.
public interface IExportToExcelService
{
ExcelResponse GetExcelFromEnumerable<T>(string fileName, IEnumerable<T> genericList);
ExcelResponse GetExcelFromDictionaryList(string fileName, List<Dictionary<string, object>> dictionaryList);
}
ExcelResponse is a reusable model that contains the file name and byte data.
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
};
}
}
The ExportExcelRequest model (used in the Controller) accepts any data to export to Excel. This will be used only on the POST endpoint.
public class ExportExcelRequest
{
public string FileName { get; set; }
public List<Dictionary<string, object>> Data { get; set; }
}
3️⃣ Converting data into DataTables
The DataTable class represents a single in-memory table organized into rows and columns. You can learn more about DataTables here.
I like to create a separate extension methods class for all mappings.
public static class IEnumerableExtensions
{
public static DataTable ToDataTable<T>(this IEnumerable<T> genericList, string fileName) {}
public static DataTable ToDataTable(this List<Dictionary<string, object>> dictionaryList, string fileName) {}
}
IEnumerable to DataTable
This method is useful whenever you have a collection and want to extract its data and map it into rows and columns.
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;
}
List of Dictionary to DataTable
This method is useful whenever you expose an endpoint that accepts a collection of data of unknown type and wish to format it into rows and columns.
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;
}
4️⃣ Create ExportExcel Service
Now, let's create the service and implement the interface.
Install ClosedXML from NuGet
Be sure to install the ClosedXML package which is needed to convert the DataTable into Excel.
Write the service
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;
}
}
5️⃣ Create a new Controller
The API controller will have two routes:
- GET — to export data from memory
- POST — to export incoming data.
[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);
}
}
}
The byte array will be returned as an Excel file via the File class in the Controller.
6️⃣ Dependency injection
Don't forget to inject the dependencies in the Program.cs file:
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();
Demo
In-memory dummy data
Request:
Excel file:
Incoming custom data
Request body:
{
"fileName": "Games",
"data": [
{
"Title": "Call of Duty 4",
"Publisher": "Activision",
"ReleaseYear": 2007,
"Genre": "FirstPersonShooter",
"Setting": "ModernDay"
},
{
"Title": "Splinter Cell",
"Publisher": "Ubisoft",
"ReleaseYear": 2002,
"Genre": "StealthAction",
"Setting": "ModernDay"
},
{
"Title": "F1 25",
"Publisher": "Electronic Arts",
"ReleaseYear": 2025,
"Genre": "Racing",
"Setting": "ModernDay"
}
]
}
Response:
That's it.
Be sure to download the whole code from the provided Gist.
See you in the next one 👋
Top comments (0)