NPOI writing XLS file converting to Azure Blob

Question

I'm trying to convert current application that uses NPOI for creating xls document on the server to Azure hosted application. I have little experience with NPOI and Azure so 2 strikes right there. I have the app uploading the xls to Blob container however it is always blank (9 bytes). From what I understand NPOI uses filestream to write to the file so I just changed that to write to the blob container.

Here is what i think are the relevant portions:

   internal void GenerateExcel(DataSet ds, int QuoteID, string ReportFileName)
{
    string ExcelFileName = string.Format("{0}_{1}.xls",ReportFileName,QuoteID);       

    try
    {


        //these 2 strings will get deleted but left here for now to run side by side at the moment

        string ReportDirectoryPath = HttpContext.Current.Server.MapPath(".") + "\\Reports";
        if (!Directory.Exists(ReportDirectoryPath))
        {
            Directory.CreateDirectory(ReportDirectoryPath);
        }

        string ExcelReportFullPath = ReportDirectoryPath + "\\" + ExcelFileName;

        if (File.Exists(ExcelReportFullPath))
        {
            File.Delete(ExcelReportFullPath);
        }

        // Create a new workbook.
        var workbook = new HSSFWorkbook();

        //Rest of the NPOI XLS rows cells etc. etc. all works fine when writing to disk////////////////

        // Retrieve storage account from connection string.
        CloudStorageAccount storageAccount = CloudStorageAccount.Parse(CloudConfigurationManager.GetSetting("StorageConnectionString"));

        // Create the blob client.
        CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();

        // Retrieve a reference to a container. 
        CloudBlobContainer container = blobClient.GetContainerReference("pricingappreports");
        // Create the container if it doesn't already exist.
        if (container.CreateIfNotExists())
        {
            container.SetPermissions(new BlobContainerPermissions { PublicAccess = BlobContainerPublicAccessType.Blob });
        }
        // Retrieve reference to a blob with the same name.
        CloudBlockBlob blockBlob = container.GetBlockBlobReference(ExcelFileName);



        // Write the output to a file on the server
        String file = ExcelReportFullPath;
        using (FileStream fs = new FileStream(file, FileMode.Create))
        {
            workbook.Write(fs);
            fs.Close();
        }
        // Write the output to a file on Azure Storage
        String Blobfile = ExcelFileName;
        using (FileStream fs = new FileStream(Blobfile, FileMode.Create))
        {
            workbook.Write(fs);
            blockBlob.UploadFromStream(fs);
            fs.Close();
        }                
    }

I'm uploading to the Blob and the file exists, why doesn't the data get written to the xls? Any help would be appreciated.

Update: I think I found the problem. Doesn't look like you can write to a file in Blob Storage. Found this Blog which pretty much answers my questions: it doesn't use NPOI but the concept is the same. http://debugmode.net/2011/08/28/creating-and-updating-excel-file-in-windows-azure-web-role-using-open-xml-sdk/ Thanks

score 0 · Answer 1 · answered Jun 14 '14 at 00:59

Can you install fiddler and check the request and the response packets? You may also need to seek back to 0 between two writes . So the correct code here could be to add the below before trying to write the stream to blob.

workbook.Write(fs);
fs.Seek(0, SeekOrigin.Begin);
blockBlob.UploadFromStream(fs);
fs.Close();

I also noticed that you are using String Blobfile = ExcelFileName instead of String Blobfile = ExcelReportFullPath.

NPOI writing XLS file converting to Azure Blob

1 Answers1