FileInfo instead of file path want to give blobstorage url

Question

I am using EPPlus to create excel sheet from existing template.

My code uploaded on azure blob storage. which is used by Azure data factory custom activity.

for This activity I need to use template excel file which is in azure blob.

I want to give URL for template file.

var newFile = new FileInfo("Report_Template.xlsx");

Is there any solution or alternative?

Because the scenario is too rare, I am not getting solution from web search.

score 1 · Answer 1 · answered Oct 26 '17 at 15:36

FileInfo is meant for disk files, not URLs. You don't need one to work with EPPlus anyway. As the Web sample shows you can save a package directly to a stream:

ExcelPackage pck = new ExcelPackage();
var ws = pck.Workbook.Worksheets.Add("Sample1");

ws.Cells["A1"].Value = "Sample 1";
ws.Cells["A1"].Style.Font.Bold = true;
var shape = ws.Drawings.AddShape("Shape1", eShapeStyle.Rect);
shape.SetPosition(50, 200);
shape.SetSize(200, 100);
shape.Text = "Sample 1 saves to the Response.OutputStream";

pck.SaveAs(Response.OutputStream);

You can use similar code to write to a blob's stream. You can open a stream to write to a blob with CloudBlockBlob.OpenWrite or the equivalent asynchronous method OpenWriteAsync :

ExcelPackage pck = new ExcelPackage();
var ws = pck.Workbook.Worksheets.Add("Sample1");

// Build the sheet then ...
var blockBlob = container.GetBlockBlobReference("somefile.xlsx");
using(var stream=blockBlob.OpenWrite())
{
    pck.SaveAs(stream);
}

Hey thanks for this! I am currently using the openwrite method however when i save the file i am getting a type mismatch , the only difference here is that i am using excel interop directives instead any ideas? — SernOne, Feb 20 '18 at 21:21
@SemOne that "one difference" is a *huge* difference. Interop means you are starting Excel which doesn't know about streams. It also means that you can't use your code in any server because a) it requires an installation of Excel and b) has to create one instance for every request. If you have a question, post a new question with the code, error and full exception if any — Panagiotis Kanavos, Feb 21 '18 at 08:47

score 1 · Answer 2 · answered Oct 19 '18 at 09:56

Yesterday, I stumbled to this Question when I wanted to load an Excel-Template from a SharePoint-Library, Change the data and save the changes as a new Excel-File back to SharePoint. So, this might answer the base Question: How to open by url not by FileInfo.

I did the following and it works:

public string CreateSheet()
    {
        string siteUrl = SPContext.Current.Site.Url;
        string retval = string.Empty;

        SPSecurity.RunWithElevatedPrivileges(delegate()
        {
            using (SPSite sc = new SPSite(siteUrl))
            {
                using (SPWeb web = sc.OpenWeb())
                {
                    SPFile spFile = web.GetFile("SiteCollectionDocuments/Template.xlsx");
                    Stream stream = spFile.OpenBinaryStream();

                    using (ExcelPackage p = new ExcelPackage(stream))
                    {
                        ExcelWorksheet sheetRoles = p.Workbook.Worksheets["Roles"];
                        sheetRoles.Cells[2, 1].Value = "Today: " + DateTime.Now.ToString("dd.MM.yyyy");

                        byte[] data = p.GetAsByteArray();
                        stream.Close();

                        web.AllowUnsafeUpdates = true;
                        SPDocumentLibrary lib = web.Lists.TryGetList("Site Collection Documents") as SPDocumentLibrary;
                        string fileName = "TheNewFileNameToUse-" + DateTime.Now.ToString("dd.MM.yyyy") + ".xlsx";
                        lib.RootFolder.Files.Add("SiteCollectionDocuments/" + fileName, data, true);

                        retval = siteUrl + "/_layouts/15/WopiFrame.aspx?sourcedoc=/SiteCollectionDocuments/" + fileName + "&action=default";
                    }
                }
            }
        });

        return retval;
    }

As you can see you can create an ExcelPackage from a stream which has been created by getting it from some url. Even if this answer is late, it might helps some others.

score 0 · Answer 3 · answered Jun 12 '21 at 16:25

You can read a blob from Azure storage into a memory stream, and then read it directly. Code example is similar to:

        var storageAccount = CloudStorageAccount.Parse("Connection_String");
       
        string dataBlobPath = "path_to_blob.xlsx";
        var dataBlobUri = new Uri(storageAccount.BlobEndpoint,dataBlobPath);
        var sourceBlob = new CloudBlockBlob(dataBlobUri, storageAccount.Credentials);
        int worksheets;

        using (Stream str = new MemoryStream())
        {
            await sourceBlob.DownloadToStreamAsync(str);
            using (ExcelPackage p = new ExcelPackage(str))
            {
                worksheets = p.Workbook.Worksheets.Count;
            }

        }

FileInfo instead of file path want to give blobstorage url

3 Answers3