POI实现单元格带下拉框效果(Drop Down Lists)

     最近想在看下poi导出excel，先看下单元格带下拉框效果怎么实现，其他的还在慢慢研究。本文参考了：

      http://poi.apache.org/spreadsheet/quick-guide.html

      http://stackoverflow.com/questions/8655185/limitation-while-generating-excel-drop-down-list-with-apache-poi

 

      直接上代码了：

      数据源如下：

    

String dropDataSource = null;
		StringBuffer sb = new StringBuffer();
		for (int i = 100; i >= 20; i--) {
			sb.append(i).append(",");
		}
		sb = sb.deleteCharAt(sb.length() - 1);
		dropDataSource = sb.toString();

 

      excel 2003实现代码如下：

     

public static void dropDownList42003(String dataSource, String filePath)
			throws Exception {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet realSheet = workbook.createSheet("下拉列表测试");
		String[] datas = dataSource.split("\\,");
		DVConstraint dvConstraint = DVConstraint
				.createExplicitListConstraint(datas);
		CellRangeAddressList addressList = null;
		HSSFDataValidation validation = null;
		for (int i = 0; i < 100; i++) {
			addressList = new CellRangeAddressList(i, i, 0, 0);
			validation = new HSSFDataValidation(addressList, dvConstraint);
			// 03 默认setSuppressDropDownArrow(false)
			// validation.setSuppressDropDownArrow(false);
			// validation.setShowErrorBox(true);
			validation.setShowErrorBox(false);// 取消弹出错误框
			realSheet.addValidationData(validation);
		}
		FileOutputStream stream = new FileOutputStream(filePath);
		workbook.write(stream);
		stream.close();
		addressList = null;
		validation = null;
	}

   效果为：

  

  

    excel 2007实现如下：

   

public static void dropDownList42007(String dataSource, String filePath)
			throws Exception {
		XSSFWorkbook workbook = new XSSFWorkbook();
		XSSFSheet sheet = workbook.createSheet("下拉列表测试");
		String[] datas = dataSource.split("\\,");
		XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
		XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
				.createExplicitListConstraint(datas);
		CellRangeAddressList addressList = null;
		XSSFDataValidation validation = null;
		for (int i = 0; i < 100; i++) {
			addressList = new CellRangeAddressList(i, i, 0, 0);
			validation = (XSSFDataValidation) dvHelper.createValidation(
					dvConstraint, addressList);
			// 07默认setSuppressDropDownArrow(true);
			// validation.setSuppressDropDownArrow(true);
			// validation.setShowErrorBox(true);
			sheet.addValidationData(validation);
		}
		FileOutputStream stream = new FileOutputStream(filePath);
		workbook.write(stream);
		stream.close();
		addressList = null;
		validation = null;
	}

   效果如下：

  

 

   注意上面的数据源大小，只从100到20，到10的话，excel 2003创建报错，错误信息为：

  

Exception in thread "main" java.lang.IllegalArgumentException: String literals in formulas can't be bigger than 255 characters ASCII
	at org.apache.poi.ss.formula.ptg.StringPtg.<init>(StringPtg.java:65)
	at org.apache.poi.hssf.usermodel.DVConstraint.createListFormula(DVConstraint.java:412)
	at org.apache.poi.hssf.usermodel.DVConstraint.createFormulas(DVConstraint.java:385)
	at org.apache.poi.hssf.usermodel.HSSFDataValidation.createDVRecord(HSSFDataValidation.java:195)
	at org.apache.poi.hssf.usermodel.HSSFSheet.addValidationData(HSSFSheet.java:396)

   excel 2007创建不报错，但是下拉框出不来。如果数据源很多，建议引用另一个sheet页的数据。

 

    数据源如下：

   

sb = new StringBuffer();
		for (int i = 200; i >= 0; i--) {
			sb.append(i).append(",");
		}
		sb = sb.deleteCharAt(sb.length() - 1);
		dropDataSource = sb.toString();

    excel 2003实现如下：

   

public static void dropDownList2003(String dataSource, String filePath)
			throws Exception {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet realSheet = workbook.createSheet("下拉列表测试");
		HSSFSheet hidden = workbook.createSheet("hidden");
		//数据源sheet页不显示
		workbook.setSheetHidden(1, true);
		String[] datas = dataSource.split("\\,");
		CellStyle style = workbook.createCellStyle();
		style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		HSSFRow row = null;
		HSSFCell cell = null;
		for (int i = 0, length = datas.length; i < length; i++) {
			row = hidden.createRow(i);
			cell = row.createCell(0);
			cell.setCellValue(Integer.valueOf(datas[i]));
		}
		Name namedCell = workbook.createName();
		namedCell.setNameName("hidden");
		namedCell.setRefersToFormula("hidden!A1:A" + datas.length);
		DVConstraint constraint = DVConstraint
				.createFormulaListConstraint("hidden");
		CellRangeAddressList addressList = null;
		HSSFDataValidation validation = null;
		row = null;
		cell = null;
		for (int i = 0; i < 100; i++) {
			row = realSheet.createRow(i);
			cell = row.createCell(0);
			cell.setCellStyle(style);
			addressList = new CellRangeAddressList(i, i, 0, 0);
			validation = new HSSFDataValidation(addressList, constraint);
			realSheet.addValidationData(validation);
			validation.setShowErrorBox(false);// 取消弹出错误框
		}
		FileOutputStream stream = new FileOutputStream(filePath);
		workbook.write(stream);
		stream.close();
		style = null;
		addressList = null;
		validation = null;
	}

 

    效果为：

   

 

      excel 2007实现如下：

     

public static void dropDownList2007(String dataSource, String filePath)
			throws Exception {
		XSSFWorkbook workbook = new XSSFWorkbook();
		XSSFSheet sheet = workbook.createSheet("下拉列表测试");
		XSSFSheet hidden = workbook.createSheet("hidden");
		//数据源sheet页不显示
		workbook.setSheetHidden(1, true);
		String[] datas = dataSource.split("\\,");
		CellStyle style = workbook.createCellStyle();
		style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		XSSFRow row = null;
		XSSFCell cell = null;
		for (int i = 0, length = datas.length; i < length; i++) {
			row = hidden.createRow(i);
			cell = row.createCell(0);
			cell.setCellValue(Integer.valueOf(datas[i]));
		}
		Name namedCell = workbook.createName();
		namedCell.setNameName("hidden");
		namedCell.setRefersToFormula("hidden!A1:A" + datas.length);
		XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
		XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
				.createFormulaListConstraint("hidden");
		CellRangeAddressList addressList = null;
		XSSFDataValidation validation = null;
		for (int i = 0; i < 100; i++) {
			row = sheet.createRow(i);
			cell = row.createCell(0);
			cell.setCellStyle(style);
			addressList = new CellRangeAddressList(i, i, 0, 0);
			validation = (XSSFDataValidation) dvHelper.createValidation(
					dvConstraint, addressList);
			sheet.addValidationData(validation);
		}
		FileOutputStream stream = new FileOutputStream(filePath);
		workbook.write(stream);
		stream.close();
		addressList = null;
		validation = null;
	}

  

    效果如下： 

 

   

 

   说明：如果你没有上面的效果或者报错，请检查poi架包的版本，或者使用版本高些的poi，我使用的是poi 3.10最新版。谢谢。

 

    全文完。