Click here to Skip to main content
15,886,026 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi..
My prob is that I am generating some data from database and keeping it in data table.
After that i intend to write it into excel file and download it. it works fine.
My boss wants me to add a functionality where now a user can mail himself the data instead of downloading it. I am using same code snippet for genrating xcel file and attaching it in mail. there are two probs i am getting
1. The attachment comes empty excel file.
2. after mail goes it still opens a dialogue box for downloading the file which i dnt want now.
Here is my code,Plzzz help me
<pre lang="c#">
int i_result = 0;
        DataTable dtResult = Fetching_Query_Data_In_Datatable(strDatatype, from, to, strHscode, strComb, strProd);

        StringBuilder dataToExport = new StringBuilder();

        dataToExport.Append("<table style=border:solid 1px blue;>");
        dataToExport.Append("<tr>");

        foreach (DataColumn dCol in dtResult.Columns)
        {
            dataToExport.Append("<td style=border:solid 1px blue;>");
            dataToExport.Append(HttpContext.Current.Server.HtmlEncode(dCol.ColumnName));
            dataToExport.Append("</td>");
        }

        dataToExport.Append("</tr>");

        foreach (DataRow dRow in dtResult.Rows)
        {
            dataToExport.Append("<tr>");
            foreach (object obj in dRow.ItemArray)
            {
                dataToExport.Append("<td style=border:solid 1px blue;>");
                dataToExport.Append(HttpContext.Current.Server.HtmlEncode(obj.ToString()));
                dataToExport.Append("</td>");
            }
            dataToExport.Append("</tr>");
        }

        dataToExport.Append("</table>");

        if (!string.IsNullOrEmpty(dataToExport.ToString()))
        {
            string strSub = "Offline data";

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=offline_data.xls");
            HttpContext.Current.Response.Charset = "";

            HttpContext.Current.Response.ContentType = "application/ms-excel";
            System.IO.StringWriter stringWrite = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
           

            HttpContext.Current.Response.Write(stringWrite.ToString());
            


            SmtpClient mailcleint = new SmtpClient();


            string SenderId = EmailTo.ToString().Trim();
            if (SenderId != "")
            {
                string SenderName = "Team Cybex";

                MailAddress MailRecepient = new MailAddress(EmailTo.ToString().Trim());
                MailAddress MailSender = new MailAddress(SenderId, SenderName);
                MailMessage MailMsg = new MailMessage(MailSender, MailRecepient);

                /******************** SEND Email TO Users **************************************/

                MailMsg.Body = "Hello";
                MailMsg.DeliveryNotificationOptions = DeliveryNotificationOptions.OnFailure;
                MailMsg.IsBodyHtml = true;
                var a = System.Net.Mail.Attachment.CreateAttachmentFromString(stringWrite.ToString(), "application/vnd.xls");
                MailMsg.Attachments.Add(a);
                MailMsg.Priority = MailPriority.Normal;
                MailMsg.Subject = strSub.ToString();
                SmtpClient mailclient = new SmtpClient();
                mailclient.Credentials = new System.Net.NetworkCredential("myID", "my password");
                mailclient.Host = "xxxx.xx.xx.xx";

                mailclient.Send(MailMsg);
                i_result = 1;
            }

        }
        //HttpContext.Current.Response.End();
        return i_result;
    }
:-
Posted

Please read the DataRow.ItemArray[^] which will show you how to fetch data from
the ItemArray and

Please revisit your

C#
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=offline_data.xls");
HttpContext.Current.Response.Charset = "";

HttpContext.Current.Response.ContentType = "application/ms-excel";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);


HttpContext.Current.Response.Write(stringWrite.ToString())


as it looks to me no need as your sending a email.

Hope it helps :)

As you requested, please the comment next to code,

