protected void bt_DownloadTemplate_Click(object sender, EventArgs e)
    {
        #region 获取最迟的销量日期
        AC_AccountMonth month = (AC_AccountMonth)ViewState["month"];
        DateTime        day   = DateTime.Today < month.EndDate ? DateTime.Today : month.EndDate;
        #endregion

        #region 判断有无选择业代
        if (string.IsNullOrEmpty(select_Staff.SelectValue) || select_Staff.SelectValue == "0")
        {
            MessageBox.Show(this, "对不起,请选择责任业代!");
            return;
        }
        #endregion

        #region 获取业代负责的零售商及所有产品数据
        int               staff       = int.Parse(select_Staff.SelectValue);
        string            condtion    = ViewState["ClientType"].ToString() == "2" ? " AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',7)='2'" : "";
        IList <CM_Client> clientlists = CM_ClientBLL.GetModelList("ClientType=" + ViewState["ClientType"].ToString() + " AND ClientManager=" + staff.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)
        {
            MessageBox.Show(this, "对不起,没有当前人直接负责的终端店!");
            return;
        }

        #endregion
        string title    = (int)ViewState["ClientType"] == 2 ? "分销商" : "零售商";
        string filename = title + ((int)ViewState["IsOpponent"] == 9 ? "赠品进货导入模板-" : "产品进销货量导入模板") + select_Staff.SelectText + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
        if ((int)ViewState["ClientType"] == 2 && (int)ViewState["IsOpponent"] == 1)
        {
            filename = "分销商产品进货导入模板" + select_Staff.SelectText + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
        }
        SVM_DownloadTemplateBLL _bll = new SVM_DownloadTemplateBLL();
        _bll.Model.Name         = filename;
        _bll.Model.AccountMonth = month.ID;
        _bll.Model.State        = 1;
        _bll.Model.IsOpponent   = (int)ViewState["IsOpponent"];
        _bll.Model.InsertStaff  = (int)Session["UserID"];
        _bll.Model.InsertTime   = DateTime.Now;
        _bll.Model.DownStaff    = staff;
        if ((int)ViewState["IsOpponent"] == 9)
        {
            _bll.Model.ProductGifts = ProdutctGifts.ToString().EndsWith(",") ? ProdutctGifts.ToString().Substring(0, ProdutctGifts.Length - 1) : ProdutctGifts.ToString();
            _bll.Model.Testers      = Testers.ToString().EndsWith(",") ? Testers.ToString().Substring(0, Testers.Length - 1) : Testers.ToString();
            _bll.Model.Gifts        = Gifts.ToString().EndsWith(",") ? Gifts.ToString().Substring(0, Gifts.Length - 1) : Gifts.ToString();
        }
        _bll.Model["UserName"]   = Session["UserName"].ToString();
        _bll.Model["ClientType"] = ViewState["ClientType"].ToString();
        _bll.Add();


        //StringBuilder builder = new StringBuilder();
        //builder.Append("alert('请到该页面下载模版!');");
        //builder.Append("window.open('ImportHistory.aspx?IsOpponent=" + ViewState["IsOpponent"].ToString()+"&ClientType=" + ViewState["ClientType"].ToString()+"','_blank')");

        //MessageBox.ResponseScript(this, builder.ToString());

        Response.Redirect("ImportHistory.aspx?IsOpponent=" + ViewState["IsOpponent"].ToString() + "&ClientType=" + ViewState["ClientType"].ToString());
    }
    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)
    {
        #region 获取最迟的销量日期
        AC_AccountMonth month = (AC_AccountMonth)ViewState["month"];
        DateTime day = DateTime.Today < month.EndDate ? DateTime.Today : month.EndDate;
        #endregion

        #region 判断有无选择业代
        if (string.IsNullOrEmpty(select_Staff.SelectValue) || select_Staff.SelectValue == "0")
        {
            MessageBox.Show(this, "对不起,请选择责任业代!");
            return;
        }
        #endregion

        #region 获取业代负责的零售商及所有产品数据
        int staff = int.Parse(select_Staff.SelectValue);
        string condtion = ViewState["ClientType"].ToString() == "2" ? " AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',7)='2'" : "";
        IList<CM_Client> clientlists = CM_ClientBLL.GetModelList("ClientType=" + ViewState["ClientType"].ToString() + " AND ClientManager=" + staff.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)
        {
            MessageBox.Show(this, "对不起,没有当前人直接负责的终端店!");
            return;
        }

        #endregion
        string title = (int)ViewState["ClientType"] == 2 ? "分销商" : "零售商";
        string filename = title+((int)ViewState["IsOpponent"] == 9 ? "赠品进货导入模板-" : "产品进销货量导入模板") + select_Staff.SelectText + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
        if ((int)ViewState["ClientType"] == 2 && (int)ViewState["IsOpponent"] == 1)
        {
            filename = "分销商产品进货导入模板" + select_Staff.SelectText + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
        }
        SVM_DownloadTemplateBLL _bll = new SVM_DownloadTemplateBLL();
        _bll.Model.Name = filename;
        _bll.Model.AccountMonth = month.ID;
        _bll.Model.State = 1;
        _bll.Model.IsOpponent = (int)ViewState["IsOpponent"];
        _bll.Model.InsertStaff = (int)Session["UserID"];
        _bll.Model.InsertTime = DateTime.Now;
        _bll.Model.DownStaff = staff;
        if ((int)ViewState["IsOpponent"] == 9)
        {
            _bll.Model.ProductGifts = ProdutctGifts.ToString().EndsWith(",") ? ProdutctGifts.ToString().Substring(0,ProdutctGifts.Length - 1) : ProdutctGifts.ToString();
            _bll.Model.Testers = Testers.ToString().EndsWith(",") ? Testers.ToString().Substring(0,Testers.Length - 1) : Testers.ToString();
            _bll.Model.Gifts = Gifts.ToString().EndsWith(",") ? Gifts.ToString().Substring(0,Gifts.Length - 1) : Gifts.ToString();
        }
        _bll.Model["UserName"] = Session["UserName"].ToString();
        _bll.Model["ClientType"] = ViewState["ClientType"].ToString();
        _bll.Add();

        //StringBuilder builder = new StringBuilder();
        //builder.Append("alert('请到该页面下载模版!');");
        //builder.Append("window.open('ImportHistory.aspx?IsOpponent=" + ViewState["IsOpponent"].ToString()+"&ClientType=" + ViewState["ClientType"].ToString()+"','_blank')");

        //MessageBox.ResponseScript(this, builder.ToString());

        Response.Redirect("ImportHistory.aspx?IsOpponent=" + ViewState["IsOpponent"].ToString() + "&ClientType=" + ViewState["ClientType"].ToString());
    }
    private void CheckOrganizeCity()
    {
        IList <SVM_DownloadTemplate> _downlist = SVM_DownloadTemplateBLL.GetModelList("IsOpponent=2 AND State IN (1,2) AND AccountMonth=" + ddl_AccountMonth.SelectedValue);

        foreach (SVM_DownloadTemplate _m in _downlist)
        {
            ListItem item = chk_Citylist.Items.FindByValue(_m["OrganizeCity"]);
            if (!item.Text.EndsWith("已操作"))
            {
                item.Text += "------已操作";
            }
        }
    }
 protected void ddl_AccountMonth2_SelectedIndexChanged(object sender, EventArgs e)
 {
     if ((int)(ViewState["IsOpponent"]) == 3 && SVM_DownloadTemplateBLL.GetModelList("AccountMonth=" + ddl_AccountMonth2.SelectedValue + " AND IsOpponent=3 AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',3)='1'").Count == 0)
     {
         string filename = "办事处月度费率导入模板" + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
         SVM_DownloadTemplateBLL _bll = new SVM_DownloadTemplateBLL();
         _bll.Model.Name            = filename;
         _bll.Model.AccountMonth    = int.Parse(ddl_AccountMonth2.SelectedValue);
         _bll.Model.State           = 1;
         _bll.Model.IsOpponent      = 3;
         _bll.Model.InsertStaff     = (int)Session["UserID"];
         _bll.Model.InsertTime      = DateTime.Now;
         _bll.Model["OrganizeCity"] = "1";
         _bll.Model["UserName"]     = Session["UserName"].ToString();
         _bll.Add();
     }
 }
 protected void ddl_AccountMonth2_SelectedIndexChanged(object sender, EventArgs e)
 {
     if ((int)(ViewState["IsOpponent"]) == 3 && SVM_DownloadTemplateBLL.GetModelList("AccountMonth=" + ddl_AccountMonth2.SelectedValue + " AND IsOpponent=3 AND MCS_SYS.dbo.UF_Spilt(ExtPropertys,'|',3)='1'").Count == 0)
     {
         string filename = "办事处月度费率导入模板" + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
         SVM_DownloadTemplateBLL _bll = new SVM_DownloadTemplateBLL();
         _bll.Model.Name = filename;
         _bll.Model.AccountMonth = int.Parse(ddl_AccountMonth2.SelectedValue);
         _bll.Model.State = 1;
         _bll.Model.IsOpponent = 3;
         _bll.Model.InsertStaff = (int)Session["UserID"];
         _bll.Model.InsertTime = DateTime.Now;
         _bll.Model["OrganizeCity"] = "1";
         _bll.Model["UserName"] = Session["UserName"].ToString();
         _bll.Add();
     }
 }
    protected void bt_DownloadTemplate_Click(object sender, EventArgs e)
    {
        SVM_DownloadTemplateBLL _bll;
        string filename;

        if (Produtcts.Length == 1)
        {
            MessageBox.Show(this, "对不起,请选择品项!");
            return;
        }

        // string filename = ddl_OrganizeCity.SelectedItem.Text.Replace("------已操作", "") + "重点品项目标导入模板" + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
        StringBuilder organizecitys = new StringBuilder();
        StringBuilder citynames     = new StringBuilder();

        foreach (ListItem item in chk_Citylist.Items)
        {
            if (item.Selected)
            {
                organizecitys.Append(item.Value + ",");
                citynames.Append(item.Text.Substring(0, item.Text.IndexOf("营业部")) + ",");
            }
        }
        if (organizecitys.ToString().EndsWith(","))
        {
            organizecitys.Remove(organizecitys.Length - 1, 1);
            citynames.Remove(citynames.Length - 1, 1);
        }
        filename = citynames.ToString() + "重点品项目标导入模板" + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
        if (filename.Length > 100)
        {
            filename = ddl_AccountMonth.SelectedItem.Text + "重点品项目标导入模板" + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
        }
        _bll                       = new SVM_DownloadTemplateBLL();
        _bll.Model.Name            = filename;
        _bll.Model.AccountMonth    = int.Parse(ddl_AccountMonth.SelectedValue);
        _bll.Model.State           = 1;
        _bll.Model.IsOpponent      = 2;
        _bll.Model.InsertStaff     = (int)Session["UserID"];
        _bll.Model.InsertTime      = DateTime.Now;
        _bll.Model["OrganizeCity"] = organizecitys.ToString();
        _bll.Model.ProductGifts    = Produtcts.ToString().Substring(1, Produtcts.Length - 2);
        _bll.Model["UserName"]     = Session["UserName"].ToString();
        _bll.Add();
        Response.Redirect("ImportHistory.aspx?IsOpponent=2");
    }
    protected void bt_DownloadTemplate_Click(object sender, EventArgs e)
    {
        SVM_DownloadTemplateBLL _bll;
        string filename;
        if (Produtcts.Length == 1)
        {
            MessageBox.Show(this, "对不起,请选择品项!");
            return;
        }

           // string filename = ddl_OrganizeCity.SelectedItem.Text.Replace("------已操作", "") + "重点品项目标导入模板" + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
        StringBuilder organizecitys = new StringBuilder();
        StringBuilder citynames = new StringBuilder();
        foreach (ListItem item in chk_Citylist.Items)
        {
            if (item.Selected)
            {
                organizecitys.Append(item.Value+",");
                citynames.Append(item.Text.Substring(0, item.Text.IndexOf("营业部"))+",");
            }
        }
        if (organizecitys.ToString().EndsWith(","))
        {
            organizecitys.Remove(organizecitys.Length - 1,1);
            citynames.Remove(citynames.Length - 1, 1);
        }
        filename = citynames.ToString() + "重点品项目标导入模板" + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
        if (filename.Length > 100) filename = ddl_AccountMonth.SelectedItem.Text + "重点品项目标导入模板" + "-" + DateTime.Today.ToString("yyyyMMdd") + ".xls";
        _bll = new SVM_DownloadTemplateBLL();
        _bll.Model.Name = filename;
        _bll.Model.AccountMonth =int.Parse(ddl_AccountMonth.SelectedValue);
        _bll.Model.State = 1;
        _bll.Model.IsOpponent = 2;
        _bll.Model.InsertStaff = (int)Session["UserID"];
        _bll.Model.InsertTime = DateTime.Now;
        _bll.Model["OrganizeCity"] = organizecitys.ToString();
        _bll.Model.ProductGifts = Produtcts.ToString().Substring(1, Produtcts.Length - 2);
        _bll.Model["UserName"] = Session["UserName"].ToString();
        _bll.Add();
        Response.Redirect("ImportHistory.aspx?IsOpponent=2");
    }
    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 string GetDownWait(string templateid)
    {
        IList <SVM_DownloadTemplate> downlist = SVM_DownloadTemplateBLL.GetModelList("State=1 AND AccountMonth=" + ddl_AccountMonth.SelectedValue + " AND ID<=" + templateid);

        return(downlist.Count > 0 ? (downlist.Count).ToString() : "");
    }
        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 + "生成操作成功!");
        }