Leverage the power of Azure

Combine the power of azure functions within PowerAutomate for unprecedented possibilities.

Introduction

In my previous blog post I showed how you can generate a PDF from a SharePoint list item, purely by using the PowerPlatform. Now I’ll show you how to convert Excel files to PDF documents using the best of both worlds from Azure and the PowerPlatform.

The case

A user places Excel documents in a SharePoint document library. These documents must be converted to PDF files and placed in a second document library. The Excel documents contain a large number columns, some business logic has to be applied to each these columns.

The solution

For the conversion from Excel to PDF, we still need the conversion to HTML as an intermediate step. Because we will be using Azure for this, the complexity of the steps in PowerAutomate will become a lot easier. The solution consists of the following three parts:

Create a PowerAutomate flow that will be triggered when a new document is added to a document library. His main task will be to push this document to an Azure blob storage for further processing of the Azure Function.
The Azure function will do its job by converting the Excel file to an HTML file. This HTML file will be placed in the output folder of the BLOB storage.
Create a second Power Automate flow with the output folder of the BLOB storage as trigger. This flow reads the HTML file, places it in OneDrive for conversion to PDF. As a final step, this flow will return the PDF file to SharePoint.

Step 1

The first flow consists of only two parts. The trigger when a document is created in the library and the blob action to copy the document to blob storage. PowerAutomate has made this step so simple using a standard connector. Awesome.

shadow border larger

Step 2

This is the Azure function implementation that will pick up any file places in the /input folder of the blob storage.

The processing of the Excel file to HTML is greatly simplified for this example and contains little more than the necessary. Just like in the previous example, I use a template.html file for the conversion to HTML. I can imagine that in scenarios in which the HTML takes on more complex forms you want to make other choices, such as the ‘html agility pack’

Something that is worth noticing is the way in which this template.html file is read in the Azure function. The classic method of loading Assembly files no longer works in Azure Functions. However, by using the ExecutionContext class it is possible to read embedded resource files. See line number 24 and 31-32.

  1using System;
  2using System.IO;
  3using Microsoft.Azure.WebJobs;
  4using Microsoft.Extensions.Logging;
  5using DocumentFormat.OpenXml.Packaging;
  6using DocumentFormat.OpenXml.Spreadsheet;
  7using System.Linq;
  8using System.Collections.Generic;
  9
 10/// <summary>
 11/// This azure function converts an Exel file into a HTML file using a template file: see '\\files\\template.html'
 12/// If the output of the html file is basic, this is a fast method to do the conversion. Another
 13/// nice option would be to use https://html-agility-pack.net/ for example.
 14/// </summary>
 15namespace Function.Convert
 16{
 17    public static class Function
 18    {
 19        [FunctionName("Function")]
 20        public static async System.Threading.Tasks.Task RunAsync(
 21            [BlobTrigger("input/{name}", Connection = "AzureWebJobsStorage")] Stream myBlob,
 22            string name,
 23            ILogger log,
 24            ExecutionContext executionContext,
 25            IBinder binder)
 26        {
 27            log.LogInformation($"C# Blob trigger function Processed blob\n Name:{name} \n Size: {myBlob.Length} Bytes");
 28
 29            // this is a method to get files loaded into the program, since resource-files cannot be loaded using reflection. 
 30            // make sure the template.html is set to : 'copy Always' in the file properties.
 31            string fileName = $"{ Directory.GetParent(executionContext.FunctionDirectory).FullName}\\files\\template.html";
 32            string htmlTemplate = File.ReadAllText(fileName);
 33
 34            using SpreadsheetDocument doc = SpreadsheetDocument.Open(myBlob, false);
 35            WorkbookPart workbookPart = doc.WorkbookPart;
 36            SharedStringTablePart sstpart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
 37            SharedStringTable sst = sstpart.SharedStringTable;
 38
 39            WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
 40            Worksheet sheet = worksheetPart.Worksheet;
 41
 42            var rows = sheet.Descendants<Row>();
 43            var cells = sheet.Descendants<Cell>();
 44            Console.WriteLine(string.Format("Row count = {0}", rows.LongCount()));
 45            Console.WriteLine(string.Format("Cell count = {0}", cells.LongCount()));
 46
 47            var header = new Dictionary<string, string>();
 48            var body = new Dictionary<string, string>();
 49
 50            foreach (Row row in rows)
 51            {
 52                // first row contains header
 53                if (row.RowIndex == 1)
 54                {
 55                    header = ExcelRow(sst, row);
 56                }
 57                else if (row.RowIndex == 2)
 58                {
 59                    // in this example we only process one row, there can me more ofcourse
 60                    body = ExcelRow(sst, row);
 61                }
 62            }
 63
 64            string html = "";
 65            foreach (var item in body)
 66            {
 67                html += @"<tr>" +
 68                        "<td>" + header[item.Key] + "</td>" +
 69                        "<td>" + item.Value + "</td>" +
 70                        "</tr>";
 71
 72            }
 73
 74            htmlTemplate = htmlTemplate.Replace("{data}", html);
 75            name = name.Split(".")[0];
 76            var outputBlob = await binder.BindAsync<TextWriter>(
 77                                    new BlobAttribute($"output/{name}.html")
 78                                    {
 79                                        Connection = "AzureWebJobsStorage"
 80                                    }
 81                             );
 82            outputBlob.WriteLine(htmlTemplate);
 83        }
 84
 85        private static Dictionary<string, string> ExcelRow(SharedStringTable sst, Row row)
 86        {
 87            var result = new Dictionary<string, string>();
 88            foreach (Cell cell in row.Descendants<Cell>())
 89            {
 90                if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
 91                {
 92                    int ssid = int.Parse(cell.CellValue.Text);
 93                    string input = cell.CellReference.Value;
 94                    string cellRef = new String(input.Where(c => c != '-' && (c < '0' || c > '9')).ToArray());
 95                    string str = sst.ChildElements[ssid].InnerText;
 96                    result.Add(cellRef, str);
 97                }
 98            }
 99            return result;
100        }
101    }
102}

open raw file

 1<html>
 2    <head>
 3        <title>Creation of a PDF file using PowerAutomate and Azure Function</title>
 4    </head>
 5    <body>
 6        <table border="1">
 7            {data}
 8        </table>
 9    </body>
10</html>