Npoi 解決下拉列表 String literals in formulas can't 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 }        

調用:

 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 }