C#数据库转表格作为附件发送邮件。datatable转excel添加附件

情况介绍

我是个刚入门的菜鸟，现有项目需要我做一个发送数据库中的部品安全值提醒邮件，于是在前辈代码基础上以及百度的帮助下，终于实现需要的效果。下面请看效果展示，是不是你需要的邮件发送功能。

我会把所有代码都发出来，多看几遍，尝试一下，你一定能学会。

实现效果

图1-1（第1种）

图1-2（第二种）

------------------------- 分割线 -------------------------

正文

1.解决方案资源管理器

找到你缺少的引用，添加进来

2.Program.cs (下面的代码对应图1-1)

using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Linq;
using System.Net.Mail;
using System.Text;
using System.Threading.Tasks;

namespace NewEmail2
{
    class Program
    {
        public static int counter = 0;

        //主方法
        static void Main(string[] args)
        {
            string file = ConfigurationManager.AppSettings["File"].ToString().Trim();//调用全局配置，指定file路径
            //获取当前日期
            string strdate = DateTime.Now.ToString("yyyy-MM-dd");
            //获取所有部品信息放在表格当中，具体代码在SQLClass工具类里
            DataTable dt = SQLClass.Getdata().Tables[0];
            
            //放在新的Table里
            DataTable dtNew = new DataTable();
            foreach (DataColumn col in dt.Columns)
            {
                dtNew.Columns.Add(col.ColumnName);
            }
            //增加行头文件名称
            dtNew.Rows.Add("部品位置", "部品名称", "在库数量", "安全值","在库与安全差值");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dtNew.Rows.Add(dt.Rows[i].ItemArray);
            }
            try
            {
                //创建数组
                ArrayList list = new ArrayList();
                //保存Excel文件
                SaveExcel(file,dtNew,strdate);
                //发送地址
                string fromAdd = "SETSendEmail@sony.com";//随意命名
                SmtpClient smtpClient = new SmtpClient("192.168.44.44");//内部邮件发送地址（你需要改成自己的）

                //声明电子邮件类
                MailMessage msg = new MailMessage();
                //接收人邮件地址
                msg.To.Add("接收方邮件");
                msg.Subject = "SET安全值检查" + strdate + "";//邮件主题

                //将table保存为Excel,并以附件的形式发送
                string attaFilesPath = @"" + file + "SET安全值检查" + strdate + ".xls";//寻址名称
                foreach (string path in attaFilesPath.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
                {
                    if (!string.IsNullOrEmpty(path) && File.Exists(path))
                    {
                        msg.Attachments.Add(new Attachment(path));//添加附件
                    }
                }

                //邮件主体
                string bodymsg = string.Empty;
                msg.BodyEncoding = Encoding.UTF8;
                msg.Body = bodymsg;
                #region
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    int x = Convert.ToInt32(dt.Rows[i]["z"]);
                    if (x <= 0)
                    {
                        string s = Convert.ToString(dt.Rows[i]["LocationLabel"]);
                        string row = s.Substring(0, 3);
                        string rack = s.Substring(3, 3);
                        string layer = s.Substring(6, 3);
                        string str = "部品" + dt.Rows[i]["GoodsName"] + "已低于安全值" + dt.Rows[i]["SafeNumber"] + "，请尽快补充" + ",位于" + row + "排" + rack + "架" + layer + "层" + "\n";
                        list.Add(str);
                        Console.Write("部品" + dt.Rows[i]["GoodsName"] + "已低于安全值" + dt.Rows[i]["SafeNumber"] + "，请尽快补充" + ",位于" + row + "排" + rack + "架" + layer + "层" + "\n");
                    }
                }
                if (list.Count > 0)
                {
                    for (int i = 0; i < list.Count; i++)
                    {
                        msg.Body += list[i].ToString();
                    }
                }
                else
                {
                    msg.Body = "所有部品都在安全值范围内";
                }
                
                Console.ReadLine();
                #endregion
                msg.From = new MailAddress(fromAdd, "SET安全值提醒 " + strdate + "");//添加发送方信息
                smtpClient.UseDefaultCredentials = false;
                smtpClient.DeliveryMethod = SmtpDeliveryMethod.Network;
                smtpClient.Send(msg);//发送邮件
                smtpClient.Dispose();
                msg.Dispose();

                DeleteFolder(file);//删除file文件
            }
            catch (Exception ex)
            {
                ex.ToString();
            }
        }
        private static void SaveExcel(string file,DataTable dtNew ,string strdate)
        {
            //使用file文件，循环把行和列中的数据加到Excel表格中
            using (FileStream fs = new FileStream(@"" + file + "SET安全值检查"+ strdate + ".xls", FileMode.OpenOrCreate))
            {
                using (StreamWriter sw = new StreamWriter(fs, Encoding.Unicode))
                {
                    string columns = string.Empty;
                    string rowData = string.Empty;
                    
                    foreach (DataColumn column in dtNew.Columns)
                    {
                        columns += column.Caption + "\t";
                    }
                    columns += "\n";
                    sw.Write(columns);
                    foreach (DataRow row in dtNew.Rows)
                    {
                        foreach (DataColumn column in dtNew.Columns)
                        {
                            rowData += row[column.Caption].ToString() + "\t";
                        }
                        rowData += "\n";
                    }
                    sw.Write(rowData);
                    sw.Flush();
                }
            }
        }
        //删除文件夹
        private static void DeleteFolder(string file)
        {
            foreach (string d in Directory.GetFileSystemEntries(file))
            {
                if (File.Exists(d))
                {
                    FileInfo fi = new FileInfo(d);
                    if (fi.Attributes.ToString().IndexOf("ReadOnly") != -1)
                        fi.Attributes = FileAttributes.Normal;
                    File.Delete(d);//直接删除其中的文件  
                }
                else
                {
                    DirectoryInfo d1 = new DirectoryInfo(d);
                    if (d1.GetFiles().Length != 0)
                    {
                        DeleteFolder(d1.FullName); //递归删除子文件夹
                    }
                    Directory.Delete(d);
                }
            }
        }
    }
}

