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;
        }