java poi给excel单元格添加下拉菜单(不限定选项个数)

0、参考资料：

是否为隐藏sheet： http://blog.csdn.net/mj158518/article/details/53931247

C#解决方案：  http://www.bubuko.com/infodetail-1667595.html

1、依赖jar包：

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-excelant</artifactId>
    <version>3.9</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-scratchpad</artifactId>
    <version>3.9</version>
</dependency>

使用maven下载jar包： http://blog.csdn.net/hjnth/article/details/78179808

2、添加下拉菜单的方法：

	 /**
	 * 单元格添加下拉菜单(不限制菜单可选项个数)<br/>
	 * [注意：此方法会添加隐藏的sheet，可调用getDataSheetInDropMenuBook方法获取用户输入数据的未隐藏的sheet]<br/>
	 * [待添加下拉菜单的单元格 -> 以下简称：目标单元格]
	 * @param @param workbook 
	 * @param @param tarSheet 目标单元格所在的sheet
	 * @param @param menuItems 下拉菜单可选项数组
	 * @param @param firstRow 第一个目标单元格所在的行号(从0开始)
	 * @param @param lastRow 最后一个目标单元格所在的行(从0开始)
	 * @param @param column 待添加下拉菜单的单元格所在的列(从0开始)
	 */
	 public static void addDropDownList(HSSFWorkbook workbook, HSSFSheet tarSheet, String[] menuItems, int firstRow, int lastRow, int column) throws Exception
	 {
		 if(null == workbook){
			 throw new Exception("workbook为null");
		 }
		 if(null == tarSheet){
			 throw new Exception("待添加菜单的sheet为null");
		 }
		 
		 //必须以字母开头，最长为31位
		 String hiddenSheetName = "a" + UUID.randomUUID().toString().replace("-", "").substring(1, 31);
		 //excel中的"名称"，用于标记隐藏sheet中的用作菜单下拉项的所有单元格
		 String formulaId = "form" + UUID.randomUUID().toString().replace("-", "");
	     HSSFSheet hiddenSheet = workbook.createSheet(hiddenSheetName);//用于存储 下拉菜单数据
	     //存储下拉菜单项的sheet页不显示
	     workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet), true);
	     
	     HSSFRow row = null;
	     HSSFCell cell = null;
	     //隐藏sheet中添加菜单数据
	     for (int i = 0; i < menuItems.length; i++)
	     {
	         row = hiddenSheet.createRow(i);
	         //隐藏表的数据列必须和添加下拉菜单的列序号相同，否则不能显示下拉菜单
	         cell = row.createCell(column);
	         cell.setCellValue(menuItems[i]);
	     }
	     
	     HSSFName namedCell = workbook.createName();//创建"名称"标签，用于链接
	     namedCell.setNameName(formulaId);
	     namedCell.setRefersToFormula(hiddenSheetName + "!A$1:A$" + menuItems.length);
	     HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(tarSheet);
	     DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(formulaId);
	     
	     CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, column, column);
	     HSSFDataValidation validation = (HSSFDataValidation)dvHelper.createValidation(dvConstraint, addressList);//添加菜单(将单元格与"名称"建立关联)
         tarSheet.addValidationData(validation);
	 }
	 

	/**
	 * 从调用addDropDownList后添加下拉菜单的Workbook中获取用户输入数据的shee列表
	 * @param book
	 * @return
	 */
	public static List<HSSFSheet> getDataSheetInDropMenuBook(HSSFWorkbook book){
		return getUnHideSheets(book);
	}
	
	/**
	 * 获取所有未隐藏的sheet
	 * @param book
	 * @return
	 */
	public static List<HSSFSheet> getUnHideSheets(HSSFWorkbook book){
		List<HSSFSheet> ret = new ArrayList<HSSFSheet>();
		if(null == book){
			return ret;
		}
		
		int sheetCnt = book.getNumberOfSheets();
		for (int i = 0; i < sheetCnt; i++) {
			if(!book.isSheetHidden(i)){
				ret.add(book.getSheetAt(i));
			}
		}
		
		return ret;
	}