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);}/***创建一个名称*@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;}创建一列数据*@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