Sending an email application to memory using OpenXML

Question

I have an Excel file created using OpenXML 2 and I want to send it as an email attachment. eg.

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

Saving a table in a temporary file with

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

works great. But the attempt to send the stream directly as the email attachment failed - 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"); 

Does anyone know how to do this?

Answer 1

Well, I got this job, albeit with some effort. To create a 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"; 

In addition, I found that mine weren’t sent right after I created them, and the reason for this is that “standalone = yes” was not added to the xml declaration of all pages, so in my AddParts function after adding the parts, I passed them to 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

Answer 2

do the following:

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

Apparently the memory stream is not cleared or something

Answer 3

For your problem with unreadable content, make sure you save () your books and worksheets and enclose your SpreadsheetDocument in a using statement so that all packages and compressed streams are cleaned, closed, etc.

 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 4

A look at the load: maybe the Attachment class expects it to read the current value in the provided stream? If so, you will probably have to “search” back to the beginning of the stream before serving 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");