Convert entire DataSet to Excel and store it in Azure blob using EPPlus c#

Question

I have used below code for generating excel but how can I pass this excel to blob

                    using (DataSet ds = finalDataSetOutput)
                    {
                        if (ds != null && ds.Tables.Count > 0)
                        {
                            using (ExcelPackage pck = new ExcelPackage())
                            {
                          foreach (DataTable dt in ds.Tables)
                            {
                                    ExcelWorksheet ws = pck.Workbook.Worksheets.Add(dt.TableName);
                                ws.Cells["A1"].LoadFromDataTable(dt, true);
                                    int i = 1;
                                    foreach (DataColumn dc in dt.Columns)
                                    {
                                        i++;
                                        if (dc.DataType == typeof(decimal))
                                            ws.Column(i).Style.Numberformat.Format = "#0.00";
                                    }

                                }
                                pck.SaveAs(new FileInfo(@"D:\SampleExcels\NewFile.xlsx"));

                            }

                        }
                    }

How to create excel and store in azure blob.

Answer 1

I recently did something similar, although I used an Azure Function to populate my Excel data from a collection rather than a DataSet. The principle should be the same - just make a connection 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);
        }
    }
}

Example class:

using System.ComponentModel;

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

NOTE: Using the System.ComponentModel.Description decorator will allow you to have column headings in the output Excel. (Details here: EPPlus custom header column names)