生成下拉列表中出現的問題

• 當一個單元格中的字節超過255時,會報異常,

• java.lang.IllegalArgumentException: String literals in formulas can’t be bigger than 255 characters ASCII

• 一般情況下生成下拉列表中使用的方法

String[] textList, int firstRow, int endRow, int firstCol,int endCol) {// 加載下拉列表內容DVConstraint constraint = DVConstraint.createExplicitListConstraint(textList);// 設置數據有效性加載在哪個單元格上,四個參數分別是：起始行、終止行、起始列、終止列CellRangeAddressList regions = new CellRangeAddressList(firstRow,endRow, firstCol, endCol);// 數據有效性對象HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);realSheet.addValidationData(data_validation_list);return realSheet;}

• DVConstraint constraint = DVConstraint.createExplicitListConstraint(textList);==直接加載數組textList==

• 當數組過大則需再創建一個sheet也存放數據,再將sheet引用到真正的realSheet中

• 完整示例

public static void main(String[] args) throws IOException {String[] countryName = {"張新花", "趙峰", "劉丹", "黃生功", "李春楠", "馬艷珍", "張建群", "趙瑞年", "井含英", "郭元維", "王文芳", "段國英", "張文婷", "陳鵬英", "常發梅", "孔繁菲", "祁洪香", "韓雅楠", "范明奎", "任順龍", "丁永樂", "馬德錄", "吳紅英", "嚴進香", "史芳", "林玲", "王有運", "樊有祥", "靳智慧", "馬梅", "陳加賢", "李萬輝", "馬斌花", "李梅林", "李生豐", "劉丹丹", "楊菊清", "賈錫通", "山永信", "陳少漁", "盧君", "任永慧", "竇珍香", "張國清", "李美玲", "曹艷慧", "劉永秀", "樊光芳", "侯尚梅", "羅生琳", "張海芬", "梁召賢", "談明寶", "賈統梅", "王生萍", "周泓宇", "江秀蘭", "孫小青", "馬占富", "吳秀萍", "馬宏偉", "德吉卓么", "梁奎先", "宋伯福", "馬占剛", "李國鋒", "史元", "馬佐輝", "李貴福", "喬世紅", "李曉紅", "荘柳紅", "戴連梅", "裴延紅", "田海兵", "黨忠霞", "星玉英", "張廷良", "韓國寶", "韓慶文", "趙玉瑩", "馬有蓮", "郭麗萍", "吳秀春", "賈延章", "石維丙", "李成梅", "喇成明", "解生旺", "運占花", "熊成吉", "賈生升", "景源德", "李文君", "馬洪淇", "李慧文", "魏學剛", "羅長平", "胡生春", "田種興", "李滿存", "石延花", "王生蘭", "趙家軍", "安生年", "田生花", "趙雪玲", "宋邦元", "張紅梅", "劉巖莉", "鐘光霖", "汪武祥", "李連發", "張雪蓮", "逯進義", "馬花", "思春梅", "牛永水", "劉萬邦", "張小蘭", "魏珍", "李永梅", "劉躍峰", "楊有德", "肖正文", "馬蘭", "郭永清", "蔡曉燕", "孫占德", "陳文娟", "王凌云", "盧世寶", "王桂梅", "宋邦宏", "李生花", "張成芳", "趙明花", "張劉賢", "趙仲慧"};HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet realSheet = workbook.createSheet("Sheet xls"); HSSFSheet hidden = workbook.createSheet("hidden"); HSSFCell cell = null;for (int i = 0, length= countryName.length; i < length; i++) { String name = countryName[i]; HSSFRow row = hidden.createRow(i); cell = row.createCell(0); cell.setCellValue(name); } CellRangeAddressList regions = new CellRangeAddressList(0,0, 0, 0);//獲取單元格的坐標System.out.println(regions.getCellRangeAddress(0).formatAsString());Name namedCell = workbook.createName(); namedCell.setNameName("hidden"); namedCell.setRefersToFormula("hidden!A1:A" + countryName.length); //加載數據,將名稱為hidden的DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden"); // 設置數據有效性加載在哪個單元格上,四個參數分別是：起始行、終止行、起始列、終止列CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0); HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);//將第二個sheet設置為隱藏workbook.setSheetHidden(1, true); realSheet.addValidationData(validation); FileOutputStream stream = new FileOutputStream("c:\\range.xls"); workbook.write(stream); stream.close();}

• 創建一個sheetHSSFSheet hidden = workbook.createSheet("hidden");
• 將數組中的數據添加到改sheet中
• 設置隱藏workbook.setSheetHidden(1, true);
• 定義名稱
  
  Name namedCell = workbook.createName();
  namedCell.setNameName("hidden");
  namedCell.setRefersToFormula("hidden!A1:A" + countryName.length);
  
• 加載到realsheet上

//加載數據,將名稱為hidden的 DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden"); // 設置數據有效性加載在哪個單元格上,四個參數分別是：起始行、終止行、起始列、終止列 CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0); HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint); realSheet.addValidationData(validation);

POI操作EXCEl樣式

//設置長寬的長度 realSheet.setDefaultColumnWidth(15); realSheet.setDefaultRowHeightInPoints(25);//設置背景色 HSSFCellStyle style = wb.createCellStyle(); style.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);// 設置背景色 style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//設置邊框 style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下邊框 style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左邊框 style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上邊框 style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右邊框//設置字體 HSSFFont font = wb.createFont(); font.setFontName("黑體"); font.setFontHeightInPoints((short) 10);//設置字體大小 font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//粗體顯示 style.setFont(font);//選擇需要用到的字體格式//居中設置 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

總結

以上是生活随笔為你收集整理的POI实现EXCEL下拉列表的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。