--- title: "How to Excel in .Net" url: "https://www.bekk.christmas/post/2024/12/how-to-excel-in-net" authors: ["Miina Lervik"] published: "2024-12-12" updated: "2024-12-18T13:54:17Z" language: "en-US" type: "article" tags: [".NET"] keywords: ["dotnet", ".Net", "Excel", "reflection", "attributes", "generic", "EPPlus"] --- # How to Excel in .Net A step by step guide to how to create one centralized Excel generator in .Net ## In short A single, generic Excel generator in .NET can create many kinds of spreadsheets by using EPPlus together with custom attributes and reflection. The data model controls column names, widths, ordering, and formatting, while the generator stays centralized and reusable, including support for nullable values and date formats. - **For:** .NET developers who generate Excel files - **Good to know beforehand:** Basic C# and reflection knowledge _AI-generated_ If your project involves generating many Excel documents, creating one generic Excel generator can save you time and effort. Instead of building custom generators for each scenario, a single, flexible generator can handle various object lists. In this article we’ll explore how to achieve this using EPPlus, a library for managing Excel spreadsheets. We will use custom attributes to control how each cell in the spreadsheet is handled. This approach lets you manage formatting directly from your data objects, keeping the Excel generator free from implementation details. ## Step 1 - Create a customizable Attribute To control how each property is displayed in the generated Excel sheet, we’ll create a custom attribute. This attribute will allow us to specify details like the name, width, and order of columns for each property. Here is the `ExcelColumnAttribute`: ```csharp [AttributeUsage(AttributeTargets.Property)] public class ExcelColumnAttribute : Attribute { public ExcelColumnAttribute( string name = null!, // make it optional int width = -1, // make it optional [CallerLineNumber] int order = 0 ) { Name = name; Width = width >= 0 ? width : null; Order = order; } public int Order { get; } public string? Name { get; set; } public int? Width { get; set; } } ``` This attribute includes: - Name: Optional column name for the spreadsheet. - Width: Optional column width. - Order: Automatically captures the property’s line number for ordering through `[CallerLineNumber]`, keeping column layout consistent with class definition. You can extend this further to include formatting options like text color or data formats. ### Example of usage Here’s how you can apply the `ExcelColumnAttribute` to a class: ```csharp public class UserModel { [ExcelColumn("User name", width: 20)] public string Name { get; set; } [ExcelColumn("Phone number", width: 10)] public string PhoneNumber { get; set; } [ExcelColumn(width: 20)] public string Email { get; set; } } ``` This setup lets the generator remain generic while giving you control over the spreadsheet layout directly from the data model. ## Step 2 - Implementing the generator Now, lets make the Excel generator itself. We’ll use the **EPPlus** library to generate Excel files. To include this library in your project, add the NuGet package using the command: ```bash dotnet add package EPPlus ``` Refer to the [EPPlus documentation](https://www.epplussoftware.com/en/Developers/) for detailed usage and licensing information. Please note: EPPlus requires a paid license for commercial projects. Next, create the `ExcelGenerator` class with one method, `CreateExcelDocument`, which generates an Excel sheet from a generic list of objects. Here is the starting point: ```csharp public class ExcelGenerator { public byte[] CreateExcelDocument(List data, string sheetName) { ExcelPackage.LicenseContext = LicenseContext.NonCommercial; using var package = new ExcelPackage(); // create a new spreadsheet with a specified name var worksheet = package.Workbook.Worksheets.Add(sheetName); // ... the rest of the code will come here } } ``` ### Using reflection to define columns and rows To dynamically create rows and columns, we use [reflection](https://learn.microsoft.com/en-us/dotnet/fundamentals/reflection/reflection) to extract properties from the objects in the list. Only properties annotated with the `ExcelColumnAttribute` will be included. The attributes allow us to specify details such as column names, widths, and order. Here’s how we retrieve and process these properties using reflection: ```csharp var properties = typeof(T).GetProperties() .Where(t => t.GetCustomAttribute() != null) .Select(property => new { Type = property, Metadata = property .GetCustomAttribute()! }) .OrderBy(i => i.Metadata.Order).ToList(); ``` ### Populate the Excel sheet With the property metadata ready, we can now create headers, set column properties (e.g., width, format), and populate rows with data. ```csharp var columnIndex = 1; // loop through each property (e.g Name, or Email) foreach (var property in properties) { // Add the header. var rowIndex = 1; var cell = worksheet.Cells[rowIndex, columnIndex]; // Use the name defined in the attribute or default to the name of the attribute cell.Value = property.Metadata.Name ?? property.Type.Name; // Make the font bold for the header cell.Style.Font.Bold = true; // Set column width if specified in the attribute if (property.Metadata.Width.HasValue) { worksheet .Column(columnIndex) .Width = property.Metadata.Width.Value; } // Set column format as text if the property is a string // (this is convenient for number strings like phone numbers) if (property.Type.PropertyType == typeof(string)) { worksheet .Column(columnIndex) .Style .Numberformat .Format = "@"; } // ... You can define other formats here // Add the rows by looping through the list of data objects. // "row" is here one object like UserModel foreach (var row in data) { // Start by moving to the next row rowIndex++; cell = worksheet.Cells[rowIndex, columnIndex]; // Sets the current cell to the value stored in the current propery // e.g Name, where the value might be "Tom" cell.Value = property.Type.GetValue(row); } columnIndex++; } ``` ### Finalizing the Excel file Lastly, we ensure the headers remain visible by freezing the first row. We then return the Excel file as a byte array: ```csharp // Freeze the first row to keep headers visible when scrolling worksheet.View.FreezePanes(2, 1); // Convert the Excel package to a byte array and return it return package.GetAsByteArray(); ``` ## Extra - Handeling formats and nullable types When working with certain data types, like dates or numbers, it is important to define appropriate formats for clear presentation in Excel. Additionally, nullable types, such as `DateTime?`, can be challenging since they may not always have a value. ### Identifying underlying types Nullable types (e.g., `DateTime?`) need special attention. We can use reflection to determine the underlying type: ```csharp var underlyingType = property.Type.PropertyType.IsGenericType ? Nullable.GetUnderlyingType(property.Type.PropertyType) : property.Type.PropertyType; ``` Here, if the property is a nullable type the `Nullable.GetUnderlyingType` retrieves the base type (e.g., `DateTime` from `DateTime?`). For non-nullable types, we directly use the `PropertyType` as is. ### Applying date formats If the property type is a date-like type (`DateTime`, `DateTimeOffset`, or `DateOnly`), we define a standard format for Excel: ```csharp if (underlyingType == typeof(DateTime) || underlyingType == typeof(DateTimeOffset) || underlyingType == typeof(DateOnly) ) { worksheet .Column(columnIndex) .Style .Numberformat .Format = "yyyy-mm-dd"; } ``` This ensures consistent formatting and improves readability in the Excel sheet. And there you have it! With a single, flexible Excel generator powered by EPPlus, you can save time, reduce repetitive code, and handle all your Excel file needs in one place. By using custom attributes and reflection, the generator stays generic while giving you complete control over how your data is presented. Whether it’s for simple reports or complex spreadsheets, this approach has you covered. Happy coding, and may your Excel files be ever clean and organized! ## Relevant resources recommended by the author - [EPPlus Documentation](https://www.epplussoftware.com/): Documentation for EPPlus - [Reflection in .Net](https://learn.microsoft.com/en-us/dotnet/fundamentals/reflection/reflection)