Logic App Function App reading and writing Binary Files

Leonard Bernstein 25 Reputation points
2024-03-28T21:21:22.23+00:00

I am having a weird problem that I have been fighting with for several days. I have the Logic App below:

My Logic App is supposed to take Excel (template) as input from Sharepoint, write to it(using function app), and output a new Excel file to SFTP component. For simplicity to describe my problem, I am down to:

  1. Read Excel file from Sharepoint
  2. Put it through C# Function App that by the way takes it and is supposed to output the SAME FILE (so no Excel manipulation)
  3. Write the output file to FTP.

When I run my function app locally and submit data using Postman, all looks good. I save function binary output and I can open it as Excel file. However, when I deploy my function app to Logic App, I can save output to FTP, but produced output file is not valid excel file. In Logic app SFTP component I am using "@binary(body('ConvertJsonToExcelV2'))"

What am I doing wrong? Please help! Here is my C# Function app code:

User's image

ConvertJsonToExcelV2.cs:

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.Azure.WebJobs.Extensions.OpenApi.Core.Attributes;
using Microsoft.Azure.WebJobs.Extensions.OpenApi.Core.Enums;
using Microsoft.Extensions.Logging;
using Microsoft.OpenApi.Models;
using NwcDevFuncApps.ConvertJsonToExcelHelper;
using System.Net;
using System.Threading.Tasks;

namespace NwcDevFuncApps
{
    public static class ConvertJsonToExcelV2
    {
        [FunctionName("ConvertJsonToExcelV2")]
        [OpenApiOperation(operationId: "ConvertJsonToExcelRun", tags: new[] { "convertjsontoexcel" })]
        [OpenApiSecurity("function_key", SecuritySchemeType.ApiKey, Name = "code", In = OpenApiSecurityLocationType.Query)]
        [OpenApiRequestBody(contentType: "application/json", bodyType: typeof(string), Description = "The root object of the body is jsontoexcel. An Excel can be included in the body as a template or pre-data (jsontoexcel.prependdata.$content)")]
        [OpenApiResponseWithBody(statusCode: HttpStatusCode.OK, contentType: "application/octet-stream", bodyType: typeof(object), Description = "The OK response")]
        public static async Task<IActionResult> Run(
        [HttpTrigger(AuthorizationLevel.Anonymous, "get", "post", Route = null)] HttpRequest req, ILogger log)
        {
            ConvertJsonToExcelWorkerV2 w = new ConvertJsonToExcelWorkerV2(req, log);
            IActionResult result = await w.DoWork();
            return result;
        }
    }
}

ConvertJsonToExcelWorkerV2.cs:

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Runtime.ExceptionServices;
using System.Text;
using System.Threading.Tasks;

namespace NwcDevFuncApps.ConvertJsonToExcelHelper
{
    internal class ConvertJsonToExcelWorkerV2
    {
        private HttpRequest Req { get; set; } = null;
        private ILogger Log { get; set; } = null;

        public ConvertJsonToExcelWorkerV2(HttpRequest req, ILogger log)
        {
            Req = req;
            Log = log;
        }

        public async Task<IActionResult> DoWork()
        {
            FileContentResult res = null;
            JObject jsonData = null;

            string requestBody = new StreamReader(Req.Body).ReadToEnd();

            using (JsonTextReader reader = new JsonTextReader(new StringReader(requestBody)))
            {
                jsonData = (JObject)JToken.ReadFrom(reader);

                var prependJValue = (JValue)jsonData.SelectToken("jsontoexcel.prependdata.$content");
                var prependBase64String = prependJValue?.ToString();

                using (MemoryStream packageStream = new MemoryStream(Convert.FromBase64String(prependBase64String)))
                {
                    packageStream.Position = 0;

                    byte[] bytes = packageStream.ToArray();
                    res = new FileContentResult(bytes, "application/octet-stream");

                    packageStream.Close();
                }
                reader.Close();
            }
            return res;
        }
    }
}
Azure Functions
Azure Functions
An Azure service that provides an event-driven serverless compute platform.
4,266 questions
Azure Logic Apps
Azure Logic Apps
An Azure service that automates the access and use of data across clouds without writing code.
2,845 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,251 questions
{count} votes