2-2（对应图1-2）

using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Linq;
using System.Net.Mail;
using System.Text;
using System.Threading.Tasks;

namespace NewEmail2
{
    class Program
    {
        public static int counter = 0;

        //主方法
        static void Main(string[] args)
        {
            string file = ConfigurationManager.AppSettings["File"].ToString().Trim();//调用全局配置，指定file路径
            //获取当前日期
            string strdate = DateTime.Now.ToString("yyyy-MM-dd");
            //获取所有部品信息放在表格当中，具体代码在SQLClass工具类里
            DataTable dt = SQLClass.Getdata().Tables[0];
            
            //放在新的Table里
            DataTable dtNew = new DataTable();
            foreach (DataColumn col in dt.Columns)
            {
                dtNew.Columns.Add(col.ColumnName);
            }
            //增加行头文件名称
            dtNew.Rows.Add("部品位置", "部品名称", "在库数量", "安全值","在库与安全差值");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dtNew.Rows.Add(dt.Rows[i].ItemArray);
            }
            try
            {
                //创建数组
                ArrayList list = new ArrayList();
                //保存Excel文件
                SaveExcel(file,dtNew,strdate);
                //发送地址
                string fromAdd = "SETSendEmail@sony.com";//随意命名
                SmtpClient smtpClient = new SmtpClient("101.122.12.12");//内部邮件发送地址（你需要改成自己的）

                //声明电子邮件类
                MailMessage msg = new MailMessage();
                //接收人邮件地址
                msg.To.Add("h@qq.com");

                msg.Subject = "SET安全值检查" + strdate + "";//邮件主题

                //将table保存为Excel,并以附件的形式发送
                string attaFilesPath = @"" + file + "SET安全值检查" + strdate + ".xls";//寻址名称
                foreach (string path in attaFilesPath.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
                {
                    if (!string.IsNullOrEmpty(path) && File.Exists(path))
                    {
                        msg.Attachments.Add(new Attachment(path));//添加附件
                    }
                }

                //邮件主体
                string bodymsg = string.Empty;
                msg.BodyEncoding = Encoding.UTF8;
                msg.IsBodyHtml = true;
                #region
                bodymsg = "<html>";
                bodymsg += "<head>";
                bodymsg += "<meta http-equiv=\"Content-Type\" content=\"text/html;charset = utf-8\">";
                bodymsg += "<style type=\"text/css\">";

                bodymsg += " td.Head{ vertical-align:middle; padding-left:2pt; padding-top:2pt; padding-right:2pt; padding-bottom:2pt; border-bottom:1pt solid Black; border-left:1pt solid Black; border-right:1pt solid Black; border-top:1pt solid Black; background-color:PaleTurquoise; text-align:center;}";
                bodymsg += " td.Row{ vertical-align:middle; padding-left:2pt; padding-top:2pt; padding-right:2pt; padding-bottom:2pt; border-bottom:1pt solid Black; border-left:1pt solid Black; border-right:1pt solid Black; border-top:1pt solid Black; background-color:Transparent; text-align:left;}";
                bodymsg += " div.Head{word-wrap:break-word; white-space:pre-wrap; width: 99%; overflow-x:hidden; font-style:normal; font-family:\"微软雅黑\"; font-size:10pt; font-weight:400; text-decoration:none; color: black; direction: ltr; unicode-bidi:normal; writing - mode:lr-tb; layout-flow:horizontal; vertical-align:middle; text-align:center; }";
                bodymsg += " div.Row{word-wrap:break-word; white-space:pre-wrap; width: 99%; overflow-x:hidden; font-style:normal; font-family:\"微软雅黑\"; font-size:10pt; font-weight:400; text-decoration:none; color: black; direction: ltr; unicode-bidi:normal; writing - mode:lr-tb; layout-flow:horizontal; vertical-align:top; }";
                bodymsg += " table.ts{border-bottom:1pt none Black; border-left:1pt none Black; border-right:1pt none Black; border-top:1pt none Black; background-color:Transparent; }";

                bodymsg += "</styly>";
                bodymsg += "</head>";
                bodymsg += "<body>";
                bodymsg += "<h5 style=\"font-size:12px;color:gray\">该邮件由系统自动发送，请勿直接回复</h5>";
                bodymsg += "</body>";
                bodymsg += "</html>";
                #endregion
                msg.Body = bodymsg;
                msg.From = new MailAddress(fromAdd, "SET安全值提醒 " + strdate + "");//添加发送方信息
                smtpClient.UseDefaultCredentials = false;
                smtpClient.DeliveryMethod = SmtpDeliveryMethod.Network;
                smtpClient.Send(msg);//发送邮件
                smtpClient.Dispose();
                msg.Dispose();

                DeleteFolder(file);//删除file文件
            }
            catch (Exception ex)
            {
                ex.ToString();
            }
        }
        private static void SaveExcel(string file,DataTable dtNew ,string strdate)
        {
            //使用file文件，循环把行和列中的数据加到Excel表格中
            using (FileStream fs = new FileStream(@"" + file + "SET安全值检查"+ strdate + ".xls", FileMode.OpenOrCreate))
            {
                using (StreamWriter sw = new StreamWriter(fs, Encoding.Unicode))
                {
                    string columns = string.Empty;
                    string rowData = string.Empty;
                    
                    foreach (DataColumn column in dtNew.Columns)
                    {
                        columns += column.Caption + "\t";
                    }
                    columns += "\n";
                    sw.Write(columns);
                    foreach (DataRow row in dtNew.Rows)
                    {
                        foreach (DataColumn column in dtNew.Columns)
                        {
                            rowData += row[column.Caption].ToString() + "\t";
                        }
                        rowData += "\n";
                    }
                    sw.Write(rowData);
                    sw.Flush();
                }
            }
        }
        //删除文件夹
        private static void DeleteFolder(string file)
        {
            foreach (string d in Directory.GetFileSystemEntries(file))
            {
                if (File.Exists(d))
                {
                    FileInfo fi = new FileInfo(d);
                    if (fi.Attributes.ToString().IndexOf("ReadOnly") != -1)
                        fi.Attributes = FileAttributes.Normal;
                    File.Delete(d);//直接删除其中的文件  
                }
                else
                {
                    DirectoryInfo d1 = new DirectoryInfo(d);
                    if (d1.GetFiles().Length != 0)
                    {
                        DeleteFolder(d1.FullName); //递归删除子文件夹
                    }
                    Directory.Delete(d);
                }
            }
        }
    }
}

