c# - 如何在 C# MVC 中将生成的 excel 文件作为电子邮件附件发送

Question

我正在做一个 MVC 项目，其中一个页面会生成一个动态变化的 DataTable。我应该做两个功能，

1)生成一个excel文件并使其可供最终用户下载 - 我已经成功完成了。

2) 通过电子邮件将 excel 附件发送给最终用户。 - 我认为它应该是一个简单的，但它看起来很复杂。我不想将 excel 临时保存在服务器中的某个位置，并将其附加到电子邮件中。由于安全限制。 (用户只需单击电子邮件按钮，它应该会到达他的电子邮件)

这是我的代码，它不做任何事情，而不是向我发送一个空的 excel 文件。

            public void EmailCurrMonthSummary()
                    {
                        DataAccess da = new DataAccess();
                        MonthEndSummary mes = new MonthEndSummary();
                        DataTable tempTable = new DataTable();
                        mes.MonthEndSummaryTable = da.GetCurrMonthSummary(); //returns a DataTable                    

                        MailMessage mail = new MailMessage();
                        mail.To.Add("User@xxx.com");
                        mail.From = new MailAddress("Sender@xxx.com");
                        mail.Body = "Hello World";
                        mail.Subject = "Month End Status";
            System.IO.MemoryStream str = DataToExcel(mes.MonthEndSummaryTable,"MonthEndSummary.xls");

            Attachment at = new Attachment(str, "MonthEndSummary.xls");

                        mail.IsBodyHtml = true;

                        SmtpClient smtp = new System.Net.Mail.SmtpClient();
                        smtp.UseDefaultCredentials = true;
                        smtp.Send(mail);
                    }


 public System.IO.MemoryStream DataToExcel(DataTable dt, string filename)
        {
            //StreamWriter sw = new StreamWriter();
            System.IO.StringWriter tw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
            if (dt.Rows.Count > 0)
            {

                DataGrid dgGrid = new DataGrid();
                dgGrid.DataSource = dt;
                dgGrid.DataBind();
                dgGrid.HeaderStyle.Font.Bold = true;
                //Get the HTML for the control.
                dgGrid.RenderControl(hw);
                //Write the HTML back to the browser.
                //Response.ContentType = application/vnd.ms-excel;
                Response.ClearContent();
                Response.Buffer = true;
                Response.ContentType = "application/vnd.ms-excel";
                Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + "");
                Response.ContentEncoding = System.Text.Encoding.Default;

            }
            System.IO.MemoryStream s = new MemoryStream();
            System.Text.Encoding Enc = System.Text.Encoding.Default;
            byte[] mBArray = Enc.GetBytes(tw.ToString());
            s = new MemoryStream(mBArray, false);

            //return Content(tw.ToString(), "application/ms-excel");
            return s;
        }

有人可以帮助我如何发送在 DataToExcel 函数中生成的 excel 吗？

Answer 1

经过一些调整，下面的代码对我来说很好。

            public void EmailCurrMonthSummary()
                    {
                        DataAccess da = new DataAccess();
                        MonthEndSummary mes = new MonthEndSummary();
                        DataTable tempTable = new DataTable();
                        mes.MonthEndSummaryTable = da.GetCurrMonthSummary(); //returns a DataTable                    

                        MailMessage mail = new MailMessage();
                        mail.To.Add("User@xxx.com");
                        mail.From = new MailAddress("Sender@xxx.com");
                        mail.Body = "Hello World";
                        mail.Subject = "Month End Status";
System.IO.MemoryStream str = DataToExcel(mes.MonthEndSummaryTable);

            Attachment at = new Attachment(str, "MonthEndSummary.xls");
            mail.Attachments.Add(at);    

                        mail.IsBodyHtml = true;

                        SmtpClient smtp = new System.Net.Mail.SmtpClient();
                        smtp.UseDefaultCredentials = true;
                        smtp.Send(mail);
                    }


 public System.IO.MemoryStream DataToExcel(DataTable dt)
        {
            //StreamWriter sw = new StreamWriter();
            System.IO.StringWriter tw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
            if (dt.Rows.Count > 0)
            {

                DataGrid dgGrid = new DataGrid();
                dgGrid.DataSource = dt;
                dgGrid.DataBind();
                dgGrid.HeaderStyle.Font.Bold = true;
                //Get the HTML for the control.
                dgGrid.RenderControl(hw);
                //Write the HTML back to the browser.
                //Response.ContentType = application/vnd.ms-excel;
                Response.ClearContent();
                Response.Buffer = true;
                Response.ContentType = "application/vnd.ms-excel";
                Response.ContentEncoding = System.Text.Encoding.Default;

            }
            System.IO.MemoryStream s = new MemoryStream();
            System.Text.Encoding Enc = System.Text.Encoding.Default;
            byte[] mBArray = Enc.GetBytes(tw.ToString());
            s = new MemoryStream(mBArray, false);

            return s;
        }