A simple, generic Excel file exporter

I recently needed to write a routine that exports a query to an Excel file, and came up with a generic solution I thought might be helpful to share. The code is in an AspNetCore project, but the same principles would work in other .NET scenarios.
Let’s start with the signature of the export method:

byte[] WriteToExcel<T>(List<T> rows, string[] headers = null, Func<T, object[]> formatterFunc = null);

The output is a byte[], which is the actual contents of the Excel file (an xlsx file). I end up returning this from my API controller as a FileResult, using:

return File(fileBytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
 $"Download_{firstRouteData.TenantName}_{localDate:MMddyy}.xlsx");

Here, fileBytes is the result from my export. By supplying the correct MIME type and a filename, the Angular web app downloading this ends up just dropping a nicely named Excel file in your Downloads folder.
Going back to the method declaration, the first parameter is a List<T> of data rows. You can pass in whatever list of data you want. In my case, I am passing the result of an EntityFrameworkCore query:

var data = await (
    from r in _tripRepo.Context.Routes
    join t in _tripRepo.Context.Trips on r.Id equals t.Route.Id
    join s in _tripRepo.Context.Stops on t.Id equals s.TripId
    join a in _tripRepo.Context.Activities on s.Id equals a.StopId
    where r.TenantId == CurrentTenantId && t.Date==localDate
    orderby r.Name, s.Sequence
    select new
    {
        RouteName = r.Name,
        TripDate = t.Date,
        t.EstimatedMiles,
        t.EstimatedTime,
        t.EstimatedTrafficTime,
        t.EstimatedTravelTime,
        s.Sequence,
        s.EstimatedArrival,
        s.EstimatedDeparture,
        LocationName = s.Location.Name,
        LocationTypeName = s.Location.LocationType.Name,
        s.Location.Address.StreetAddress1,
        s.Location.Address.City,
        s.Location.Address.PostalCodeZIP,
        State = s.Location.Address.State.ShortName,
        s.Location.ServiceWindowStartHHMM,
        s.Location.ServiceWindowEndHHMM,
        ActivityTypeName = a.ActivityType.Name,
        a.DurationMinutes
    }
).ToListAsync();

