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 }