Sending an email attachment in memory using OpenXML

Question

I've got an Excel file that's built using OpenXML 2 and I want to send it as an email attachment. e.g.

    System.IO.MemoryStream stream = new System.IO.MemoryStream();
    SpreadsheetDocument package = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
    AddParts(package); //created using document reflector

Saving the spreadsheet to a temp file using

stream.WriteTo(new System.IO.FileStream(@"c:\test.xlsx", System.IO.FileMode.Create));

works fine. But trying to send the stream directly as an email attachment fails - just get an empty file attached to the email when I do

System.Net.Mail.Attachment file = new System.Net.Mail.Attachment(stream, "MobileBill.xlsx", "application/vnd.ms-excel");

Anbody know how to do this?

Answer 1

Thinking out load: could it be, that the Attachment class expects to read from the current possition in the provided stream? If this is the case, you would probably have to "seek" back to the beginning of the stream, before feeding it to the Attachment constructor:

AddParts(package); //created using document reflector
stream.Seek(0, SeekOrigin.Begin);
System.Net.Mail.Attachment file = new System.Net.Mail.Attachment(stream, "MobileBill.xlsx", "application/vnd.ms-excel");

Answer 2

For your "content unreadable" problem, make sure to Save() your Workbooks and Worksheets and enclose your SpreadsheetDocument in a using statement to ensure all packages and zipped streams are flushed, closed and so on.

System.IO.MemoryStream stream = new System.IO.MemoryStream();
using (SpreadsheetDocument package = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook)))
{
    AddParts(package); 
    //Save if AddParts hasn't done it
}
System.Net.Mail.Attachment file =  ...

Answer 3

Ok, I got this working, though through some effort. To create the stream:

MemoryStream stream = new MemoryStream();

using (SpreadsheetDocument package = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
  Excel.CreateSpreadsheet(package, Excel_Methods.CreateSpotQuoteOut(), true);
}

stream.Seek(0, SeekOrigin.Begin);

System.Net.Mail.Attachment attach = new System.Net.Mail.Attachment(stream, "spreadsheet.xlsx");

attach.ContentDisposition.CreationDate = DateTime.Now;
attach.ContentDisposition.ModificationDate = DateTime.Now;
attach.ContentDisposition.Inline = false;
attach.ContentDisposition.Size = stream.Length;
attach.ContentType.MediaType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; 

Also, I found that mine were not being sent right after I created them, and the reason for that is "standalone=yes" was not being added to the xml declaration of all the pages, so in my AddParts function, after adding the parts, I passed them into this function:

private static void AddXMLStandalone(OpenXmlPart part)
        {
            System.IO.StreamWriter writer = new System.IO.StreamWriter(part.GetStream());

            XmlDocument doc = new XmlDocument();
            doc.Load(part.GetStream());

            doc.InnerXml = doc.InnerXml.Substring(doc.InnerXml.IndexOf("?>") + 2);
            doc.InnerXml = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>" + doc.InnerXml;

            part.GetStream().SetLength(doc.InnerXml.Length);
            doc.Save(writer);
            writer.Flush();
            writer.Close();            
        }

Good luck!