Send a spreadsheet as an email attachment using SpreadsheetGear

Question

A client asked me to improve one of their applications in order to send an email with an Excel spreadsheet. They seemed to have an old copy of SpreadsheetGear (2009), so I thought it could save time if I use it.

How this happens, SpreadsheetGear does a great job of creating a spreadsheet, but it's hard for me to send it as an email attachment. Just wondered if anyone did this? Theoretically, this should be pretty easy, my current code is as follows:

/// <summary> /// Creates an email attachment based upon the inbound workbook /// </summary> /// <param name="workbook">The workbook</param> /// <returns>an email Attachment</returns> private static Attachment CreateAttachment(string id, IWorkbook workbook) { // Open up a memorystream and save out to it System.IO.MemoryStream memoryStream = new System.IO.MemoryStream(); workbook.SaveToStream(memoryStream, SpreadsheetGear.FileFormat.OpenXMLWorkbook); return new Attachment(memoryStream, id + ".xls", "application/vnd.ms-excel"); } 

where the book is a fully populated worksheet. After that, the Attachment object is placed in the System.Net.Mail.MailMessage object using

 MailMessage.Attachments.Add(attachment); 

What I see is:

• the letter is sent normally, complete with the attachment of the correct name
• however, the attachment is empty

In dev environment, I put debug code there along the lines

 workbook.SaveAs("c:\\test.xls", SpreadsheetGear.FileFormat.OpenXMLWorkbook); 

which gave the desired spreadsheet as a file. But, obviously, since I ultimately just send it electronically, I would also not write to disk as soon as possible. Any pointers to where I got it wrong?

Thanks Pete

(I must finally add that updating to the latest SpreadsheetGear is not an option, its either this approach or does it manually! And the environment is VS2008, .net 3.5)

Answer 1

I tried using the code in your question. He worked for me with two small modifications.

1) Since you are saving the OpenXMLWorkbook format, I changed the extension to .xlsx instead of .xls.

2) After saving the workbook.SaveToStream method, I added the following line of code.

 memoryStream.Seek(0, System.IO.SeekOrigin.Begin); 

This sets the position to the beginning of the stream. When I tried the code without it, I received an error message when I tried to open the Excel attachment.

Without these changes, I got errors, but I never had an empty application. I am using the latest version of SSG, but I do not understand why this should matter in this case.

Answer 2

  private void GenerateFile(List<TrickleFieldInfo> info) { MemoryStream memoryStream = new MemoryStream(); try { var workBook = new XLWorkbook(); var workSheet = workBook.Worksheets.Add("trickle_field"); workSheet.Cell(1, 1).Value = "Debit"; workSheet.Cell(1, 2).Value = "Credit"; workSheet.Cell(1, 3).Value = "Balance"; workSheet.Range(1, 1, 1, 3).AddToNamed("heading"); workSheet.Cell(2, 1).InsertData(info.AsEnumerable()); var titlesStyle = workBook.Style; titlesStyle.Font.Bold = true; titlesStyle.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; titlesStyle.Fill.BackgroundColor = XLColor.Cyan; workBook.NamedRanges.NamedRange("heading").Ranges.Style = titlesStyle; workSheet.Columns().AdjustToContents(); workBook.SaveAs(memoryStream); memoryStream.Seek(0, System.IO.SeekOrigin.Begin); EmailToBackOffice(ref memoryStream); } catch (Exception) { throw; } finally { memoryStream.Dispose(); memoryStream.Close(); } } public void EmailToBackOffice(ref MemoryStream memoryStream) { SmtpClient client = new SmtpClient(); MailMessage msg = new MailMessage(); msg.Body = ""; msg.IsBodyHtml = true; msg.Subject = "<Email subject here>"; msg.Priority = MailPriority.Normal; msg.Sender = new MailAddress(<From address here>); msg.From = new MailAddress(<From address here>); msg.To.Add(new MailAddress(<To address here>)); var attachment = new System.Net.Mail.Attachment(memoryStream, "trickle_field.xlsx", "application/vnd.ms-excel"); msg.Attachments.Add(attachment); client.Host = "Host name here"; client.Credentials = new System.Net.NetworkCredential("username", "password"); client.Send(msg); } 

Answer 3

Step 1.

DLL DownLoad ClosedXML and add a link to the project

step2. Download DocumentFormat.OpenXML and add a link to the Project

using System.IO;

using ClosedXML.Excel;

public void send mail () {

DataTable dt = smp.GetDataTable ("select l.EMPID as [EmpID], e.Emp_Name as [EmpName], l.LDate as [ApplicationDate], l.StartDate as [FromDate], l.EndDate as [ToDate] from LeaveTable ");

XLWorkbook wb = new XLWorkbook ();

var ws = wb.Worksheets.Add (dt, sheetName);

System.IO.MemoryStream memoryStream = new System.IO.MemoryStream (); wb.SaveAs (MemoryStream);

memoryStream.Seek (0, System.IO.SeekOrigin.Begin);

System.Net.MailAttachment attachment = new System.Net.Mail.Attachment (memoryStream, sheetName + ".xlsx", "application / vnd.ms-excel");

SendEmail (To, To1, Subject, Body, attachment);

}

public static bool SendEmail (string pTo, string pTo1, string pSubject, string pBody, application System.Net.MailAttachment) {

  MailMessage myMail = new MailMessage(); ConfigurationSettings.AppSettings["emailid"].ToString(); SmtpSection settings =(SmtpSection) ConfigurationManager.GetSection("system.net/mailSettings/smtp"); var email = settings.Network.UserName; bool ssl = Convert.ToBoolean(ConfigurationManager.AppSettings["ssl"].ToString()); myMail.From = new MailAddress(email); myMail.To.Add(pTo); if (pTo1 != "") { myMail.CC.Add(pTo1); } myMail.Subject = pSubject; myMail.Body = pBody; myMail.Priority = MailPriority.High; myMail.Attachments.Add(attachment); SmtpClient client = new SmtpClient(); client.EnableSsl = ssl; client.DeliveryMethod = System.Net.Mail.SmtpDeliveryMethod.Network; client.Send(myMail); LogMessage.LogMsg = "Mailsent to:" + pTo + myMail.Body; LogMessage.CreateLogFile(); return true; }