实用小程序：C#读取Excel并根据每一条条目群发邮件（一）

在自学C#的时候顺便开发的一个小程序，用于从Excel中读取信息并群发邮件，基本代码都是胡写的，但是，吃水不忘挖井人，至少能够方便比我还新的新手了

程序大致流程
1. 从Config.xml读取用来发送邮件的邮件账户
2. 从Address.xls读取通讯录
3. 从内容xls文件中的条目匹配通讯录并发送邮件

首先是Config.xml配置文件

<?xml version="1.0" encoding="utf-8"?>
<config>
    <sendHost>smtp.qq.com</sendHost><!--服务器的地址，在相关的网站可以查到-->
    <sendPort></sendPort><!--服务器的端口号,一般情况下为空即可，服务器那边一般会自己给你搞定的，你填了有时候反而会出问题-->
    <ssl>yes</ssl><!--服务器是否启用SSL安全-->
    <uname>12345678@qq.com</uname><!--邮箱用户名-->
    <upass>12345678</upass><!--密码-->
</config>

接下来是如何读取这一个文件
Config.cs
这个是整个程序的配置文件

using System;
using System.Collections.Generic;
using System.IO;
using System.Text;

namespace mail_poster.Util
{
    class Config
    {
        public const int personMax = 100;//单据数量上限，防止出现死循环
        public const int nameStrRow = 2;//标题栏所处的行数
        public const string contactFileName = "address.xlsx";//通讯录文件的文件名
        public const string configFilePath = "config.xml";//配置文件的文件名
        public const string titleFile = "header.htm";//表头HTM的文件名
        public const string mailCache = "mail.htm";

        //-----------------------------XML_Value
        private string sendHost = null;
        private string sendPort = null;
        private bool ssl = false;
        private string uname;
        private string upass;

        public static string logFilePath {
            get { return Directory.GetCurrentDirectory() + "/log.txt"; }
        }

        public static string mailCachePath
        {
            get { return Directory.GetCurrentDirectory() + "/" + mailCache; }
        }

        public static string titleFilePath
        {
            get { return Directory.GetCurrentDirectory() + "/" + titleFile; }
        }

        //通讯录文件所处地址
        public static string contactFilePath
        {
            get { return Directory.GetCurrentDirectory() + "/" + contactFileName; }
        }

        //服务器发送邮件地址
        public string SendHost
        {
            get { return sendHost; }
            set { sendHost = value; }
        }
        //服务器发送邮件端口
        public string SendPort
        {
            get { return sendPort; }
            set { sendPort = value; }
        }
        //服务器启用了SSL
        public bool UseSsl
        {
            get { return ssl; }
            set { ssl = value; }
        }
        //用户名
        public string Username
        {
            get { return uname; }
            set { uname = value; }
        }
        //密码
        public string Password
        {
            get { return upass; }
            set { upass = value; }
        }
    }
}

接下来是xml文件读取工具
ConfigReader.cs
用到了.net的Xml类库，用来读取Config.xml

using mail_poster.Util;
using System.Xml;
using System;
using System.IO;

namespace mail_poster
{
    /// <summary>
    /// 读取配置文件
    /// </summary>
    class ConfigReader
    {
        public static Config ReadConfig()
        {
            Config config = new Config();
            XmlDocument xml = new XmlDocument();
            try
            {
                xml.Load(Config.configFilePath);
            }
            catch (FileNotFoundException e)
            {
                throw new MyException("配置文件丢失!!!",e);
            }
            try
            {
                config.SendHost = xml.SelectSingleNode("config/sendHost").InnerText;
                config.SendPort = xml.SelectSingleNode("config/sendPort").InnerText;
                String ssl = xml.SelectSingleNode("config/ssl").InnerText;
                config.UseSsl = ssl.Equals("yes") ? true : false;
                config.Username = xml.SelectSingleNode("config/uname").InnerText;
                config.Password = xml.SelectSingleNode("config/upass").InnerText;
                return config;
            }
            catch (Exception e)
            {
                throw new MyException("配置文件读取时出现异常", e);
            }

        }

    }
}

接下来是读取Xls文件相关的类
XlsReader.cs
用的是微软官方的Interop

using Excel = Microsoft.Office.Interop.Excel;
using System.Collections;
using mail_poster.Util;
using System;
using System.Runtime.InteropServices;
using System.Diagnostics;
using System.Collections.Generic;

namespace mail_poster
{
    class XlsReader
    {
        Excel.Application excel;
        OpenXls xls;
        string fileName;

        public XlsReader(string fileName)
        {
            this.fileName = fileName;
            InitExcel();

        }

        /// <summary>
        /// 保存打开的工作簿信息
        /// </summary>
        private class OpenXls
        {
            /// <summary>
            /// 构造函数
            /// </summary>
            /// <param name="excel">Excel.Application进程</param>
            /// <param name="filePath">打开的文件完整路径</param>
            /// <param name="sheetIndex">打开的工作表序号</param>
            public OpenXls(Excel.Application excel, string filePath)
            {
                Workbook = excel.Workbooks.Open(filePath, Type.Missing, true);
                Worksheet = (Excel.Worksheet)Workbook.Worksheets[1];
            }
            ~OpenXls()
            {
                Close();
            }
            public Excel.Workbook Workbook;
            public Excel.Worksheet Worksheet;
            public void Close()
            {

                if (Worksheet != null)
                    Marshal.FinalReleaseComObject(Worksheet);
                if (Workbook != null)
                    Marshal.FinalReleaseComObject(Workbook);
            }
        }



