通过附件Excel File xls(未创建Excel文件)C#发送数据集数据电子邮件

Question

我想用C#中的电子邮件excel文件附件发送数据集数据，但我不想物理创建excel文件。这可以用MemoryStream完成，但我做不到。

另一个问题是，我想设置Excel文件的编码类型，因为数据可能是俄语或土耳其语的特殊字符。

请帮帮我…

这是我的示例代码。。。

<!-- language: c# -->
var response = HttpContext.Response;
response.Clear();
response.Charset = "utf-8";
response.ContentEncoding = System.Text.Encoding.Default;
GridView excelGridView = new GridView();
excelGridView.DataSource = InfoDataSet;
excelGridView.DataBind();
excelStringWriter = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(excelStringWriter);
excelGridView.RenderControl(htw);
byte[] ExcelData = emailEncoding.GetBytes(excelStringWriter.ToString());
MemoryStream ms = new MemoryStream(ExcelData);
mailMessage.Attachments.Add(new Attachment(ms, excelFileName, "application/ms-excel"));
<!-- language: c# -->

Answer 1

这里有另一个简单易用的excel attchment

public string SendMail(string LastId)
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
        SqlCommand cmd = new SqlCommand("sp_GetMailData", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@LastID", LastId);
        con.Open();
        string result = "0";
        string temptext = "";
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt=new DataTable();
        da.Fill(dt);
        //ExportToSpreadsheet(dt,"My sheet");
        GridView gv = new GridView();
        gv.DataSource = dt;
        gv.DataBind();
        AttachandSend(gv);
        con.Close();
        return result.ToString();
    }
    public void AttachandSend(GridView gv)
    {
        StringWriter stw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(stw);
        gv.RenderControl(hw);
        System.Text.Encoding Enc = System.Text.Encoding.ASCII;
        byte[] mBArray = Enc.GetBytes(stw.ToString());
        System.IO.MemoryStream mAtt = new System.IO.MemoryStream(mBArray, false);
        System.Net.Mail.MailMessage mailMessage = new System.Net.Mail.MailMessage();
        MailAddress address = new
        MailAddress("xxxxxxxxxxxxx", "Admin");
        mailMessage.Attachments.Add(new Attachment(mAtt, "sales.xls"));
        mailMessage.Body = "Hi PFA";
        mailMessage.From = address;
        mailMessage.To.Add("xxxxxxxxxxxx");
        mailMessage.Subject = "xxxxxxxxxxxxxx";
        mailMessage.IsBodyHtml = true;
        var smtp = new SmtpClient();
        smtp.Send(mailMessage);
    }

Answer 2

这是您的解决方案

 private static Stream DataTableToStream(DataTable table)
        {
            const string semiColon = ";";
            var ms = new MemoryStream();
            var sw = new StreamWriter(ms);
            foreach (DataColumn column in table.Columns)
            {
                sw.Write(column.ColumnName);
                sw.Write(semiColon);
            }
            sw.Write(Environment.NewLine);
            foreach (DataRow row in table.Rows)
            {
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    sw.Write(row[i].ToString().Replace(semiColon, string.Empty));
                    sw.Write(semiColon);
                }
                sw.Write(Environment.NewLine);
            }
            return ms;
        }
        private static MailMessage CreateMail(string from,
            string to,
            string subject,
            string body,
            string attname,
            Stream tableStream)
        {
            // using System.Net.Mail
            var mailMsg = new MailMessage(from, to, subject, body);
            tableStream.Position = 0;
            mailMsg.Attachments.Add(
                new Attachment(tableStream, attname, CsvContentType));
            return mailMsg;
        }
        private const string CsvContentType = "application/ms-excel";
        private static void ExportToSpreadsheetInternal(Stream tableStream, string name)
        {
            HttpContext context = HttpContext.Current;
            context.Response.Clear();
            context.Response.ContentType = CsvContentType;
            context.Response.AppendHeader(
                "Content-Disposition"
                , "attachment; filename=" + name + ".xls");
            tableStream.Position = 0;
            tableStream.CopyTo(context.Response.OutputStream);
            context.Response.End();
        }

        public static void ExportToSpreadsheet(DataTable table, string name)
        {
            var stream = DataTableToStream(table);
            var mailMsg = CreateMail("[e-mail]",
                "[e-mail]",
                "spread",
                "the spread",
                name,
                stream);
            //ExportToSpreadsheetInternal(stream, name);
            // send the mailMsg with SmtpClient (config in your web.config)
            var smtp = new SmtpClient();
            smtp.Send(mailMsg);
        }

将此方法称为

 ExportToSpreadsheet(DataTable table, string name)