private void ButtonSendTestMail_Click(object sender, RoutedEventArgs e) { if (string.IsNullOrEmpty(ViewModel.TestMailAddress)) { System.Windows.MessageBox.Show("先输入测试地址!"); } else { try { SheetConfigModel mainSheetConfig = SheetConfigModels[MainConfigModel.MainSheetName]; MailDataModel mailDataModel = new MailDataModel { From = MailConfigModel.MailAddress, Password = MailConfigModel.MailPassword, SMTPAddress = MailConfigModel.SMTPAddress, IsbodyHtml = true, To = ViewModel.TestMailAddress, Subject = FormatString(CalcParamValues(SubjectParams, mainSheetConfig.StartingLine), MailConfigModel.MailSubject), Body = FormatString(CalcParamValues(BodyParams, mainSheetConfig.StartingLine), File.ReadAllText(MainConfigModel.TemplatePath)), Port = MailConfigModel.Port, EnableSsl = MailConfigModel.EnableSsl, Priority = MailConfigModel.Priority, Attachments = CalcParamValues(Attachments, mainSheetConfig.StartingLine), }; MailSenderUtil.Send(mailDataModel); System.Windows.MessageBox.Show("发送成功!"); } catch (Exception ex) { System.Windows.MessageBox.Show(ex.Message); } } }
/// <summary> /// 计算参数 /// </summary> /// <param name="paramKeys"></param> /// <param name="rowIndex"></param> /// <returns></returns> private IList <string> CalcParamValues(IList <string> paramKeys, int rowIndex) { SheetConfigModel mainSheetConfig = SheetConfigModels[MainConfigModel.MainSheetName]; IList <string> result = new List <string>(); foreach (var paramKey in paramKeys) { object[,] paramArray = ReadRangeValues(paramKey); string[] vs = paramKey.Split('.'); if (vs.Length == 3) { result.Add(GetValue(1, paramArray)); } else { if (vs[0] == MainConfigModel.MainSheetName) { string value = paramArray[rowIndex - mainSheetConfig.StartingLine + 1, 1] == null ? string.Empty : paramArray[rowIndex - mainSheetConfig.StartingLine + 1, 1].ToString(); result.Add(value); } else { object[,] mainSheetUniquelyParam = ReadRangeValues(MainConfigModel.MainSheetName + "." + mainSheetConfig.UniquelyIdentifiesLine); string mainSheetUniquelyValue = mainSheetUniquelyParam[rowIndex - mainSheetConfig.StartingLine + 1, 1] == null ? string.Empty : mainSheetUniquelyParam[rowIndex - mainSheetConfig.StartingLine + 1, 1].ToString(); object[,] targetSheetUniquelyParam = ReadRangeValues(vs[0] + "." + SheetConfigModels[vs[0]].UniquelyIdentifiesLine); bool hasUniquelyValue = false; int targetSheetRowIndex = 0; for (int i = 1; i < targetSheetUniquelyParam.Length + 1; i++) { string uniquelyValue = targetSheetUniquelyParam[i, 1] == null ? string.Empty : targetSheetUniquelyParam[i, 1].ToString(); if (uniquelyValue == mainSheetUniquelyValue) { hasUniquelyValue = true; targetSheetRowIndex = i; break; } } if (hasUniquelyValue) { object[,] targetSheetParam = ReadRangeValues(vs[0] + "." + vs[1]); string value = GetValue(targetSheetRowIndex, targetSheetParam); result.Add(value); } else { throw new ApplicationException("无法找到" + mainSheetUniquelyValue + "的唯一标识!"); } } } } return(result); }
private object[,] ReadRangeValues(string paramKey) { string[] vs = paramKey.Split('.'); object[,] paramArray; if (ParamsDict.ContainsKey(paramKey)) { paramArray = ParamsDict[paramKey]; } else { SheetConfigModel sheetConfigModel = SheetConfigModels[vs[0]]; if (vs.Length == 2) { var array = SheetName2ExcelSheet[vs[0]].Range[vs[1] + sheetConfigModel.StartingLine + ":" + vs[1] + sheetConfigModel.EndLine].Value[10]; if (array as object[, ] != null) { paramArray = array; } else { int[] arrParam = { 1, 1 }; paramArray = Array.CreateInstance(typeof(object), arrParam, arrParam) as object[, ]; paramArray[1, 1] = array; } } else { int[] arrParam = { 1, 1 }; paramArray = Array.CreateInstance(typeof(object), arrParam, arrParam) as object[, ]; paramArray[1, 1] = SheetName2ExcelSheet[vs[0]].Range[vs[1] + vs[2]].Value[10]; } ParamsDict[paramKey] = paramArray; } return(paramArray); }
/// <summary> /// 构建邮件 /// </summary> /// <returns></returns> private void BuildAndSendMails() { if (Workbook != null) { try { //主sheet配置 SheetConfigModel mainSheetConfig = SheetConfigModels[MainConfigModel.MainSheetName]; //模板字符串 string bodyHtmlString = File.ReadAllText(MainConfigModel.TemplatePath); //失败个数 int faildCount = 0; //邮件总数 int mailCount = mainSheetConfig.EndLine - mainSheetConfig.StartingLine + 1; //成功的标识 object[,] successSimple = ReadRangeValues(MainConfigModel.MainSheetName + "." + MainConfigModel.SuccessSimpleLocation); //回写的标识地址 IList <string> mailDataSuccessLocation = new List <string>(); for (int i = mainSheetConfig.StartingLine; i <= mainSheetConfig.EndLine; i++) { PushMessage("正在处理(" + (i - mainSheetConfig.StartingLine + 1) + "/" + mailCount + ")\n"); try { if (!(MainConfigModel.SuccessSimple == (successSimple[i - mainSheetConfig.StartingLine + 1, 1] == null ? string.Empty : successSimple[i - mainSheetConfig.StartingLine + 1, 1].ToString()))) { MailDataModel mailDataModel = new MailDataModel { From = MailConfigModel.MailAddress, Password = MailConfigModel.MailPassword, SMTPAddress = MailConfigModel.SMTPAddress, IsbodyHtml = true, To = FormatString(CalcParamValues(MailToParams, i), MailConfigModel.MailTo), Subject = FormatString(CalcParamValues(SubjectParams, i), MailConfigModel.MailSubject), Body = FormatString(CalcParamValues(BodyParams, i), bodyHtmlString), Port = MailConfigModel.Port, EnableSsl = MailConfigModel.EnableSsl, Priority = MailConfigModel.Priority, Attachments = CalcParamValues(Attachments, i), }; MailSenderUtil.Send(mailDataModel); mailDataSuccessLocation.Add(MainConfigModel.SuccessSimpleLocation + i); } } catch (Exception ex) { PushMessage(ex.Message + "\n"); faildCount++; continue; } } //回写成功标志 foreach (var range in GetMergeRanges(LoadWorksheet(Workbook, MainConfigModel.MainSheetName), mailDataSuccessLocation)) { range.Value2 = MainConfigModel.SuccessSimple; } PushMessage("处理完毕。" + "\n"); PushMessage("总数量" + (mailCount) + "\n"); PushMessage("失败数量" + (faildCount) + "\n"); } catch (Exception ex) { PushMessage("出现错误:" + ex.Message + "\n"); } } }
/// <summary> /// 加载配置文件 /// </summary> /// <returns></returns> private bool LoadConfig() { string configPath = ViewModel.ConfigFilePath; if (File.Exists(configPath)) { try { MainConfigModel = new MainConfigModel { FilePath = IniFileReadUtil.ReadIniData("Main", "FilePath", null, configPath), SuccessSimple = IniFileReadUtil.ReadIniData("Main", "SuccessSimple", null, configPath), SuccessSimpleLocation = IniFileReadUtil.ReadIniData("Main", "SuccessSimpleLocation", null, configPath), SheetNames = IniFileReadUtil.ReadIniData("Main", "SheetNames", null, configPath).Split(',').ToList(), MainSheetName = IniFileReadUtil.ReadIniData("Main", "MainSheetName", null, configPath), TemplatePath = IniFileReadUtil.ReadIniData("Main", "TemplatePath", null, configPath), BodyParamCount = int.Parse(IniFileReadUtil.ReadIniData("Main", "BodyParamCount", null, configPath)), SubjectParamCount = int.Parse(IniFileReadUtil.ReadIniData("Main", "SubjectParamCount", null, configPath)), MailToParamCount = int.Parse(IniFileReadUtil.ReadIniData("Main", "MailToParamCount", null, configPath)), AttachmentCount = int.Parse(IniFileReadUtil.ReadIniData("Main", "AttachmentCount", null, configPath)), }; MailConfigModel = new MailConfigModel { MailTo = IniFileReadUtil.ReadIniData("Mail", "MailTo", null, configPath), MailAddress = IniFileReadUtil.ReadIniData("Mail", "MailAddress", null, configPath), MailPassword = IniFileReadUtil.ReadIniData("Mail", "MailPassword", null, configPath), MailSubject = IniFileReadUtil.ReadIniData("Mail", "MailSubject", null, configPath), SMTPAddress = IniFileReadUtil.ReadIniData("Mail", "SMTPAddress", null, configPath), Port = int.Parse(IniFileReadUtil.ReadIniData("Mail", "Port", null, configPath)), EnableSsl = bool.Parse(IniFileReadUtil.ReadIniData("Mail", "EnableSsl", null, configPath)), Priority = int.Parse(IniFileReadUtil.ReadIniData("Mail", "Priority", null, configPath)), }; foreach (var sheetName in MainConfigModel.SheetNames) { SheetConfigModel sheetConfigModel = new SheetConfigModel { StartingLine = int.Parse(IniFileReadUtil.ReadIniData(sheetName, "StartingLine", null, configPath)), EndLine = int.Parse(IniFileReadUtil.ReadIniData(sheetName, "EndLine", null, configPath)), UniquelyIdentifiesLine = IniFileReadUtil.ReadIniData(sheetName, "UniquelyIdentifiesLine", null, configPath), }; SheetConfigModels[sheetName] = sheetConfigModel; } for (int i = 0; i < MainConfigModel.BodyParamCount; i++) { BodyParams.Add(IniFileReadUtil.ReadIniData("BodyParams", i.ToString(), null, configPath)); } for (int i = 0; i < MainConfigModel.SubjectParamCount; i++) { SubjectParams.Add(IniFileReadUtil.ReadIniData("SubjectParams", i.ToString(), null, configPath)); } for (int i = 0; i < MainConfigModel.MailToParamCount; i++) { MailToParams.Add(IniFileReadUtil.ReadIniData("MailToParams", i.ToString(), null, configPath)); } for (int i = 0; i < MainConfigModel.AttachmentCount; i++) { Attachments.Add(IniFileReadUtil.ReadIniData("Attachments", i.ToString(), null, configPath)); } //加载工作簿相关内容 Workbook = LoadWorkbook(MainConfigModel.FilePath); foreach (var sheetName in MainConfigModel.SheetNames) { SheetName2ExcelSheet[sheetName] = LoadWorksheet(Workbook, sheetName); } PushMessage("读取成功。\n"); return(true); } catch (Exception ex) { PushMessage("读取失败。\n"); PushMessage(ex.Message + "\n"); return(false); } } else { PushMessage("找不到配置文件!\n"); return(false); } }