Exporting data to Excel in C#

February 26, 2021

c#epplushow-to

What
Setting up the project
Define the method signature
Implementing the excel generation logic
Adding export view-models
Customizing the column header name
Ignore certain properties from being exported to Excel
Formatting the displayed date values
Styling the header row
Unit test excel generation logic

What

We’ll code along and see how we can achieve the following things.

Encapsulate Excel generation logic inside a class. To make the code legible, we’ll allow chaining the methods. The final consumption code will look like below.

var excelPackage = new ExcelBuilder();

var data = await excelPackage
    .SetWorkbookTitle("test")
    .SetDateFormat("dd-MM-YYYY")
    .AddWorksheet(studentsSheetArgs)
    .AddWorksheet(religionsSheetArgs)
    .GenerateExcelAsync();

Generate multiple sheets within an Excel workbook.
Automatically set column header names based on the object property name.
Customize the column header name.
Ignore certain properties from being populated in the generated excel.
Format the date display value.
Unit test excel generation logic.

You can find all the code related to this post in this Github Repository

Setting up the project

Create a folder to house the .NET Core solution

mkdir ExportToExcel
cd ExportToExcel
dotnet new sln

Optionally, initialize git repository and add .gitignore
```
git init
dotnet new gitignore
```
Create a class library and add it to the solution
```
dotnet new classlib -o ExportToExcel.Services
dotnet sln add ExportToExcel.Services
```
We are not going to run the application directly, chances are you already have a service layer in your main application, and the Excel generation logic would go there. Thus, I’ve created the project as class library instead.
Later we’ll add a xUnit unit-test project.
We’ll rely on EPPlus for the core Excel generation functionality. So, let’s add the package to ExportToExcel.Services project.
```
dotnet add ExportToExcel.Services package EPPlus
```

Define the method signature

Lets’s start by defining the interface that other parts of our code will consume.

// ExportToExcel/ExportToExcel.Services/IGenerateExcelService.cs

using System.Threading.Tasks;
using ExportToExcel.Services.Dtos;

namespace ExportToExcel.Services
{
    public interface IGenerateExcelService
    {
        IGenerateExcelService SetWorkbookTitle(string title);

        IGenerateExcelService AddWorksheet<ViewModelType>(WorksheetArgs<ViewModelType> worksheetArgs);

        IGenerateExcelService SetDateFormat(string dateFormat);

        Task<byte[]> GenerateExcelAsync();
    }
}

WorksheetArgs contains necessary data to generate a single sheet. ViewModelType is a generic parameter. Each item in the Records collection holds the data of an Excel row. This is similar to the entity-to-table mapping in any ORM framework.

// ExportToExcel/ExportToExcel.Services/Dtos/WorksheetArgs.cs

using System.Collections.Generic;

namespace ExportToExcel.Services.Dtos
{
    /// <summary>
    /// Contains arguments required to generate an Excel worksheet
    /// </summary>
    public class WorksheetArgs<ViewModelType>
    {
        public string Title { get; set; }

        public IEnumerable<ViewModelType> Records { get; set; }
    }
}

Implementing the excel generation logic

Let’s add GenerateExcelService that implements IGenerateExcelService.

// ExportToExcel/ExportToExcel.Services/Concrete/GenerateExcelService.cs

using System.Threading.Tasks;
using ExportToExcel.Services.Dtos;
using OfficeOpenXml;

namespace ExportToExcel.Services.Concrete
{
    public class GenerateExcelService : IGenerateExcelService
    {
        private readonly ExcelPackage _excelPackage;
        private string _dateFormat = "dd/MM/YYYY";

        public GenerateExcelService()
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            _excelPackage = new ExcelPackage();
        }

        public IGenerateExcelService SetWorkbookTitle(string title)
        {
            _excelPackage.Workbook.Properties.Title = title;

            return this;
        }

        public IGenerateExcelService AddWorksheet<ViewModelType>(WorksheetArgs<ViewModelType> worksheetArgs)
        {
            // TODDO

            return this;
        }

        public IGenerateExcelService SetDateFormat(string dateFormat)
        {
            _dateFormat = dateFormat;

            return this;
        }

        public async Task<byte[]> GenerateExcelAsync()
        {
            return await _excelPackage.GetAsByteArrayAsync();
        }

      }
  }

