protected void btn_excel_Click(object sender, EventArgs e)
{
if (this.upExcelFile.PostedFile.FileName.Length > 0)
{
this.initState();
}
else
{
this.lbl_upresult.Text = "请选择要上传的Excel文件!";
return;
}
string filename = upExcelFile.PostedFile.FileName;
string subfile = filename.Substring(filename.LastIndexOf(".") + 1);
if (subfile.ToUpper() != "XLS" && subfile.ToUpper() != "XLSX")
{
this.lbl_upresult.Text = "导入文件格式错误,必须为XLS,XLSX格式!";
ProductInfoBus.LogInsert(userinfo.CompanyCD, userinfo.DeptID, userinfo.UserID, Request.QueryString["ModuleID"].ToString(), 0, 0, this.lbl_upresult.Text);
}
else
{
try
{
//获取企业上传文件路径
string upfilepath = ProductInfoBus.GetCompanyUpFilePath(userinfo.CompanyCD);//得到格式如:"D:\zhou"
//获取企业并构造企业上传文件名称
Session["newfile"] = DateTime.Now.ToString("yyyyMMddhhmmss") + filename.Substring(filename.LastIndexOf("\\") + 1);
//Session["newfile"] = "HR_ZHOU.xls";
upExcelFile.PostedFile.SaveAs(upfilepath + @"\" + Session["newfile"].ToString());
this.lbl_upresult.Text = "Excel文件上传成功!";
this.setup1.Enabled = true;
//将excel中的数据读取到ds中
try
{
ds = EmployeeInfoBus.ReadEexcel(upfilepath + @"\" + Session["newfile"].ToString(), userinfo.CompanyCD);
if (ds.Tables[0].Rows.Count < 1)
{
initState();
this.lbl_upresult.Text = "您导入的Excel表没有数据!";
return;
}
}
catch (Exception ex)
{
initState();
this.lbl_upresult.Text = "数据读取失败,原因：" + ex.Message;
ProductInfoBus.LogInsert(userinfo.CompanyCD, userinfo.DeptID, userinfo.UserID, Request.QueryString["ModuleID"].ToString(), 0, 0, this.lbl_upresult.Text);
}
}
catch (Exception ex)
{
this.setup1.Enabled = false;
this.lbl_upresult.Text = "数据读取失败,原因：" + ex.Message;
ProductInfoBus.LogInsert(userinfo.CompanyCD, userinfo.DeptID, userinfo.UserID, Request.QueryString["ModuleID"].ToString(), 0, 0, this.lbl_upresult.Text);
}
}
}
/// <summary>
/// 上传excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btn_excel_Click(object sender, EventArgs e)
{
string upfilepath = "";
if (upExcelFile.PostedFile.FileName.Length < 1)
{
this.lbl_result.Text = "请选择要上传的Excel文件!";
return;
}
else
{
this.initvalidate();
}
string filename = upExcelFile.PostedFile.FileName;
string subfile = filename.Substring(filename.LastIndexOf(".") + 1);
if (subfile.ToUpper() != "XLS" && subfile.ToUpper() != "XLSX")
{
this.lbl_result.Text = "导入文件格式错误,必须为XLS,XLSX格式!";
}
else
{
try
{
//获取企业上传文件路径
upfilepath = ProductInfoBus.GetCompanyUpFilePath(UserInfo.CompanyCD);//得到格式如:"D:\zhou"
//获取企业并构造企业上传文件名称
Session["newfile"] = DateTime.Now.ToString("yyyyMMddhhmmss") + filename.Substring(filename.LastIndexOf("\\") + 1);
upfilepath += @"\" + Session["newfile"].ToString();
upExcelFile.PostedFile.SaveAs(upfilepath);
this.lbl_result.Text = "Excel文件上传成功!";
this.setup2.Enabled = true;
//将excel中的数据读取到ds中
try
{
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;data source=" + upfilepath;
string sql = "SELECT distinct * FROM [Sheet1$] WHERE 流水号 IS NOT NULL";
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sql, connStr);
dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count < 1)
{
initvalidate();
this.lbl_result.Text = "您导入的Excel表没有数据!";
}
}
catch (Exception ex)
{
initvalidate();
this.lbl_result.Text = "数据读取失败,原因：" + ex.Message.ToString();
ProductInfoBus.LogInsert(UserInfo.CompanyCD, UserInfo.DeptID, UserInfo.UserID, Request.QueryString["ModuleID"].ToString(), 0, 0, this.lbl_result.Text);
}
}
catch (Exception ex)
{
this.setup2.Enabled = false;
this.lbl_result.Text = "数据读取失败,原因：" + ex.Message;
ProductInfoBus.LogInsert(UserInfo.CompanyCD, UserInfo.DeptID, UserInfo.UserID, Request.QueryString["ModuleID"].ToString(), 0, 0, this.lbl_result.Text);
}
finally
{
if (File.Exists(upfilepath))
{
File.Delete(upfilepath);
}
}
}
}
protected void btn_excel_Click(object sender, EventArgs e)
{
#region 文件
//上传文件
if (upExcelFile.PostedFile.FileName.Length < 1)
{
this.lbl_result.Text = "请选择要上传的Excel文件!";
return;
}
else
{
this.initvalidate();
}
string filename = upExcelFile.PostedFile.FileName;
string subfile = filename.Substring(filename.LastIndexOf(".") + 1);
if (subfile.ToUpper() != "XLS" && subfile.ToUpper() != "XLSX")
{
this.lbl_result.Text = "导入文件格式错误,必须为XLS,XLSX格式!";
//ProductInfoBus.LogInsert(userinfo.CompanyCD, userinfo.DeptID, userinfo.UserID, "", 0, "导入文件格式错误");
ProductInfoBus.LogInsert(userinfo.CompanyCD, userinfo.DeptID, userinfo.UserID, Request.QueryString["ModuleID"].ToString(), 0, 0, this.lbl_result.Text);
}
else
{
try
{
//获取企业上传文件路径
string upfilepath = ProductInfoBus.GetCompanyUpFilePath(userinfo.CompanyCD);//得到格式如:"D:\zhou"
//获取企业并构造企业上传文件名称
Session["newfile"] = DateTime.Now.ToString("yyyyMMddhhmmss") + filename.Substring(filename.LastIndexOf("\\") + 1);
upExcelFile.PostedFile.SaveAs(upfilepath + @"\" + Session["newfile"].ToString());
#region
try
{
//ds = ProductInfoBus.ReadEexcel(upfilepath + @"\" + Session["newfile"].ToString(), userinfo.CompanyCD);//导入临时表
ds = ConvertXlsToDataSet(upfilepath + @"\" + Session["newfile"].ToString());
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
try
{
string RetVal = ds.Tables[0].Rows[0]["序号"].ToString() + ds.Tables[0].Rows[0]["客户编号"].ToString() +
ds.Tables[0].Rows[0]["客户名称"].ToString() + ds.Tables[0].Rows[0]["客户简介"].ToString() +
ds.Tables[0].Rows[0]["人员编号"].ToString() + ds.Tables[0].Rows[0]["分管业务员"].ToString() +
ds.Tables[0].Rows[0]["区域"].ToString() +
ds.Tables[0].Rows[0]["省"].ToString() + ds.Tables[0].Rows[0]["市"].ToString() +
ds.Tables[0].Rows[0]["联系人"].ToString() + ds.Tables[0].Rows[0]["电话"].ToString() +
ds.Tables[0].Rows[0]["手机"].ToString() + ds.Tables[0].Rows[0]["传真"].ToString() +
ds.Tables[0].Rows[0]["在线咨询"].ToString() + ds.Tables[0].Rows[0]["公司网址"].ToString() +
ds.Tables[0].Rows[0]["邮编"].ToString() + ds.Tables[0].Rows[0]["电子邮件"].ToString() +
ds.Tables[0].Rows[0]["收货地址"].ToString() + ds.Tables[0].Rows[0]["经营范围"].ToString() +
ds.Tables[0].Rows[0]["单位性质"].ToString() + ds.Tables[0].Rows[0]["成立时间"].ToString() +
ds.Tables[0].Rows[0]["注册资本(万元)"].ToString() + ds.Tables[0].Rows[0]["资产规模(万元)"].ToString() +
ds.Tables[0].Rows[0]["年销售额(万元)"].ToString() + ds.Tables[0].Rows[0]["员工总数(个)"].ToString() +
ds.Tables[0].Rows[0]["法人代表"].ToString() + ds.Tables[0].Rows[0]["行业"].ToString() +
ds.Tables[0].Rows[0]["注册地址"].ToString() + ds.Tables[0].Rows[0]["关系描述"].ToString() +
ds.Tables[0].Rows[0]["发展计划"].ToString() + ds.Tables[0].Rows[0]["合作方法"].ToString();
}
catch
{
this.lbl_result.Text = "数据读取失败,可能原因：Excel模板格式不正确";
//initvalidate();
this.tr_result.Visible = false;
this.lbl_validateend.Visible = false;
ProductInfoBus.DeleteFile(userinfo.CompanyCD, Session["newfile"].ToString());
return;
}
this.lbl_result.Text = "Excel文件上传成功!";
this.setup1.Enabled = true;
this.setup2.Enabled = false;
this.setup3.Enabled = false;
this.setup4.Enabled = false;
this.setup5.Enabled = false;
this.setup6.Enabled = false;
this.setup7.Enabled = false;
this.setup8.Enabled = false;
this.tr_result.Visible = false;
ProductInfoBus.DeleteFile(userinfo.CompanyCD, Session["newfile"].ToString());
}
else
{
initvalidate();
ProductInfoBus.LogInsert(userinfo.CompanyCD, userinfo.DeptID, userinfo.UserID, Request.QueryString["ModuleID"].ToString(), 0, 0, this.lbl_result.Text);
this.lbl_result.Text = "您导入的Excel表没有数据!";
}
}
else
{
initvalidate();
ProductInfoBus.LogInsert(userinfo.CompanyCD, userinfo.DeptID, userinfo.UserID, Request.QueryString["ModuleID"].ToString(), 0, 0, this.lbl_result.Text);
this.lbl_result.Text = "您导入的Excel表没有数据!";
}
}
catch (Exception ex)
{
//ProductInfoBus.LogInsert(userinfo.CompanyCD, userinfo.DeptID, userinfo.UserID, "", 0, "数据读取失败");
ProductInfoBus.LogInsert(userinfo.CompanyCD, userinfo.DeptID, userinfo.UserID, Request.QueryString["ModuleID"].ToString(), 0, 0, this.lbl_result.Text);
initvalidate();
this.lbl_result.Text = "数据读取失败,原因：" + ex.Message.ToString();
//this.lbl_result.Text = "数据读取失败!";
}
#endregion
}
catch (Exception ex)
{
//this.setup1.Enabled = false;
ProductInfoBus.LogInsert(userinfo.CompanyCD, userinfo.DeptID, userinfo.UserID, Request.QueryString["ModuleID"].ToString(), 0, 0, this.lbl_result.Text);
this.lbl_result.Text = "数据读取失败,原因：" + ex.Message;
}
}
#endregion
}
protected void imgExportExcelUC_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
/*文件名*/
string FileName = string.Empty;
/*上传后完整的文件路径包含文件名*/
string FileNewUrl = string.Empty;
/*验证登陆*/
if (UserInfo == null)
{
ErrorMsg += "登录已超时，请重新登陆\\n";
}
#region 验证
/*获取公司的上传路径*/
string FileUrl = ProductInfoBus.GetCompanyUpFilePath(UserInfo.CompanyCD);
/*验证该公司路径是否存在 不存在则创建*/
DirectoryInfo dir = new DirectoryInfo(FileUrl);
if (!dir.Exists)
{
try
{
dir.Create();
}
catch (Exception ex)
{
MessageBox(ex.ToString(), "msg9");
return;
}
}
/*验证是否选择了文件*/
if (string.IsNullOrEmpty(fileExportExcel.PostedFile.FileName))
{
MessageBox("请选择需要导入的Excel文件", "msg1");
return;
}
/*验证文件类型*/
string FileExtension = fileExportExcel.PostedFile.FileName.Split('.')[1].ToUpper();
if (FileExtension != "XLS" && FileExtension != "XLSX")
{
ErrorMsg += "文件错误，请上传正确的Excel文件\\n";
}
/*判断是否存在异常*/
if (!string.IsNullOrEmpty(ErrorMsg))
{
MessageBox(ErrorMsg, "msg2");
return;
}
/*上传文件*/
FileName = Guid.NewGuid().ToString() + "." + FileExtension.ToLower();
FileNewUrl = FileUrl + "\\" + FileName;
try
{
fileExportExcel.PostedFile.SaveAs(FileNewUrl);
}
catch (Exception ex)
{
MessageBox(ex.ToString(), "msg10");
}
#endregion
#region Excel转换成datatable
/*验证是否包含异常信息*/
if (!string.IsNullOrEmpty(ErrorMsg))
{
MessageBox(ErrorMsg, "msg3");
return;
}
/*将Excel转换成DataTable*/
System.Data.DataTable dt = ExcelToDataTable(FileNewUrl);
/*验证Excel是否为空*/
if (dt == null || dt.Rows.Count <= 0)
{
MessageBox("Excel文件为空，请重新上传Excel文件", "msg6");
return;
}
/*将Excel转换成DataTable后，抛弃上传的Excel文件*/
DelExcel(FileNewUrl);
/*重新设置列名*/
dt.Columns[0].ColumnName = "ProductName";
dt.Columns[1].ColumnName = "ApplyCount";
dt.Columns[2].ColumnName = "ApplyDate";
/*重复商品验证*/
Hashtable htKeys = new Hashtable();
Hashtable htHas = new Hashtable();
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow fRow = dt.Rows[i];
for (int j = i + 1; j < dt.Rows.Count; j++)
{
DataRow sRow = dt.Rows[j];
if (fRow["ProductName"].ToString() == sRow["ProductName"].ToString() && !htHas.ContainsKey(j))
{
//ErrorMsg += "第" + (i + 2).ToString() + "行与第" + (j + 2).ToString() + "行商品名称相同：" + fRow["ProductName"].ToString()+"\\n";
if (htKeys.ContainsKey(fRow["ProductName"].ToString()))
{
htKeys[fRow["ProductName"].ToString()] += (j + 2).ToString() + "，";
htHas.Add(j, j);
}
else
{
htKeys.Add(fRow["ProductName"].ToString(), (i + 2).ToString() + "，" + (j + 2).ToString() + "，");
htHas.Add(i, i);
htHas.Add(j, j);
}
}
}
}
/*提示错误信息*/
if (htKeys.Count > 0)
{
foreach (string key in htKeys.Keys)
{
ErrorMsg += "商品 " + key + " 同时出现在第 " + htKeys[key].ToString().Substring(0, htKeys[key].ToString().Length - 1) + " 行\\n";
}
ErrorMsg += "可直接按 Ctrl+C 复制该对话框";
MessageBox(ErrorMsg, "msg7");
return;
}
/*验证数值*/
Regex IsNumeric = new Regex(@"^(-?\d+)(\.\d+)?$");
/*验证日期*/
Regex IsDate = new Regex(@"^((((1[6-9]|[2-9]\d)\d{2})-(0?[13578]|1[02])-(0?[1-9]|[12]\d|3[01]))|(((1[6-9]|[2-9]\d)\d{2})-(0?[13456789]|1[012])-(0?[1-9]|[12]\d|30))|(((1[6-9]|[2-9]\d)\d{2})-0?2-(0?[1-9]|1\d|2[0-8]))|(((1[6-9]|[2-9]\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))-0?2-29-)) (20|21|22|23|[0-1]?\d):[0-5]?\d:[0-5]?\d$");
/*验证需求数量与需求日期 格式*/
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
/*验证是否有空值存在*/
if (row["ProductName"] == null || row["ProductName"].ToString() == string.Empty ||
row["ApplyCount"] == null || row["ApplyCount"].ToString() == string.Empty ||
row["ApplyDate"] == null || row["ApplyDate"].ToString() == string.Empty)
{
ErrorMsg += "第" + (i + 2).ToString() + "行数据存在空值\\n";
continue;
}
/*验证需求数量格式*/
if (!IsNumeric.IsMatch(row["ApplyCount"].ToString()))
{
ErrorMsg += "第" + (i + 2).ToString() + "行计划数量格式错误，不是数值类型\\n";
}
/*验证需求日期格式*/
if (!IsDate.IsMatch(row["ApplyDate"].ToString()))
{
ErrorMsg += "第" + (i + 2).ToString() + "行计划交货日期格式错误，不是有效的日期类型。例：" + DateTime.Now.ToString("yyyy-MM-dd") + "\\n";
}
}
if (!string.IsNullOrEmpty(ErrorMsg))
{
ErrorMsg += "可直接按 Ctrl+C 复制该对话框";
MessageBox(ErrorMsg, "msg5");
return;
}
/*构造商品名称字符，用于数据库检索*/
string ProductNameKeys = string.Empty;
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i == dt.Rows.Count - 1)
{
ProductNameKeys += "'" + dt.Rows[i]["ProductName"].ToString() + "'";
}
else
{
ProductNameKeys += "'" + dt.Rows[i]["ProductName"].ToString() + "',";
}
}
/*使用商品名称查询数据库*/
DataTable dtQuery = XBase.Business.Office.PurchaseManager.PurchaseApplyBus.GetGoodsByProductName(UserInfo.CompanyCD, ProductNameKeys);
/*验证Excel中的商品是否存在*/
if (dtQuery.Rows.Count != dt.Rows.Count)
{
//存在未找到的商品 遍历比较
for (int i = 0; i < dt.Rows.Count; i++)
{
bool Flag = false;
foreach (DataRow row in dtQuery.Rows)
{
if (row["ProductName"].ToString() == dt.Rows[i]["ProductName"].ToString())
{
Flag = true;
}
}
if (!Flag)
{
ErrorMsg += "第" + (i + 2).ToString() + "行未找到商品名称为 " + dt.Rows[i]["ProductName"].ToString() + " 的商品，请检查产品名称是否正确\\n";
}
}
}
if (!string.IsNullOrEmpty(ErrorMsg))
{
MessageBox(ErrorMsg, "msgr");
return;
}
/*重新构造DataTable*/
DataTable dtNew = new DataTable();
dtNew.Columns.Add(new DataColumn("ID", typeof(string)));
dtNew.Columns.Add(new DataColumn("ProdNo", typeof(string)));
dtNew.Columns.Add(new DataColumn("ProductName", typeof(string)));
dtNew.Columns.Add(new DataColumn("Specification", typeof(string)));
dtNew.Columns.Add(new DataColumn("ColorName", typeof(string)));
dtNew.Columns.Add(new DataColumn("UnitID", typeof(string)));
dtNew.Columns.Add(new DataColumn("CodeName", typeof(string)));
dtNew.Columns.Add(new DataColumn("ApplyCount", typeof(string)));
dtNew.Columns.Add(new DataColumn("ApplyDate", typeof(string)));
dtNew.Columns.Add(new DataColumn("ApplyPrice", typeof(string)));
dtNew.Columns.Add(new DataColumn("TotalPrice", typeof(string)));
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow drow = dt.Rows[i];
foreach (DataRow row in dtQuery.Rows)
{
if (row["ProductName"].ToString() == drow["ProductName"].ToString())
{
DataRow newRow = dtNew.NewRow();
newRow["ID"] = row["ID"].ToString();
newRow["ProdNo"] = row["ProdNo"].ToString();
newRow["ProductName"] = row["ProductName"].ToString();
newRow["Specification"] = row["Specification"].ToString();
newRow["ColorName"] = row["ColorName"].ToString();
newRow["CodeName"] = row["CodeName"].ToString();
newRow["ApplyCount"] = drow["ApplyCount"].ToString();
newRow["ApplyDate"] = Convert.ToDateTime(drow["ApplyDate"].ToString()).ToString("yyyy-MM-dd");
newRow["UnitID"] = row["UnitID"].ToString();
newRow["ApplyPrice"] = row["StandardBuy"].ToString();
decimal StandardBuy = row["StandardBuy"] == null ? 0 :Convert.ToDecimal(row["StandardBuy"].ToString());
decimal ApplyCount = drow["ApplyCount"] == null ? 0 : Convert.ToDecimal(drow["ApplyCount"].ToString());
newRow["TotalPrice"] = (StandardBuy * ApplyCount).ToString("0.00");
dtNew.Rows.Add(newRow);
}
}
}
/*将数据添加到页面*/
decimal TotalCount = decimal.Zero;
decimal TotalPrice = decimal.Zero;
divDetailTbl1.InnerHtml = CreateHtml(dtNew, ref TotalCount, ref TotalPrice);
txtPlanCnt.Value = TotalCount.ToString("0.00");
txtPlanMoney.Value = TotalPrice.ToString("0.00");
divDetailTbl2.InnerHtml = CreateHtmlForTwo(dtNew);
#endregion
}