你的位置:首页 > ASP.net教程

[ASP.net教程]Npoi 解决下拉列表 String literals in formulas cant be bigger than 255 Chars ASCII


代码:

 1 public static void dropDownList(string[] datas, string filePath) 2 { 3   HSSFWorkbook workbook = new HSSFWorkbook(); 4   ISheet sheet = workbook.CreateSheet("下拉列表测试"); 5   ISheet hidden = workbook.CreateSheet("hidden"); 6   //数据源sheet页不显示 7   workbook.SetSheetHidden(workbook.GetSheetIndex(hidden), true); 8   ICellStyle style = workbook.CreateCellStyle(); 9   style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");10   style.Alignment = HorizontalAlignment.Center;11   style.VerticalAlignment = VerticalAlignment.Center;12   IRow row = null;13   ICell cell = null;14   for (int i = 0; i < datas.Length; i++)15   {16     row = hidden.CreateRow(i);17     cell = row.CreateCell(0);18     cell.SetCellValue(datas[i]);19   }20   IName namedCell = workbook.CreateName();21   namedCell.NameName = "hidden";22   namedCell.RefersToFormula = "hidden!A$1:A$" + datas.Length;23   HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet as HSSFSheet);24   IDataValidationConstraint dvConstraint = (IDataValidationConstraint)dvHelper.CreateFormulaListConstraint("hidden");25   CellRangeAddressList addressList = null;26   HSSFDataValidation validation = null;27   for (int i = 0; i < datas.Length; i++)28   {29     row = sheet.CreateRow(i);30     cell = row.CreateCell(0);31     cell.CellStyle = style;32     addressList = new CellRangeAddressList(i, i, 0, 0);33     validation = (HSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);34     sheet.AddValidationData(validation);35   }36 37   FileStream stream = new FileStream(filePath, FileMode.OpenOrCreate);38   workbook.Write(stream);39   stream.Close();40 }    

View Code

调用:

 1 static void Main(string[] args) 2 { 3   int max = 100; 4   string[] datas = new string[max]; 5   for (int i = 0; i < max; i++) 6   { 7     datas[i] = "" + i; 8   } 9 10   string filePath = @"F:\\test.xls";11   dropDownList(datas, filePath);12 13   Console.Read();14 }