Generate Excel Report using Closed XML in .NET

What we gonna do?

Reports are essential for tracking key performance indicators, analyzing data, and making informed decisions. Common formats include PDF, Word, PowerPoint, and Excel. Excel is widely preferred due to its availability, ease of use, data visualization options, and powerful data manipulation capabilities.

10 years ago, Years ago, Crystal Reports was commonly used in .NET applications for generating back-office reports. However, designing reports was complex, requiring proficiency in Crystal Reports. Alternatives often relied on Microsoft Office/Interop, necessitating Office installation on target servers.

ClosedXML.Report is a modern, open-source .NET library built on top of ClosedXML. It enables developers to create, read, and manipulate Excel documents in the XML-based Office Open XML format. The library simplifies working with Excel files programmatically, allowing report generation, data visualizations, and other Excel-based solutions without requiring Microsoft Excel installation on the target system. This makes it ideal for automating Excel-related tasks and integrating Excel functionality into custom applications.

In this article, we'll explore how to generate Excel reports using ClosedXML.Report in .NET.

Why we gonna do?

Closed XML is an open-source project and has a large community of developers and users. Its repository is maintained on GitHub and is actively updated by the development team.

We can consider using Closed XML Report for the following options and benefits,

It is open source and it is built on top of ClosedXML
No need to install Microsoft Excel or Interop in target server
Integrate Excel into custom applications
Save development time
Improve data processing performance
It is compatible with latest .NET
Get all the benefits of excel

How we gonna do?

Generating Simple Excel Report

Sometimes, we have a list of records that needs to be directly exported to Excel. With ClosedXML.Report, this is a simple and easy process, which can be accomplished in four steps.

Install ClosedXML.Report Nuget Package

Convert List / IEnumerable data to DataTable


public static DataTable ToDataTable<T>(this IEnumerable<T> data, string name)
{
    PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
    DataTable table = new(name);

    foreach (PropertyDescriptor prop in properties)
        table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);

    foreach (T item in data)
    {
        DataRow row = table.NewRow();

        foreach (PropertyDescriptor prop in properties)
            row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;

        table.Rows.Add(row);
    }

    return table;
}

Add DataTable to Excel WorkSheet


var data = new TableOfContents().Contents;
var table = data.ToDataTable("contents");

using var wb = new XLWorkbook();
wb.Worksheets.Add(table).ColumnsUsed().AdjustToContents();

Save it to excel file or MemoryStream and get bytes[] from workbook and send to browser for download


// save to file
wb.SaveAs("report.xlsx");

// or get bytes to return excel file from web api
using var stream = new MemoryStream();
wb.SaveAs(stream);
return stream.ToArray();

@using ClosedXML.Excel
@using Microsoft.JSInterop

@implements IAsyncDisposable

<button @onclick="SimpleExcel">Download Simple Excel</button>

@code 
{
    private IJSObjectReference? module;
    private string? base64String;

    [Inject] private TableOfContents TableOfContents { get; set; } = default!;
    [Inject] private IJSRuntime JSRuntime { get; set; } = default!;

    protected override void OnInitialized()
    {
        var data = TableOfContents.AllContents;
        var table = data.ToDataTable("contents");

        using var wb = new XLWorkbook();
        wb.Worksheets.Add(table).ColumnsUsed().AdjustToContents();
        using var stream = new MemoryStream();
        wb.SaveAs(stream);

        var bytes = stream.ToArray();
        base64String = Convert.ToBase64String(bytes);
    }

    protected override async Task OnAfterRenderAsync(bool firstRender)
    {
        if (firstRender)
        {
            module = await JSRuntime.InvokeAsync<IJSObjectReference>("import", "./excel-download.js");
        }
    }

    private async Task SimpleExcel()
    {
        if (module is not null)
        {
            await module.InvokeVoidAsync("saveAsFile", "TableOfContents.xlsx", base64String);
        }
    }

    async ValueTask IAsyncDisposable.DisposeAsync()
    {
        if (module is not null)
        {
            await module.DisposeAsync();
        }
    }
}

internal static class Utilities
{
    public static DataTable ToDataTable<T>(this IEnumerable<T> data, string name)
    {
        PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
        DataTable table = new(name);

        foreach (PropertyDescriptor prop in properties)
            table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);

        foreach (T item in data)
        {
            DataRow row = table.NewRow();

            foreach (PropertyDescriptor prop in properties)
                row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;

            table.Rows.Add(row);
        }

