Send spreadsheet as email attachment using SpreadsheetGear

Question

A client has asked me to enhance one of their apps in order to send out an email containing an excel spreadsheet. They happened to have an old copy of SpreadsheetGear (2009) lying around, so I thought it might save time if I used it.

As it happens SpreadsheetGear was great to actually create the spreadsheet, but I'm having trouble sending it as an email attachment. Just wondered if anyone had done this? In theory it 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 workbook is a fully-populated SpreadsheetGear workbook. After this, the Attachment object is pushed into a System.Net.Mail.MailMessage object using

MailMessage.Attachments.Add(attachment);

What I am seeing is:

the email gets sent ok, complete with an attachment of the correct name
however the attachment is empty

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

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

which yielded the desired spreadsheet, as a file. But obviously since I am ultimately just sending this electronically I'd just as soon avoid writing to the disk at all, if I can. Any pointers on where I've gone wrong?

Thanks, Pete

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

score 4 · Accepted Answer · answered Nov 19 '12 at 21:40

4

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

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

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

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

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

Without these modifications, I got errors, but I never got an empty attachment. I am using the most recent version of SSG, but I don't see why that should matter in this case.

answered Nov 19 '12 at 21:40

Daniel

5,602
4
33
36

yes thanks Daniel. I started thinking of it in terms of a stream issue rather than SpreadsheetGear issue, and I tried adding a line of code which resets the position in the stream. I'm just in the process of getting that tested now (it takes a while - I need to promote it as far as their UAT environment before I stop running into permissions issues with SMTP servers!) Had not spotted the extension but what you say makes sense. – PeteH Nov 20 '12 at 09:26
1

setting memoryStream.Position = 0 did the trick. Thanks for your input. – PeteH Nov 20 '12 at 14:52
wow thanks you saved my day, i almost did a whole work around that i really didn't like because this was not working for me! – CMS Jul 26 '16 at 06:51

score -1 · Answer 2 · answered Sep 03 '15 at 11:41

step.1

DownLoad ClosedXML dll and add reference to project

step2. Download DocumentFormat.OpenXML and add reference to 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.Mail.Attachment 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, System.Net.Mail.Attachment attachment) {

            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;

        }

I'm sorry but this answer, however well thought out, does not appear to relate to SpreadsheetGear. If I still had the problem (I don't hence the accepted answer) your answer would not have been helpful. — PeteH, Sep 29 '15 at 10:22

Send spreadsheet as email attachment using SpreadsheetGear

2 Answers2