Access excel file from blob trigger azure function using epplus

Question

I have created a blob trigger azure function which is called whenever a new file(only excel file in my case) is added to the blob storage. Can anyone suggest how to fetch the data of the file from blob storage and convert it into excel using epplus.

public void Run([BlobTrigger("myblobcontainer/{name}", Connection = "AzureStorage")]CloudBlockBlob myBlob, string name, ILogger log)
{
 try
 {
  ProcessData(myBlob,name);
 }
 catch (Exception ex)
 {
 }
}

public int ProcessData(CloudBlockBlob myBlob, string name)
{
   CloudStorageAccount IMAccount;
   IMAccount = CloudStorageAccount.Parse("my azure storage connection");

   var blobReference= //Get the path of file in blob

  //Basically here I want to read 'myBlob' and convert it back into excel
}

I am a bit confused about how to get the path of the file (in var blobReference) in the blob and convert it to excel using epplus. Anyone, please suggest how to do that.

Answer 1

The below sample code will help you in populating my excel data from a collection to your blob container and create a new CloudBlockBlob

[FunctionName("WriteExcelToBlob")]
public async Task Run(
    [TimerTrigger("*/30 * * * * *")] TimerInfo timer,
    [Blob("excelFiles", FileAccess.Write, Connection = "Storage")] CloudBlobContainer blobContainer,
    ILogger log
)
{
    var fileNameSuffix = DateTime.Now.ToString("yyyyMMdd_HHmmss");

    var myCollection = new List<MyObject>();

    var newBlobName = $"myFile_{fileNameSuffix}.xlsx";
    var newBlob = blobContainer.GetBlockBlobReference(newBlobName);

    using (var excel = new ExcelPackage())
    {
        var worksheet = excel.Workbook.Worksheets.Add("My Worksheet");
        worksheet.Cells.LoadFromCollection(myCollection);

        using (var stream = await newBlob.OpenWriteAsync())
        {
            excel.SaveAs(stream);
        }
    }
}

Below is the example of using class

using System.ComponentModel;

public class MyObject
{
    [Description("Name")]
    public string Name { get; set; }
    [Description("Home Address")]
    public string HomeAddress { get; set; }
}

The link EPPlus custom header column names will be showing on how to get the headings in output excel for System.ComponentModel.Description

And as DeepDave-MT suggested we cannot read the excel directly from Blob we need to download it and below is the sample code to download it.

string connectionString = "";
            BlobServiceClient blobServiceClient = new BlobServiceClient(connectionString);
            BlobContainerClient containerClient = blobServiceClient.GetBlobContainerClient("test");
            BlobClient blobClient = containerClient.GetBlobClient("sample.xlsx");
            
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            using (var stream = await blobClient.OpenReadAsync(new BlobOpenReadOptions(true)))
            using (ExcelPackage package = new ExcelPackage(stream))
            {
                //get the first worksheet in the workbook
                ExcelWorksheet worksheet = package.Workbook.Worksheets.FirstOrDefault();
                int colCount = worksheet.Dimension.End.Column;  //get Column Count
                int rowCount = worksheet.Dimension.End.Row;     //get row count
                for (int row = 1; row <= rowCount; row++)
                {
                    for (int col = 1; col <= colCount; col++)
                    {
                        Console.WriteLine(" Row:" + row + " column:" + col + " Value:" + worksheet.Cells[row, col].Value.ToString().Trim());
                    }
                }
               
            }




        }

For further info check SO1 and SO2 .

Answer 2

If you wan't to read the file as soon as it stored in Blob, you'll need to use BlobTrigger. Here's how you'd setup the function -

[FunctionName("XlsxFunction")]
public static async Task ReadXlsx(
    [BlobTrigger("mycontainer/{fileName}.xlsx", Connection = "MyBlobConnection")] Stream file, string fileName,
    ILogger log)
{
    //install EPPlus nuget
    //using OfficeOpenXml; 
    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
    using (var package = new ExcelPackage(file))
    {
        var worksheet = package.Workbook.Worksheets[0];
        
        for(int row = 0; row < worksheet.Dimension.End.Row; row++)
        {
            for(int col = 0; col < worksheet.Dimension.End.Column; col++)
            {
                //process things;
            }
        }

        //final thing here & save;
    }
}

I've never used EPPlus, but this is how you can start reading the file using the library.

Note:

1. To avoid reading unnecessary non-excel file, you can see that I've included the .xlsx extension in the BlobTrigger.
2. You'll require EPPlus License to use the library for commercial purposes. Addition to this point, please be aware that using the library within (only for internal purposes) a commercial company also requires a commercial license as well. For more info on EPPlus License - https://www.epplussoftware.com/en/LicenseOverview/LicenseFAQ

And you can follow the readme here on different ways to add the license - https://github.com/EPPlusSoftware/EPPlus

Finally,

In the BlobTrigger, I've added the Connection="MyBlobConnection", which means I need to include this key in the local.settings.json (& the application environment configuration after the Function is published to Azure).

This will look something like this -

{
    "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "UseDevelopmentStorage=true",
    "MyBlobConnection": "UseDevelopmentStorage=true",
    "FUNCTIONS_WORKER_RUNTIME": "dotnet"
  }
}

From the above, note that you can use "AzureWebJobsStorage" directly rather than a custom "MyBlobConnection". This depends on your use case. Your blob connection string can be found in the Azure Storage Service in Azure Portal. For more info - Connection String to an Azure Cloud Storage Account

Additionally, note that I've captured the "fileName" with the BlobTrigger. You can use this to make sure not to run the code if the file name is not what you expected.

e.g. you can add this condition at the start of the function method -

if(!fileName.Contains("accounts")) return;
// continue the processing of data;