HBH.Fingers10.ExcelExport 1.0.3

Excel Export

Simple classes to generate Excel/CSV Report in ASP.NET Core.

To export/download the IEnumerable<T> data as an excel file, add action method in your controller as shown below. Return the data as ExcelResult<T>/CSVResult<T> by passing filtered/ordered data, sheet name and file name. ExcelResult/CSVResult Action Result that I have added in the Nuget package. This will take care of converting your data as excel/csv file and return it back to browser.

Note: This tutorial contains example for downloading/exporting excel/csv from Asp.Net Core Backend.

Give a Star ⭐️

If you liked ExcelExport project or if it helped you, please give a star ⭐️ for this repository. That will not only help strengthen our .NET community but also improve development skills for .NET developers in around the world. Thank you very much 👍

Columns

Name

Column names in Excel Export can be configured using the below attributes

• [Display(Name = "")]
• [DisplayName(“”)]

Report Setup

To customize the Excel Column display in your report, use the following attribute

• [IncludeInReport]
• [NestedIncludeInReport]
• [IncludeAllInReportAttribute]
• [ExcludeFromReportAttribute]

And here are the options,

Please note: From v.2.0.0, simple properties in your models with [IncludeInReport] attribute will be displayed in excel report. You can add any level of nesting to your models using [NestedIncludeInReport] attribute.

NuGet:

• Fingers10.ExcelExport v3.0.0

Package Manager:

PM> Install-Package Fingers10.ExcelExport

.NET CLI:

> dotnet add package Fingers10.ExcelExport

Root Model

public class DemoExcel
{
    public int Id { get; set; }

    [IncludeInReport(Order = 1)]
    public string Name { get; set; }

    [IncludeInReport(Order = 2)]
    public string Position { get; set; }

    [Display(Name = "Office")]
    [IncludeInReport(Order = 3)]
    public string Offices { get; set; }

    [NestedIncludeInReport]
    public DemoNestedLevelOne DemoNestedLevelOne { get; set; }
}

Nested Level One Model:

public class DemoNestedLevelOne
{
    [IncludeInReport(Order = 4)]
    public short? Experience { get; set; }

    [DisplayName("Extn")]
    [IncludeInReport(Order = 5)]
    public int? Extension { get; set; }

    [NestedIncludeInReport]
    public DemoNestedLevelTwo DemoNestedLevelTwos { get; set; }
}

Nested Level Two Model:

public class DemoNestedLevelTwo
{
    [DisplayName("Start Date")]
    [IncludeInReport(Order = 6)]
    public DateTime? StartDates { get; set; }

    [IncludeInReport(Order = 7)]
    public long? Salary { get; set; }
}

Action Method

public async Task<IActionResult> GetExcel()
{
    // Get you IEnumerable<T> data
    var results = await _demoService.GetDataAsync();
    return new ExcelResult<DemoExcel>(results, "Demo Sheet Name", "Fingers10");
}

public async Task<IActionResult> GetCSV()
{
    // Get you IEnumerable<T> data
    var results = await _demoService.GetDataAsync();
    return new CSVResult<DemoExcel>(results, "Fingers10");
}

Page Handler

public async Task<IActionResult> OnGetExcelAsync()
{
     // Get you IEnumerable<T> data
    var results = await _demoService.GetDataAsync();
    return new ExcelResult<DemoExcel>(results, "Demo Sheet Name", "Fingers10");
}

public async Task<IActionResult> OnGetCSVAsync()
{
     // Get you IEnumerable<T> data
    var results = await _demoService.GetDataAsync();
    return new CSVResult<DemoExcel>(results, "Fingers10");
}

Include All Modal Columns In Report using IncludeAllInReportAttribute

[IncludeAllInReport]
public class DemoExcel
{
    public int Id { get; set; }

    public string Name { get; set; }

    public string Position { get; set; }

    public string Offices { get; set; }
}

Exclude some Columns from Report using ExcludeFromReportAttribute

for example here offices column is excluded

[IncludeAllInReport]
public class DemoExcel
{
    public int Id { get; set; }

    public string Name { get; set; }

    public string Position { get; set; }

    [ExcludeFromReport]
    public string Offices { get; set; }
}

Pass columns definitions as a parameter to the constructor

For example if you have this Modal class

public class Employee
{
    public string EmployeeName { get; set; }
    public DateTime? StartDate { get; set; }
    public long? BasicSalary { get; set; }
}

You have two ways to pass columns definitions

1 - Pass columns definitions as tuple params

Excel

public async Task<IActionResult> OnGetExcelAsync()
{
     // Get you IEnumerable<T> data
    var results = await _demoService.GetDataAsync();
    return new ExcelResult<Employee>(
            data: results,
            sheetName: "Fingers10",
            fileName: "Fingers10",
            ("EmployeeName", "Employee Name", 1), // prop name, label, order
            ("StartDate", "Start Date", 2),
            ("BasicSalary", "Basic Salary", 3)
    );
}

• CSV

public async Task<IActionResult> OnGetExcelAsync()
{
     // Get you IEnumerable<T> data
    var results = await _demoService.GetDataAsync();
    return new CSVResult<Employee>(
            data: results,
            fileName: "Fingers10",
            ("EmployeeName", "Employee Name", 1), // prop name, label, order
            ("StartDate", "Start Date", 2),
            ("BasicSalary", "Basic Salary", 3)
    );
}

2 - Pass columns definitions as List of ExcelColumnDefinition

in this way the columns are ordered based on the order of the passed list

Excel

public async Task<IActionResult> OnGetExcelAsync()
{
     // Get you IEnumerable<T> data
    var results = await _demoService.GetDataAsync();
    return new ExcelResult<Employee>(
            data: results,
            sheetName: "Fingers10",
            fileName: "Fingers10",
            new List<ExcelColumnDefinition>()
            {
                new ("EmployeeName", "Employee Name"),
                new ("StartDate", "Start Date"),
                new ("BasicSalary", "Basic Salary")
            }
    );
}

• CSV

public async Task<IActionResult> OnGetExcelAsync()
{
     // Get you IEnumerable<T> data
    var results = await _demoService.GetDataAsync();
    return new CSVResult<Employee>(
            data: results,
            fileName: "Fingers10",
            new List<ExcelColumnDefinition>()
            {
                new ("EmployeeName", "Employee Name"),
                new ("StartDate", "Start Date"),
                new ("BasicSalary", "Basic Salary")
            }
    );
}

Target Platform

• .Net Standard 2.0

Tools Used

• Visual Studio Community 2019

Other Nuget Packages Used

• ClosedXML (0.95.0) - For Generating Excel Bytes
• Microsoft.AspNetCore.Mvc.Abstractions (2.2.0) - For using IActionResult
• System.ComponentModel.Annotations (4.7.0) - For Reading Column Names from Annotations

Author

• Abdul Rahman - Software Developer - from India. Software Consultant, Architect, Freelance Lecturer/Developer and Web Geek.

Contributions

Feel free to submit a pull request if you can add additional functionality or find any bugs (to see a list of active issues), visit the Issues section. Please make sure all commits are properly documented.

License

ExcelExport is release under the MIT license. You are free to use, modify and distribute this software, as long as the copyright header is left intact.

Enjoy!

Sponsors/Backers

I'm happy to help you with my Nuget Package. Support this project by becoming a sponsor/backer. Your logo will show up here with a link to your website. Sponsor/Back via