Email DataTable as Excel Attachment

The following code allows a developer to convert a DataTable into a MemoryStream so that it can be attached to an e-mail. It accepts a DataTable and binds it to a GridView. Next, it renders the GridView into a StringWriter object. To convert a string into a byte array I used the Encoding object and wrote the byte array into the MemoryStream. Attaching the MemoryStream to an email is easy. Read the usage snippet below.

Usage:

DirectExcel excel = new DirectExcel();

System.IO.MemoryStream ms = excel.ExportToStream(myDataTable);

Attachment attachFile = new Attachment(ms, “filename.xls”, “application/vnd.ms-excel”);

MailMessage mail = new MailMessage();

mail.Attachments.Add(attachFile);

 

 

In order for the above snippet to work, you will need to copy the ExportToStream method below and paste it in your DirectExcel Class. If you don’t already have a DirectExcel Class click here to copy it from my previous post.

 

public System.IO.MemoryStream ExportToStream(DataTable objDt)

{

    //remove any columns specified.

    foreach (string colName in ColumnsToRemove)

    {

        objDt.Columns.Remove(colName);

    }

 

    this.DataSource = objDt;

    this.DataBind();

 

    System.IO.StringWriter sw = new System.IO.StringWriter();

    System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(sw);

    this.RenderControl(hw);

    string content = sw.ToString();

    byte[] byteData = Encoding.Default.GetBytes(content);

 

    System.IO.MemoryStream mem = new System.IO.MemoryStream();

    mem.Write(byteData, 0, byteData.Length);

    mem.Flush();

    mem.Position = 0; //reset position to the begining of the stream

    return mem;

} 

Leave a Reply