java生成帶有下拉列表框的Excel

packagecom.lawstar.wwwrot.util;importjava.io.FileOutputStream;importjava.util.HashMap;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.hssf.usermodel.*;importorg.apache.poi.ss.usermodel.*;importorg.apache.poi.ss.util.CellRangeAddressList;publicclassCreateExcelMoBusiness{privatestaticStringEXCEL_HIDE_SHEET_NAME="excelhidesheetname";privatestaticStringHIDE_SHEET_NAME_PROVINCE="provinceList";privateHashMapmap=newHashMap();//設置下拉列表的內容privatestaticString[]provinceList={" 浙江","山東"};publicstaticvoidmain(String[]args){//使用事例Workbookwb=newHSSFWorkbook();createExcelMo(wb);creatExcelHidePage(wb);setDataValidation(wb);FileOutputStreamfileOut;try{fileOut=newFileOutputStream("d://test1.xls");wb.write(fileOut);fileOut.close();}catch(Exceptione){e.printStackTrace();publicstaticvoid createExcelMo(Workbookwb){Sheetsheet=wb.createSheet("用戶分類添加批導");//Createarowandputsomecellsinit.Rowsare0based.Rowrow=sheet.createRow(0);Cellcell=row.createCell(0);cell.setCellValue("手機號碼");cell=row.createCell(1);cell.setCellValue("所屬父類");}/***設置模板文件的橫向表頭單元格的樣式*@paramwb*@return*/publicstaticvoidcreatExcelHidePage(Workbookworkbook){SheethideInfoSheet=workbook.createSheet(EXCEL_HIDE_SHEET_NAME);//隱藏一些信息//在隱藏頁設置選擇信息//第二行設置省份名稱列表RowprovinceNameRow=hideInfoSheet.createRow(1);creatRow(provinceNameRow,provinceList);//第二行設置省份名稱列表creatExcelNameList(workbook,HIDE_SHEET_NAME_PROVINCE,2,provinceList.length,false);//設置隱藏頁標志workbook.setSheetHidden(workbook.getSheetIndex(EXCEL_HIDE_SHEET_NAME),true);}/***創(chuàng)建一個名稱*@paramworkbook*/privatestaticvoidcreatExcelNameList(Workbookworkbook,StringnameCode,intorder,intsize,booleancascadeFlag){Namename;name=workbook.createName();name.setNameName(nameCode);name.setRefersToFormula(EXCEL_HIDE_SHEET_NAME+"!"+creatExcelNameList(order,size,cascadeFlag));}/***名稱數據行列計算表達式*@paramworkbook*/privatestaticStringcreatExcelNameList(intorder,intsize,booleancascadeFlag){charstart='A';if(cascadeFlag){start='B';if(size<=25){charend=(char)(start+size-1);return"$"+start+"$"+order+":$"+end+"$"+order;}else{* * 使用已定義的數據源方式設置一個數據驗證}}charendPrefix='A';charendSuffix='A';if((size-25)/26==0||size==51){//26-51之間，包括邊界(僅兩次字母表計算)if((size-25)%26==0){//邊界值endSuffix=(char)('A'+25);}else{endSuffix=(char)('A'+(size-25)%26-1);}}else{//51以上if((size-25)%26==0){endSuffix=(char)('A'+25);endPrefix=(char)(endPrefix+(size-25)/26-1);}else{endSuffix=(char)('A'+(size-25)%26-1);endPrefix=(char)(endPrefix+(size-25)/26);}}return"$"+start+"$"+order+":$"+endPrefix+endSuffix+"$"+order;}}else{if(size<=26){charend=(char)(start+size-1);return"$"+start+"$"+order+":$"+end+"$"+order;}else{charendPrefix='A';charendSuffix='A';if(size%26==0){endSuffix=(char)('A'+25);if(size>52&&size/26>0){endPrefix=(char)(endPrefix+size/26-2);}else{endSuffix=(char)('A'+size%26-1);if(size>52&&size/26>0){endPrefix=(char)(endPrefix+size/26-1);}}return"$"+start+"$"+order+":$"+endPrefix+endSuffix+"$"+order;}創(chuàng)建一列數據*@paramcurrentRow*@paramtextList*/privatestaticvoidcreatRow(RowcurrentRow,String[]textList){if(textList!=null&&textList.length>0){inti=0;for(StringcellValue:textList){CelluserNameLableCell=currentRow.createCell(i++);userNameLableCell.setCellValue(cellValue);}}}/***添加數據驗證選項*@paramsheet*/publicstaticvoidsetDataValidation(Workbookwb){intsheetIndex=wb.getNumberOfSheets();if(sheetIndex>0){for(inti=0;i<sheetIndex;i++){Sheetsheet=wb.getSheetAt(i);if(!EXCEL_HIDE_SHEET_NAME.equals(sheet.getSheetName())){DataValidationdata_validation_list=null;//省份選項添加驗證數據for(inta=2;a<3002;a++){data_validation_list=getDataValidationByFormula(HIDE_SHEET_NAME_PROVINCE,a,2);sheet.addValidationData(data_validation_list);}}}}/**}}*@paramformulaString*@paramnaturalRowIndex*@paramnaturalColumnIndex*@return*/privatestaticDataValidationgetDataValidationByFormula(StringformulaString,intnaturalRowIndex,intnaturalColumnIndex){//加載下拉列表內容DVConstraintconstraint=DVConstraint.createFormulaListConstraint(formulaString);//設置數據有效性加載在哪個單元格上。//四個參數分別是：起始行、終止行、起始列、終止列intfirstRow=naturalRowIndex-1;intlastRow=naturalRowIndex-1;intfirstCol=naturalColumnIndex-1;intlastCol=naturalColumnIndex-1;CellRangeAddressListregions=newCellRangeAddressList(firstRow,lastRow,1,1);//數據有效性對象DataValidationdata_validation_list=newHSSFDa