protected override void SetExcelColumns(Sheet worksheet, DataFeedTemplate data) { ushort colindex = 0; ushort colindex2 = 0; HSSFSheet sheet = ((HSSFSheet)worksheet); CellStyle style = sheet.Workbook.CreateCellStyle(); style.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); for (int i = 0; i < data.BasicColumns.Count; i++) { var item = data.BasicColumns[i]; if (item.Type == DataType.LIST) { if (item.Type == DataType.LIST && item.List != null && item.List.Count > 0) { CellRangeAddressList regions = new CellRangeAddressList(HeaderNameRowIndex + 1, 65535, colindex2, colindex2); Name range = WorkBook.CreateName(); HSSFSheet sheetDetails = (HSSFSheet)WorkBook.GetSheet(DETAILSHEET); string celName = GetExcelColumnIndex(colindex); range.RefersToFormula = DETAILSHEET + "!$" + celName + "$2" + ":$" + celName + "$" + (item.List.Count + 1); range.NameName = string.Format("sheet{0}ranges{1}", worksheet.Workbook.GetSheetIndex(worksheet), item.Number); DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(range.NameName); HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint); sheet.AddValidationData(dataValidate); sheet.SetDefaultColumnStyle(colindex2, style); } colindex++; } this.SetExcelColumns(worksheet, item.Width * 600, colindex2); colindex2++; } if (data.Properties != null && data.Properties.Count > 0) { for (int i = 0; i < data.Properties.Count; i++) { var item = data.Properties[i]; if (item.Type == DataType.LIST && item.List != null && item.List.Count > 0) { CellRangeAddressList regions = new CellRangeAddressList(HeaderNameRowIndex + 1, 65535, colindex2, colindex2); Name range = WorkBook.CreateName(); HSSFSheet sheetDetails = (HSSFSheet)WorkBook.GetSheet(DETAILSHEET); string celName = GetExcelColumnIndex(colindex); range.RefersToFormula = DETAILSHEET + "!$" + celName + "$2" + ":$" + celName + "$" + (item.List.Count + 1); range.NameName = BuildRangeName( item.Number, worksheet.Workbook.GetSheetIndex(worksheet) ); DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(range.NameName); HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint); sheet.AddValidationData(dataValidate); sheet.SetDefaultColumnStyle(colindex2, style); } this.SetExcelColumns(worksheet, item.Width * 600, colindex2); colindex++; colindex2++; } } }