How to read data from Excel File saved in an blob in azure with EPPlus library

Question

I'm trying to read my excel files saved in my azure storage container like this

  string connectionString = Environment.GetEnvironmentVariable("AZURE_STORAGE_CONNECTION_STRING");          

  BlobServiceClient blobServiceClient = new BlobServiceClient(connectionString);

  BlobContainerClient containerClient = blobServiceClient.GetBlobContainerClient("concursos");

  foreach (BlobItem blobItem in containerClient.GetBlobs())
  {     

    BlobClient blobClient = containerClient.GetBlobClient(blobItem.Name);

    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

    using (var stream=blobClient.OpenRead(new BlobOpenReadOptions(true)))
    using (ExcelPackage package = new ExcelPackage(stream))
    {
     ExcelWorksheet worksheet = package.Workbook.Worksheets.FirstOrDefault();
     int colCount = worksheet.Dimension.End.Column;  
     int rowCount = worksheet.Dimension.End.Row;
     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());
        }
    }

But the sentence

ExcelWorksheet worksheet = package.Workbook.Worksheets.FirstOrDefault();

throws me an error:System.NullReferenceException: 'Object reference not set to an instance of an object.' worksheet was null

I debug an see fine my stream an my package

The excels in blobs are like this one .xls

Any idea, please?

Thanks

Answer 1

The problem was the file extension of the excel files in blobs

Only works fone with .xlsx not with .xls

Thanks

Answer 2

Please check if worksheet is empty .This error occurs if there is empty sheet with empty coumns and rows.

I tried to reproduce the same Initially I tried to read a excel sheet with EPplus , where starting column and rows are filled and not empty and could execute and read successfully using the same code as yours.

Then I removed column1 to be empty and stored in blob and tried to read it and got null reference exception.

The Dimension object of the ExcelWorksheet will be null if the worksheet was just initialized and is empty . And so throws null reference exception, AFAIK , the only way is to check if files are empty or to add content to it before accessing them so that if columns are empty , it would not throw exception.

worksheet.Cells[1, 1].Value = "Some text value";

Same way try to add worksheet, to avoid exception if in case there are no sheets in container blob.

ExcelWorksheet worksheet = new ExcelPackage().Workbook.Worksheets.Add("Sheet1");

This code will not throw an exception since the Dimension object was initialized by adding content to the worksheet.If the loaded ExcelWorksheet already contains data, you will not face this issue.

       ExcelWorksheet worksheet = package.Workbook.Worksheets.First();
      //or ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
                   
    // Add below line to add new sheet , if no sheets are present and returning null exception
//ExcelWorksheet worksheet = new ExcelPackage().Workbook.Worksheets.Add("Sheet1");

    //Add below line to add column and row , if sheet is empty and returning  null exception
       worksheet.Cells[1, 1].Value = " This is the end of worksheet";
                   
              int colCount = worksheet.Dimension.End.Column;
              int rowCount = worksheet.Dimension.End.Row;

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

You can alternatively check if the value is null.

if(worksheet.cells[row,column].value != null)
   {
         //proceed with code
   }