send excelsheet to azure function for processing

In our current project we want users to be able to upload excel files on our website, and those excel sheets should be processed (read every row and store them in database) using an Azure Function.

This functionality used to be in the backend from our .Net Core MVC project, using a Task<IActionResult> post method which accepted an IFormFile parameter. The sheet was processed (reading all the rows).

However, Azure functions doesn't seem to accept an IFormFile parameter. My question is how can I add this excelsheet data to the request?

This is post method that from the web app, that activates the azure function:

[HttpPost]
public async Task<ActionResult> Bulkimport(IFormFile excelsheet)
    {

        string azFunctionUri = "https://azfunctionsUri.azurewebsites.net";
        if (customerExists)
        {
            using (MemoryStream ms = new MemoryStream())
            {
                 await excelsheet.CopyToAsync(ms).ConfigureAwait(false);
                 string content = Encoding.ASCII.GetString(ms.ToArray());
                 // use content as parameter in api builder method
                 IApiBuilder apiBuilder = new ApiBuilder();
                 HttpResponseMessage buildRequest = await apiBuilder.BuildFileRequest(azFunctionUri, content); // This builds the request and sends it to the azure function.
             }
         }
         return View();
      }

BuildFileRequest.cs (helper method that constructs the request):

public async Task<HttpResponseMessage> BuildFileRequest(string uri, string content)
{
     MultipartFormDataContent multiContent = new MultipartFormDataContent();

     byte[] payload = Encoding.UTF8.GetBytes(content);
     multiContent.Add(new ByteArrayContent(payload), "files"); // name must be "files"
     HttpResponseMessage response = await httpClient.PostAsync($"{uri}", multiContent).ConfigureAwait(false);

     return response;
}

I am stuck on how to receive this request in the Function and construct the excelfile from the request, so the rows can be read and processed in the database.

1 answer

  • answered 2020-02-17 05:47 Jim Xu

    According to my test, we can use the following code to receive the excel file.

    1. Client Code. This is my test xlsx file
    using (var httpClient = new HttpClient())
                {
                    using (var form = new MultipartFormDataContent())
                    {
                        using (var fs = File.OpenRead(filePath))
                        {
                            using (var streamContent = new StreamContent(fs))
                            {
                                using (var fileContent = new ByteArrayContent(await streamContent.ReadAsByteArrayAsync()))
                                {
                                    fileContent.Headers.ContentType = MediaTypeHeaderValue.Parse("multipart/form-data");
    
                                    form.Add(fileContent, "file", Path.GetFileName(filePath));
                                    HttpResponseMessage response = await httpClient.PostAsync(url, form);
                                }
                            }
                        }
                    }
                }
    
    1. Azure function
     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.");
                foreach (var file in req.Form.Files) {
                 //Process excel file
                 /* For example  use sdk : DocumentFormat.OpenXml. 
    For more details, please refer to https://docs.microsoft.com/en-us/office/open-xml/how-to-parse-and-read-a-large-spreadsheet
    
    */
                    using (var stream = new MemoryStream()) {
                        await file.CopyToAsync(stream);
                        stream.Position = 0;
                        using (SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, false)) {
                            WorkbookPart workbookPart = doc.WorkbookPart;
                            SharedStringTablePart sstpart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
                            SharedStringTable sst = sstpart.SharedStringTable;
    
                            WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
                            Worksheet sheet = worksheetPart.Worksheet;
    
                            var cells = sheet.Descendants<Cell>();
                            var rows = sheet.Descendants<Row>();
    
                            log.LogInformation(string.Format("Row count = {0}", rows.LongCount()));
                            log.LogInformation(string.Format("Cell count = {0}", cells.LongCount()));
    
    
    
                        }
    
    
    
                    }
    
                }
     return new OkObjectResult("Ok")
    }
    

    enter image description here