2

I'm trying to generate an Excel spreadsheet dynamically, using OpenXMLPowerTools v4.5.3.2, DocumentFormat.OpenXML v2.9.1, called from a ASP.Net Core web app.

I've verified that able to generate the spreadsheet OK.

The problem is that I need to generate the spreadsheet ... and return it as a MEMORY STREAM (so the ASP.Net Core web controller

HCExcelReport.cs:

   class HCExcelReport
    {
        protected SpreadsheetDocument doc;
        protected MemorySpreadsheet ms;
        protected string tmpFile = System.IO.Path.GetTempFileName();

        public MemoryStream Generate(List<CandidateRecords> candidateRecords)
        {
            MemoryStream memoryStream = null;
            using (OpenXmlMemoryStreamDocument streamDoc = OpenXmlMemoryStreamDocument.CreateSpreadsheetDocument())
            {
                doc = streamDoc.GetSpreadsheetDocument();
                ms = new MemorySpreadsheet();

                ...
                // Save to disk=
                doc.SaveAs(tmpFile);  // <-- Successfully writes .. but file remains open
                doc.Dispose();   // <-- file *STILL* remains open
            }

            // Copy to memory stream
            using (FileStream fileStream = new FileStream(tmpFile, FileMode.Open, FileAccess.Read))  // <-- Exception: the file is in use by another process!!!!
            {
                memoryStream = new MemoryStream();
                fileStream.CopyTo(memoryStream);
                memoryStream.Position = 0;
            }
            return memoryStream;

I've tried countless things ...

... But if I use PowerTools, I don't see any alternatives to doc.SaveAs()...

... And if I use doc.SaveAs(), the file seems to remain "in use" until my web app exits.

Q: Any suggestions?

FoggyDay
  • 11,962
  • 4
  • 34
  • 48

1 Answers1

1

Please look at this post How to copy a file while it is being used by another process

// Copy to memory stream
using (FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
    memoryStream  = new MemoryStream();
    fs.CopyTo(memoryStream );
    fs.Close();
}
return memoryStream;

regards marcel

marcelloman
  • 26
  • 2
  • 1
    Thank you, but I wound up giving up on OpenXMLPowerTools. It shouldn't have unnecessarily left the file "locked", and I shouldn't have to go to "extreme measures" (like using Volume Shadow Copy Service (VSS), as cited in your link). Your solution (fs.CopyTo()) is a lot less drastic ... but it still shouldn't be necessary. Thank you anyway. And let me "accept" your reply :) – FoggyDay Jul 21 '20 at 21:14