public List<OneMail> ReadExcelFileByPath(string path)
{
int i = 0;
OneMail mail = new OneMail();
string[] tmp = null;
List<OneMail> ret = new List<OneMail>();
ExcelQueryFactory excel = new ExcelQueryFactory(path);
int Worksheetcount = excel.GetWorksheetNames().Count();
for (i = 0; i < Worksheetcount; i++)
{
IQueryable<Row> query = from p in excel.Worksheet(i)
select p;
foreach (Row item in query)
{
if (!string.IsNullOrEmpty(item[0].ToString().Trim()))
{
if (!string.IsNullOrEmpty(mail.alias))
{
ret.Add(mail);
}
mail = new OneMail();
tmp = item[2].ToString().Replace(')', ' ').Split('(');
mail.alias = item[0].ToString().Trim();
mail.TMin = tmp[0].Trim();
mail.UT = tmp[1].Trim();
/// temporary use
mail.MTD = mail.UT;
///
mail.SRList = new List<SR>();
}
else
{
mail.SRList.Add(new SR(
item[1].ToString().Trim(),
item[3].ToString().Trim(),
Convert.ToInt32(item[2].ToString().Trim()
)));
}
}
}
if (!string.IsNullOrEmpty(mail.alias))
{
ret.Add(mail);
}
return ret;
}
public string SpellEmail(string alias, OneMail mail)
{
DateTime now = DateTime.Now.AddDays(-1);
if (now.DayOfWeek.ToString() == "Sunday")
{
now = DateTime.Now.AddDays(-3);
}
StringBuilder strB = new StringBuilder();
strB.Append(preGreet);
strB.Append(alias);
strB.Append(posGreet);
strB.Append(preUT);
strB.Append(mail.UT);
strB.Append(posUT);
strB.Append(preMTD);
strB.Append(mail.MTD);
strB.Append(posMTD);
strB.Append(note1);
strB.Append(preAlias);
strB.Append(alias);
strB.Append(posAlias);
strB.Append(preDate);
strB.Append(now.Year + "-" + now.Month + "-" + now.Day);
strB.Append(posDate);
strB.Append(note2);
strB.Append(preTable);
strB.Append(alias);
strB.Append(posTable);
strB.Append(mail.TMin + " Min");
strB.Append(preSR);
foreach (SR x in mail.SRList)
{
strB.Append(preSRNum);
strB.Append(x.Number);
strB.Append(posSRNum);
strB.Append(preSRTitle);
strB.Append(x.Title);
strB.Append(posSRTitle);
strB.Append(preSRTime);
strB.Append(x.Min + " Min");
strB.Append(posSRTime);
}
strB.Append(posSR);
return strB.ToString();
}