POI增加 数据验证 下拉

POI增加验证列

List<String> nationality = new ArrayList<String>();
		List<String> last_education = new ArrayList<String>();
		List<String> graduated_yotei = new ArrayList<String>();
		List<String> entrance_period = new ArrayList<String>();
	    List<String> sexList = 	new ArrayList<String>();

Map<int[],List<String>> dataMap = new HashMap<int[],List<String>>();
				dataMap.put(new int[]{1,2000,5,5},nationality);
				dataMap.put(new int[]{1,2000,8,8},last_education);
				dataMap.put(new int[]{1,2000,17,17},graduated_yotei);
				dataMap.put(new int[]{1,2000,13,13},courseNames);
				dataMap.put(new int[]{1,2000,6,6},sexList);
				dataMap.put(new int[]{1,2000,12,12},entrance_period);


				//ExcelUtil.setSheetDropList(xlsTemplateFileName, outputFileName, dataMap, 0);
				ExcelUtil.setSheetDropList(xlsTemplateFileName, outputFileName, dataMap, 0,1);

 

public static void setSheetDropList(String xlsTemplateFileName, String outputFileName,Map<int[],List<String>> dataMap,int sheetIndex,int hideSheetIndex) throws IOException {


		FileInputStream fileInputStream = new FileInputStream(xlsTemplateFileName);

		FileUtils.forceMkdirParent(new File(outputFileName));

        if(xlsTemplateFileName.toLowerCase().endsWith("xlsx")){

			// ファイルの読み込み
			XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);

			// hide sheet を取得する
			XSSFSheet hideSheet = workbook.getSheetAt(hideSheetIndex);
			String hideSheetName = hideSheet.getSheetName();
			//setValue(Sheet sheet, int rowIndex, int cellIndex, Object value)

			// sheetを取得する
			XSSFSheet sheet = workbook.getSheetAt(sheetIndex);

			XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);

			for (Map.Entry<int[],List<String>> entry : dataMap.entrySet()) {

				   int[] postions =  entry.getKey();
				   List<String> list =  entry.getValue();

				   if(list == null ||  list.size()==0) {
					   continue;
				   }
				 //{1,2000,8,8}
				   for(int i=0;i<list.size();i++) {
					   int rowIndex = postions[0]+ i;
					   int cellIndex = postions[2]  ;
					   String value = list.get(i);
					   setValue(hideSheet,rowIndex,cellIndex,value);
				   }

					String[] array = new String[list.size()];
					String[] data=list.toArray(array);


					// ボックスのオプションのデータは、制約として
					XSSFDataValidationConstraint dvConstraint =
					    (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(data);			//new String[]{"a", "b", "c"}
					// ドロップダウンボックスの範囲に設定する
					CellRangeAddressList addressList =
					    new CellRangeAddressList(postions[0],postions[1],postions[2],postions[3]);
					// DataValidationオブジェクトを作成する
/*					XSSFDataValidation validation =
					    (XSSFDataValidation)dvHelper.createValidation(dvConstraint, addressList);
					validation.setShowErrorBox(true);*/

					//String strFormula = "temp!$A$1:$A$100";

					String  lie =   excelColIndexToStr(postions[2]+1);
					String  heng_start = (postions[0]+1) +"";
					String  heng_end =( postions[0] + list.size()) + "";

					String	strFormula = hideSheetName +"!$"+lie +"$" + heng_start + ":$"+lie + "$"+heng_end;

					// =$F$2:$F$7

					XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(ValidationType.LIST,strFormula);

					XSSFDataValidation validation = (XSSFDataValidation)dvHelper.createValidation(constraint, addressList);
					sheet.addValidationData(validation);

					// 指定したワークシートに作用する
					sheet.addValidationData(validation);

			}

			FileOutputStream stream = new FileOutputStream(outputFileName);
		    workbook.write(stream);
		    stream.close();
		    workbook.close();
        }else if(xlsTemplateFileName.toLowerCase().endsWith("xls")) {
        	// ファイルの読み込み
        	HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
			// hide sheet を取得する
        	HSSFSheet hideSheet = workbook.getSheetAt(hideSheetIndex);
        	// sheetを取得する
        	HSSFSheet sheet = workbook.getSheetAt(0);

        	HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet);

			for (Map.Entry<int[],List<String>> entry : dataMap.entrySet()) {

				   int[] postions =  entry.getKey();
				   List<String> list =  entry.getValue();

					 //{1,2000,8,8}
				   for(int i=0;i<list.size();i++) {
					   int rowIndex = postions[0]+ i;
					   int cellIndex = postions[2] ;
					   String value = list.get(i);
					   setValue(hideSheet,rowIndex,cellIndex,value);

				   }

					String[] array = new String[list.size()];
					String[] data=list.toArray(array);

				    // ボックスのオプションのデータは、制約として
		        	DataValidationConstraint dvConstraint =
					    dvHelper.createExplicitListConstraint(data);


		        	// ドロップダウンボックスの範囲に設定する
					CellRangeAddressList addressList =
					    new CellRangeAddressList(postions[0],postions[1],postions[2],postions[3]);
					// DataValidationオブジェクトを作成する
					HSSFDataValidation validation =
					    (HSSFDataValidation)dvHelper.createValidation(dvConstraint, addressList);
					validation.setShowErrorBox(true);

					// 指定したワークシートに作用する
					sheet.addValidationData(validation);
			}

			FileOutputStream stream = new FileOutputStream(outputFileName);
			workbook.write(stream);
		    stream.close();
		    workbook.close();
        }
	}