3.PublicDB.cs

using System.Data;
using System.Data.SqlClient;

namespace NewEmail2
{
    class PublicDB
    {
        //数据库连接字符串 
        protected static string constring = "Data Source=43.98.3.2;Initial Catalog=Data_Set;User ID=sa;Password=555555";
        private static SqlConnection connection;

        /// <summary>
        /// 定义连接对象
        /// </summary>
        public static SqlConnection Connection
        {
            get
            {
                if (connection == null)
                {
                    connection = new SqlConnection(constring);
                    connection.Open();
                }
                else if (connection.State == ConnectionState.Closed)
                { connection.Open(); }
                else if (connection.State == ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Open();
                }
                return connection;
            }
        }
        /// <summary>
        /// 执行SQL语句并返回Int
        /// </summary>
        /// <param name="sql">执行语句</param>
        /// <returns></returns>
        public static int ExecuteCommand(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            int result = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return result;
        }
        /// <summary>
        /// 执行SQL语句并返回SqlDataReader
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static SqlDataReader GetReader(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            SqlDataReader dr = cmd.ExecuteReader();
            cmd.Dispose();
            return dr;
        }
        /// <summary>
        /// 查询表Selectcode
        /// </summary>
        /// <param name="SQL">查询语句</param>
        /// <returns>DataTable</returns>
        public static DataTable GetDtatTable(string SQL)
        {
            SqlDataAdapter da = new SqlDataAdapter(SQL, Connection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            dt.Dispose();
            da.Dispose();
            return dt;
        }

        /// <summary>
        ///  执行SQL语句并返回DataSet
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataSet GetDataSet(string sql)
        {
            SqlDataAdapter da = new SqlDataAdapter(sql, Connection);
            DataSet ds = new DataSet();
            da.Fill(ds);
            ds.Dispose();
            da.Dispose();
            return ds;
        }
    }
}

4.SQLClass.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace NewEmail2
{
    class SQLClass
    {
        internal static DataSet Getdata()
        {
            DataSet ds = new DataSet();
            string strSql = " SELECT LocationLabel ,GoodsName,PresentNumber,SafeNumber,PresentNumber - SafeNumber as z FROM ItemInfo ";//PresentNumber - SafeNumber as z
            ds = PublicDB.GetDataSet(strSql.ToString().Trim());
            return ds;
        }

    }
}

 5.App.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
  </startup>
  <appSettings>
    <add key="File" value="C:\Users\source\repos\NewEmail2\NewEmail2\Files\"/>
  </appSettings>
</configuration>

6.数据库方面

 至此，所有代码都贴出来了，原谅我技术力不够，不能深入浅出的讲解，但如果你遇到问题，请私信我，我一定尽力提供帮助。另外，文中有错误和可以改进的部分，请直接指出，万分感谢