        return table;
    }
}

Demo Space

Generating Complex Excel Report

In an enterprise, it is common to have complex data sets that need to be generated as Excel files. The data may include nested lists of data, such as one-to-many relationships, and this can be displayed in Excel as nested tables. This is also possible using ClosedXML.Report, and can be achieved in five steps.

Install ClosedXML.Report Nuget Package
Design a report Template. This step involves more work. We need to design a excel template to be used and add it in our project directory and enable copy to output on build. This template used in next step. We need to follow simple rules when designing the template.
1. The first column A needs to be left empty in Excel as it is used by ClosedXML for internal processing.
2. We need to use {{PropertyName}} placeholder to let Closed XML know what data needs to be replaced in the excel cells.
  
  The placeholder can contain any valid C# expression like {{GeneratedOn.ToString("dd-MM-yyyy")}}. You can either format the data in code itself or use excel to do formatting or apply any styles in excel to cell while designing template. You can also place images. This can be useful when we generate Confidential report and add confidential classification image to report directly in excel.
3. While building a document, ClosedXML.Report finds all named ranges and determines data sources by their name. Range name should coincide with the name of the variable serving a data source for this range. For nested tables, the range name is built using an underscore (_). E.g., to output values from Data[].AuthorChannels[].ArticleDetails[] the range name must be Data_AuthorChannels_ArticleDetails.
  
  Also we need to make sure that each data range must have minimum of one column and two rows (one row for data and an empty row for CLosedXML to do processing). That said, let's see some ranges in action.

Load the template and add data variable to template


var template = new XLTemplate(@"ILoveDotNetContentTemplate.xlsx");
template.AddVariable(data);

Generate the report using template


template.Generate();

Save it to excel file or MemoryStream and get bytes[] from workbook and send to browser for download


// save to file
template.SaveAs("report.xlsx");

// or get bytes to return excel file from web api
using var stream = new MemoryStream();
template.WorkBook.SaveAs(stream);
return stream.ToArray();


internal class Program
{
    private static void Main(string[] args)
    {
        Console.WriteLine("I Love .NET - https://ilovedotnet.org");

        var data = new TableOfContents().Contents;

        var complexdata = new
        {
            PlatformName = "I Love .NET",
            ReportName = "Contet Report",
            CreatedBy = "Abdul Rahman",
            GeneratedOn = DateTime.Now,
            Data = data.GroupBy(content => content.Author)
                       .Select(x => new
                       {
                           AuthorName = x.Key,
                           TotalArticles = x.Count(),
                           AuthorChannels = x.GroupBy(y => y.Type)
                                              .Select(z => new
                                              {
                                                  ChannelName = z.Key,
                                                  TotalArticles = z.Count(),
                                                  ArticleDetails = z.Select(c => new 
                                                  {
                                                      ArticleName = c.Title,
                                                      CreatedOn = c.CreatedOn,
                                                      Url = c.ContentUrl
                                                  }).ToList()
                                              })
                                              .ToList()
                       }).ToList()
        };

        var template = new XLTemplate(@"ILoveDotNetContentTemplate.xlsx");

        template.AddVariable(complexdata);
        template.Generate();
        template.Workbook.Worksheets
            .Worksheet(template.Workbook.Worksheets.First().Name)
            .ColumnsUsed()
            .AdjustToContents();

        template.SaveAs("report.xlsx");

        //Show report
        Process.Start(new ProcessStartInfo("report.xlsx") { UseShellExecute = true });

        Console.ReadLine();
    }
}

Demo Space

More Information

Additional information can be found in the official documentation, accessible via the official documentation. They support various types of reports and provide sample templates on their website.

Summary

In this article we learn't how to generate simple and complex excel report using ClosedXML.Report. Closed XML is a .NET library that enables developers to create, read, and manipulate Microsoft Excel files without using Microsoft Excel. It provides an API that simplifies the process of working with Excel files, making it easier to automate tasks, integrate Excel into custom applications, and improve data processing performance. Closed XML is an open-source project with an active community of developers and users, and is regularly updated. By using Closed XML, developers can efficiently and effectively generate Excel reports and other solutions, without having to depend on Microsoft Excel being installed on the target system.

👉🏼 Click here to Join I ❤️ .NET WhatsApp Channel to get 🔔 notified about new articles and other updates.

Table of Contents