Intro

We typically use Azure Function to create APIs or scheduled jobs. For REST APIs that return JSON as response, it's quite easy, but sometimes, we need another format besides JSON, like a file. Recently I have been working on a project that needs to generate Excel reports from Azure Function. I am using .NET 6.0, C# with Azure Function V4. Let's see how to do it.

ClosedXML

I chose ClosedXML for generating Excel files. 

"ClosedXML is a .NET library for reading, manipulating and writing Excel 2007+ (.xlsx, .xlsm) files. It aims to provide an intuitive and user-friendly interface to deal with the underlying OpenXML API."

Install the NuGet package into your Azure Function.

PM> Install-Package ClosedXML

Now we add code in function logic.

Create an empty excel file

// create workbook
using var workbook = new XLWorkbook(XLEventTracking.Disabled);
var sheet = workbook.Worksheets.Add("996 Sheet");

Add header row

// add header
var headers = new List<string[]>
{
    new[] { "Item", "Value" }
};
sheet.Cell(1, 1).InsertData(headers);

Add data

// add data
var sampleData = new List<Work996Data>
{
    new () { Item = "Study", Value = "985" },
    new () { Item = "Work", Value = "996" },
    new () { Item = "Sick", Value = "ICU" },
    new () { Item = "Resign", Value = "251" }
};

sheet.Cell(2, 1).InsertData(sampleData);

Add styles

// add styles
var headerRange = sheet.Range(1, 1, 1, headers[0].Length);
headerRange.Style.Font.Bold = true;
headerRange.Style.Font.FontColor = XLColor.White;
headerRange.Style.Fill.BackgroundColor = XLColor.Black;

sheet.SheetView.FreezeRows(1);
sheet.SheetView.FreezeColumns(1);
sheet.RangeUsed().SetAutoFilter();
sheet.ColumnsUsed().Width = 20;

var bodyRange = sheet.RangeUsed();
bodyRange.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
bodyRange.Style.Border.OutsideBorderColor = XLColor.Black;
bodyRange.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
bodyRange.Style.Border.InsideBorderColor = XLColor.Black;

Return File in Azure Function

The final step is to output the Excel workbook as a file. Just like in ASP.NET Core, we can use FileStreamResult to return a file stream. For Excel format, set the media type to application/vnd.openxmlformats-officedocument.spreadsheetml.sheet. Then the browser will know it is a file for download.

// output file
var memoryStream = new MemoryStream();
workbook.SaveAs(memoryStream);
memoryStream.Seek(0, SeekOrigin.Begin);

const string mediaType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
return new FileStreamResult(memoryStream, mediaType)
{
    FileDownloadName = "996Book.xlsx"
};

This is how the Excel file look like:

The complete code

using System.Collections.Generic;
using System.Threading.Tasks;
using ClosedXML.Excel;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Logging;
using System.IO;

namespace Excel996;

public static class GetExcel
{
    [FunctionName("GetExcel")]
    public static async Task<IActionResult> Run(
        [HttpTrigger(AuthorizationLevel.Anonymous, "get", "post", Route = null)] HttpRequest req,
        ILogger log)
    {
        log.LogInformation("C# HTTP trigger function processed a request.");

        // create workbook
        using var workbook = new XLWorkbook(XLEventTracking.Disabled);
        var sheet = workbook.Worksheets.Add("996 Sheet");

        // add header
        var headers = new List<string[]>
        {
            new[] { "Item", "Value" }
        };
        sheet.Cell(1, 1).InsertData(headers);

        // add data
        var sampleData = new List<Work996Data>
        {
            new () { Item = "Study", Value = "985" },
            new () { Item = "Work", Value = "996" },
            new () { Item = "Sick", Value = "ICU" },
            new () { Item = "Resign", Value = "251" }
        };

        sheet.Cell(2, 1).InsertData(sampleData);

        // add styles
        var headerRange = sheet.Range(1, 1, 1, headers[0].Length);
        headerRange.Style.Font.Bold = true;
        headerRange.Style.Font.FontColor = XLColor.White;
        headerRange.Style.Fill.BackgroundColor = XLColor.Black;

        sheet.SheetView.FreezeRows(1);
        sheet.SheetView.FreezeColumns(1);
        sheet.RangeUsed().SetAutoFilter();
        sheet.ColumnsUsed().Width = 20;

        var bodyRange = sheet.RangeUsed();
        bodyRange.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
        bodyRange.Style.Border.OutsideBorderColor = XLColor.Black;
        bodyRange.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
        bodyRange.Style.Border.InsideBorderColor = XLColor.Black;

        // output file
        var memoryStream = new MemoryStream();
        workbook.SaveAs(memoryStream);
        memoryStream.Seek(0, SeekOrigin.Begin);

        const string mediaType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        return new FileStreamResult(memoryStream, mediaType)
        {
            FileDownloadName = "996Book.xlsx"
        };
    }
}

public class Work996Data
{
    public string Item { get; set; }
    public string Value { get; set; }
}

Notice

Please keep in mind that this method is only used for Excel files that can be generated in a few seconds. If your Excel code takes a long time to complete. Please consider using a cloud native design pattern like "Asynchronous Request-Reply pattern". TLDR, just return HTTP 202 in your Azure Function, generate Excel file in backend, store it in Azure Storage Account, then let user download later.