        public void Close()
        {
            if (excel != null)
            {
                excel.Workbooks.Close();
                excel.Quit();
                Marshal.FinalReleaseComObject(excel);
            }

        }
        /// <summary>
        /// 初始化Excel进程
        /// </summary>
        /// <returns></returns>
        public Excel.Application InitExcel()
        {
            //檢查PC有無Excel在執行
            bool flag = false;
            foreach (var item in Process.GetProcesses())
            {
                if (item.ProcessName == "EXCEL")
                {
                    flag = true;
                    break;
                }
            }

            if (!flag)
            {
                this.excel = new Excel.Application();
                //excel.Visible = false;
                //excel.DisplayAlerts = false;
            }
            else
            {
                object obj = Marshal.GetActiveObject("Excel.Application");//引用已在執行的Excel
                excel = obj as Excel.Application;
            }
            xls = new OpenXls(excel, fileName);
            return excel;
        }

        /// <summary>
        /// 检查读取的XLS文件的合法性
        /// </summary>
        /// <returns>return<10合法,返回标题行数</returns>
        public bool CheckXls()
        {
            int row = Config.nameStrRow;
            String str1 = xls.Worksheet.Cells[row, 1].Value;
            String str2 = xls.Worksheet.Cells[row, 2].Value;
            if (str1 != null && str1.Equals("编号"))
                if (str2 != null && str2.Equals("姓名"))
                    return true;
            return false;
        }

        /// <summary>
        /// 读取一个合法的XLS
        /// </summary>
        /// <returns></returns>
        public ArrayList GetXlsForSend()
        {
            ArrayList datalist = new ArrayList();
            //TODO 加入Config值

            //表格数据内容(去除标题和表列的部分的内容)起始行，结尾行，有效列数
            int rowStart = 0, rowEnd = 0, colCount = getColNo();
            //计算表格的开头和末尾

            //开头
            for (int i = 1; i < Config.personMax; i++)
            {
                String cellVal = System.Convert.ToString((xls.Worksheet.Cells[i, 1]).Value);
                if (cellVal != null && cellVal.Equals("1"))
                {
                    rowStart = i;
                    break;
                }

            }
            //结尾
            for (int i = rowStart; i < Config.personMax; i++)
            {
                String cellVal = System.Convert.ToString((xls.Worksheet.Cells[i, 2]).Value);
                if (cellVal == null)
                {
                    rowEnd = i - 1;
                    break;
                }

            }
            for (int j = rowStart; j <= rowEnd; j++)/*按照行读取每个人*/
            {
                string[] data = new string[colCount];
                for (int i = 1; i <= colCount; i++)
                {
                    String cellVal = System.Convert.ToString((xls.Worksheet.Cells[j, i]).Value);
                    data[i - 1] = (cellVal == null ? "" : cellVal);//防止出现空值
                }
                datalist.Add(data);
            }
            return datalist;

        }

        /// <summary>
        /// 读取有效列数
        /// </summary>
        /// <returns></returns>
        private int getColNo()
        {
            int count = 0, overflow = 2;
            for (int i = 1; i < 30;)
            {
                if (overflow < 0) break;
                string cell = (xls.Worksheet.Cells[2, i]).Value;
                if (cell != null) count = i;
                else overflow--;
                i += (xls.Worksheet.Cells[2, i].MergeArea.Columns.Count);
            }
            return count;
        }

        /// <summary>
        /// 检查通讯录合法性
        /// </summary>
        /// <returns></returns>
        public bool CheckContact()
        {
            String cellVal1 = xls.Worksheet.Cells[1, 1].Value;
            String cellVal2 = xls.Worksheet.Cells[1, 2].Value;
            //检测合法性
            if (cellVal1.Equals("姓名") && cellVal2.Equals("邮箱地址"))
                return true;
            return false;
        }

        /// <summary>
        /// 读取通讯录
        /// </summary>
        /// <returns></returns>
        public ArrayList GetContact()
        {
            ArrayList contactList = new ArrayList();
            try
            {
                //读取内容
                for (int i = 2; i < Config.personMax; i++)
                {
                    String cellVal1 = xls.Worksheet.Cells[i, 1].Value;
                    String cellVal2 = xls.Worksheet.Cells[i, 2].Value;
                    if (cellVal1 != null && cellVal2 != null)
                    {
                    }
                    contactList.Add(new string[] { cellVal1, cellVal2 });
                }
            }
            catch (Exception)
            {
                throw;
            }
            return contactList;
        }
    }
}

这里是去掉了标题之后，实际程序读取的Excel部分(跳过多少行Const在了Config.cs)

现在，我们将Excel读进内存里了，剩下的部分，我下一篇继续写