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);
}
}
