Npoi 解決下拉列表 String literals in formulas can't be bigger than 255 Chars ASCII，npoiliterals
代碼:
![]()
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 }
