How to Generate Excel Report in C#

To generate an Excel report in C# using ClosedXML.Report, you'll need to follow these steps

How to generate excel report in C#

1. Install ClosedXML and ClosedXML.Report via NuGet Package Manager.
2. Design your Excel template using ClosedXML.
3. Populate your Excel template with data using ClosedXML.Report.

We will create a new Windows Forms Application project.

Next, Open your form designer, then drag and drop Button, TextBox, Label and DataGridView controls from the Visual Studio toolbox into your form designer. You can design a simple UI that allows you to select an excel template file, then fill data and generate an excel report from based on the excel template file.

After you finish designing the form, you need to right click on your project, then select Manage Nuget Packages.

c# generate excel report

Search and Install ClosedXML and ClosedXML.Report via NuGet Package Manager

You can install these packages via the NuGet Package Manager Console using the following commands:

Install-Package ClosedXML
Install-Package ClosedXML.Report

ClosedXML.Report is a tool for report generation and data analysis in .NET applications through the use of Microsoft Excel. ClosedXML.Report is a .NET-library for report generation Microsoft Excel without requiring Excel to be installed on the machine that's running the code.

We will use Entity Framework to retrieve data from sql database, You can add an ADO.NET Entity Data Model to your project by right-clicking on your project, then add ADO.NET Entity Data Model

We will use the Customer table in the Northwind database to practice this demo

Next, You need to rebuild your project, then add a bindingsource to your DataGridView.

Adding a Form_Load event handler that allows you to retrieve customer data from the Northwind database, then set datasource to the bindingsource.

private void Form1_Load(object sender, EventArgs e)
{
    NorthwindEntities db = new NorthwindEntities();
    customerBindingSource.DataSource = db.Customers.ToList();
}

Adding a click event handler to the Browse button that allows you to get the excel template name.

// c# openfiledialog
private void btnBrowse_Click(object sender, EventArgs e)
{
    using (OpenFileDialog ofd = new OpenFileDialog() { Filter = "Excel Workbook|*.xlsx", Multiselect = false })
    {
        if (ofd.ShowDialog() == DialogResult.OK)
            txtFileName.Text = ofd.FileName;
    }
}

Finally, Add a click event handler to the Generate button that allows you to generate excel report based on row you select from DataGridView. We will populate the Excel template with data using ClosedXML.Report:

// generate excel report using c#
// c# savefiledialog
private void btnGenerate_Click(object sender, EventArgs e)
{
    if (string.IsNullOrEmpty(txtFileName.Text))
    {
        MessageBox.Show("Please select your template file.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
        return;
    }
    using (SaveFileDialog sfd = new SaveFileDialog() { Filter = "Excel Workbook|*.xlsx", ValidateNames = true })
    {
        if (sfd.ShowDialog() == DialogResult.OK)
        {
            try
            {
                // c# create a template processor
                var template = new XLTemplate(txtFileName.Text);
                var customer = customerBindingSource.Current as Customer;
                if (customer != null)
                {
                    // c# populate the template with data
                    template.AddVariable(customer);
                    // c# generate the report
                    template.Generate();
                    // c# save the generated Excel file
                    template.SaveAs(sfd.FileName);
                }
                MessageBox.Show("You have successfully created an excel report.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    }
}

Design your Excel template using ClosedXML

Create an Excel template (.xlsx) file with placeholders for data. You can use normal Excel functionality like cell references, formulas, and styling. ClosedXML.Report will replace the placeholders with actual data.

This is a basic example help you generate excel reports from the excel template file in c# using ClosedXml.Report library.  ClosedXML.Report provides more advanced features for generating Excel reports, such as loops, conditional formatting, and grouping.