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

package wstar.wwwrot.util;import java.io.FileOutputStream;import java.util.HashMap;import ermodel.Row;import ermodel.*;import ermodel.*;import org.apache.poi.ss.util.CellRangeAddressList;public class CreateExcelMoBusiness {private static String EXCEL_HIDE_SHEET_NAME = "excelhidesheetname"; private static String HIDE_SHEET_NAME_PROVINCE = "provinceList";private HashMap map = new HashMap();//设置下拉列表的内容private static String[] provinceList = {"浙江","山东"};public static void main(String[] args) {//使用事例Workbook wb = new HSSFWorkbook();createExcelMo(wb);creatExcelHidePage(wb);setDataValidation(wb);FileOutputStream fileOut;try {fileOut = new FileOutputStream("d://test1.xls");wb.write(fileOut);fileOut.close();} catch (Exception e) {e.printStackTrace();}}public static void createExcelMo(Workbook wb){Sheet sheet = wb.createSheet("用户分类添加批导");// Create a row and put some cells in it. Rows are 0 based.Row row = sheet.createRow(0);Cell cell = row.createCell(0);cell.setCellValue("手机号码");cell = row.createCell(1);cell.setCellValue("所属父类");}/*** 设置模板文件的横向表头单元格的样式* @param wb* @return*/public static void creatExcelHidePage(Workbook workbook){ Sheet hideInfoSheet =workbook.createSheet(EXCEL_HIDE_SHEET_NAME);//隐藏一些信息//在隐藏页设置选择信息//第二行设置省份名称列表Row provinceNameRow = hideInfoSheet.createRow(1);creatRow(provinceNameRow, provinceList);//第二行设置省份名称列表creatExcelNameList(workbook, HIDE_SHEET_NAME_PROVINCE, 2, provinceList.length, false);//设置隐藏页标志workbook.setSheetHidden(workbook.getSheetIndex(EXCEL_HIDE_SHEET_N AME), true);}/*** 创建一个名称* @param workbook*/private static void creatExcelNameList(Workbook workbook,String nameCode,int order,int size,boolean cascadeFlag){Name name;name = workbook.createName();name.setNameName(nameCode);name.setRefersToFormula(EXCEL_HIDE_SHEET_NAME+"!"+creatExcelName List(order,size,cascadeFlag));}/*** 名称数据行列计算表达式* @param workbook*/private static String creatExcelNameList(int order,int size,boolean cascadeFlag){char start = 'A';if(cascadeFlag){start = 'B';if(size<=25){char end = (char)(start+size-1);return "$"+start+"$"+order+":$"+end+"$"+order;}else{char endPrefix = 'A';char endSuffix = '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){char end = (char)(start+size-1);return "$"+start+"$"+order+":$"+end+"$"+order;}else{char endPrefix = 'A';char endSuffix = '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; }}}/*** 创建一列数据* @param currentRow* @param textList*/private static void creatRow(Row currentRow,String[] textList){if(textList!=null&&textList.length>0){int i = 0;for(String cellValue : textList){Cell userNameLableCell = currentRow.createCell(i++);userNameLableCell.setCellValue(cellValue);}}}/*** 添加数据验证选项* @param sheet*/public static void setDataValidation(Workbook wb){int sheetIndex = wb.getNumberOfSheets();if(sheetIndex>0){for(int i=0;i<sheetIndex;i++){Sheet sheet = wb.getSheetAt(i);if(!EXCEL_HIDE_SHEET_NAME.equals(sheet.getSheetName())){DataValidation data_validation_list = null;//省份选项添加验证数据for(int a=2;a<3002;a++){data_validation_list =getDataValidationByFormula(HIDE_SHEET_NAME_PROVINCE,a,2);sheet.addValidationData(data_validation_list);}}}}}/*** 使用已定义的数据源方式设置一个数据验证* @param formulaString* @param naturalRowIndex* @param naturalColumnIndex* @return*/private static DataValidation getDataValidationByFormula(String formulaString,int naturalRowIndex,int naturalColumnIndex){//加载下拉列表内容DVConstraint constraint =DVConstraint.createFormulaListConstraint(formulaString);//设置数据有效性加载在哪个单元格上。