C#
private void ATestMethod()
    {

        int i_result = 0;

        //IMP: Make sure you are getting data into dtResult
        DataTable dtResult = Fetching_Query_Data_In_Datatable(strDatatype, from, to, strHscode, strComb, strProd);


        StringBuilder dataToExport = new StringBuilder();

        dataToExport.Append("<table style=border:solid 1px blue;>");
        dataToExport.Append("<tr>");

        foreach (DataColumn dCol in dtResult.Columns)
        {
            dataToExport.Append("<td style=border:solid 1px blue;>");
            dataToExport.Append(HttpContext.Current.Server.HtmlEncode(dCol.ColumnName));
            dataToExport.Append("</td>");
        }

        dataToExport.Append("</tr>");

        foreach (DataRow dRow in dtResult.Rows)
        {
            dataToExport.Append("<tr>");
            foreach (object obj in dRow.ItemArray)
            {
                dataToExport.Append("<td style=border:solid 1px blue;>");
                dataToExport.Append(HttpContext.Current.Server.HtmlEncode(obj.ToString()));
                dataToExport.Append("</td>");
            }
            dataToExport.Append("</tr>");
        }

        dataToExport.Append("</table>");

        if (!string.IsNullOrEmpty(dataToExport.ToString()))
        {
            string strSub = "Offline data";

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=offline_data.xls");
            HttpContext.Current.Response.Charset = "";

            HttpContext.Current.Response.ContentType = "application/ms-excel";

            System.IO.StringWriter stringWrite = new System.IO.StringWriter(dataToExport); //Added dataToExport

            System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

            HttpContext.Current.Response.Write(stringWrite.ToString());
            SmtpClient mailcleint = new SmtpClient();
            string SenderId = EmailTo.ToString().Trim();
            if (SenderId != "")
            {
                string SenderName = "Team Cybex";

                MailAddress MailRecepient = new MailAddress(EmailTo.ToString().Trim());
                MailAddress MailSender = new MailAddress(SenderId, SenderName);
                MailMessage MailMsg = new MailMessage(MailSender, MailRecepient);

                /******************** SEND Email TO Users **************************************/

                MailMsg.Body = "Hello";
                MailMsg.DeliveryNotificationOptions = DeliveryNotificationOptions.OnFailure;
                MailMsg.IsBodyHtml = true;
                var a = System.Net.Mail.Attachment.CreateAttachmentFromString(stringWrite.ToString(), "application/vnd.xls");
                MailMsg.Attachments.Add(a);
                MailMsg.Priority = MailPriority.Normal;
                MailMsg.Subject = strSub.ToString();
                SmtpClient mailclient = new SmtpClient();
                mailclient.Credentials = new System.Net.NetworkCredential("myID", "my password");
                mailclient.Host = "xxxx.xx.xx.xx";

                mailclient.Send(MailMsg);
                i_result = 1;
            }
            HttpContext.Current.Response.End(); //Need this line of code.

        }


Should work (need to provide valid email address stuff):)
 
Share this answer
 
Comments
Sandeep Mewara 8-May-12 11:34am    
Comment from OP:
I am still not getting still what both u mean. Please can u correct the error in my code and send it to me.. Please
Mohammad A Rahman 9-May-12 2:42am    
Thank you Sandeep, Hopefully problem will be fixed if OP make changes as I mentioned :)
Taresh Uppal 9-May-12 0:26am    
I am not getting you...where I am goin wrong... means why my excel attachment is empty ?
Mohammad A Rahman 9-May-12 2:02am    
I had quick test your code looks like problem in the System.IO.StringWriter stringWrite = new System.IO.StringWriter();. The problem you havn't pass any string or stringbuilder object as input or you havn't set the string for it. For quick fix try with this line System.IO.StringWriter stringWrite = new System.IO.StringWriter(dataToExport);

Hope it helps :)
Mohammad A Rahman 9-May-12 2:41am    
See the update plz.
The attachment will be empty: you carefully build up dataToExport,check it's not empty, and then ignore it. Try using that as the attachment in some form!
 
Share this answer
 
Comments
Sandeep Mewara 8-May-12 11:34am    
Comment from OP:
I am still not getting still what both u mean. Please can u correct the error in my code and send it to me.. Please

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900