poi 2003/2007 下拉框生成

最新推荐文章于 2022-05-26 13:43:12 发布

taoshi006

最新推荐文章于 2022-05-26 13:43:12 发布

阅读量210

点赞数

分类专栏： J2EE 文章标签： POI 生成兼容2003/2007 下拉框的excel

本文链接：https://blog.csdn.net/taoshi006/article/details/84619977

版权

J2EE 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

POI 提供了很好的Excel 操作API，我这是用POI 3.8 主要核心jar包

poi-3.8-20120326.jar
poi-ooxml-3.8-20120326.jar



/**
 * 生成下载模板，附带下拉框
 * @param wb ：工作簿
 * @param datas：下拉框数据源
 * @param selectCols：要生成下拉框的列
 * @param sheetName：放置下拉框的sheet名称
 * @param dataCol：放置下拉框数据的col字母
 * @param dataColNum：放置下拉框数据的col 数字 0开始
 * @param maxSelCols：生成下拉框的行数
 * @return
 */
public static Workbook createSelectSheet(Workbook wb,
                                    String[] datas,
				int selectCols,
                   String sheetName,String dataCol,
                   int dataColNum,int maxSelCols){
	DataValidationHelper dvHelper = null;
	DataValidationConstraint dvConstraint = null;
	DataValidation validation = null;
	CellRangeAddressList addressList = null;
	Sheet hidden = wb.createSheet(sheetName);//创建隐藏的sheet 用于放置下拉数据
	wb.setSheetHidden(wb.getNumberOfSheets() -1, true);//专门放置数据源的sheet
	Name namedCell = wb.createName();
	namedCell.setNameName(sheetName);
//	namedCell.setRefersToFormula(sheetName+"!$A$1:$A$10" + datas.length);
	namedCell.setRefersToFormula(sheetName+"!$"+dataCol+"$1:$"+dataCol+"$"+datas.length);
	Sheet sheet =  wb.getSheetAt(0);
	int sheetRows = maxSelCols;//当前sheet页的行数
	Row row = null;
	Cell cell = null;
	if (wb instanceof HSSFWorkbook) {//xls
	for (int i = 0, length = datas.length; i < length; i++) { //将数据源的数据放置到选定sheet的一列
	row = (HSSFRow)hidden.createRow(i);
	cell = (HSSFCell)row.createCell(dataColNum);
	cell.setCellValue(datas[i]);
			}
	DVConstraint constraint = DVConstraint.createFormulaListConstraint(sheetName);
	addressList = new CellRangeAddressList(1, sheetRows, selectCols, selectCols);
	validation = new HSSFDataValidation(addressList, constraint);
	sheet.addValidationData(validation);
	validation.setShowErrorBox(false);// 取消弹出错误框
	} else {//XLSX
	for (int i = 0, length = datas.length; i < length; i++) {
	row =  (XSSFRow)hidden.createRow(i);
	cell = (XSSFCell)row.createCell(dataColNum);
	cell.setCellValue(datas[i]);
	}
	dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
	dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(sheetName);
	addressList = new CellRangeAddressList(1, sheetRows, selectCols, selectCols);
	validation = (XSSFDataValidation) dvHelper.createValidation(
						dvConstraint, addressList);
	sheet.addValidationData(validation);//将下拉框放置到sheet
		}
		return wb;
	}

调用此方法
wb = ExcelUtil.createSelectSheet(wb, list, 5,"hd","A",0,65534);//创建机构名称的下拉框

list 是数据源 string 数组

5 是放置到模板的第6列

hd 是隐藏放置下拉框数据的sheet

A 是sheet中放置下拉数据的列

0表示第一列

65534 在模板中生成下拉框65534行