Hello I am exporting an excel file directly from the database and also mailing to the email address. but it executes till the end and Stops at Respone.End() Method it is not solved by removing Response.clear or creating file at the destination folder.
Here is the code written to export and Email Excel FIle using closed XML.
protected void ExportExcel(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection(data_string))
{
System.Diagnostics.Debug.WriteLine(@search_sql_query);
using (SqlCommand cmd = new SqlCommand(@search_sql_query))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
try {
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt, "Customers");
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=SqlExport.xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
byte[] bytes = MyMemoryStream.ToArray();
using (MailMessage mm = new MailMessage("xyz@xyz.com", "xyz@xyz.com"))
{
mm.Subject = "REPORT";
mm.Body = "Please Find the Attachment";
//Add Byte array as Attachment.
mm.Attachments.Add(new Attachment(new MemoryStream(bytes), "SqlExport.xlsx"));
mm.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Host = "smtp.office365.com";
smtp.EnableSsl = true;
System.Net.NetworkCredential credentials = new System.Net.NetworkCredential();
credentials.UserName = "xyz@xyz.com";
credentials.Password = "abcxyz";
smtp.UseDefaultCredentials = true;
smtp.Credentials = credentials;
smtp.Port = 587;
smtp.Send(mm);
}
Response.Flush();
Response.End();
MyMemoryStream.Close();
}
}
}
}catch(Exception exee)
{
System.Diagnostics.Debug.WriteLine(exee.ToString());
}
}
}
}
}