郑德才博客

 直接使用代码显示结果如下：

    public partial class Form1 : Form
    {
        Excel.Application Excel = null;
        Excel.Workbook xBook = null;
        Excel.Worksheet xSheet = null;
        string sFileName;
        int maxCols;  //列数
        int MailCount = 0;  // 职工数
        //char MailColumn; //邮件列标
        //int emailCol;

        public Form1()
        {
            InitializeComponent();
        }

        //加载界面
        private void Form1_Load(object sender, EventArgs e)
        {
            DateTime.Now.ToShortTimeString();
            DateTime dt = DateTime.Now; //获取当前时间
            string Year = dt.Year.ToString();  //获取当前年份
            string Month = dt.Month.ToString();  //获取当前月份
            txtTitle.Text = Year + "年" + Convert.ToString(int.Parse(Month) - 1) + "月" + "员工工资单";
            cboCols.Items.Clear();  //加载事件就清空cboCols
            string AppPath = Application.StartupPath + @"\";
            string IniFile = "kernel32.ini";
            string iniPath = AppPath + IniFile;  //获取路径
            INIClass GetPath = new INIClass(iniPath);  //实例化对象
            bool get = GetPath.ExistINIFile();
            string DecodePassWord;

            if (get == true)
            {
                txtSMTPServer.Text = GetPath.IniReadValue("SMTPServer", "SMTPServer");  //读取SMTPServer的值
                txtUserName.Text = GetPath.IniReadValue("UserName", "UserName");  //读取UserName的值
                DecodePassWord = GetPath.IniReadValue("UserPwd", "UserPwd");  //读取UserPwd的值
                txtUserPwd.Text = Decode(DecodePassWord.Trim());
                txtSenderMail.Text = GetPath.IniReadValue("SenderMail", "SenderMail");  //读取SendMail的值
            }
            //判断是否存在 ini 文件 ，若则读取配置

            //当改变SMTPServer、UserName、UserPwd、SendMail则自动激活保存按钮
            txtSMTPServer.TextChanged += new System.EventHandler(Save_TextChanged);
            txtUserName.TextChanged += new System.EventHandler(Save_TextChanged);
            txtUserPwd.TextChanged += new System.EventHandler(Save_TextChanged);
            txtSenderMail.TextChanged += new System.EventHandler(Save_TextChanged);
        }

        //激活保存按钮
        private void Save_TextChanged(object sender, EventArgs e)
        {
            btnSave.Enabled = true;
        }

        //打开Excel文件，并将标题加载到cboCols中
        private void btnOpenFile_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDailog1 = new OpenFileDialog();
            openFileDailog1.Filter = "Excel 文件(*.xls)|*.xls|所有文件(*.*)|*.*";
            if (openFileDailog1.ShowDialog() == DialogResult.OK)
            {
                btnSendMail.Enabled = true;  //打开Excel文件时激活发送按钮

                sFileName = openFileDailog1.FileName;
                //MessageBox.Show(sFileName);

                Excel = new Excel.Application();  //实例化Excel对象
                xBook = Excel.Workbooks._Open(sFileName,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value
                    , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                    , Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                xSheet = (Excel.Worksheet)xBook.Sheets[1];  //获取Sheets1表

                string val;
                int x;

                cboCols.Items.Clear();
                for (x = 1; x < 100; x++)  //遍历标题
                {
                    char ch = (char)(64 + x);
                    Excel.Range rng1 = xSheet.get_Range(ch + "1", Type.Missing);
                    if (rng1.Value2 == null)
                    {
                        break;
                    }
                    val = rng1.Value2.ToString();
                    if (val.Length == 0)
                    {
                        break;
                    }
                    //ComboBoxItem cbi1 = new ComboBoxItem();
                    cboCols.Items.Add(val);

                    //if (rng1.Value2.ToString() == "E-mail" || rng1.Value2.ToString() == "电子邮件地址" || rng1.Value2.ToString() == "邮箱地址")  //读Excel中的邮箱地址列标题并赋给cboCols默认值
                    //{
                    //    MailColumn = ch;
                    //}
                }

                maxCols = x - 1;
                xBook.Close(Missing.Value, Missing.Value, Missing.Value);  //关闭Excel里面的值
                xBook = null;  //
                Excel.Quit();
                Excel = null;
            }
        }

        //读取信息并发送邮件
        private void btnSendMail_Click(object sender, EventArgs e)
        {
            string SendFormat=this.rdoTransverse.Checked?"横向":"纵向";
            if (SendFormat == "横向")
            {
                //if (cboCols.Text.ToString() != "E-mail" && cboCols.Text.ToString() != "电子邮件地址" && cboCols.Text.ToString() != "邮箱地址")  //判断cboCols中是否是邮件地址，不是，则提示错误，然后重新加载Form1的load事件
                //{
                //    MessageBox.Show("对不起，你所选择的不是邮箱地址列，请重新选择！", "错误提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                //    Form1_Load(sender, e);
                //}

                //else
                //{

                progressBar1.Value = 0;
                Excel = new Excel.Application();  //实例化Excel对象
                xBook = Excel.Workbooks._Open(sFileName,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value
                    , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                    , Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                xSheet = (Excel.Worksheet)xBook.Sheets[1];  //获取Sheets1表
                MailCount = 0;
                for (int i = 1; i < 10000; i++)  //获取员工条数
                {
                    char ch = (char)65;
                    Excel.Range rng1 = xSheet.get_Range(ch + i.ToString(), Type.Missing);
                    if (rng1.Value2 == null)
                    {
                        break;
                    }
                    MailCount++;
                }

                //赋发邮件中的初始值
                char y = ' ';

                string email, content, header;

                string val;

                int count = 0;

                for (int x = 2; x <= MailCount; x++)  // 循环遍历个人工资信息条数，把个人的工资情况发送到员工邮箱
                {
                    header = "<html><head><meta http-equiv='Content-Type' content='text/html; charset=gb2312'><title>" + txtTitle.Text + "</title></head><body>";
                    header += "<p align='left' style='font-size:18px'>" + txtTitle.Text + "</p>";
                    header += "<table width='600' border='1' cellspacing='1' cellpadding='1'><tr>";

                    for (int j = 1; j <= maxCols; j++)  //输出标题
                    {
                        char ch = (char)(64 + j);
                        Excel.Range rng1 = xSheet.get_Range(ch + "1", Type.Missing);
                        val = rng1.Value2.ToString();
                        if (rng1.Value2.ToString() == cboCols.Text)  //判断是邮箱列，则跳过，并获取邮箱列的下标，一种是字母型式，一种是数值型式
                        {
                            y = ch;
                            count = j;
                            //MessageBox.Show(y.ToString());
                            continue;
                        }
                        header += "<td align='center'>" + val + "</td>";  // 循环加入标题
                    }

                    //判断所选中的列是否是邮件地址列
                    Excel.Range rng2 = (Excel.Range)xSheet.get_Range(y + "2", Type.Missing);
                    if (CheckEmail(rng2.Value2.ToString()))
                    {
                        MessageBox.Show("你所选择的列不是邮件地址列！");
                        xBook.Close(Missing.Value, Missing.Value, Missing.Value);  //关闭Excel里面的值
                        xBook = null;  //
                        Excel.Quit();
                        Excel = null;
                        Form1_Load(sender, e);
                        return;
                    }

                    header += "</tr><tr>";

                    if (cboCols.Text != "")
                    {
                        Excel.Range rng1 = xSheet.get_Range((char)y + x.ToString(), Type.Missing);  //获取email地址
                        email = rng1.Value2.ToString();
                        if (CheckEmail(email))  //判断邮箱地址格式是否正确
                        {
                            MessageBox.Show("第" + x + "列的邮件地址格式有误！");
                            //Form1_Load(sender, e);
                            continue;
                        }
                        //MessageBox.Show(email);
                    }
                    else
                    {
                        break;
                    }

                    content = header;

                    for (int k = 1; k <= maxCols; k++)  //循环遍历个人工资信息情况，并以表格形式显示
                    {
                        //char ch = (char)(64 + x);
                        //Excel.Range rng1 = xSheet.get_Range(ch + x.ToString, Type.Missing);
                        Excel.Range rng1 = (Excel.Range)xSheet.Cells[x, k];
                        //string tp = rng1.GetType().GetType().;

                        if (!CheckEmail(rng1.Value2.ToString()))  //判断是否是邮件地址，则跳过输出到下一个条记录
                        {
                            continue;
                        }

                        if (rng1.Value2 == null)
                        {
                            continue;
                        }
                        //MessageBox.Show(rng1.Value2.ToString());
                        //if( date )  DateTime.Parse(rng1.Value2.ToString());

                        if (rng1.Value2.GetType().ToString() == "System.String") //判断值的类型，如果是数值型，则把内容居中在表格中显示
                        {
                            content += "<td>" + rng1.Text + "</td>";  //循环加入职工信息，包括工资信息详情
                        }
                        else
                        {
                            content += "<td align='center'>" + rng1.Text + "</td>";
                        }
                    }

                    content += "</tr></table></body></html>";
                    SendEmail(email, content);
                    progressBar1.Value = x * 100 / (MailCount);  //进度条显示情况

                }
                //Close xls
                progressBar1.Value = 100;
                MessageBox.Show("邮件发送完成!");
                xBook.Close(Missing.Value, Missing.Value, Missing.Value);  //关闭Excel里面的值
                xBook = null;  //
                Excel.Quit();
                Excel = null;
                return;
                //}
            }
            else
            {
                progressBar1.Value = 0;
                Excel = new Excel.Application();  //实例化Excel对象
                xBook = Excel.Workbooks._Open(sFileName,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value
                    , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                    , Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                xSheet = (Excel.Worksheet)xBook.Sheets[1];  //获取Sheets1表
                MailCount = 0;
                for (int i = 1; i < 10000; i++)  //获取员工条数
                {
                    char ch = (char)65;
                    Excel.Range rng1 = xSheet.get_Range(ch + i.ToString(), Type.Missing);
                    if (rng1.Value2 == null)
                    {
                        break;
                    }
                    MailCount++;
                }

                //赋发邮件中的初始值
                char y = ' ';

                string email, content, header;

                string val;

                int count = 0;

                for (int x = 2; x <= MailCount; x++)  // 循环遍历个人工资信息条数，把个人的工资情况发送到员工邮箱
                {
                    header = "<html><head><meta http-equiv='Content-Type' content='text/html; charset=gb2312'><title>" + txtTitle.Text + "</title></head><body>";
                    header += "<p align='left' style='font-size:18px'>" + txtTitle.Text + "</p>";
                    header += "<table width='200' border='1' cellspacing='1' cellpadding='1'>";

                    for (int j = 1; j <= maxCols; j++)  //输出标题
                    {
                        char ch = (char)(64 + j);
                        Excel.Range rng1 = xSheet.get_Range(ch + "1", Type.Missing);
                        val = rng1.Value2.ToString();
                        if (rng1.Value2.ToString() == cboCols.Text)  //判断是邮箱列，则跳过，并获取邮箱列的下标，一种是字母型式，一种是数值型式
                        {
                            y = ch;
                            count = j;
                            //MessageBox.Show(y.ToString());
                            continue;
                        }
                        header += "<tr><td align='left'>" + val + "</td>";  // 循环加入标题

                        Excel.Range rng2 = (Excel.Range)xSheet.Cells[x, j];
                           
                        if (!CheckEmail(rng2.Value2.ToString()))  //判断是否是邮件地址，则跳过输出到下一个条记录                          
                        {                             
                            continue;                   
                        }  
                        if (rng2.Value2 == null)                  
                        {                    
                            continue;                      
                        }                
                        if (rng2.Value2.GetType().ToString() == "System.String") //判断值的类型，如果是数值型，则把内容居中在表格中显示       
                        {
                            header += "<td align='left'>" + rng2.Text + "</td></tr>";  //循环加入职工信息，包括工资信息详情
                        }
                        else 
                        {
                            header += "<td align='left'>" + rng2.Text + "</td></tr>";
                        }
                    }

                    //判断所选中的列是否是邮件地址列
                    Excel.Range rng3 = (Excel.Range)xSheet.get_Range(y + "2", Type.Missing);
                    if (CheckEmail(rng3.Value2.ToString()))
                    {
                        MessageBox.Show("你所选择的列不是邮件地址列！");
                        xBook.Close(Missing.Value, Missing.Value, Missing.Value);  //关闭Excel里面的值
                        xBook = null;  //
                        Excel.Quit();
                        Excel = null;
                        Form1_Load(sender, e);
                        return;
                    }

                    header += "</tr><tr>";

                    if (cboCols.Text != "")
                    {
                        Excel.Range rng1 = xSheet.get_Range((char)y + x.ToString(), Type.Missing);  //获取email地址
                        email = rng1.Value2.ToString();
                        if (CheckEmail(email))  //判断邮箱地址格式是否正确
                        {
                            MessageBox.Show("第" + x + "列的邮件地址格式有误！");
                            //Form1_Load(sender, e);
                            continue;
                        }
                        //MessageBox.Show(email);
                    }
                    else
                    {
                        break;
                    }
                    content = header;
                    content += "</table></body></html>";
                    SendEmail(email, content);
                    progressBar1.Value = x * 100 / (MailCount);  //进度条显示情况

                }
                //Close xls
                progressBar1.Value = 100;
                MessageBox.Show("邮件发送完成!");
                xBook.Close(Missing.Value, Missing.Value, Missing.Value);  //关闭Excel里面的值
                xBook = null;  //
                Excel.Quit();
                Excel = null;
                return;
            }
        }

        public bool CheckEmail(string strEmail)
        {
            int i, j;
            string strTmp, strResult;
            string strWords = "abcdefghijklmnopqrstuvwxyz_-.0123456789"; //定义合法字符范围

            bool blResult = false;
            strTmp = strEmail.Trim();
            //检测输入字符串是否为空，不为空时才执行代码。
            if (!(strTmp == "" || strTmp.Length == 0))
            { //判断邮件地址中是否存在“@”号
                if ((strTmp.IndexOf("@") < 0))
                {
                    blResult = true;
                    return blResult;
                }
                //以“@”号为分割符，把地址切分成两部分，分别进行验证。
                string[] strChars = strTmp.Split(new char[] { '@' });
                foreach (string strChar in strChars)
                {
                    i = strChar.Length; //“@”号前部分或后部分为空时。
                    if (i == 0)
                    {
                        blResult = true;
                        return blResult;
                    }
                    //逐个字进行验证，如果超出所定义的字符范围strWords，则表示地址非法。
                    for (j = 0; j < i; j++)
                    {

                        strResult = strChar.Substring(j, 1).ToLower();//逐个字符取出比较

                        if (strWords.IndexOf(strResult) < 0)

                        {
                            blResult = true;
                            return blResult;
                        }
                    }
                }
            }
            return blResult;
        }

        //发送邮件验证
        private bool SendEmail(string addr, string content)
        {

            SmtpClient mailClient = new SmtpClient(txtSMTPServer.Text);

            //Credentials登陆SMTP服务器的身份验证.
            mailClient.Credentials = new NetworkCredential(txtUserName.Text, txtUserPwd.Text);

            //test@qq.com发件人地址、test@tom.com收件人地址
            MailMessage message = new MailMessage(new MailAddress(txtSenderMail.Text), new MailAddress(addr));

            message.Body = content;//邮件内容
            message.Subject = txtTitle.Text;//邮件主题
            message.Sender = new MailAddress(txtSenderMail.Text);
            message.IsBodyHtml = true;

            //Attachment 附件
            //Attachment att = new Attachment(@"C:\hello.txt");
            //message.Attachments.Add(att);//添加附件
            //Console.WriteLine("Start Send Mail....");
            //发送....

            try
            {
                mailClient.Send(message);
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return false;
            }

            return true;
        }

        //保存邮件配置
        private void btnSave_Click(object sender, EventArgs e)
        {
            string AppPath = Application.StartupPath + @"\";
            string IniFile = "kernel32.ini";
            string iniPath = AppPath + IniFile;  //获取路径
            INIClass GetPath = new INIClass(iniPath);  //创建实例化对象

            string SMTPServerSection = "SMTPServer";
            string SMTPServerKey = "SMTPServer";
            string SMTPServerKeyValue = txtSMTPServer.Text;
            GetPath.IniWriteValue(SMTPServerSection, SMTPServerKey, SMTPServerKeyValue);  //写入SMTPServer的值

            string FileName = iniPath;
            string UserNameSection = "UserName";
            string UserNameKey = "UserName";
            string UserNameKeyValue = txtUserName.Text;
            GetPath.IniWriteValue(UserNameSection, UserNameKey, UserNameKeyValue);  //写入UserName的值

            MD5CryptoServiceProvider md5 = new MD5CryptoServiceProvider();
            string UserPwdSection = "UserPwd";
            string UserPwdKey = "UserPwd";
            string UserPwdKeyValue = Encode(txtUserPwd.Text);
            GetPath.IniWriteValue(UserPwdSection, UserPwdKey, UserPwdKeyValue);  //写入UserPwd的值

            string SenderMailSection = "SenderMail";
            string SenderMailKey = "SenderMail";
            string SenderMailKeyValue = txtSenderMail.Text;
            GetPath.IniWriteValue(SenderMailSection, SenderMailKey, SenderMailKeyValue);  //写入SendMail的值
        }

        //加密算法的方法
        const string KEY_64 = "VavicApp";//注意了，是8个字符，64位
        const string IV_64 = "VavicApp";
        public string Encode(string data)
        {
            byte[] byKey = System.Text.ASCIIEncoding.ASCII.GetBytes(KEY_64);
            byte[] byIV = System.Text.ASCIIEncoding.ASCII.GetBytes(IV_64);

            DESCryptoServiceProvider cryptoProvider = new DESCryptoServiceProvider();
            int i = cryptoProvider.KeySize;
            MemoryStream ms = new MemoryStream();
            CryptoStream cst = new CryptoStream(ms, cryptoProvider.CreateEncryptor(byKey, byIV), CryptoStreamMode.Write);

            StreamWriter sw = new StreamWriter(cst);
            sw.Write(data);
            sw.Flush();
            cst.FlushFinalBlock();
            sw.Flush();
            return Convert.ToBase64String(ms.GetBuffer(), 0, (int)ms.Length);
        }

        //解密算法的方法
        public string Decode(string data)
        {
            byte[] byKey = System.Text.ASCIIEncoding.ASCII.GetBytes(KEY_64);
            byte[] byIV = System.Text.ASCIIEncoding.ASCII.GetBytes(IV_64);

            byte[] byEnc;
            try
            {
                byEnc = Convert.FromBase64String(data);
            }
            catch
            {
                return null;
            }

            DESCryptoServiceProvider cryptoProvider = new DESCryptoServiceProvider();
            MemoryStream ms = new MemoryStream(byEnc);
            CryptoStream cst = new CryptoStream(ms, cryptoProvider.CreateDecryptor(byKey, byIV), CryptoStreamMode.Read);
            StreamReader sr = new StreamReader(cst);
            return sr.ReadToEnd();
        }

        private void btnSave_MouseClick(object sender, MouseEventArgs e)
        {
            MessageBox.Show("保存成功！");
        }
    }