From version 5 of EPPlus, the team decided to require license for commercial applications.

ExcelPackage.LicenseContext = LicenseContext.NonCommercial; inside the constructor indicates that we're using for non-commercial purpose.

Since the rest of the code is self explanatory I’ll skip to the AddWorksheet method implementation.

The AddWorksheet method has the following content.

// ExportToExcel/ExportToExcel.Services/Concrete/GenerateExcelService.cs

using System.Collections.Generic;
using System.Threading.Tasks;
using ExportToExcel.Services.Dtos;
using OfficeOpenXml;
using OfficeOpenXml.Table;

namespace ExportToExcel.Services.Concrete
{
    public class GenerateExcelService : IGenerateExcelService
    {
        ...

        public IGenerateExcelService AddWorksheet<ViewModelType>(WorksheetArgs<ViewModelType> worksheetArgs)
        {
            var worksheet = _excelPackage.Workbook.Worksheets.Add(worksheetArgs.Title);

            LoadDataToWorksheet(worksheet, worksheetArgs.Records);

            worksheet.Cells.AutoFitColumns();
            worksheet.IgnoredErrors.Add(worksheet.Cells[$"A1:{worksheet.Dimension.End.Address}"]);

            return this;
        }

        ...

        private static void LoadDataToWorksheet<T>(ExcelWorksheet worksheet, IEnumerable<T> records)
        {
            worksheet.Cells["A1"].LoadFromCollection(records, true);
        }
      }
  }

In this method, we delegate the heavy lifting of Excel geneartion logic to the EPPlus library. The LoadDataToWorksheet method delegats its task to EPPlus’s LoadFromCollection method. The second parameter true indicates that we want to print the header row.

We could also manually create a DataTable instance and use the LoadFromDataTable method. Consider it if you want a custom LoadFromCollection implementation that offers more control.

You can learn more on available EPPlus API by glancing through the test cases.

EPPlus test cases

Before customizing the AddWorksheet method further, let’s add some classes defining the table structure.

Adding export view-models

Say we want to export the following details of some students, Id, Name, Date of Birth, Gender. We’ll start by defining those properties in a POCO.

// ExportToExcel/ExportToExcel.Services/ExportViewModels/StudentExportViewModel.cs

using System;

namespace ExportToExcel.Services.ExportViewModels
{
    public enum Gender
    {
        Male,
        Female
    }

    public class StudentExportViewModel
    {
        public string Id { get; set; }

        public string Name { get; set; }

        public DateTime DateOfBirth { get; set; }

        public Gender Gender { get; set; }
    }
}

To demonstrate multiple worksheet generation, I’ll also add ReligionExportViewModel class.

// ExportToExcel/ExportToExcel.Services/ExportViewModels/ReligionExportViewModel.cs

namespace ExportToExcel.Services.ExportViewModels
{
    public class ReligionExportViewModel
    {
        public string Name { get; set; }
    }
}

Customizing the column header name

LoadFromCollection method will automatically set the column name based on the property name. To set DateOfBirth column header name to Date of Birth we can annotate the property with DisplayName attribute. If you picked LoadFromDataTable approach, you'll need to manually set the column name by taking the attribute value using reflection.

// ExportToExcel/ExportToExcel.Services/ExportViewModels/StudentExportViewModel.cs

using System;
using System.ComponentModel;
using ExportToExcel.Services;

namespace ExportToExcel.Services.ExportViewModels
{
    ...

    public class StudentExportViewModel : IExportViewModel
    {
        ...

        [DisplayName("Date of Birth")]
        public DateTime DateOfBirth { get; set; }

        ...
    }
}

Ignore certain properties from being exported to Excel

At this point, the logic adds column header for all the public properties. Let’s see how we can exclude StudentExportViewModel.Id property.

Annotate StudentExportViewModel class with EpplusTable attribute and the Id property with the EpplusIgnore attribute.

// ExportToExcel/ExportToExcel.Services/ExportViewModels/StudentExportViewModel.cs

using System;
using OfficeOpenXml.Attributes;

namespace ExportToExcel.Services.ExportViewModels
{
    ...

    [EpplusTable]
    public class StudentExportViewModel
    {
        [EpplusIgnore]
        public string Id { get; set; }

