How to read an excel file stored in Azure Storage as a Blob File

Question

I want to read an excel file stored in an Azure Storage container as a Blob using Epplus package in C#. I have tried to do something with this code.

string uri = blob.Uri.AbsoluteUri;
FileInfo fileInfo = new FileInfo(uri);
ExcelPackage p = new ExcelPackage(fileInfo);
ExcelWorksheet ws = p.Workbook.Worksheets[1];
Console.WriteLine(ws.Cells[1,1].Value.ToString());

It's throwing an error?

Answer 1

We cannot use Azure blob url to initialize the FileInfo object, it will throw the error System.NotSupportedException: 'The given path's format is not supported.'.

So if you want to read excel file stored in Azure blob, we need to download it at first.

For example

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());
            }
        }
    }
}