I ❤️ .NET

In this article, let's learn about how to do Generate Excel Report using ClosedXML.Report in .NET.

Table of Contents

1. Introduction
2. What is Closed XML ?
3. Why Closed XML ?
4. Generating Simple Excel Report
5. Generating Complex Excel Report
6. More Information
7. Summary

Introduction

Reports are needed in an enterprise to provide insights into key performance indicators, track progress towards goals, do some analysis, and make informed decisions. The most common report formats availabe are in PDF, Word, Power Point, Excel, etc. Excel reports are preferred because they are widely available, easy to use, and provide a variety of data visualization options. Additionally, Excel allows for easy data manipulation and calculation, making it a convenient tool for generating reports.

8 years ago, we used to generate reports using Crystal Reports in .NET for back-office monitoring in applications. Designing reports was tedious and sometimes complex, as it required an understanding of and proficiency in Crystal Reports. While there were other tools available for generating reports, they required Microsoft Office/Interop to be installed on the target servers.

What is Closed XML ?

ClosedXML.Report is an open-source .NET library built on top of ClosedXML that allows developers to create, read, and manipulate Microsoft Excel documents in an XML-based format (Office Open XML). It provides an API that simplifies the process of working with Excel files, allowing developers to create, update, and manipulate spreadsheets without having to use Microsoft Excel itself. This makes it an attractive option for automating Excel-related tasks and integrating Excel functionality into custom applications. With Closed XML, developers can create custom reports, data visualizations, and other Excel-based solutions, without having to depend on Microsoft Excel being installed on the target system.

Why Closed XML ?

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.

As with any open-source software, it is important to thoroughly evaluate the source code and the project's reputation before using it in production. You can assess the trustworthiness of Closed XML by looking at its code quality, the number of contributors, and the frequency of updates.

Overall, Closed XML has a positive reputation and is widely used by many developers. It is an active project with regular updates, and the development team is responsive to issues and feature requests. You can review its GitHub repository and the community feedback to determine its trustworthiness.

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

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

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.

1. Install ClosedXML.Report Nuget Package

2. Convert List / IEnumerable data to DataTable

   Code Sample - Convert IEnumerable to DataTable

3. Add DataTable to Excel WorkSheet

   Code Sample - Add DataTable to Excel WorkSheet using Closed XML

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

   Code Sample - Save Closed XML WorkSheet to File

Demo - Generate Simple Excel Report Using Closed XML

I attempted to export a table of contents from I Love .NET, and the output is as follows.

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.

1. Install ClosedXML.Report Nuget Package

2. 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.

      

      

      

3. Load the template and add data variable to template

   Code Sample - Load the Excel Template and Data Variable to Template

4. Generate the report using template

   Code Sample - Generate Excel using ClosedXML Report Template

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

   Code Sample - Save Closed XML Template to File

Demo - Generating Complex Excel Report

I attempted to export complex real-world data from I Love .NET, and the output is as follows. You can alos notice how Generated Date get formatted as expected.

Template:

Report:

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.