        ...
    }
}

That’s all what we need to do. LoadFromDataCollection method will take care of the rest.

Formatting the displayed date values

Now we have a problem with DateTime properties. We’ll see some numbers in the exported Excel instead of the actual date. Let’s see how we can fix it.

One approach would be to directly set the formatted string in the view-model or define a read only property that returns the desired string representation of the date. While this will work, we can automate this task by using little bit of reflection.

// ExportToExcel/ExportToExcel.Services/Concrete/GenerateExcelService.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;
using ExportToExcel.Services.Dtos;
using OfficeOpenXml;
using OfficeOpenXml.Attributes;

namespace ExportToExcel.Services.Concrete
{
    public class GenerateExcelService : IGenerateExcelService
    {
        ...

        public IGenerateExcelService AddWorksheet<ViewModelType>(WorksheetArgs<ViewModelType> worksheetArgs)
        {
            ...

            LoadDataToWorksheet(worksheet, worksheetArgs.Records);
            FormatDateDisplayValue<ViewModelType>(worksheet);

            return this;
        }

        ...

        private void FormatDateDisplayValue<T>(ExcelWorksheet worksheet)
        {
            var properties = GetNotIgnoredProperties<T>();
            var dateColumnIndexes = new List<int>();

            for (var i = 0; i < properties.Length; i++)
            {
                var currentPropertyType = properties[i].PropertyType;
                if (currentPropertyType == typeof(DateTime) || currentPropertyType == typeof(DateTime?))
                {
                    dateColumnIndexes.Add(i + 1); // first column index starts in 1
                }
            }

            dateColumnIndexes.ForEach(columnIndex =>
            {
                worksheet.Column(columnIndex).Style.Numberformat.Format = _dateFormat;
            });
        }

        private static PropertyInfo[] GetNotIgnoredProperties<ViewModelType>()
        {
            return typeof(ViewModelType)
                .GetProperties(BindingFlags.Instance | BindingFlags.Public)
                .Where(p => !Attribute.IsDefined(p, typeof(EpplusTableColumnAttribute)) && !Attribute.IsDefined(p, typeof(EpplusIgnore)))
                .ToArray();
        }
      }
  }

Styling the header row

Let’s differentiate our header row by making the text bold and adding a fill.

// ExportToExcel/ExportToExcel.Services/Concrete/GenerateExcelService.cs

using System;
using System.Collections.Generic;
using System.Drawing;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;
using ExportToExcel.Services.Dtos;
using ExportToExcel.Services.Utils;
using OfficeOpenXml;
using OfficeOpenXml.Attributes;
using OfficeOpenXml.Style;

namespace ExportToExcel.Services.Concrete
{
    public class GenerateExcelService : IGenerateExcelService
    {
        ...

        public IGenerateExcelService AddWorksheet<ViewModelType>(WorksheetArgs<ViewModelType> worksheetArgs)
        {
            ...

            LoadDataToWorksheet(worksheet, worksheetArgs.Records);
            FormatDateDisplayValue<ViewModelType>(worksheet);

            var headerRange = GetHeaderRange(worksheet);
            StyleHeaderRow(worksheet, headerRange);
            worksheet.Cells[headerRange].AutoFilter = true;

            worksheet.Cells.AutoFitColumns();

            return this;
        }

        ...

        private static string GetHeaderRange(ExcelWorksheet worksheet)
        {
            var lastColumnAddress = $"{ExcelUtils.GetExcelColumnName(worksheet.Dimension.End.Column)}1";

            return $"A1:{lastColumnAddress}";
        }

        private static void StyleHeaderRow(ExcelWorksheet worksheet, string headerRange)
        {
            using (var rng = worksheet.Cells[headerRange])
            {
                rng.Style.Font.Bold = true;
                rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
                rng.Style.Fill.BackgroundColor.SetColor(Color.DarkGray);
            }
        }
    }
}

For this to work, we need to know the header address range. We can simply accept it as a user input in WorksheetArgs DTO, but, where is the fun in that. Let’s add a utility method to automate it 😉

// ExportToExcel/ExportToExcel.Services/Utils/ExcelUtils.cs

using System;

