protected void bt_DownloadTemplate_Click(object sender, EventArgs e) { Button bt = (Button)sender; GridViewRow drv = (GridViewRow)bt.NamingContainer; int rowIndex = drv.RowIndex; SVM_DownloadTemplateBLL _bll; int templateid = int.Parse(gv_downtemplate.DataKeys[rowIndex]["SVM_DownloadTemplate_ID"].ToString()); _bll = new SVM_DownloadTemplateBLL(templateid); if (_bll.Model.State == 1) { MessageBox.Show(this, "模版还未生成,目前在生成队列中第" + GetDownWait(templateid.ToString()) + "个"); return; } string path = ConfigHelper.GetConfigString("AttachmentPath"); if (path.StartsWith("~")) path = Server.MapPath(path); if (!path.EndsWith("\\")) path = path + "\\"; if (ViewState["IsOpponent"].ToString() != "2" && ViewState["IsOpponent"].ToString() != "3") { path += "ImportExcelSVM\\Download\\" + Session["UserName"].ToString() + "\\" + _bll.Model.Name; } else { path += "ImportExcelSVM\\Download\\" + _bll.Model.AccountMonth.ToString() + "\\" + _bll.Model.Name; } Response.Write(path); _bll.Model.State = 3; Downloadfile(path, _bll.Model.Name); _bll.Update(); }
protected void bt_DownloadTemplate_Click(object sender, EventArgs e) { Button bt = (Button)sender; GridViewRow drv = (GridViewRow)bt.NamingContainer; int rowIndex = drv.RowIndex; SVM_DownloadTemplateBLL _bll; int templateid = int.Parse(gv_downtemplate.DataKeys[rowIndex]["SVM_DownloadTemplate_ID"].ToString()); _bll = new SVM_DownloadTemplateBLL(templateid); if (_bll.Model.State == 1) { MessageBox.Show(this, "模版还未生成,目前在生成队列中第" + GetDownWait(templateid.ToString()) + "个"); return; } string path = ConfigHelper.GetConfigString("AttachmentPath"); if (path.StartsWith("~")) { path = Server.MapPath(path); } if (!path.EndsWith("\\")) { path = path + "\\"; } if (ViewState["IsOpponent"].ToString() != "2" && ViewState["IsOpponent"].ToString() != "3") { path += "ImportExcelSVM\\Download\\" + Session["UserName"].ToString() + "\\" + _bll.Model.Name; } else { path += "ImportExcelSVM\\Download\\" + _bll.Model.AccountMonth.ToString() + "\\" + _bll.Model.Name; } Response.Write(path); _bll.Model.State = 3; Downloadfile(path, _bll.Model.Name); _bll.Update(); }
private void CreateKeyTargetDownTemplate(int templateid, string path) { int cloumn = 8; SVM_DownloadTemplateBLL _bll = new SVM_DownloadTemplateBLL(templateid); if (_bll.Model.State != 5) { Exception err; string error; AC_AccountMonth month = new AC_AccountMonthBLL(_bll.Model.AccountMonth).Model; DateTime day = (DateTime.Today < month.EndDate) ? DateTime.Today : month.EndDate; string message = ""; IList<Addr_OrganizeCity> _cityList; if (_bll.Model["OrganizeCity"] != "1") { _cityList = Addr_OrganizeCityBLL.GetModelList("Level3_SuperID IN (" + _bll.Model["OrganizeCity"] + ") AND Level=" + ConfigHelper.GetConfigString("OrganizeCity-CityLevel") + " Order By Level3_SuperID"); } else { _cityList = Addr_OrganizeCityBLL.GetModelList("Level=" + ConfigHelper.GetConfigString("OrganizeCity-CityLevel") + " Order By Level3_SuperID"); } object missing = Missing.Value; ApplicationClass ExcelApp = null; try { ExcelApp = new ApplicationClass { Visible = false, DisplayAlerts = false }; Workbook workbook1 = null; Worksheet worksheet1 = null; try { workbook1 = ExcelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"]; worksheet1.Name = "重点品项"; int bgcolor1 = ColorTranslator.ToOle(Color.LightYellow); int bgcolor2 = ColorTranslator.ToOle(Color.LightGreen); worksheet1.Cells[1, 1] = "营业部"; worksheet1.Cells[1, 2] = "办事处ID"; worksheet1.Cells[1, 3] = "办事处"; worksheet1.Cells[1, 4] = "归属月份"; if (_bll.Model.IsOpponent == 2) { worksheet1.Cells[1, 5] = "办事处业绩目标额"; cloumn = 6; } else { worksheet1.Name = "月度费率"; worksheet1.Cells[1, 5] = "办事处月度费率目标"; worksheet1.Cells[1, 6] = "办事处月度费率达成"; } worksheet1.get_Range("B2", "B2").ColumnWidth = 15; worksheet1.get_Range("C2", "C2").ColumnWidth = 20; worksheet1.get_Range("D2", "E2").ColumnWidth = 10; worksheet1.get_Range("A1", "A1").RowHeight = 50; worksheet1.get_Range("A1", "CC1").WrapText = true; worksheet1.get_Range("A1", "CC1").Font.Bold = true; worksheet1.get_Range("A1", "CC1000").Font.Size = 9; worksheet1.get_Range("A1", "CC1000").HorizontalAlignment = XlHAlign.xlHAlignCenter; int sellrow = 2; foreach (Addr_OrganizeCity city in _cityList) { worksheet1.Cells[sellrow, 1] = new Addr_OrganizeCityBLL(city.SuperID).Model.Name; worksheet1.Cells[sellrow, 2] = city.ID; worksheet1.Cells[sellrow, 3] = city.Name; worksheet1.Cells[sellrow, 4] = "'" + month.Name; #region 按營業部設置顏色 if (sellrow > 0) { if (new Addr_OrganizeCityBLL(city.SuperID).Model.Name == ((Range)worksheet1.Cells[sellrow - 1, 1]).Text.ToString()) { worksheet1.get_Range(worksheet1.Cells[sellrow, 1], worksheet1.Cells[sellrow, 4]).Interior.Color = worksheet1.get_Range(worksheet1.Cells[sellrow - 1, 1], worksheet1.Cells[sellrow - 1, 4]).Interior.Color; } else { if (int.Parse(worksheet1.get_Range(worksheet1.Cells[sellrow - 1, 1], worksheet1.Cells[sellrow - 1, 4]).Interior.Color.ToString()) == bgcolor1) worksheet1.get_Range(worksheet1.Cells[sellrow, 1], worksheet1.Cells[sellrow, 4]).Interior.Color = bgcolor2; else worksheet1.get_Range(worksheet1.Cells[sellrow, 1], worksheet1.Cells[sellrow, 4]).Interior.Color = bgcolor1; } } else { worksheet1.get_Range(worksheet1.Cells[sellrow, 1], worksheet1.Cells[sellrow, 4]).Interior.Color = bgcolor1; } #endregion sellrow++; } if (_bll.Model.IsOpponent == 2) { if (!string.IsNullOrEmpty(_bll.Model.ProductGifts)) { if (_bll.Model.ProductGifts.EndsWith(",")) { _bll.Model.ProductGifts = _bll.Model.ProductGifts.Substring(0, _bll.Model.ProductGifts.Length - 1); } IList<PDT_Product> productlists = PDT_ProductBLL.GetModelList("ID IN (" + _bll.Model.ProductGifts + ") ORDER BY ISNULL(SubUnit,999999),Code"); //将产品写入表头 for (int i = 0; i < productlists.Count; i++) { worksheet1.Cells[1, cloumn + i] = productlists[i].ShortName; #region 按品牌设置产品列颜色 if (i > 0) { if (productlists[i].Brand == productlists[i - 1].Brand) { worksheet1.get_Range(worksheet1.Cells[1, cloumn + i], worksheet1.Cells[1000, cloumn + i]).Interior.Color = worksheet1.get_Range(worksheet1.Cells[1, cloumn + i - 1], worksheet1.Cells[1000, cloumn + i - 1]).Interior.Color; } else { if (int.Parse(worksheet1.get_Range(worksheet1.Cells[1, cloumn + i - 1], worksheet1.Cells[1000, cloumn + i - 1]).Interior.Color.ToString()) == bgcolor1) worksheet1.get_Range(worksheet1.Cells[1, cloumn + i], worksheet1.Cells[1000, cloumn + i]).Interior.Color = bgcolor2; else worksheet1.get_Range(worksheet1.Cells[1, cloumn + i], worksheet1.Cells[1000, cloumn + i]).Interior.Color = bgcolor1; } } else { worksheet1.get_Range(worksheet1.Cells[1, cloumn + i], worksheet1.Cells[1000, cloumn + i]).Interior.Color = bgcolor1; } #endregion } #region 设置表格格式 //设置行高 worksheet1.get_Range(worksheet1.Cells[2, 1], worksheet1.Cells[sellrow - 1, 1]).RowHeight = 16; //设置表格单元格格线 worksheet1.get_Range(worksheet1.Cells[1, 1], worksheet1.Cells[sellrow - 1, cloumn + productlists.Count - 1]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); #endregion } } worksheet1.Activate(); ExcelApp.AlertBeforeOverwriting = false; workbook1.SaveAs(path, XlFileFormat.xlExcel8, "", "", false, false, XlSaveAsAccessMode.xlNoChange, 1, false, missing, missing, missing); } catch (Exception exception1) { err = exception1; error = "Message:" + err.Message + "<br/>Source:" + err.Source + "<br/>StackTrace:" + err.StackTrace + "<br/>"; message = message + "系统错误-1!" + err.Message; } finally { if (workbook1 != null) { workbook1.Close(false, missing, missing); } if (worksheet1 != null) { Marshal.ReleaseComObject(worksheet1); } if (workbook1 != null) { Marshal.ReleaseComObject(workbook1); } worksheet1 = null; workbook1 = null; if (File.Exists(path)) { _bll.Model.Path = path; _bll.Model.State = 2; } } } catch (Exception exception2) { err = exception2; error = "Message:" + err.Message + "<br/>Source:" + err.Source + "<br/>StackTrace:" + err.StackTrace + "<br/>"; message = message + "系统错误-2!" + err.Message; } finally { if (ExcelApp != null) { ExcelApp.Workbooks.Close(); ExcelApp.Quit(); Marshal.ReleaseComObject(ExcelApp); ExcelApp = null; } GC.Collect(); } if (message != "") { _bll.Model.State = 4; } _bll.Model.Remark = message; _bll.Update(); string filename = path.Substring(path.LastIndexOf('\\') + 1); this.SendMessage("生成重点品项导入模版", (message != "") ? (filename + "-" + message) : (filename + "生成操作成功!")); } }
private void CreateDownTemplate(int templateid, string path) { SVM_DownloadTemplateBLL _bll = new SVM_DownloadTemplateBLL(templateid); if (_bll.Model.State == 5) return;//模版作废 AC_AccountMonth month = new AC_AccountMonthBLL(_bll.Model.AccountMonth).Model; DateTime day = DateTime.Today < month.EndDate ? DateTime.Today : month.EndDate; string message = ""; #region 获取业代负责的零售商及所有产品数据 string condtion = _bll.Model["ClientType"] == "2" ? " AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',7)='2'" : " AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',89) !='1'"; IList<CM_Client> clientlists = CM_ClientBLL.GetModelList("ClientType=" + _bll.Model["ClientType"] + " AND ClientManager=" + _bll.Model.DownStaff.ToString() + " AND ApproveFlag=1" + condtion + " AND OpenTime<'" + month.EndDate.AddDays(1).ToString("yyyy-MM-dd") + "' AND ISNULL(CloseTime,GETDATE())>='" + month.BeginDate.ToString("yyyy-MM-dd") + "' ORDER BY Code"); if (clientlists.Count == 0) { message = "对不起,没有当前人直接负责的客户!"; _bll.Model.Remark = message; _bll.Update(); return; } #endregion #region 生成Excel文件 object missing = System.Reflection.Missing.Value; ApplicationClass ExcelApp = null; try { ExcelApp = new ApplicationClass(); ExcelApp.Visible = false; ExcelApp.DisplayAlerts = false; Workbook workbook1 = null; Worksheet worksheet1 = null, worksheet2 = null; try { workbook1 = ExcelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"]; if (_bll.Model["ClientType"] == "2") { worksheet1.Name = "分销商进货"; WriteSheet(worksheet1, "MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',28)!='1' AND Brand IN (SELECT ID FROM dbo.PDT_Brand WHERE IsOpponent='1') AND State=1 AND ApproveFlag=1 ORDER BY ISNULL(SubUnit,999999),Code", clientlists, month, 6); } else { worksheet2 = (Worksheet)workbook1.Worksheets.Add(missing, worksheet1, 1, missing); worksheet1.Name = "零售商进货"; worksheet2.Name = "零售商销货"; string ParamValue = Addr_OrganizeCityParamBLL.GetValueByType(1, 26); ParamValue = string.IsNullOrEmpty(ParamValue) ? "0" : ParamValue; ParamValue = ParamValue.EndsWith(",") ? ParamValue.Remove(ParamValue.Length - 1) : ParamValue; WriteSheet(worksheet2, "MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',28)!='1' AND Brand NOT IN(" + ParamValue + ") AND Brand IN (SELECT ID FROM dbo.PDT_Brand WHERE IsOpponent='1')AND MCS_SYS.dbo.UF_Spilt2('MCS_PUB.dbo.PDT_Product',ExtPropertys,'IntegralPoints')!=1 AND State=1 AND ApproveFlag=1 ORDER BY ISNULL(SubUnit,999999),Code", clientlists, month, 8); WriteSheet(worksheet1, "MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',28)!='1' AND Brand NOT IN(" + ParamValue + ") AND Brand IN (SELECT ID FROM dbo.PDT_Brand WHERE IsOpponent='1')AND MCS_SYS.dbo.UF_Spilt2('MCS_PUB.dbo.PDT_Product',ExtPropertys,'IntegralPoints')!=1 AND State=1 AND ApproveFlag=1 ORDER BY ISNULL(SubUnit,999999),Code", clientlists, month, 6); } worksheet1.Activate(); ExcelApp.AlertBeforeOverwriting = false; workbook1.SaveAs(path, XlFileFormat.xlExcel8, "", "", false, false, XlSaveAsAccessMode.xlNoChange, 1, false, missing, missing, missing); } catch (System.Exception err) { string error = "Message:" + err.Message + "<br/>" + "Source:" + err.Source + "<br/>" + "StackTrace:" + err.StackTrace + "<br/>"; message += "系统错误-1!" + err.Message; } finally { if (workbook1 != null) workbook1.Close(false, missing, missing); if (worksheet1 != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet1); if (_bll.Model["ClientType"] != "2" && worksheet2 != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet2); if (workbook1 != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook1); worksheet1 = null; if (_bll.Model["ClientType"] != "2") { worksheet2 = null; } workbook1 = null; if (File.Exists(path)) { _bll.Model.Path = path; _bll.Model.State = 2; } } } catch (System.Exception err) { string error = "Message:" + err.Message + "<br/>" + "Source:" + err.Source + "<br/>" + "StackTrace:" + err.StackTrace + "<br/>"; message += "系统错误-2!" + err.Message; } finally { if (ExcelApp != null) { ExcelApp.Workbooks.Close(); ExcelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp); ExcelApp = null; } GC.Collect(); //GC.WaitForPendingFinalizers(); } #endregion if (message != "") _bll.Model.State = 4; _bll.Model.Remark = message; _bll.Update(); string filename = path.Substring(path.LastIndexOf('\\') + 1); SendMessage("生成成品导入模版", message != "" ? filename + "-" + message : filename + "生成操作成功!"); }
private void CrateGiftDownTemplate(int templateid, string path) { SVM_DownloadTemplateBLL _bll = new SVM_DownloadTemplateBLL(templateid); if (_bll.Model.State == 5) return;//模版作废 AC_AccountMonth month = new AC_AccountMonthBLL(_bll.Model.AccountMonth).Model; DateTime day = DateTime.Today < month.EndDate ? DateTime.Today : month.EndDate; string message = ""; #region 获取业代负责的零售商及所有产品数据 string condtion = _bll.Model["ClientType"] == "2" ? " AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',7)='2'" : ""; IList<CM_Client> clientlists = CM_ClientBLL.GetModelList("ClientType=" + _bll.Model["ClientType"] + " AND ClientManager=" + _bll.Model.DownStaff.ToString() + " AND ActiveFlag=1 AND ApproveFlag=1" + condtion + " AND OpenTime<'" + day.ToString("yyyy-MM-dd") + " 23:59:59' ORDER BY Code"); if (clientlists.Count == 0) { message = "对不起,没有当前人直接负责的客户!"; _bll.Model.Remark = message; _bll.Update(); return; } #endregion #region 生成Excel文件 object missing = System.Reflection.Missing.Value; ApplicationClass ExcelApp = null; try { ExcelApp = new ApplicationClass(); ExcelApp.Visible = false; ExcelApp.DisplayAlerts = false; Workbook workbook1 = null; Worksheet worksheet1 = null, worksheet2 = null, worksheet3 = null; try { workbook1 = ExcelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"]; worksheet2 = (Worksheet)workbook1.Worksheets.Add(missing, worksheet1, 1, missing); worksheet3 = (Worksheet)workbook1.Worksheets.Add(missing, worksheet2, 1, missing); worksheet1.Name = "成品赠品"; worksheet2.Name = "试用装"; worksheet3.Name = "高值促销品"; //将产品写入表头 if (!string.IsNullOrEmpty(_bll.Model.ProductGifts)) WriteSheet(worksheet1, "ID IN(" + _bll.Model.ProductGifts + ")", clientlists, month, 6); if (!string.IsNullOrEmpty(_bll.Model.Testers)) WriteSheet(worksheet2, "ID IN(" + _bll.Model.Testers + ")", clientlists, month, 6); if (!string.IsNullOrEmpty(_bll.Model.Gifts)) WriteSheet(worksheet3, "ID IN(" + _bll.Model.Gifts + ")", clientlists, month, 6); worksheet1.Activate(); ExcelApp.AlertBeforeOverwriting = false; workbook1.SaveAs(path, XlFileFormat.xlExcel8, "", "", false, false, XlSaveAsAccessMode.xlNoChange, 1, false, missing, missing, missing); } catch (System.Exception err) { string error = "Message:" + err.Message + "<br/>" + "Source:" + err.Source + "<br/>" + "StackTrace:" + err.StackTrace + "<br/>"; message += "系统错误-1!" + err.Message; } finally { if (workbook1 != null) workbook1.Close(false, missing, missing); if (worksheet1 != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet1); if (worksheet2 != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet2); if (worksheet3 != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet3); if (workbook1 != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook1); worksheet1 = null; worksheet2 = null; workbook1 = null; if (File.Exists(path)) { _bll.Model.Path = path; _bll.Model.State = 2; } } } catch (System.Exception err) { string error = "Message:" + err.Message + "<br/>" + "Source:" + err.Source + "<br/>" + "StackTrace:" + err.StackTrace + "<br/>"; message += "系统错误-2!" + err.Message; } finally { if (ExcelApp != null) { ExcelApp.Workbooks.Close(); ExcelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp); ExcelApp = null; } GC.Collect(); //GC.WaitForPendingFinalizers(); } #endregion if (message != "") _bll.Model.State = 4; _bll.Model.Remark = message; _bll.Update(); string filename = path.Substring(path.LastIndexOf('\\') + 1); SendMessage("生成赠品导入模版", message != "" ? filename + "-" + message : filename + "生成操作成功!"); }