[Azure] In-Stream Excel Manipulation in .NET Core, using Azure Blob Storage and Azure Functions

Level of Difficulty: Junior – Senior.

So you have an Excel template stored in Azure Blob Storage which will be read and manipulated by inserting data into the Excel template returning the manipulated file in stream. This can either happen (Time Trigger) on a schedule or per request (HTTP Trigger) basis. The decision is to build the logic out using a C# Azure Function. The data to be inserted into the Excel Template will be sent into the function as the request body.

The Azure Function will form part of a larger solution which will send the data into the Azure Function. The file content of the Excel template will be read from Azure Blob storage the data received in the request body will be inserted before sending the new file content back in the response body.

A possible use case of this could be bulk processing a file that is being used in a Power Automate cloud flow. The existing Excel connector allows for single entries into an Excel document once the data has been manipulated which can become quite time consuming. To optimise such a solution, the Azure Function could handle the data validation, manipulation and bulk entry in a much more timely manner.

Depending on how the consumption requirement, an Azure Function could be a rather cost-effective solution. Azure Functions can be licensed using an app service plan, a consumption plan or a premium plan. The consumption plan comes with a free grant which allows a free grant of 1 million executions per month, per subscription. When deploying an Azure Function, a Blob Storage resource is required. In a case where a Blob Storage resource does not exist, one will be created when deploying a new Azure Function from Visual Studio. App Insights are not required when deploying an Azure Function, however, it is an extremely useful resource to use when debugging the Function.

What are the steps?

The steps that we will be following are:

1. Create Azure Function Project (Using .NET Core 3.1)
2. Create Excel Document
3. Upload Document to Blob Storage
4. Add Functionality to the Azure Function
   1. Install Packages
   2. Create Models
   3. Connect Blob Storage
   4. Add Manipulation Logic
5. Test
6. Publish

Let’s dive deeper.

Create an Azure Function Project

For the creation and deployment of the Azure Function, we will be using Visual Studios. Before you open Visual Studio, open the Visual Studio Installer and ensure that Azure development is installed, as well as ASP.NET and web development:

Once those have been installed and VS has been launched, Create a new project, select C# Azure Function

Configure your project appropriately before hitting Create. Note that the name of your project does not necessary depict the name of your function. That can still be changed.

Ensure that you have .NET Core 3 installed as Azure Functions currently support 3.1 and not 2.1. You can install the .NET Core 3 SDK here. Select Http trigger and set up the blob storage account or use the emulator if you don’t have a blob storage account then setup the authorization level, before hitting Create.

Let’s rename the Function1.cs file to ProcessInformation.cs. Ensure that you change the class and function name too, before saving the changes (Ctrl+S):

Next, let’s create the Excel Document.

Create an Excel Document

For the purpose of this use case, we will be creating an Excel document, named Information, with one sheet, named Details, with a table named PersonalDetails. The PersonalDetails table will contain three columns, namely: Name, Surname and Date of Birth.

To insert a table on a worksheet, navigate to the Insert tab, select Table then select the range and check the ‘My table has headers’ checkbox, before hitting OK:

Once the table has been created, change the column names. To change the table name, focus on the table by clicking on one of the cells in the table, the Table Design tab will pop up. Navigate to the Table Design tab and change the Table Name:

Save the document and prepare for upload to Blob Storage.

Upload Document to Blob Storage

For the sake of this post, I am going to assume you already have a Blob Storage resource setup. If you don’t, check out this post on how to create blob storage and upload files to a container.

Navigate to your blob storage account and relevant container before selecting Upload. Navigate to the file that you would like to upload and select Upload.

Let’s get back to the Azure Function and start adding functionality.

Add Functionality to the Azure Function

Before we can add the necessary functionality to the Azure Function, we first need to install the relevant packages, using NuGet Package Manager.

Install Azure.Storage.Blobs

Azure.Storage.Blobs is used to read files from and write files to the relevant Azure Blob Storage Account, based on the connection string provided.

Navigate to the NuGet Package Manager (Tools > NuGet Package Manager > Manage NuGet Packages for Solution…), then search for Azure.Storage.Blobs and install the latest stable version of the package.

Install ClosedXML

ClosedXML is used to manipulate the Excel document.

Navigate to the NuGet Package Manager (Tools > NuGet Package Manager > Manage NuGet Packages for Solution…), then search for ClosedXML and install the latest stable version of the package.

Now that the libraries are installed, let’s create the models that we need to define for the input data that will be received from the request body as well as the data structure for the Excel table.

Create PersonalDetails Model

The PersonalDetailsModel will be used to represent the structure of the data to be written to the Excel table named PersonalDetails.

The code for the model can be added as a partial class to the bottom of the Process Information class. The code for the model should look something like this:

partial class PersonalDetails
{
    [JsonProperty("Name")]
    public string Name { get; set; }
    [JsonProperty("Surname")]
    public string Surname { get; set; }
    [JsonProperty("DateOfBirth")]
    public string DateOfBirth { get; set; }
}

Create InputData Model

The InputDataModel will be used to represent the structure of the data to be received as the request body. The information passed through as the request body needs to be written to the Excel table named PersonalDetails.

The code for the model can be added as a partial class to the bottom of the Process Information class. The code for the model should look something like this:

partial class InputData
{
    [JsonProperty("PersonalDetails")]
    public List<PersonalDetails> PersonalDetails { get; set; }
}

In order to use the List capability, ensure that you’ve added System.Collections.Generic to your using statements.

Read File From Blob

Ensure that you add Azure.Storage.Blob to the using statements as a reference before creating the method that will read the file from the storage account.

