public static List <ExcelRow> GetWorksheet(Excel.Worksheet worksheet)
{
Excel.Range range = worksheet.UsedRange;
int numEntries = range.Rows.Count;
List <ExcelRow> rows = new List <ExcelRow>(numEntries);
Object[,] valueArray = (Object[, ])range.get_Value(Excel.XlRangeValueDataType.xlRangeValueDefault);
if (numEntries < 2)
{
MessageBox.Show("Worksheet is empty.", "Error");
return(null);
}
for (int i = 2; i < numEntries + 1; ++i)
{
try
{
ExcelRow newRow = new ExcelRow(valueArray, i);
rows.Add(newRow);
}
catch (InvalidExcelEntryException ex)
{
DialogResult skipRow = MessageBox.Show(
String.Format("An invalid data error occured while parsing.\n\nRow: {0}\nData type: {1}\nData value: {2}\n\nPress \"OK\" to skip this row and continue or \"Cancel\" to discontinue loading operation.", i.ToString(), ex.DataType, ex.DataValue),
"Error",
MessageBoxButtons.OKCancel);
if (skipRow == DialogResult.Cancel)
{
return(null);
}
}
catch (Exception ex)
{
DialogResult skipRow = MessageBox.Show(
String.Format("An unidentified error occured while parsing row {0}:\n\n{1}\n\nPress \"OK\" to skip this row and continue or \"Cancel\" to discontinue loading operation.", i.ToString(), ex.Message),
"Error",
MessageBoxButtons.OKCancel);
if (skipRow == DialogResult.Cancel)
{
return(null);
}
}
}
return(rows);
}
public static List<ExcelRow> GetWorksheet(Excel.Worksheet worksheet)
{
Excel.Range range = worksheet.UsedRange;
int numEntries = range.Rows.Count;
List<ExcelRow> rows = new List<ExcelRow>(numEntries);
Object[,] valueArray = (Object[,])range.get_Value(Excel.XlRangeValueDataType.xlRangeValueDefault);
if (numEntries < 2)
{
MessageBox.Show("Worksheet is empty.", "Error");
return null;
}
for (int i = 2; i < numEntries + 1; ++i)
{
try
{
ExcelRow newRow = new ExcelRow(valueArray, i);
rows.Add(newRow);
}
catch (InvalidExcelEntryException ex)
{
DialogResult skipRow = MessageBox.Show(
String.Format("An invalid data error occured while parsing.\n\nRow: {0}\nData type: {1}\nData value: {2}\n\nPress \"OK\" to skip this row and continue or \"Cancel\" to discontinue loading operation.", i.ToString(), ex.DataType, ex.DataValue),
"Error",
MessageBoxButtons.OKCancel);
if (skipRow == DialogResult.Cancel)
return null;
}
catch (Exception ex)
{
DialogResult skipRow = MessageBox.Show(
String.Format("An unidentified error occured while parsing row {0}:\n\n{1}\n\nPress \"OK\" to skip this row and continue or \"Cancel\" to discontinue loading operation.", i.ToString(), ex.Message),
"Error",
MessageBoxButtons.OKCancel);
if (skipRow == DialogResult.Cancel)
return null;
}
}
return rows;
}
public static void Send(Account account, ExcelRow entry)
{
try
{
ServicePointManager.ServerCertificateValidationCallback += (o, c, ch, er) => true;
MailMessage mailMsg = new MailMessage();
MailAddress fromAddress;
if (account.DisplayName == "")
{
fromAddress = new MailAddress(account.EmailAddress);
}
else
{
fromAddress = new MailAddress(account.EmailAddress, account.DisplayName);
}
mailMsg.To.Add(entry.EmailAddress);
mailMsg.From = fromAddress;
mailMsg.IsBodyHtml = true;
// Subject and Body
mailMsg.Subject = String.Format("Your Monthly Energy Report Card");
mailMsg.Body = new EmailBody(TEMPLATES[entry.ExperimentalCondition - 1]).Generate(entry);
// Init SmtpClient and send on port 587 in my case. (Usual=port25)
SmtpClient smtpClient = new SmtpClient(account.SmtpHostname, account.PortNumber);
smtpClient.EnableSsl = account.SslIsEnabled;
smtpClient.Credentials = new System.Net.NetworkCredential(account.LoginId, account.Password);
smtpClient.Send(mailMsg);
}
// TODO: add exception handling for email sending
catch (Exception e)
{
Console.WriteLine(e.Message);
throw;
}
}
public string Generate(ExcelRow entry)
{
string toReturn = "";
for (int i = 0; i < m_paragraphText.Count; ++i)
{
toReturn += m_paragraphText[i];
if (m_insertionKeys.Count > i)
{
switch (m_insertionKeys[i])
{
case InsertionKey.Usage:
toReturn += String.Format("{0:0.#}", entry.YourEnergyUse);
break;
case InsertionKey.A:
toReturn += USER_GROUP_A[entry.Lifestyle];
break;
case InsertionKey.UserGroup:
toReturn += USER_GROUP_NAMES[entry.Lifestyle];
break;
case InsertionKey.YouTendTo:
toReturn += USER_GROUP_TEND_TO[entry.Lifestyle];
break;
case InsertionKey.When:
toReturn += USER_GROUP_WHEN[entry.Lifestyle];
break;
case InsertionKey.Mean:
toReturn += String.Format("{0:0.#}", entry.MeanEnergyUse);
break;
case InsertionKey.Low:
toReturn += String.Format("{0:0.#}", entry.LowestEnergyUse);
break;
case InsertionKey.High:
toReturn += String.Format("{0:0.#}", entry.HighestEnergyUse);
break;
case InsertionKey.RatingTitle:
toReturn += RATING_TITLES[entry.Rating];
break;
case InsertionKey.RatingText:
toReturn += String.Format(
RATING_TEXTS[entry.Rating],
Emphasize(USER_GROUP_NAMES[entry.Lifestyle])
);
break;
case InsertionKey.CursorYou:
toReturn += String.Format(
"{0:0.#}",
(entry.YourEnergyUse - entry.LowestEnergyUse) /
(entry.HighestEnergyUse - entry.LowestEnergyUse) *
(81.4 - 1.3) + 1.3
);
break;
case InsertionKey.CursorMean:
toReturn += String.Format(
"{0:0.#}",
(entry.MeanEnergyUse - entry.LowestEnergyUse) /
(entry.HighestEnergyUse - entry.LowestEnergyUse) *
(81.4 - 1.3) + 1.3
);
break;
case InsertionKey.UserGroupImg:
toReturn += USER_GROUP_IMG_URLS[entry.Lifestyle];
break;
}
}
}
return(toReturn);
}
public static void Send(Account account, ExcelRow entry)
{
try
{
ServicePointManager.ServerCertificateValidationCallback += (o, c, ch, er) => true;
MailMessage mailMsg = new MailMessage();
MailAddress fromAddress;
if (account.DisplayName == "")
fromAddress = new MailAddress(account.EmailAddress);
else
fromAddress = new MailAddress(account.EmailAddress, account.DisplayName);
mailMsg.To.Add(entry.EmailAddress);
mailMsg.From = fromAddress;
mailMsg.IsBodyHtml = true;
// Subject and Body
mailMsg.Subject = String.Format("Room {0}'s weekly energy report card", entry.RoomNumber);
switch (entry.MessageType)
{
case MESSAGE_TYPE_CONTROL:
mailMsg.Body = s_emailBodyControl.Generate(entry.RoomNumber, entry.YourEnergyUse, entry.OtherEnergyUse, entry.BestEnergyUse, entry.Rating);
break;
case MESSAGE_TYPE_GENERIC:
mailMsg.Body = s_emailBodyGeneric.Generate(entry.RoomNumber, entry.YourEnergyUse, entry.OtherEnergyUse, entry.BestEnergyUse, entry.Rating);
break;
case MESSAGE_TYPE_PERSONALIZED:
mailMsg.Body = s_emailBodyPersonalized.Generate(entry.RoomNumber, entry.YourEnergyUse, entry.OtherEnergyUse, entry.BestEnergyUse, entry.Rating);
break;
}
// Init SmtpClient and send on port 587 in my case. (Usual=port25)
SmtpClient smtpClient = new SmtpClient(account.SmtpHostname, account.PortNumber);
smtpClient.EnableSsl = account.SslIsEnabled;
smtpClient.Credentials = new System.Net.NetworkCredential(account.LoginId, account.Password);
smtpClient.Send(mailMsg);
}
// TODO: add exception handling for email sending
catch (Exception e)
{
Console.WriteLine(e.Message);
throw;
}
}