namespace ExportToExcel.Services.Utils
{
    public static class ExcelUtils
    {
        /// <summary>
        /// Works like below
        /// <code>
        /// 1 -> A
        /// 26 -> Z
        /// 28 -> AB
        /// 53 -> BA
        /// </code>
        /// </summary>
        /// <param name="columnNumber">first column starts at one</param>
        public static string GetExcelColumnName(int columnNumber)
        {
            var dividend = columnNumber;
            var columnName = string.Empty;

            while (dividend > 0)
            {
                var modulo = (dividend - 1) % 26;
                columnName = $"{Convert.ToChar(65 + modulo)}{columnName}";

                dividend = (dividend - modulo) / 26;
            }

            return columnName;
        }
    }
}

Unit test excel generation logic

Let’s create and add an xUnit test project to the solution

dotnet new xunit -o ExportToExcel.UnitTests
dotnet add ExportToExcel.UnitTests reference ExportToExcel.Services
dotnet sln add ExportToExcel.UnitTests

Let’s first assert if GetExcelColumnName behaves as expected

// ExportToExcel/ExportToExcel.UnitTests/ExcelUtilsTests.cs

using System.Collections.Generic;
using ExportToExcel.Services.Utils;
using Xunit;

namespace ExportToExcel.UnitTests
{
    public class ExcelUtilsTests
    {
        [Fact]
        public void GetExcelColumnName_Should_Behave_As_Expected()
        {
            #region Arrange

            var inputVsExpectedOutput = new Dictionary<int, string>
            {
                {1, "A"},
                {26, "Z"},
                {28, "AB"},
                {53, "BA"}
            };

            #endregion

            #region ActAndAssert

            foreach (var (input, expectedOutput) in inputVsExpectedOutput)
            {
                var output = ExcelUtils.GetExcelColumnName(input);

                Assert.Equal(output, expectedOutput);
            }

            #endregion
        }
    }
}

You can use the test-runner from your IDE or use dot-net-core-cli to run the tests.

dotnet test

Now let’s test excel generation logic.

// ExportToExcel/ExportToExcel.UnitTests/ExportToExcelServiceTests.cs

using System;
using System.Collections.Generic;
using System.IO;
using System.Threading.Tasks;
using ExportToExcel.Services.Concrete;
using ExportToExcel.Services.Dtos;
using ExportToExcel.Services.ExportViewModels;
using Xunit;

namespace ExportToExcel.UnitTests
{
    public class ExportToExcelServiceTests
    {
        [Fact]
        public async Task Export_Should_Succeed()
        {
            #region Arrange

            var students = new List<StudentExportViewModel>();
            var religions = new List<ReligionExportViewModel>();
            for (var i = 0; i < 5; i++)
            {
                students.Add(new StudentExportViewModel
                {
                    Id = Guid.NewGuid().ToString(),
                    Name = $"student-{i}",
                    DateOfBirth = DateTime.Today,
                    Gender = Gender.Male,
                });

                religions.Add(new ReligionExportViewModel
                {
                    Name = $"religion-{i}"
                });
            }

            var studentsSheetArgs = new WorksheetArgs<StudentExportViewModel>
            {
                Records = students,
                Title = "Students"
            };
            var religionsSheetArgs = new WorksheetArgs<ReligionExportViewModel>
            {
                Records = religions,
                Title = "Religions"
            };

            #endregion

            #region Act

            var excelService = new GenerateExcelService();

            var data = await excelService
                .SetWorkbookTitle("test")
                .SetDateFormat("dd-MM-YYYY")
                .AddWorksheet(studentsSheetArgs)
                .AddWorksheet(religionsSheetArgs)
                .GenerateExcelAsync();

            #endregion

            #region Assert

            Assert.NotEmpty(data);

            // await File.WriteAllBytesAsync("Should_Be_Able_To_Upload_And_Download_That_File.xlsx", data);

            #endregion
        }
    }
}

Uncomment the line to write the Excel data to a file. If the test succeeds, you should see the file inside the bin/Debug/netcoreapp3.1 directory of the xUnit project. Note that netcoreapp3.1 will change depending on the installed .NET Core SDK version.

await File.WriteAllBytesAsync("Should_Be_Able_To_Upload_And_Download_That_File.xlsx", data);

That’s all for today friend. Remember to relax and smile 😉. Also, feel free to share your thoughts in the comments.

Table of Contents