C# Azure File Storage CloudFile.OpenWrite issue with OpenXml.SpreadsheetDocument...need FileMode and FileAccess options?

Question

I'm working with DocumentFormat.OpenXml.SpreadsheetDocument and opening a template of an Excel document, writing to it and saving it.

It works like charm from a normal File stream:

using (var documentStream = System.IO.File.Open("--somePath--", FileMode.Open, FileAccess.ReadWrite))
{
    using (var document = SpreadsheetDocument.Open(documentStream, true))
    {
        // do something
    }
}

Notice the SpreadsheetDocument.Open

Now, I'm rewriting this application to Azure, and using Azure storage and it's .NET File library in "WindowsAzure.Storage" package.

It works like a charm, all up to a point where I want to fill the same excel files in Azure.

using (var documentStream = _GetRootDirectoryOfAccount().GetFileReference("--someRelativePath--").OpenWrite(null))
{
    using (var document = SpreadsheetDocument.Open(documentStream, true))
    {
        // do something
    }
}

The first part "_GetRootDirectoryOfAccount().GetFileReference" works 100%, then OpenWrite(null) really opens a Stream.

However, when that Stream is pushed towards the Spreadsheet:

SpreadsheetDocument.Open(documentStream, true)

It breaks with:

System.IO.IOException: 'Cannot open package because FileMode or FileAccess value is not valid for the stream.'

And it is because on the Stream the settings are not set:

System.IO.File.Open("--somePath--", FileMode.Open, FileAccess.ReadWrite)

Does anyone know how to get around this? Or a solution?

Please :)

Answer 1

Does anyone know how to get around this? Or a solution?

The return type of _GetRootDirectoryOfAccount().GetFileReference("--someRelativePath--").OpenWrite(null)) is CloudFileStream `

It seems that CloudFileStream is not supported by SpreadsheetDocument.Open().

Please have a try to use the following code,it works correctly on my side. After update the content, we could use file.UploadFromFile() or file.UploadFromStream() to upload the file.;

var file = _GetRootDirectoryOfAccount().GetFileReference("--someRelativePath--");
var memoryStream = new MemoryStream();
file.DownloadToStream(memoryStream);
using (var document = SpreadsheetDocument.Open(memoryStream, true))
{
  // do something
}

The following is my demo code.

var connectionString = "DefaultEndpointsProtocol=https;AccountName=accountName;AccountKey=xxxxx;EndpointSuffix=core.windows.net";
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(connectionString);
CloudFileClient fileClient = storageAccount.CreateCloudFileClient();

// Get a reference to the file share we created previously.
CloudFileShare share = fileClient.GetShareReference("test"); //share name

if (share.Exists())
{
   // Get a reference to the root directory for the share.
   CloudFileDirectory rootDir = share.GetRootDirectoryReference();

   // Get a reference to the directory we created previously.
   CloudFileDirectory sampleDir = rootDir.GetDirectoryReference("custom"); 
   // Ensure that the directory exists.
   if (sampleDir.Exists())
   {
       // Get a reference to the file we created previously.
       var file = sampleDir.GetFileReference("OpenXMl.xlsx"); //file name

       // Ensure that the file exists.
        if (file.Exists())
        {
            // Write the contents of the file to the console window.
            Console.WriteLine(file.DownloadTextAsync().Result);
            var memoryStream = new MemoryStream();
            file.DownloadToStream(memoryStream);
            using (var document = SpreadsheetDocument.Open(memoryStream, true))
            {
               // do something
            }
        }
     }

}