基于winform的工资单群发工具

摘要

开山之作，如有不足还请见晾。
主要内容：使用C#开发一款简单的工资单群发工具。
开发背景：公司目前采用微信截图的方式发送工资单，效率低下导致不一定每次都发~尴尬
涉及内容：

1. Excel导入
2. 邮件发送

界面效果

实现思路

1. Excel中维护工资单信息，必须存在“姓名”、“邮箱” 两列，用于确定邮箱地址
2. 在 邮箱设置 中维护邮箱账号、密码、SMTP服务器
3. 点击“导入Excel” 将工资单文件导入，在数据预览中将显示导入的内容
4. 点击“批量发送”，根据导入数据中邮箱列进行邮件发送

将Excel导入成DataTable

/// <summary>
/// 将excel文件内容读取到DataTable数据表中
/// </summary>
/// <param name="fileName">文件完整路径名</param>
/// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名：true=是，false=否</param>
/// <returns>DataTable数据表</returns>
public static DataTable ReadExcelToDataTable(string fileName, string sheetName = null, bool isFirstRowColumn = true)
{
   //定义返回值
   var dtResult = new DataTable();
   //定义WookBook
   IWorkbook workbook = null;
   //Sheet页
   ISheet sheet = null;
   //定义数据起始行
   int startDataRow = 0;
   try
   {
       //指定文件是否存在
       if (!File.Exists(fileName))
       {
           return null;
       }
       //根据文件流创建excel数据结构
       using (FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
       {
           workbook = WorkbookFactory.Create(fileStream);
       }
       //获取指定名称的Sheet页，若果未指定名称，则获取第一个sheet页
       if(string.IsNullOrEmpty(sheetName))
       {
           sheet = workbook.GetSheetAt(0);
       }
       else
       {
           sheet = workbook.GetSheet(sheetName);
       }
       //如果没有Sheet页，则返回
       if(sheet == null)
       {
           return null;
       }
       IRow firstRow = sheet.GetRow(0);
       //添加标题
       if (isFirstRowColumn)
       {
           for (int i = firstRow.FirstCellNum; i < firstRow.LastCellNum; i++)
           {
               ICell cell = firstRow.GetCell(i);
               if (cell == null)
               {
                   continue;
               }
               var cellValue = cell.StringCellValue;
               if (cellValue == null)
               {
                   continue;
               }
               DataColumn column = new DataColumn(cellValue);
               dtResult.Columns.Add(column);
           }
           startDataRow = sheet.FirstRowNum + 1;
       }
       else
       {
           startDataRow = sheet.FirstRowNum;
       }
       //添加数据
       for (int i = startDataRow; i <= sheet.LastRowNum; i++)
       {
           IRow row = sheet.GetRow(i);
           if (row == null)
           {
               continue;
           }
           DataRow dataRow = dtResult.NewRow();
           for (int j = row.FirstCellNum; j < firstRow.LastCellNum; ++j)
           {
               ICell cell = row.GetCell(j);
               if (cell == null)
               {
                   continue;
               }
               dataRow[j] = cell.ToString();
           }
           dtResult.Rows.Add(dataRow);
       }
   }
   catch(Exception ex)
   {
       throw ex;
   }
   return dtResult;
}

邮件发送方法（非原创）

 public class EmailHandle
 {
     private string _serviceType = "SMTP";
     private string _host;
     private object LogManager;

     /// <summary>
     /// 发送者邮箱
     /// </summary>
     public string From { get; set; }

     /// <summary>
     /// 接收者邮箱列表
     /// </summary>
     public List<string> To { get; set; }

     /// <summary>
     /// 抄送者邮箱列表
     /// </summary>
     public string[] Cc { get; set; }

     /// <summary>
     /// 秘抄者邮箱列表
     /// </summary>
     public string[] Bcc { get; set; }

     /// <summary>
     /// 邮件主题
     /// </summary>
     public string Subject { get; set; }

     /// <summary>
     /// 邮件内容
     /// </summary>
     public string Body { get; set; }

     /// <summary>
     /// 是否是HTML格式
     /// </summary>
     public bool IsBodyHtml { get; set; }

     /// <summary>
     /// 附件列表
     /// </summary>
     public string[] Attachments { get; set; }

     /// <summary>
     /// 邮箱服务类型(Pop3,SMTP,IMAP,MAIL等)，默认为SMTP
     /// </summary>
     public string ServiceType
     {
         get { return _serviceType; }
         set { _serviceType = value; }
     }

     /// <summary>
     /// 邮箱服务器，如果没有定义邮箱服务器，则根据serviceType和Sender组成邮箱服务器
     /// </summary>
     public string Host
     {
         get { return _host; }
         set { _host = value; }
     }

     /// <summary>
     /// 邮箱账号(默认为发送者邮箱的账号)
     /// </summary>
     public string UserName { get; set; }

     /// <summary>
     /// 邮箱密码(默认为发送者邮箱的密码)，默认格式GB2312
     /// </summary>
     public string Password { get; set; }

     /// <summary>
     /// 邮箱优先级
     /// </summary>
     public MailPriority MailPriority { get; set; }

     /// <summary>
     ///  邮件正文编码格式
     /// </summary>
     public Encoding Encoding { get; set; }

     /// <summary>
     /// 构造参数，发送邮件，使用方法备注：公开方法中调用
     /// </summary>
     public int Send()
     {
         var mailMessage = new MailMessage();
         //读取To  接收者邮箱列表
         try
         {
             if (this.To != null && this.To.Count > 0)
             {
                 foreach (string to in this.To)
                 {
                     if (string.IsNullOrEmpty(to)) continue;
                     mailMessage.To.Add(new MailAddress(to.Trim()));
                 }
             }
             //读取Cc  抄送者邮件地址
             if (this.Cc != null && this.Cc.Length > 0)
             {
                 foreach (var cc in this.Cc)
                 {
                     if (string.IsNullOrEmpty(cc)) continue;
                     mailMessage.CC.Add(new MailAddress(cc.Trim()));
                 }
             }
             //读取Attachments 邮件附件
             if (this.Attachments != null && this.Attachments.Length > 0)
             {
                 foreach (var attachment in this.Attachments)
                 {
                     if (string.IsNullOrEmpty(attachment)) continue;
                     mailMessage.Attachments.Add(new Attachment(attachment));
                 }
             }
             //读取Bcc 秘抄人地址
             if (this.Bcc != null && this.Bcc.Length > 0)
             {
                 foreach (var bcc in this.Bcc)
                 {
                     if (string.IsNullOrEmpty(bcc)) continue;
                     mailMessage.Bcc.Add(new MailAddress(bcc.Trim()));
                 }
             }
             //读取From 发送人地址
             mailMessage.From = new MailAddress(this.From);
             //邮件标题
             Encoding encoding = Encoding.GetEncoding("GB2312");
             mailMessage.Subject = this.Subject;
             //邮件正文是否为HTML格式
             mailMessage.IsBodyHtml = this.IsBodyHtml;
             //邮件正文
             mailMessage.Body = this.Body;
             mailMessage.BodyEncoding = this.Encoding;
             //邮件优先级
             mailMessage.Priority = this.MailPriority;
             //发送邮件代码实现
             var smtpClient = new SmtpClient
             {
                 Host = this.Host,
                 EnableSsl = true,
                 Credentials = new NetworkCredential(this.UserName, this.Password)
             };
             //加这段之前用公司邮箱发送报错：根据验证过程，远程证书无效
             //加上后解决问题
             ServicePointManager.ServerCertificateValidationCallback = delegate (Object obj, X509Certificate certificate, X509Chain chain, SslPolicyErrors errors) { return true; };
             //认证
             smtpClient.Send(mailMessage);
             return 1;
         }
         catch (Exception ex)
         {
             return -1;
         }
     }
 }

按键事件-导入Excel

/// <summary>
/// 导入Excel
/// </summary>
private void btnImpExcel_Click(object sender, EventArgs e)
{
    //首先根据打开文件对话框，选择要打开的文件
    OpenFileDialog ofd = new OpenFileDialog();
    //打开文件对话框筛选器，默认显示文件类型
    ofd.Filter = "Excel表格|*.xlsx|Excel97-2003表格|*.xls|所有文件|*.*";
    //定义文件路径
    string strPath;
    if (ofd.ShowDialog() == DialogResult.OK)
    {
        try
        {
            DataTable dt = new DataTable();
            strPath = ofd.FileName;
            dt = ExcelHandle.ReadExcelToDataTable(strPath, null, true);
            dgv.DataSource = dt;
            dgv.AllowUserToAddRows = false;
            dgv.AllowUserToDeleteRows = false;
        }
        catch (Exception ex)
        {
            MessageBox.Show("导入Excel失败：" + ex.Message);//捕捉异常
        }
    }
}

按键事件-批量发送

/// <summary>
/// 发送邮件
/// </summary>
private void BtnSend_Click(object sender, EventArgs e)
{
    try
    {
        if (string.IsNullOrEmpty(this.tbUserName.Text))
        {
            MessageBox.Show("请输入发件人账号！");
            return;
        }
        if (string.IsNullOrEmpty(this.tbPassword.Text))
        {
            MessageBox.Show("请输入发件人密码！");
            return;
        }
        if (string.IsNullOrEmpty(this.tbSmtp.Text))
        {
            MessageBox.Show("请选择SMTP服务器！");
            return;
        }

        EmailHandle emailHandle = new EmailHandle();
        emailHandle.From = this.tbUserName.Text.Trim();

        foreach (DataGridViewRow row in dgv.Rows)
        {
            //拼接邮件内容
            var sb = new StringBuilder();
            sb.Append($"您好，{DateTime.Now.ToString("yyyy-MM")}的工资已发放，请查收。感谢您的辛苦工作，您辛苦了！<br><br>");
            sb.Append("<table border=1 cellspacing=0 bordercolor=black >");
            sb.Append("<tr>");
            foreach (DataGridViewColumn column in dgv.Columns)
            {
                if (column.Name.Equals("HasSend"))
                {
                    continue;
                }
                sb.Append($"<td>{column.Name}</td>");
            }
            sb.Append("</tr>");
            sb.Append("<tr>");
            foreach (DataGridViewColumn column in dgv.Columns)
            {
                if (column.Name.Equals("HasSend"))
                {
                    continue;
                }
                sb.Append($"<td>{row.Cells[column.Name].Value.ToString().Trim()}</td>");
            }
            sb.Append("</tr>");
            sb.Append("</table>");

            var list = new List<string>();
            list.Add(row.Cells["邮箱"].Value.ToString().Trim());
            emailHandle.To = list;
            emailHandle.Subject = DateTime.Now.ToString("yyyy-MM") + "_薪资单_" + row.Cells["姓名"].Value.ToString();
            emailHandle.Body = sb.ToString();
            emailHandle.IsBodyHtml = true;
            emailHandle.UserName = this.tbUserName.Text.Trim();
            emailHandle.Password = this.tbPassword.Text.Trim();
            emailHandle.Host = this.tbSmtp.Text;
            emailHandle.Send();
            row.Cells["HasSend"].Value = true;
        }
        MessageBox.Show("全部发送成功!");
    }
    catch (Exception ex)
    {
        MessageBox.Show("发送失败:" + ex.Message.ToString());
    }
}

其他

其他均为控件的简单应用，此处不再做详细的介绍！