Retrieving Blank Excel upon attaching excel in Mail

Question

Im using this code to Send a Mail with excel attachment from gridview, it works fine for the mail part but the excel attachment is always blank i have already debugged the code and sure that the datasource of the Gridview passes the data it needed to the gridview, it seems that im not rendering the gridview to excel right. the whole process is in a for each loop depending on the number of elements inside the foreach..

am i missing some code?

protected void MailButton_Click(object sender, EventArgs e)
{
    List<FOAM> foamList = new List<FOAM>(servs.GetAreaList());
    foreach (FOAM foam in foamList)
    {
        Session["Area"] = foam.ItemAreaCode;
        Session["Principal"] = foam.PrincipalAmount;
        Session["Accountability"] = foam.AccountableAmount;
        Session["Count"] = foam.ItemCount;

        foamdetails.ItemAreaCode = foam.ItemAreaCode;
        FOAMTemplateGridview.DataSource = servs.GetFoamAsOfUnsettledforAOM(foamdetails);
        FOAMTemplateGridview.DataBind();

        StringWriter _writer = new StringWriter();
        HttpContext.Current.Server.Execute("AreaManagersMail.aspx", _writer);

        StringWriter stw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(stw);
        FOAMTemplateGridview.RenderControl(hw);

        MailMessage newMail = new MailMessage();
        newMail.Priority = MailPriority.High;
        newMail.To.Add("[email protected]");
        newMail.Subject = "Unsettled FOAM As of " + DateTime.Today.ToString("MMMM dd, yyyy") + "-A" + foam.ItemAreaCode;

        System.Text.Encoding Enc = System.Text.Encoding.ASCII;
        byte[] mBArray = Enc.GetBytes(stw.ToString());
        System.IO.MemoryStream mAtt = new System.IO.MemoryStream(mBArray, false);

        newMail.Attachments.Add(new Attachment(mAtt, "test.xls"));
        newMail.Body = _writer.ToString();
        newMail.From = new MailAddress("[email protected]");
        newMail.IsBodyHtml = true;
        SmtpClient SmtpSender = new SmtpClient();
        SmtpSender.Port = 25;
        SmtpSender.Host = "MailHost";
        SmtpSender.Send(newMail);

        newMail.Dispose();
    }


}

Answer 1

You are getting blank excel because you are getting bytes from stw which is defined but it has never been given a value.

stw is only a new object with no value that's why you are getting 0 bytes array in mBArray.

And than you assigned mAtt = new System.IO.MemoryStream(mBArray, false);. So as mBArray is empty so is mAtt and you have created file from mAtt which is empty that's why you are getting empty file in email. I guess you need to change this line

byte[] mBArray = Enc.GetBytes(stw.ToString());

to

byte[] mBArray = Enc.GetBytes(_writer.ToString());

Answer 2

Well found the answer myself,

instead of directly rendering the gridview i passed the value of the gridview in a TABLE then the table is the one i used for rendering,

Sample below:

    Table table = new Table();     
    table.Rows.AddAt(3, FOAMTemplateGridview.HeaderRow);

                foreach (GridViewRow row in FOAMTemplateGridview.Rows)
                {
                    int index = 0;
                    table.Rows.Add(row);
                    foreach (TableCell tc in row.Cells)
                    {
                        switch (index)
                        {

                            case 0: tc.HorizontalAlign = HorizontalAlign.Left;
                                break;
                            case 1: tc.HorizontalAlign = HorizontalAlign.Right;
                                break;
                            case 2: tc.HorizontalAlign = HorizontalAlign.Right;
                                break;
                            case 3: tc.HorizontalAlign = HorizontalAlign.Left;
                                break;
                            case 4: tc.HorizontalAlign = HorizontalAlign.Right;
                                break;
                            case 5: tc.HorizontalAlign = HorizontalAlign.Left;
                                break;
                            case 6: tc.HorizontalAlign = HorizontalAlign.Left;
                                break;
                            case 7: tc.HorizontalAlign = HorizontalAlign.Right;
                                break;
                            case 8: tc.HorizontalAlign = HorizontalAlign.Left;
                                break;
                            case 9: tc.HorizontalAlign = HorizontalAlign.Right;
                                break;
                            case 10: tc.HorizontalAlign = HorizontalAlign.Right;
                                break;
                            case 11: tc.HorizontalAlign = HorizontalAlign.Right;
                                break;
                            case 12: tc.HorizontalAlign = HorizontalAlign.Right;
                                break;
                            case 13: tc.HorizontalAlign = HorizontalAlign.Right;
                                break;
                            case 14: tc.HorizontalAlign = HorizontalAlign.Left;
                                break;
                            case 15: tc.HorizontalAlign = HorizontalAlign.Left;
                                break;
                            case 16: tc.HorizontalAlign = HorizontalAlign.Left;
                                break;
                        }
                        tc.Attributes.Add("class", "text");
                        tc.BorderStyle = System.Web.UI.WebControls.BorderStyle.Solid;
                        tc.BorderColor = Color.Black;
                        tc.Font.Name = "Arial";
                        tc.Font.Size = 10;
                        index++;
                    }

                }
            StringWriter stw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(stw);
            hw.WriteLine(@"<style>.text { mso-number-format:\@; } </style>");
            table.RenderControl(hw);

            MailMessage newMail = new MailMessage();
            newMail.Priority = MailPriority.High;
            newMail.To.Add(foam.AomMail);
            newMail.Subject = "Subject";
            System.Text.Encoding Enc = System.Text.Encoding.ASCII;
            byte[] mBArray = Enc.GetBytes(stw.ToString());
            System.IO.MemoryStream mAtt = new System.IO.MemoryStream(mBArray, false);
            newMail.Attachments.Add(new Attachment(mAtt, "sales.xls"));  

Then there you go i can attach the right file upon sending mail rather than the blank.