コード例 #1
0
        public ActionResult Template(int? tagId)
        {

            var basicInfoSheetName = ProUploadService.BASIC_SHEET;
            var moreInfoSheetName = ProUploadService.MORE_SHEET;
            var supportSheetName = "不要修改";

            var headerLabels = new Dictionary<string, dynamic>() { 
                {"商品代码",new {dataformat=0,width=10}},
                {"商品名称",new {dataformat=0,width=20}},
                {"描述",new {dataformat=0,width=50}},
                {"吊牌价",new {dataformat=2,width=8}},
            {"现价",new {dataformat=2,width=8}},
            {"品牌名",new {dataformat=0,width=20}},
            { "分类名",new {dataformat=0,width=20}},
            {"门店名",new {dataformat=0,width=20}},
            { "促销活动编码",new {dataformat=0,width=20}},
            { "专题编码(多个以,分割)",new {dataformat=0,width=20}},
             {"可销售",new {dataformat=2,width=5}},
             {"商品货号",new {dataformat=0,width=10}}
            };
            var workbook = new HSSFWorkbook();
            var headerLabelCellStyle = workbook.CreateCellStyle();
            headerLabelCellStyle.BorderBottom = BorderStyle.THIN;
            headerLabelCellStyle.BorderLeft = BorderStyle.THIN;
            headerLabelCellStyle.BorderRight = BorderStyle.THIN;
            headerLabelCellStyle.BorderTop = BorderStyle.THIN;
            headerLabelCellStyle.WrapText = true;
            var headerLabelFont = workbook.CreateFont();
            headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD;
            headerLabelCellStyle.SetFont(headerLabelFont);
            //set support sheet
            var supportSheet = workbook.CreateSheet(supportSheetName);
            workbook.SetSheetHidden(workbook.GetSheetIndex(supportSheet), true);
            Func<int, dynamic, int> supportFill = (rowIndex, data) =>
            {
                var brandRow = supportSheet.CreateRow(rowIndex++);
                var brandCodeCol = brandRow.CreateCell(1);
                brandCodeCol.SetCellType(CellType.STRING);
                brandCodeCol.SetCellValue(data.Id);
                var brandNameCol = brandRow.CreateCell(2);
                brandNameCol.SetCellType(CellType.STRING);
                brandCodeCol.SetCellValue(data.Name);
                return rowIndex;
            };
            int brandRowIndex = 0;
            foreach (var brand in _brandRepo.Get(b => b.Status != (int)DataStatus.Deleted).OrderBy(b => b.Name).Select(b => new { Id = b.Id, Name = b.Name }))
            {
                brandRowIndex = supportFill(brandRowIndex, brand);
            }
            int tagRowIndex = brandRowIndex;
            var tagLinq = _tagRepo.Get(b => b.Status != (int)DataStatus.Deleted).OrderBy(b => b.Name).Select(b => new { Id = b.Id, Name = b.Name });
            //if (tagId.HasValue)
            //    tagLinq = tagLinq.Where(t => t.Id == tagId.Value);
            foreach (var tag in tagLinq.OrderBy(t => t.Name))
            {
                tagRowIndex = supportFill(tagRowIndex, tag);
            }
            int storeRowIndex = tagRowIndex;
            foreach (var store in _storeRepo.Get(b => b.Status != (int)DataStatus.Deleted).OrderBy(b => b.Name).Select(b => new { Id = b.Id, Name = b.Name }))
            {
                storeRowIndex = supportFill(storeRowIndex, store);
            }

            //set basic sheet 
            var sheet1 = workbook.CreateSheet(basicInfoSheetName);
            //workbook.SetSheetOrder(basicInfoSheetName, 0);

            var rowFirst = sheet1.CreateRow(0);
            Action<int, string, dynamic> cellSetting = (cellindex, desc, option) =>
            {
                var cell = rowFirst.CreateCell(cellindex);
                cell.SetCellType(CellType.STRING);
                cell.SetCellValue(desc);
                cell.CellStyle = headerLabelCellStyle;
                sheet1.SetColumnWidth(cellindex, option.width * 255);

                var currentCellStyle = workbook.CreateCellStyle();
                currentCellStyle.DataFormat = (short)option.dataformat;
                sheet1.SetDefaultColumnStyle(cellindex, currentCellStyle);
            };
            int index = 0;
            foreach (var key in headerLabels.Keys)
            {
                cellSetting(index++, key, headerLabels[key]);
            }
            //set constraint
            DVConstraint brandConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B$1:$B${1}", supportSheetName, brandRowIndex));
            CellRangeAddressList brandaddressList = new CellRangeAddressList(1, 1000, 5, 5);
            HSSFDataValidation branddataValidation = new HSSFDataValidation(brandaddressList, brandConstaint);
            branddataValidation.SuppressDropDownArrow = false;
            sheet1.AddValidationData(branddataValidation);

            DVConstraint tagConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, brandRowIndex + 1, tagRowIndex));
            CellRangeAddressList tagaddressList = new CellRangeAddressList(1, 1000, 6, 6);
            HSSFDataValidation tagdataValidation = new HSSFDataValidation(tagaddressList, tagConstaint);
            tagdataValidation.SuppressDropDownArrow = false;
            sheet1.AddValidationData(tagdataValidation);

            DVConstraint storeConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, tagRowIndex + 1, storeRowIndex));
            CellRangeAddressList storeaddressList = new CellRangeAddressList(1, 1000, 7, 7);
            HSSFDataValidation storedataValidation = new HSSFDataValidation(storeaddressList, storeConstaint);
            storedataValidation.SuppressDropDownArrow = false;
            sheet1.AddValidationData(storedataValidation);

            DVConstraint is4saleConstaint = DVConstraint.CreateExplicitListConstraint(new string[] { ProUploadService.IS_4SALE_YES, ProUploadService.IS_4SALE_NO });
            CellRangeAddressList is4saleaddressList = new CellRangeAddressList(1, 1000, 10, 10);
            HSSFDataValidation is4saledataValidation = new HSSFDataValidation(is4saleaddressList, is4saleConstaint);
            is4saledataValidation.SuppressDropDownArrow = false;
            sheet1.AddValidationData(is4saledataValidation);

            //set sheet2
            //create property value sheet
            var propertyLinq = _propertyRepo.Get(b => b.Status != (int)DataStatus.Deleted)
                            .Join(Context.Set<TagEntity>(), o => o.CategoryId, i => i.Id, (o, i) => new { P = o, C = i })
                            .OrderBy(b => b.P.CategoryId)
                            .ThenBy(b => b.P.PropertyDesc);
            int propertyRowIndex = storeRowIndex;
            foreach (var tag in tagLinq)
            {
                var propertyFromIndex = propertyRowIndex;
                foreach (var property in propertyLinq.Where(p => p.C.Id == tag.Id).Select(p=>new {Id = p.P.Id,Name=p.P.PropertyDesc}))
                {
                    propertyRowIndex = supportFill(propertyRowIndex, property);
                }
                if (propertyRowIndex > propertyFromIndex)
                {
                    var rName = workbook.CreateName();
                    rName.RefersToFormula = string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, propertyFromIndex + 1, propertyRowIndex);
                    rName.NameName = tag.Name;
                }
                foreach (var property in propertyLinq.Where(p => p.C.Id == tag.Id))
                {
                    int valueFromIndex = propertyRowIndex;
                    foreach (var pvalue in _valueRepo.Get(b => b.Status != (int)DataStatus.Deleted && b.PropertyId == property.P.Id).Select(p => new { Id = p.Id, Name = p.ValueDesc }))
                    {
                        propertyRowIndex = supportFill(propertyRowIndex, pvalue);
                    }
                    if (propertyRowIndex > valueFromIndex)
                    {
                        var rName = workbook.CreateName();
                        rName.RefersToFormula = string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, valueFromIndex + 1, propertyRowIndex);
                        rName.NameName = string.Format("{0}_{1}", property.C.Name, property.P.PropertyDesc);
                    }
                }
            }

            var moreheaderLabels = new Dictionary<string, dynamic>() { 
                {"商品代码",new {dataformat=0,width=10}},
                 {"属性名",new {dataformat=0,width=20}},
                 {"属性值",new {dataformat=0,width=10}}
                };
            var sheet2 = workbook.CreateSheet(moreInfoSheetName);
            var morerowFirst = sheet2.CreateRow(0);
            int moreCellIndex = 0;
            foreach (var key in moreheaderLabels.Keys)
            {
                var cell = morerowFirst.CreateCell(moreCellIndex);
                cell.SetCellType(CellType.STRING);
                cell.SetCellValue(key);
                cell.CellStyle = headerLabelCellStyle;
                sheet2.SetColumnWidth(moreCellIndex, moreheaderLabels[key].width * 255);

                var currentCellStyle = workbook.CreateCellStyle();
                currentCellStyle.DataFormat = (short)moreheaderLabels[key].dataformat;
                sheet2.SetDefaultColumnStyle(moreCellIndex, currentCellStyle);
                moreCellIndex++;

            }
            for (int i = 3; i <= 11; i++)
            {
                var cell = morerowFirst.CreateCell(i);
                cell.SetCellType(CellType.STRING);
                cell.SetCellValue(string.Empty);
                cell.CellStyle = headerLabelCellStyle;
                sheet2.SetColumnWidth(i, 10 * 255);
            
            }


            //set merge cell for last cell
            sheet2.AddMergedRegion(new CellRangeAddress(0, 0, 2, 11));
            //set constraint
            DVConstraint codeConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$A${1}:$A${2}", basicInfoSheetName, 2, 1000));
            CellRangeAddressList codeaddressList = new CellRangeAddressList(1, 1000, 0, 0);
            HSSFDataValidation codedataValidation = new HSSFDataValidation(codeaddressList, codeConstaint);
            codedataValidation.SuppressDropDownArrow = false;
            sheet2.AddValidationData(codedataValidation);



            for (int i = 1; i < 1000; i++)
            {
                //create hide helper cell
                sheet2.SetArrayFormula(string.Format("VLOOKUP($A${0},'{1}'!$A$1:$L$1000,7,FALSE)&\"_\"&$B${0}",i+1,basicInfoSheetName),
                            new CellRangeAddress(i,i,12,12));
                //create hide helper cell
                sheet2.SetArrayFormula(string.Format("VLOOKUP($A${0},'{1}'!$A$1:$L$1000,7,FALSE)", i + 1, basicInfoSheetName),
                            new CellRangeAddress(i, i, 13, 13));
                sheet2.SetColumnHidden(12, true);
                sheet2.SetColumnHidden(13, true);
                DVConstraint pConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("INDIRECT($N${0})", i + 1)); // DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, storeRowIndex + 1, propertyRowIndex));
                CellRangeAddressList paddressList = new CellRangeAddressList(i,i, 1, 1);
                HSSFDataValidation pdataValidation = new HSSFDataValidation(paddressList, pConstaint);
                pdataValidation.SuppressDropDownArrow = false;
                sheet2.AddValidationData(pdataValidation);
                //set constraint
                DVConstraint pvConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("INDIRECT($M${0})", i + 1));
                CellRangeAddressList pvaddressList = new CellRangeAddressList(i, i, 2, 11);
                HSSFDataValidation pvdataValidation = new HSSFDataValidation(pvaddressList, pvConstaint);
                pvdataValidation.SuppressDropDownArrow = false;
                sheet2.AddValidationData(pvdataValidation);
            }


            workbook.SetActiveSheet(workbook.GetSheetIndex(sheet1));

            var ms = new MemoryStream();
            workbook.Write(ms);
            ms.Position = 0;
            var downloadName = tagId.HasValue ? string.Format("商品上传模版-{0}.xls", tagId.Value) : "商品上传模块.xls";
            return File(ms, "application/vnd.ms-excel", downloadName);

        }