How I use stream for create Excel Document and then send it to mail? - .Net - Development Tools

Hi all,

I need to create a Excel document with OpenXml, but I can save on disk.

Then I need to send the document for Mail.

The genetarion of document and the sending of documenti work perfectly with a path string.

I try to use stream but I have some problems, please see code below (in red the text of interest):

using (MemoryStream stream = new MemoryStream())

                    using (SpreadsheetDocument document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
                                {
                                   WorkbookPart workbookPart1 = document.AddWorkbookPart();
                                   GenerateWorkbookPart1Content(workbookPart1);

                                   WorksheetPart worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>("rId1");
                                   GenerateWorksheetPart1Content(worksheetPart1, columns, dvListViewData, dtFull, site, list);

                                   TableDefinitionPart tableDefinitionPart1 = worksheetPart1.AddNewPart<TableDefinitionPart>("rId14");
                                   GenerateTableDefinitionPart1Content(tableDefinitionPart1, columns, dvListViewData, list);

{

Hyperlinks hyperlinks1 = new Hyperlinks();

                                   for (int i = 0; i < dvListViewData.Count; i++)
                                   {
                                       for (int j = 0; j < columns.Length; j++)
                                       {
                                           if (columns[j].ToString().Equals("LinkFilename"))
                                           {
                                               System.Web.UI.WebControls.HyperLink hl = new HyperLink();
                                               hl.Text = dvListViewData[i][columns[j].ToString()].ToString();
                                               hl.NavigateUrl = site.Url + "/_layouts/DocSetHome.aspx?id=/moc/" + dvListViewData[i]["parentList"] + "/" + hl.Text;
                                               Uri indirizzo = new Uri(hl.NavigateUrl.ToString());

                                               worksheetPart1.AddHyperlinkRelationship(new System.Uri(indirizzo.AbsoluteUri, System.UriKind.Absolute), true, "rId" + (i + 1).ToString());
                                           }
                                       }
                                   }
                                }

                                SmtpClient smtpClient = new SmtpClient();
                                smtpClient.Host = site.WebApplication.OutboundMailServiceInstance.Server.Address;
                                string from = site.WebApplication.OutboundMailSenderAddress;

                                MailMessage mailMessage = new MailMessage();
                                mailMessage.From = new MailAddress(from);
                                mailMessage.Subject = subject;

                                Attachment Att = new Attachment(stream, "prova.xlsx");
                                mailMessage.Attachments.Add(Att);

                                string[] mailToArray = mailTo.Split(';');
                                for (int i = 0; i < mailToArray.Length; i++)
                                {
                                    mailMessage.To.Add(mailToArray[i]);
                                }

smtpClient.Send(mailMessage);
}

thanks for your time ;)

The problem is stream object is still in hold by SpreadsheetDocument. so, the system will throw error when it is used in Attachment.

Try using mail sending,outside SpreadsheetDocument.

using(MemoryStream mStream =....)

{

using (SpreadsheetDocument document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))

{

........

}

SmtpClient smtpClient = new SmtpClient();

smtpClient.Host = site.WebApplication.OutboundMailServiceInstance.Server.Address;

string from = site.WebApplication.OutboundMailSenderAddress;

MailMessage mailMessage = new MailMessage();

mailMessage.From = new MailAddress(from);

mailMessage.Subject = subject;

Attachment Att = new Attachment(stream, "prova.xlsx");

mailMessage.Attachments.Add(Att);

string[] mailToArray = mailTo.Split(';');

for (int i = 0; i < mailToArray.Length; i++)

{

mailMessage.To.Add(mailToArray[i]);

}

smtpClient.Send(mailMessage);

}