public void TestSelectedSheetshort() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet1 = (HSSFSheet)wb.CreateSheet("Sheet1"); HSSFSheet sheet2 = (HSSFSheet)wb.CreateSheet("Sheet2"); HSSFSheet sheet3 = (HSSFSheet)wb.CreateSheet("Sheet3"); HSSFSheet sheet4 = (HSSFSheet)wb.CreateSheet("Sheet4"); ConfirmActiveSelected(sheet1, true); ConfirmActiveSelected(sheet2, false); ConfirmActiveSelected(sheet3, false); ConfirmActiveSelected(sheet4, false); wb.SetSelectedTab((short)1); // see Javadoc, in this case selected means "active" Assert.AreEqual(wb.ActiveSheetIndex, (short)wb.ActiveSheetIndex); // Demonstrate bug 44525: // Well... not quite, since isActive + isSelected were also Added in the same bug fix if (sheet1.IsSelected) { //throw new AssertionFailedError("Identified bug 44523 a"); Assert.Fail("Identified bug 44523 a"); } wb.SetActiveSheet(1); if (sheet1.IsActive) { //throw new AssertionFailedError("Identified bug 44523 b"); Assert.Fail("Identified bug 44523 b"); } ConfirmActiveSelected(sheet1, false); ConfirmActiveSelected(sheet2, true); ConfirmActiveSelected(sheet3, false); ConfirmActiveSelected(sheet4, false); Assert.AreEqual(0, wb.FirstVisibleTab); //wb.DisplayedTab=((short)2); //Assert.AreEqual(2, wb.FirstVisibleTab); //Assert.AreEqual(2, wb.DisplayedTab); }
public void TestActiveSheetAfterDelete_bug40414() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet0 = wb.CreateSheet("Sheet0"); NPOI.SS.UserModel.ISheet sheet1 = wb.CreateSheet("Sheet1"); NPOI.SS.UserModel.ISheet sheet2 = wb.CreateSheet("Sheet2"); NPOI.SS.UserModel.ISheet sheet3 = wb.CreateSheet("Sheet3"); NPOI.SS.UserModel.ISheet sheet4 = wb.CreateSheet("Sheet4"); // Confirm default activation/selection ConfirmActiveSelected(sheet0, true); ConfirmActiveSelected(sheet1, false); ConfirmActiveSelected(sheet2, false); ConfirmActiveSelected(sheet3, false); ConfirmActiveSelected(sheet4, false); wb.SetActiveSheet(3); wb.SetSelectedTab(3); ConfirmActiveSelected(sheet0, false); ConfirmActiveSelected(sheet1, false); ConfirmActiveSelected(sheet2, false); ConfirmActiveSelected(sheet3, true); ConfirmActiveSelected(sheet4, false); wb.RemoveSheetAt(3); // after removing the only active/selected sheet, another should be active/selected in its place if (!sheet4.IsSelected) { throw new AssertionException("identified bug 40414 a"); } if (!sheet4.IsActive) { throw new AssertionException("identified bug 40414 b"); } ConfirmActiveSelected(sheet0, false); ConfirmActiveSelected(sheet1, false); ConfirmActiveSelected(sheet2, false); ConfirmActiveSelected(sheet4, true); sheet3 = sheet4; // re-align local vars in this Test case // Some more cases of removing sheets // Starting with a multiple selection, and different active sheet wb.SetSelectedTabs(new int[] { 1, 3, }); wb.SetActiveSheet(2); ConfirmActiveSelected(sheet0, false, false); ConfirmActiveSelected(sheet1, false, true); ConfirmActiveSelected(sheet2, true, false); ConfirmActiveSelected(sheet3, false, true); // removing a sheet that is not active, and not the only selected sheet wb.RemoveSheetAt(3); ConfirmActiveSelected(sheet0, false, false); ConfirmActiveSelected(sheet1, false, true); ConfirmActiveSelected(sheet2, true, false); // removing the only selected sheet wb.RemoveSheetAt(1); ConfirmActiveSelected(sheet0, false, false); ConfirmActiveSelected(sheet2, true, true); // The last remaining sheet should always be active+selected wb.RemoveSheetAt(1); ConfirmActiveSelected(sheet0, true, true); }
public new void TestSheetSelection() { HSSFWorkbook b = new HSSFWorkbook(); b.CreateSheet("Sheet One"); b.CreateSheet("Sheet Two"); b.SetActiveSheet(1); b.SetSelectedTab(1); b.FirstVisibleTab = (1); Assert.AreEqual(1, b.ActiveSheetIndex); Assert.AreEqual(1, b.FirstVisibleTab); }
public void TestSelectedSheet_bug44523() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet1 = wb.CreateSheet("Sheet1"); NPOI.SS.UserModel.ISheet sheet2 = wb.CreateSheet("Sheet2"); NPOI.SS.UserModel.ISheet sheet3 = wb.CreateSheet("Sheet3"); NPOI.SS.UserModel.ISheet sheet4 = wb.CreateSheet("Sheet4"); ConfirmActiveSelected(sheet1, true); ConfirmActiveSelected(sheet2, false); ConfirmActiveSelected(sheet3, false); ConfirmActiveSelected(sheet4, false); wb.SetSelectedTab(1); // see Javadoc, in this case selected means "active" Assert.AreEqual(wb.ActiveSheetIndex, (short)wb.ActiveSheetIndex); // Demonstrate bug 44525: // Well... not quite, since isActive + isSelected were also Added in the same bug fix if (sheet1.IsSelected) { throw new AssertionException("Identified bug 44523 a"); } wb.SetActiveSheet(1); if (sheet1.IsActive) { throw new AssertionException("Identified bug 44523 b"); } ConfirmActiveSelected(sheet1, false); ConfirmActiveSelected(sheet2, true); ConfirmActiveSelected(sheet3, false); ConfirmActiveSelected(sheet4, false); }
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); }