You can create a class named BlobStorage beneath the ProcessInformation class which will contain the method, named ReadTemplateFromBlob, which reads the file from your storage account and returns the content as a MemoryStream. The class, with the method will look something like this:

class BlobStorage
{
    public MemoryStream ReadTemplateFromBlob()
    {
        // TO DO: Add Connection String and move into secure storage
        string connectionString = "<connectionstring>";
        string container = "excel-templates";

        // Get blob
        BlobContainerClient blobContainerClient = new BlobContainerClient(connectionString, container);
        var blob = blobContainerClient.GetBlobClient("Information.xlsx");

        MemoryStream memoryStream = new MemoryStream();

        // Download blob
        blob.DownloadTo(memoryStream);

        return memoryStream;
    }
}

The reason we create a class is to allow for the expansion of the code to encapsulate any other functionality that is specific to BlobStorage.

Add Excel Processing Logic

Now, let’s create some functionality that will allow us to write data into the Excel document. Don’t forget to add the ClosedXML.Excel to the references.

Create a class named ExcelProcessing beneath the ProcessInformation class which will contain the method, named PopulateExcelTable, which will take in the data to be written to Excel along with the name of the sheet and table that the data should be written into. The method will return the content as a MemoryStream. The class, with the method will look something like this:

class ExcelProcessing
{
    public MemoryStream PopulateExcelTable (List<PersonalDetails> personalDetailsList, MemoryStream stream, string sheetName, string tableName)
    {
        // Link to existing workbook
        using var wbook = new XLWorkbook(stream);

        // Link to sheer
        var ws = wbook.Worksheet(sheetName);

        // Set row offset
        int currentRow = 2;
        
        // Replace data in existing Excel Table
        var table = wbook.Table(tableName);
        table.ReplaceData(personalDetailsList, propagateExtraColumns: true);

        // Save file
        wbook.SaveAs(stream);

        // Create new stream
        MemoryStream memoryStream = stream;

        // Return stream
        return memoryStream;
    }
}

The reason we create a class is to allow for the expansion of the code to encapsulate any other functionality that is specific to ExcelProcessing, like reading from Excel, for example.

There is some brilliant functionality available for further manipulating an Excel document, including the use of formulas and formatting.

Now let’s bring this together in the main function.

Add Function Logic

The Azure Function is going to be used to receive the request body, deserialise it and parse it through to the ExcelProcessing class to populate the Excel table. Once the table has been populated, the content of the Excel document will be returned as a MemoryStream. The code for the static function class should look a little something like this:

public static class ProcessInformation
    {
        [FunctionName("ProcessInformation")]
        public static async Task Run(
            [HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
            ILogger log)
        {
            log.LogInformation("C# HTTP trigger function processed a request to process information.");

            try
            {
                // Create blank request body
                string requestBody = String.Empty;

                // Populate request body
                using (StreamReader streamReader = new StreamReader(req.Body))
                {
                    requestBody = await streamReader.ReadToEndAsync();
                }

                log.LogInformation("Request body received.");

                // Convert JSON string to object
                var data = JsonConvert.DeserializeObject<InputData>(requestBody.ToString());

                log.LogInformation("JSON deserialised and parsed through model.");
                
                // Create new list of PersonalData and populate with InputData
                List<PersonalDetails> personalDetailsList = data.PersonalDetails;

                log.LogInformation("New list of personal details created.");

                // Save Excel Template file from Blob Storage
                BlobStorage blobStorage = new BlobStorage();
                var stream = blobStorage.ReadTemplateFromBlob();

                log.LogInformation("Template retrieved from blob.");

                // Populate Excel Spreadsheet
                ExcelProcessing excelProcessing = new ExcelProcessing();

                var excelStream = excelProcessing.PopulateExcelTable(personalDetailsList, stream, "Details", "PersonalDetails");

                log.LogInformation("Excel populated");

                // Create response
                var response = req.HttpContext.Response;
                response.ContentType = "application/json";

                // Create stream writer and memory stream
                using StreamWriter streamWriter = new StreamWriter(response.Body);

                // Add the memory stream to the stream writer/request.Body
                await streamWriter.WriteLineAsync("[\"" + Convert.ToBase64String(excelStream.ToArray()) + "\"]");
                await streamWriter.FlushAsync();
            }
            catch (Exception e)
            {
                log.LogInformation($"Error processing information: {e.Message}");
            }
        }
    }

You can test the solution locally, using Postman.

Test the Azure Function

Ensure that Postman is installed then run the Azure Function. Grab the URL that pops up in the command line and paste that into Postman:

Hit the body tab, click on the raw radio button, change the type to JSON and paste the following into the body:

{
    "PersonalDetails": [
        {
            "Name": "Jacqui",
            "Surname": "Muller",
            "DateOfBirth": "1996/07/27"
        },
        {
            "Name": "John",
            "Surname": "Doe",
            "DateOfBirth": "2000/01/01"
        }
    ]
}

When you hit Send, you should see a response similar to this:

This response can be converted to binary to save the file which can be incorporated into the Power Automate flow.

Lastly, we need to publish this solution to Azure from Visual Studio.

Publish

Navigate to the solution explorer and double click on Connected Services. If you don’t see connected services, right click on the function and select Publish:

Set up your publish profile by selecting Azure as the target of the deployment, then hit Next:

Next, select Azure Function App (Windows), before selecting Next:

If you don’t already have a resource, add a new one:

Fill in and validate all required details, before hitting Create:

Once the resource has been created, you can hit Publish:

Once your Azure Function has been successfully published, you may test the function using Postman and enhance as you see fit.

This solution is available on Github.

Did this help you? Did you get stuck? Feel free to reach out by leaving a comment below.