private void WriteSheet(Worksheet worksheet, string conditon, IList<CM_Client> clientlists, AC_AccountMonth Month, int cloumn) { IList<PDT_Product> productlists = PDT_ProductBLL.GetModelList(conditon); int bgcolor1 = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightYellow); int bgcolor2 = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGreen); #region 创建表头 if (clientlists[0].ClientType == 2) { worksheet.Cells[1, 1] = "分销商ID"; worksheet.Cells[1, 2] = "分销商编号"; worksheet.Cells[1, 3] = "分销商名称"; worksheet.Cells[1, 4] = "分销商分类"; worksheet.Cells[1, 5] = "归属月份"; } else { worksheet.Cells[1, 1] = "零售商ID"; worksheet.Cells[1, 2] = "零售商编号"; worksheet.Cells[1, 3] = "零售商名称"; worksheet.Cells[1, 4] = "零售商分类"; worksheet.Cells[1, 5] = "归属月份"; } if (cloumn == 8) { worksheet.Cells[1, 6] = "导购ID"; worksheet.Cells[1, 7] = "导购姓名"; } worksheet.get_Range("B2", "B2").ColumnWidth = 15; worksheet.get_Range("C2", "C2").ColumnWidth = 20; worksheet.get_Range("D2", "E2").ColumnWidth = 10; worksheet.get_Range("A1", "A1").RowHeight = 50; worksheet.get_Range("A1", "CC1").WrapText = true; worksheet.get_Range("A1", "CC1").Font.Bold = true; worksheet.get_Range("A1", "CC1000").Font.Size = 9; worksheet.get_Range("A1", "CC1000").HorizontalAlignment = XlHAlign.xlHAlignCenter; #region 将零售商信息写入表格内 int sellrow = 2; foreach (CM_Client client in clientlists) { worksheet.Cells[sellrow, 1] = client.ID; worksheet.Cells[sellrow, 2] = client.Code; worksheet.Cells[sellrow, 3] = client.FullName; try { worksheet.Cells[sellrow, 4] = client.ClientType == 2 ? "分销商" : DictionaryBLL.GetDicCollections("CM_RT_Classify")[client["RTClassify"]].Name; } catch { ;} worksheet.Cells[sellrow, 5] = "'" + Month.Name; if (cloumn == 8) { StringBuilder condition = new StringBuilder(" PM_Promotor.BeginWorkDate<'" + Month.EndDate.AddDays(1).ToString("yyyy-MM-dd") + "' AND ISNULL(PM_Promotor.EndWorkDate,GETDATE())>='" + Month.BeginDate.ToString("yyyy-MM-dd") + "' AND PM_Promotor.ApproveFlag=1 "); condition.Append("AND ID in (SELECT Promotor FROM PM_PromotorInRetailer WHERE Client = " + client.ID.ToString() + ")"); IList<PM_Promotor> promotorlists = PM_PromotorBLL.GetModelList(condition.ToString()); for (int j = 0; j < promotorlists.Count; j++) { if (j > 0) { worksheet.Cells[sellrow, 1] = client.ID; worksheet.Cells[sellrow, 2] = client.Code; worksheet.Cells[sellrow, 3] = client.FullName; try { worksheet.Cells[sellrow, 4] = DictionaryBLL.GetDicCollections("CM_RT_Classify")[client["RTClassify"]].Name; } catch { ;} worksheet.Cells[sellrow, 5] = "'" + Month.Name; } worksheet.Cells[sellrow, 6] = promotorlists[j].ID; worksheet.Cells[sellrow, 7] = promotorlists[j].Name; if (j != promotorlists.Count - 1) sellrow++; } } sellrow++; } #endregion #endregion //将产品写入表头 for (int i = 0; i < productlists.Count; i++) { worksheet.Cells[1, cloumn + i] = productlists[i].ShortName; #region 按品牌设置产品列颜色 if (i > 0) { if (productlists[i].Brand == productlists[i - 1].Brand) { worksheet.get_Range(worksheet.Cells[1, cloumn + i], worksheet.Cells[1000, cloumn + i]).Interior.Color = worksheet.get_Range(worksheet.Cells[1, cloumn + i - 1], worksheet.Cells[1000, cloumn + i - 1]).Interior.Color; } else { if (int.Parse(worksheet.get_Range(worksheet.Cells[1, cloumn + i - 1], worksheet.Cells[1000, cloumn + i - 1]).Interior.Color.ToString()) == bgcolor1) worksheet.get_Range(worksheet.Cells[1, cloumn + i], worksheet.Cells[1000, cloumn + i]).Interior.Color = bgcolor2; else worksheet.get_Range(worksheet.Cells[1, cloumn + i], worksheet.Cells[1000, cloumn + i]).Interior.Color = bgcolor1; } } else { worksheet.get_Range(worksheet.Cells[1, cloumn + i], worksheet.Cells[1000, cloumn + i]).Interior.Color = bgcolor1; } #endregion } #region 设置表格格式 //设置行高 worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[sellrow - 1, 1]).RowHeight = 16; //设置表格单元格格线 worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[sellrow - 1, cloumn + productlists.Count - 1]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); #endregion }
private string ImportGifts(Worksheet worksheet, string remark, AC_AccountMonth month, int insertstaff, IList<PDT_Product> productlists, out int State) { string ImportInfo = ""; int cloumn = 6; int sellinrow = 1; DateTime maxday = DateTime.Today < month.EndDate ? DateTime.Today : month.EndDate; ImportInfo += "<br/>"; ImportInfo += "<span style='color: Red'>-------------------------------------------------------------------------</span><br/>"; ImportInfo += "<span style='color: Red'>----" + remark + "----</span><br/>"; State = 0; PDT_Product product; #region 读取Excel表格 while (true) { cloumn = 6; sellinrow++; if (((Range)worksheet.Cells[sellinrow, 1]).Value2 == null) { break; } int clientid = 0; if (!int.TryParse(((Range)worksheet.Cells[sellinrow, 1]).Value2.ToString(), out clientid)) { continue; } #region 验证数据 CM_Client client = new CM_ClientBLL(clientid).Model; if (client == null || client.FullName != ((Range)worksheet.Cells[sellinrow, 3]).Text.ToString()) { ImportInfo += "<span style='color: Red'>ID号:" + ((Range)worksheet.Cells[sellinrow, 1]).Text.ToString() + (client.ClientType == 3 ? ",零售商ID与零售商名称" : ",分销商ID与分销商名称") + "不匹配!</span><br/>"; State = 4; continue; } int uplimit = client.ClientType == 3 ? 5000 : 9000; if (((Range)worksheet.Cells[sellinrow, 5]).Text.ToString() != month.Name) { ImportInfo += "<span style='color: Red'>ID号:" + ((Range)worksheet.Cells[sellinrow, 1]).Text.ToString() + ",归属月份必须为【" + month.Name + "】</span><br/>"; State = 4; continue; } #endregion #region 组织销量头 SVM_SalesVolumeBLL bll = null; IList<SVM_SalesVolume> svmlists = SVM_SalesVolumeBLL.GetModelList("Client=" + clientid.ToString() + " AND InsertStaff!=1" + " AND Type IN(1,2) AND AccountMonth=" + month.ID.ToString() //+ " AND SalesDate='" + salesdate.ToString("yyyy-MM-dd") + " AND Flag=11 AND (Remark='" + remark + "' OR Remark NOT IN ('Excel批量导入本品赠品','Excel批量导入试用装赠品','Excel批量导入高值促销品'))"); if (svmlists.Count > 0) { if (svmlists.FirstOrDefault(p => p.ApproveFlag == 1) != null) { ImportInfo += "<span style='color: Red'>ID号:" + ((Range)worksheet.Cells[sellinrow, 1]).Text.ToString() + (client.ClientType == 3 ? ",该零售商" : ",该分销商") + "当月的本品赠品进货已审核,不可再次导入!</span><br/>"; continue; } //仅存在一条符合条件的赠品,进行覆盖 if (svmlists.Count == 1 && svmlists[0].Remark == remark) { bll = new SVM_SalesVolumeBLL(svmlists[0].ID); bll.Items.Clear(); } } if (bll == null) { bll = new SVM_SalesVolumeBLL(); bll.Model.Client = clientid; bll.Model.OrganizeCity = client.OrganizeCity; bll.Model.Supplier = client.Supplier; bll.Model.AccountMonth = month.ID; bll.Model.SalesDate = maxday; bll.Model.Type = 2; bll.Model.ApproveFlag = 2; bll.Model.Flag = 11; //赠品进货 bll.Model["IsCXP"] = "Y"; bll.Model.InsertStaff = insertstaff; bll.Model.Remark = remark; } #endregion bll.Model["SubmitFlag"] = "1"; IList<SVM_SalesVolume_Detail> details = new List<SVM_SalesVolume_Detail>(); int quantity = 0; bool wrongflag = false;//判断导入数量是否正常(除空导致的异常) bool isnumber = false; while (true) { if (((Range)worksheet.Cells[1, cloumn]).Text.ToString() == string.Empty) { break; } IList<PDT_Product> products = PDT_ProductBLL.GetModelList("ShortName='" + ((Range)worksheet.Cells[1, cloumn]).Text + "' AND State=1"); if (products.Count > 0) { product = products[0]; } else { ImportInfo += "<span style='color: Red'>赠品产品名称:" + ((Range)worksheet.Cells[1, cloumn]).Text + "在赠品列表中不存在!</span><br/>"; State = 4; cloumn++; continue; } if (product != null && ((Range)worksheet.Cells[sellinrow, cloumn]).Value2 != null) { #region 读取各产品销量 if (!isnumber && int.TryParse(((Range)worksheet.Cells[sellinrow, cloumn]).Value2.ToString(), out quantity)) { isnumber = true; } if (quantity != 0 && quantity <= uplimit && quantity >= 0) { decimal factoryprice = 0, salesprice = 0; PDT_ProductPriceBLL.GetPriceByClientAndType(client.ID, product.ID, 2, out factoryprice, out salesprice); if (factoryprice == 0) factoryprice = product.FactoryPrice; if (salesprice == 0) salesprice = product.NetPrice; SVM_SalesVolume_Detail detail = new SVM_SalesVolume_Detail(); detail.Product = product.ID; detail.FactoryPrice = factoryprice; detail.SalesPrice = salesprice; detail.Quantity = quantity; details.Add(detail); } else if (trim(((Range)worksheet.Cells[sellinrow, cloumn]).Text.ToString()) != "" && ((Range)worksheet.Cells[sellinrow, cloumn]).Text.ToString() != "0") { wrongflag = true; break; } #endregion } cloumn++; } if (wrongflag) { ImportInfo += "<span style='color: Red'>ID号:" + clientid.ToString() + (client.ClientType == 3 ? ",零售商" : ",分销商:") + client.FullName + "的赠品进货单未能导入,赠品名称:" + ((Range)worksheet.Cells[1, cloumn]).Text + "数量填写错误"; State = 4; continue; } #region 更新销量至数据库 if (bll.Model.ID > 0) { if (details.Count > 0) { bll.DeleteDetail(); //先清除原先导入的数据 bll.Model.UpdateStaff = insertstaff; bll.Items = details; bll.AddDetail(); bll.Update(); ImportInfo += "<span style='color: Blue'>ID号:" + clientid.ToString() + (client.ClientType == 3 ? ",零售商:" : ",分销商:") + client.FullName + " 的原有日期为:" + bll.Model.SalesDate.ToString("yyyy-MM-dd") + " 的赠品进货单被成功更新!产品SKU数:" + bll.Items.Count.ToString() + ",产品总数量:" + bll.Items.Sum(p => p.Quantity).ToString() + "</span><br/>"; } if (details.Count == 0 && isnumber) { bll.DeleteDetail(); } } else { if (details.Count > 0 || svmlists.Count == 0) //没有产品也新增一条空销量头 { bll.Items = details; if (bll.Add() > 0) { foreach (SVM_SalesVolume m in svmlists) { bll = new SVM_SalesVolumeBLL(m.ID); bll.DeleteDetail(); bll.Delete(); } } ImportInfo += "<span style='color: Black'>ID号:" + clientid.ToString() + (client.ClientType == 3 ? ",零售商:" : ",分销商:") + client.FullName + " 的赠品进货单已成功导入!产品SKU数:" + bll.Items.Count.ToString() + ",产品总数量:" + bll.Items.Sum(p => p.Quantity).ToString() + "</span><br/>"; } } #endregion } #endregion return ImportInfo; }