基于winform的工资单群发工具
工资单群发小工具
摘要
开山之作，如有不足还请见晾。
主要内容：使用C#开发一款简单的工资单群发工具。
开发背景：公司目前采用微信截图的方式发送工资单，效率低下导致不一定每次都发~尴尬
涉及内容：
- Excel导入
- 邮件发送
界面效果
实现思路
- Excel中维护工资单信息，必须存在“姓名”、“邮箱” 两列，用于确定邮箱地址
- 在 邮箱设置 中维护邮箱账号、密码、SMTP服务器
- 点击“导入Excel” 将工资单文件导入，在数据预览中将显示导入的内容
- 点击“批量发送”，根据导入数据中邮箱列进行邮件发送
将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());
}
}
其他
其他均为控件的简单应用，此处不再做详细的介绍.
需要源码的可以给我留言…
本程序曾经发布在****供下载，因个人信息忘记删除（尴尬）撤回，已下载的网友还请不要泄露本人信息，谢谢！