How to add excel sheet from blob , as 2nd sheet to my generated byte stream sheet in c#

Question

I have a sheet in blob storage say : Instructions.xlsx
I have generated a dynamic .xlsx sheet with db values , which i am able to download say : DbRecords.xlsx

I want my Instructions.xlsx as a 2nd sheet to be added to my DbRecords.xlsx in c#

i am expecting a excel sheet to contain both sheets , 1 my generated file from db as well as sheet from blob as sheet 2.

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. — Community, Jan 14 '23 at 14:42
you can do excel operations by using Microsoft.Office.Interop.Excel — Rajesh Mopati, Jan 16 '23 at 02:57

score 0 · Answer 1 · answered Jan 16 '23 at 11:08

After reproducing from my end, I could able to achieve your requirement by first downloading the file from storage account using CloudBlockBlob and then I have merged the required files using Microsoft.Office.Interop.Excel. I have taken reference from these 2 threads Thread1 and Thread2 Below is the complete code that worked for me.

using System.IO;
using Microsoft.Office.Interop.Excel;
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Blob;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "<YOUR_STORAGEACCOUNT_CONNECTION_STRING>";

            CloudStorageAccount cloudStorageAccount = CloudStorageAccount.Parse(connectionString);
            CloudBlobClient cloudBlobClient = cloudStorageAccount.CreateCloudBlobClient();

            CloudBlobContainer cloudBlobContainer = cloudBlobClient.GetContainerReference("<CONTAINER_NAME>");
            CloudBlockBlob cloudBlockBlob = cloudBlobContainer.GetBlockBlobReference("<BLOB_NAME>");
           
            Stream file = File.OpenWrite(@"<PATH_IN_YOUR_LOCAL_STORAGE>" + "Instructions.xlsx");

            cloudBlockBlob.DownloadToStreamAsync(file);
            file.Close();

            Application app = new Application();
            app.Visible = true;
            Workbook workbook1 = app.Workbooks.Add(@"<Path_to_Instructions.xlsx>");
            Workbook workbook2 = app.Workbooks.Add(@"<Path_to_DbRecords.xlsx>");
            for (int i = 2; i <= app.Workbooks.Count; i++)
            {
                for (int j = 1; j <= app.Workbooks[i].Worksheets.Count; j++)
                {
                    Worksheet ws = (Worksheet)app.Workbooks[i].Worksheets[j];
                    ws.Copy(app.Workbooks[1].Worksheets[1]);
                }
            }
            app.Workbooks[1].SaveCopyAs(@"final.xlsx");
            workbook1.Close(0);
            workbook2.Close(0);
            app.Quit();
        }
    }
}

How to add excel sheet from blob , as 2nd sheet to my generated byte stream sheet in c#

1 Answers1