private List<MailModel> SearchEmailData()
{
MailModel model = new MailModel();
model.Source = ((ListItem)SourceCombo.SelectedItem).Id;
model.ProductType = ((ListItem)TypeComboBox.SelectedItem).Id;
model.Country = ((ListItem)CountryComboBox.SelectedItem).Id;
model.Verify1 = Convert.ToInt32(((ListItem)VerifyComboBox.SelectedItem).Id);
model.Email = EmailTxtBox.Text.Trim();
model.Username = NameTxtBox.Text.Trim();
return Dao.GetMailModelList(model);
}
public void UpdateMailVerify(MailModel item)
{
string sql = @"update mail_address set Verify1 = " + item.Verify1 + " where id = " + item.Id;
dbHelper.ExecuteNonQuery(sql);
}
public List<MailModel> GetMailModelList(MailModel searchItem)
{
string sql = "SELECT id, Email, Company, Country, Username, Subject,ProductType, Source, Verify1,Verify2, SendDate FROM mail_address";
SQLiteParameter[] parameter = null;
if (searchItem != null)
{
List<SQLiteParameter> parameterList = new List<SQLiteParameter>();
string condition = " where 1 = 1";
if (!string.IsNullOrEmpty(searchItem.Email))
{
condition += " and Email like '%"+searchItem.Email+"%'";
}
if (!string.IsNullOrEmpty(searchItem.Username)) {
condition += " and Username like '%" + searchItem.Username + "%'";
}
if (!string.IsNullOrEmpty(searchItem.ProductType))
{
condition += " and ProductType = @ProductType";
parameterList.Add(new SQLiteParameter("@ProductType", searchItem.ProductType));
}
if (!string.IsNullOrEmpty(searchItem.Source))
{
condition += " and Source = @Source";
parameterList.Add(new SQLiteParameter("@Source", searchItem.Source));
}
if (searchItem.Verify1 >= 0)
{
condition += " and Verify1 = " + searchItem.Verify1;
}
if (!string.IsNullOrEmpty(searchItem.Country))
{
condition += " and Country = @Country";
parameterList.Add(new SQLiteParameter("@Country", searchItem.Country));
}
sql = sql + condition;
parameter = parameterList.ToArray();
}
DataTable dt = dbHelper.ExecuteDataTable(sql, parameter);
List<MailModel> list = new List<MailModel>();
foreach (DataRow row in dt.Rows)
{
MailModel model = new MailModel();
model.Id = Convert.ToInt32(row["id"]);
model.Email = Convert.ToString(row["Email"]);
model.Company = Convert.ToString(row["Company"]);
model.Country = Convert.ToString(row["Country"]);
model.Username = Convert.ToString(row["Username"]);
model.Subject = Convert.ToString(row["Subject"]);
model.ProductType = Convert.ToString(row["ProductType"]);
model.Source = Convert.ToString(row["Source"]);
model.Verify1 = Convert.ToInt32(row["Verify1"]);
model.Verify2 = Convert.ToInt32(row["Verify2"]);
model.SendDate = Convert.ToString(row["SendDate"]);
list.Add(model);
}
return list;
}
void worker_DoWork(object sender, DoWorkEventArgs e)
{
string SelectExcelFile = filePath.Text;
Workbook workBook = new Workbook();
try
{
workBook.Open(SelectExcelFile);
}
catch (Exception ex)
{
errorMsg.Text = "打开选定的Excel文件出错: " + ex.Message;
this.importBtn.Enabled = true;
this.pictureBox1.Visible = false;
return;
}
List<MailModel> mailModelList = new List<MailModel>();
foreach (Worksheet sheet in workBook.Worksheets)
{
int EmailCol = -1;
int CountryCol = -1;
int BuyerCol = -1;
int CompanyCol = -1;
int SubjectCol = -1;
int DateCol = -1;
int ProductTypeCol = -1;
int SourceCol = -1;
string sheetName = sheet.Name;
Cells cells = sheet.Cells;
for (int j = 0; j < cells.MaxDataColumn + 1; j++)
{
string value = sheet.Cells[0, j].StringValue.Trim();
if ("email".Equals(value.ToLower()))
{
EmailCol = j;
}
if ("country".Equals(value.ToLower()))
{
CountryCol = j;
}
if ("name".Equals(value.ToLower()))
{
BuyerCol = j;
}
if ("company".Equals(value.ToLower()))
{
CompanyCol = j;
}
if ("date".Equals(value.ToLower()))
{
DateCol = j;
}
if ("subject".Equals(value.ToLower()))
{
SubjectCol = j;
}
if ("product".Equals(value.ToLower()))
{
ProductTypeCol = j;
}
if ("source".Equals(value.ToLower()))
{
SourceCol = j;
}
System.Diagnostics.Trace.WriteLine(value);
}
for (int i = 1; i < cells.MaxDataRow + 1; i++)
{
if (EmailCol == -1)
{
continue;
}
string Email = sheet.Cells[i, EmailCol].StringValue.Trim();
if (!FormatValidation.IsEmail(Email))
{
continue;
}
MailModel model = new MailModel();
model.Email = Email;
if (CountryCol != -1)
{
model.Country = sheet.Cells[i, CountryCol].StringValue.Trim();
}
if (BuyerCol != -1)
{
string name = sheet.Cells[i, BuyerCol].StringValue.Trim();
name = name.Replace(":", "").Trim();
name = name.Replace(",", "").Trim();
if (name.StartsWith("dear") || name.StartsWith("Dear"))
{
name = name.Substring(4).Trim();
}
if (name.StartsWith("Mr.") || name.StartsWith("Ms."))
{
name = name.Substring(3).Trim();
}
if (name.StartsWith("Mr") || name.StartsWith("Ms"))
{
name = name.Substring(2).Trim();
}
model.Username = name;
}
if (CompanyCol != -1)
{
model.Company = sheet.Cells[i, CompanyCol].StringValue.Trim();
}
if (SubjectCol != -1)
{
model.Subject = sheet.Cells[i, SubjectCol].StringValue.Trim();
}
if (DateCol != -1)
{
model.SendDate = sheet.Cells[i, DateCol].StringValue.Trim();
}
if (ProductTypeCol != -1)
{
string product = sheet.Cells[i, ProductTypeCol].StringValue.Trim();
if (product.ToLower().IndexOf("moblie") >= 0)
{
model.ProductType = "Moblie";
}
else if (product.ToLower().IndexOf("netbook") >= 0)
{
model.ProductType = "NetBook";
}
else if (product.ToLower().IndexOf("projector") >= 0)
{
model.ProductType = "Projector";
}
else if (product.ToLower().IndexOf("ebook") >= 0 || product.ToLower().IndexOf("e-book") >= 0)
{
model.ProductType = "Other";
}
else {
model.ProductType = product;
}
}
else if (!sheetName.ToLower().StartsWith("sheet"))
{
model.ProductType = sheetName;
}
else
{
model.ProductType = "Other";
}
if (SourceCol != -1)
{
model.Source = sheet.Cells[i, SourceCol].StringValue.Trim();
}
mailModelList.Add(model);
}
}
if (mailModelList.Count == 0)
{
errorMsg.Text = "此Excel中未包含任何邮件数据。请重新选择。";
this.importBtn.Enabled = true;
this.pictureBox1.Visible = false;
return;
}
MailModelDAO mailModelDAO = DAOFactory.Instance.GetMailModelDAO();
mailModelDAO.Insert(mailModelList);
this.importBtn.Enabled = true;
this.pictureBox1.Visible = false;
SuccessLoadData = true;
this.Close();
}