You’ll notice that the select statement is using an anonymous type (new { …. ). I can still pass the list to my exporter func… I dont have to declare the type in code. In the anonymous type, some of the assignments specify a property name, for instance TripDate = t.Date, others don’t, for instance t.EstimatedTravelTime. When a name is specified, the anonymous type uses that property name, otherwise the  assigned identifier.
The generic exporter will use these property names as the headers in the first row of the Excel file, but I also allow you to supply a custom list of headers to use in the second optional parameter of the call. The exporter will write the values of each property to the file, using whatever default formatting that gives you. If you want to be in more control, you can specify a function that does the formatting. In my code, I supply both:

var fileBytes = _fp.WriteToExcel(
    data,
    new[]
    {
        "Route",
        "Date",
        "Est. Miles",
        "Est. Time",
        "Est. Traffic Time",
        "Est. Travel Time",
        "Sequence",
        "Est. Arrival",
        "Est. Departure",
        "Location",
        "Location Type",
        "Street Address",
        "City",
        "ZIP",
        "State",
        "Service Window Start",
        "Service Window End",
        "Activity Type",
        "Duration (mins)"
    },
    r => new object[]
    {
        r.RouteName,
        r.TripDate.ToShortDateString(),
        $"{r.EstimatedMiles:F1}",
        FormatTime(r.EstimatedTime),
        FormatTime(r.EstimatedTrafficTime),
        FormatTime(r.EstimatedTravelTime),
        r.Sequence,
        r.EstimatedArrival?.LocalDateTime.ToShortTimeString(),
        r.EstimatedDeparture?.LocalDateTime.ToShortTimeString(),
        r.LocationName,
        r.LocationTypeName,
        r.StreetAddress1,
        r.City,
        r.PostalCodeZIP,
        r.State,
        r.ServiceWindowStartHHMM,
        r.ServiceWindowEndHHMM,
        r.ActivityTypeName,
        r.DurationMinutes
    });

Here, FormatTime is a local function that converts a nullable Double representing time in seconds to a more pleasant hours and minutes string:

string FormatTime(double? seconds) =>
 !seconds.HasValue ? "" : $"{(int)seconds/3600}h {(int)seconds/60%60}m";

The result of this formatting is an Excel file that looks something like this:

OK – now that we have seen the signature of the method, what I pass in, and the results… let’s delve into the magic in the black box!
First of all, I am using the excellent https://www.nuget.org/packages/EPPlus
This package makes reading and writing Excel (XLSX) files a breeze. The first few rows of code in this method just create a new Worksheet called “Export”. I use a MemoryStream as the ‘backing storage’ for writing the file in memory. The rows and columns both start at 1, so I set that up.

public byte[] WriteToExcel<T>(
    List<T> rows,
    string[] headers = null,
    Func<T, object[]> formatterFunc = null)
{
    using (var ms = new MemoryStream())
    {
        using (var p = new ExcelPackage(ms))
        {
            var row = 1;
            var col = 1;
            var ws = p.Workbook.Worksheets.Add("Export");

Now I need to write the header row. If I supplied headers, I use those, otherwise I get the names of the properties of the data type  using typeof(T).GetProperties(), and pis.Select( pi => pi.Name).

var pis = typeof(T).GetProperties();
foreach (var h in headers ?? pis.Select(pi => pi.Name).ToArray())
{
    ws.Cells[row, col++].Value = h;
}

Now I need to iterate over the data and write it out. For each row, I either use the raw property values, using pis.Select( pi => pi.GetValue(0)), or I pass the row to the supplied formatter function, which returns an array of objects to use instead.

foreach (var o in rows)
{
    row++;
    col = 1;
    var values = formatterFunc == null ?
        pis.Select(pi => pi.GetValue(o)) : formatterFunc(o);
    foreach (var v in values)
    {
        ws.Cells[row, col++].Value = v;
    }
}

Finally, I Auto Fit all the columns to the data in them, and return the file contents as an array of bytes:

ws.Cells.AutoFitColumns();
return p.GetAsByteArray();

Putting this all together:

public byte[] WriteToExcel<T>(
    List<T> rows,
    string[] headers = null,
    Func<T, object[]> formatterFunc = null)
{
    using (var ms = new MemoryStream())
    {
        using (var p = new ExcelPackage(ms))
        {
            var row = 1;
            var col = 1;
            var ws = p.Workbook.Worksheets.Add("Export");
            var pis = typeof(T).GetProperties();
            foreach (var h in headers ?? pis.Select(pi => pi.Name).ToArray())
            {
                ws.Cells[row, col++].Value = h;
            }
            foreach (var o in rows)
            {
                row++;
                col = 1;
                var values = formatterFunc == null ?
                    pis.Select(pi => pi.GetValue(o)) : formatterFunc(o);
                foreach (var v in values)
                {
                    ws.Cells[row, col++].Value = v;
                }
            }
            ws.Cells.AutoFitColumns();
            return p.GetAsByteArray();
        }
    }
}

So very little code, and remarkably flexible!
I hope you find this useful in your projects.
 
 

Related Blog Posts

We hope you’ve found this to be helpful and are walking away with some new, useful insights. If you want to learn more, here are a couple of related articles that others also usually find to be interesting:

Our Gear Is Packed and We're Excited to Explore with You

Ready to come with us? 

Together, we can map your company’s tech journey and start down the trails. If you’re set to take the first step, simply fill out the form below. We’ll be in touch – and you’ll have a partner who cares about you and your company. 

We can’t wait to hear from you! 

This field is for validation purposes and should be left unchanged.

Together, we can map your company’s tech journey and start down the trails. If you’re set to take the first step, simply fill out the form below. We’ll be in touch – and you’ll have a partner who cares about you and your company. 

We can’t wait to hear from you! 

Montage Portal

Montage Furniture Services provides furniture protection plans and claims processing services to a wide selection of furniture retailers and consumers.

Project Background

Montage was looking to build a new web portal for both Retailers and Consumers, which would integrate with Dynamics CRM and other legacy systems. The portal needed to be multi tenant and support branding and configuration for different Retailers. Trailhead architected the new Montage Platform, including the Portal and all of it’s back end integrations, did the UI/UX and then delivered the new system, along with enhancements to DevOps and processes.

Logistics

We’ve logged countless miles exploring the tech world. In doing so, we gained the experience that enables us to deliver your unique software and systems architecture needs. Our team of seasoned tech vets can provide you with:

Custom App and Software Development

We collaborate with you throughout the entire process because your customized tech should fit your needs, not just those of other clients.

Cloud and Mobile Applications

The modern world demands versatile technology, and this is exactly what your mobile and cloud-based apps will give you.

User Experience and Interface (UX/UI) Design

We want your end users to have optimal experiences with tech that is highly intuitive and responsive.

DevOps

This combination of Agile software development and IT operations provides you with high-quality software at reduced cost, time, and risk.

Trailhead stepped into a challenging project – building our new web architecture and redeveloping our portals at the same time the business was migrating from a legacy system to our new CRM solution. They were able to not only significantly improve our web development architecture but our development and deployment processes as well as the functionality and performance of our portals. The feedback from customers has been overwhelmingly positive. Trailhead has proven themselves to be a valuable partner.

– BOB DOERKSEN, Vice President of Technology Services
at Montage Furniture Services

Technologies Used

When you hit the trails, it is essential to bring appropriate gear. The same holds true for your digital technology needs. That’s why Trailhead builds custom solutions on trusted platforms like .NET, Angular, React, and Xamarin.

Expertise

We partner with businesses who need intuitive custom software, responsive mobile applications, and advanced cloud technologies. And our extensive experience in the tech field allows us to help you map out the right path for all your digital technology needs.

  • Project Management
  • Architecture
  • Web App Development
  • Cloud Development
  • DevOps
  • Process Improvements
  • Legacy System Integration
  • UI Design
  • Manual QA
  • Back end/API/Database development

We partner with businesses who need intuitive custom software, responsive mobile applications, and advanced cloud technologies. And our extensive experience in the tech field allows us to help you map out the right path for all your digital technology needs.

Our Gear Is Packed and We're Excited to Explore with You

Ready to come with us? 

Together, we can map your company’s tech journey and start down the trails. If you’re set to take the first step, simply fill out the contact form. We’ll be in touch – and you’ll have a partner who cares about you and your company. 

We can’t wait to hear from you! 

This field is for validation purposes and should be left unchanged.

Thank you message.

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.