EPPPLUS EXCEL AS AN EMAIL ATTACHMENT

Mar 17, 2013 at 11:12 AM
Hi I am using EPPPlus to return a excel file and also to send the same file as an attachment. I could open the excel file and also got attachement but when I open the email attachment I am getting error message saying File is corrupted. Please look at the belwo code and suggest me changes.

MemoryStream outputStream = new MemoryStream();
using (ExcelPackage pck = new ExcelPackage(outputStream))
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Demo");
ws.Cells["A1"].LoadFromDataTable(tbl, true);
using (ExcelRange rng = ws.Cells[1, 1, 1, tbl.Columns.Count])
{
rng.Style.Font.Bold = true; rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189));
rng.Style.Font.Color.SetColor(Color.White);
}
using (ExcelRange col = ws.Cells[2, 1, 2 + tbl.Rows.Count, 1])
{ col.Style.Numberformat.Format = "#,##0.00"; col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
} MailMessage mail = null; try
{
mail = new MailMessage();
mail.From = new MailAddress("@@@@@@.com", "SystemMail");
mail.To.Add(new MailAddress(emailAddress));
mail.Subject = "Store Finder Geocoding";
mail.IsBodyHtml = true;
string message = "";
message += ".LLHere is the attachment with list of stores and geocode values you requested." + "</br>";
mail.Body = message; outputStream.Position = 0;
Attachment attachment = new Attachment(outputStream, "Geocoding.xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
mail.Attachments.Add(attachment);
mail.SubjectEncoding = System.Text.Encoding.UTF8;
mail.BodyEncoding = System.Text.Encoding.UTF8;
SmtpClient client = new SmtpClient("localhost"); client.Send(mail);
}
catch (Exception ex) { throw ex; }
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", String.Format(CultureInfo.InvariantCulture, "attachment; filename={0}", "geo.xlsx"));
Response.BinaryWrite(pck.GetAsByteArray());
Response.End();
Oct 9, 2014 at 9:17 AM
Edited Oct 9, 2014 at 2:22 PM
hi all
i've the same problem. is there a workaround ?
many thanks
here is my code:
Imports System.IO
Imports System
Imports System.Text
Imports OfficeOpenXml.style
Imports OfficeOpenXml

Module Module3
    Public Sub main()
        Dim oDal As New DAL1

        Dim ds As DataSet = oDal.getIncassi("Piano")

        Dim m As New MemoryStream()

        Dim package As ExcelPackage = New ExcelPackage(m)

        Dim wsheet As ExcelWorksheet = package.Workbook.Worksheets.Add("Sheet 1")

        wsheet.Cells("A1").LoadFromDataTable(ds.Tables(0), True)

        package.Save()
        m.Position = 0

        inviaMail(m)
        package = Nothing
        m = Nothing
    End Sub

    Public Sub inviaMail(ByVal attchMe As System.IO.MemoryStream)
        Dim emailTo As String = "me@me.it"

        Dim ms As New MemoryStream(attchMe.GetBuffer(), 0, attchMe.GetBuffer().Length)

        Dim attch As System.Net.Mail.Attachment = New System.Net.Mail.Attachment(ms, "Piano.xlsx", _
                 "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")

        Dim Mail As New System.Net.Mail.SmtpClient
        Dim message As New System.Net.Mail.MailMessage
        Dim fromAddress As New System.Net.Mail.MailAddress("Sender@me.it")

        message.To.Add(emailTo)

        message.Subject = "Prova XLSX al volo "
        message.Attachments.Add(attch)

        message.Body = "Body della mail"

        message.From = fromAddress

        Mail.Host = "myhst.me.it" 'server_name here
        Try
            Mail.Send(message)
        Catch ex As Exception
            Throw New Exception("Il servizio di posta non e' al momento attivo")
        End Try
        Mail = Nothing
        message = Nothing
        fromAddress = Nothing
        attch.Dispose()
        GC.Collect()

    End Sub
Oct 10, 2014 at 6:03 AM
Solved!!
For those who want to send an email with attached an EPPPlus "on the fly" (not written on disk) using .NET:
imports System.IO
Imports System
Imports System.Text
Imports OfficeOpenXml.style
Imports OfficeOpenXml
' insert code for layer data -------
    Dim  m As New MemoryStream()
    Dim package As ExcelPackage = New ExcelPackage(m)
    Dim wsheet As ExcelWorksheet = package.Workbook.Worksheets.Add("Sheet 1")
' insert code for eppplus xlsx -------
     package.Save()
    m.Position = 0
' insert code for email -----
     Dim attch As System.Net.Mail.Attachment = New System.Net.Mail.Attachment(m, "name.xlsx", _
                     "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
    message.Attachments.Add(attch)
' send mail
DONE.