前两天要求做一个批量发送邮件
邮件内容是从excel中读取数据 然后发送
直接贴出代码
using HOSBS.Framework.Utility; using NPOI.HSSF.UserModel; using NPOI.XSSF.UserModel; using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Web; using System.Web.Mvc; using System.Net.Mail; using System.Net; using NPOI.SS.UserModel; using NPOI.HSSF.Util; using System.Drawing; namespace HOSBS.Web.Admin.Areas.HOS.Controllers { public class WorksController : Controller { // // GET: /HOS/Works/ public ActionResult Index() { return View(); } public string path = "~/exceldownload/"; private string StorageRoot { get { return Path.Combine(Server.MapPath(path)); } } public ActionResult UploadFiles() { HttpPostedFileBase file = Request.Files["file1"]; if (file != null) { Stream streamfile = file.InputStream; DataTable dt = new DataTable(); var houzhuiname = Path.GetExtension(file.FileName); try { if (houzhuiname == ".xls") { HSSFWorkbook hssfworkbook = new HSSFWorkbook(streamfile); dt = ImExport(dt, hssfworkbook); GetData(dt); } else { XSSFWorkbook hssfworkbook = new XSSFWorkbook(streamfile); dt = ImExport(dt, hssfworkbook); GetData(dt); } return Json(1); } catch { } } return Json(0); } #region 两种不同版本的操作excel /// <summary> /// Excel2007的版本，扩展名是.xlsx /// </summary> /// <param name="dt"></param> /// <param name="hssfworkbook"></param> /// <returns></returns> private static DataTable ImExport(DataTable dt, XSSFWorkbook hssfworkbook) { NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) { //dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());] dt.Columns.Add(sheet.GetRow(0).Cells[j].ToString()); } while (rows.MoveNext()) { XSSFRow row = (XSSFRow)rows.Current; DataRow dr = dt.NewRow(); for (int i = 0; i < 19; i++) { NPOI.SS.UserModel.ICell cell = row.GetCell(i); if (cell == null) { dr[i] = null; } else { dr[i] = cell.ToString(); } } dt.Rows.Add(dr); } return dt; } /// <summary> /// Excel2003以前（包括2003）的版本 /// </summary> /// <param name="dt"></param> /// <param name="hssfworkbook"></param> /// <returns></returns> private static DataTable ImExport(DataTable dt, HSSFWorkbook hssfworkbook) { NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) { dt.Columns.Add(sheet.GetRow(0).Cells[j].ToString()); //dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString()); } while (rows.MoveNext()) { HSSFRow row = (HSSFRow)rows.Current; DataRow dr = dt.NewRow(); for (int i = 0; i < 19; i++) { NPOI.SS.UserModel.ICell cell = row.GetCell(i); if (cell == null) { dr[i] = null; } else { dr[i] = cell.ToString(); } } dt.Rows.Add(dr); } return dt; } /// <summary> /// 生成excel /// </summary> /// <param name="dt"></param> /// <returns></returns> public FileResult GetData(DataTable dt) { int flag = 0; foreach (DataRow item in dt.Rows) { DataTable dtresult = dt.Copy(); dtresult.Rows.Clear(); flag++; if (flag % 2 == 0) { dtresult.Rows.Add(item.ItemArray); NPOI.HSSF.UserModel.HSSFWorkbook book = CreateNewBook(dtresult); if (!Directory.Exists(StorageRoot)) { Directory.CreateDirectory(StorageRoot); } string strdate = DateTime.Now.ToString("yyyyMMddhhmmssffff");//获取当前时间 var path = Path.Combine(StorageRoot, strdate + "Excel.xls"); // 写入到客户端 using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write)) { book.Write(fs); } var fullName = Path.Combine(StorageRoot, strdate); SendMail(path, item[18].ToString()); dtresult.Rows.Clear(); flag = 0; } else { dtresult.Rows.Add(item.ItemArray); } } return null; } //创建的excel文档 并且设置excel的style格式 private static HSSFWorkbook CreateNewBook(DataTable dtresult) { //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("工资条"); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); ICellStyle style = book.CreateCellStyle(); style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; ICellStyle style2 = book.CreateCellStyle(); style2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; var newStyle = book.CreateCellStyle(); newStyle.FillPattern = FillPattern.SolidForeground; newStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightYellow.Index; newStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; newStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; newStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; newStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; IFont font1 = book.CreateFont(); font1.FontName = "微软雅黑"; font1.Boldweight = (short)FontBoldWeight.Bold; style.SetFont(font1); newStyle.SetFont(font1); var newStyle2 = book.CreateCellStyle(); newStyle2.FillPattern = FillPattern.SolidForeground; newStyle2.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightYellow.Index; newStyle2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; newStyle2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; newStyle2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; newStyle2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //row1.RowStyle.FillBackgroundColor = ""; for (int i = 0; i < dtresult.Columns.Count - 1; i++) { sheet1.SetColumnWidth(i, 25 * 150); row1.CreateCell(i).SetCellValue(dtresult.Columns[i].ColumnName); row1.Cells[i].CellStyle = style; if (i == 17 || i == 9) { row1.GetCell(i).CellStyle = newStyle; } } //将数据逐步写入sheet1各个行 for (int i = 0; i < dtresult.Rows.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); for (int j = 0; j < dtresult.Columns.Count - 1; j++) { rowtemp.CreateCell(j).SetCellValue(dtresult.Rows[i][j].ToString().Trim()); rowtemp.Cells[j].CellStyle = style2; if (j == 17 || j == 9) { rowtemp.GetCell(j).CellStyle = newStyle2; } } } return book; } /// <summary> /// 发送邮件 /// </summary> /// <param name="datapath"></param> /// <param name="mailaddress"></param> public void SendMail(string datapath, string mailaddress) { var mailnAddress = SystemManager.ConfigInfo("mailnAddress"); var mailPwd = SystemManager.ConfigInfo("mailPwd"); var mainUserName = SystemManager.ConfigInfo("mainUserName"); MailMessage mailObj = new MailMessage(); mailObj.From = new MailAddress(mailnAddress); //发送人邮箱地址 mailObj.To.Add(mailaddress); //收件人邮箱地址 mailObj.Subject = "工资条"; //主题 mailObj.Body = "工资条"; //正文 SmtpClient smtp = new SmtpClient(); smtp.Host = "smtp.exmail.qq.com"; //smtp服务器名称 smtp.UseDefaultCredentials = true; smtp.Credentials = new NetworkCredential(mainUserName, mailPwd); //发送人的登录名和密码 Attachment inline = new Attachment(datapath); mailObj.Attachments.Add(inline); smtp.Send(mailObj); } #endregion } }
然后发送人的邮箱和密码就在配置文件中配置
<!--发送人邮箱地址-->
<add key="mailnAddress" value="*****.com"/>
<!--发送人的登录名-->
<add key="mainUserName" value="*****.com"/>
<!--发送人的密码-->
<add key="mailPwd" value="*****"/>
获取config键值
public static string ConfigInfo(string key) { try { string Result = ConfigurationManager.AppSettings[key].ToString(); if (!string.IsNullOrEmpty(Result)) { return Result; } } catch (Exception) { } return ""; }
