EXCEL POI单元格下拉的两种实现方式

最新推荐文章于 2024-05-23 11:23:35 发布

q4930153

最新推荐文章于 2024-05-23 11:23:35 发布

阅读量3.6k

点赞数 1

分类专栏： JAVA 文章标签： java

本文链接：https://blog.csdn.net/q4930153/article/details/126723992

版权

JAVA 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

如果要对单元格进行下拉校验，POI中提供了两种方法：

1. 必须从下拉中选择

				DataValidationHelper helper = sheet.getDataValidationHelper();
				//设置下拉框数据
				String optionsArray [] = new String[cellValidation.getOptions().size()];
				cellValidation.getOptions().toArray(optionsArray) ;
				DataValidationConstraint constraint = helper.createExplicitListConstraint( optionsArray);
				//设置生效的起始行、终止行、起始列、终止列
				CellRangeAddressList cellAddressList = new CellRangeAddressList(cellValidation.getStartRow(),
						cellValidation.getEndRow(),cellValidation.getStartCol(),cellValidation.getEndCol());
				DataValidation validation = helper.createValidation(constraint,cellAddressList);
				//适配xls和xlsx
				if(validation instanceof HSSFDataValidation){
					validation.setSuppressDropDownArrow(false);
				}else{
					validation.setSuppressDropDownArrow(true);
					validation.setShowErrorBox(true);
				}
				sheet.addValidationData(validation) ;

2. 选择下拉或者手动输入

                String hidddenSheetName =  "hidden" + sheetIndex ;
				Sheet hidden = workbook.createSheet(hidddenSheetName );
				Cell cell = null;
				List<String> options = cellValidation.getOptions() ;
				int i = 0 ;
				for ( ; i < options.size() ; i ++ ) {
					Row row = hidden.createRow(i);
					cell = row.createCell(0);
					cell.setCellValue(options.get(i));
				}
				Row row = hidden.createRow(i);
				row.createCell(i);

				Name namedCell = workbook.createName();
				namedCell.setNameName(hidddenSheetName);
				namedCell.setRefersToFormula(hidddenSheetName + "!$A$1:$A$" + (i+1));
				//sheet设置为隐藏
				workbook.setSheetHidden(workbook.getSheetIndex(hidden), true);

				//加载数据,将名称为hidden的
				DataValidationConstraint constraint = null;
				// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
				CellRangeAddressList addressList = new CellRangeAddressList(
						cellValidation.getStartRow(), cellValidation.getEndRow(),
						cellValidation.getStartCol(), cellValidation.getEndCol());
				// 创建 DataValidation
				DataValidation validation = null;
				if (sheet instanceof XSSFSheet || sheet instanceof SXSSFSheet) {
					DataValidationHelper dvHelper = sheet.getDataValidationHelper();
					constraint = dvHelper.createFormulaListConstraint(hidddenSheetName);
					validation = dvHelper.createValidation(constraint, addressList);
				} else {
					constraint = DVConstraint.createFormulaListConstraint(hidddenSheetName);
					validation = new HSSFDataValidation(addressList, constraint);
				}
				if (validation instanceof HSSFDataValidation ) {
					validation .setSuppressDropDownArrow(false);
				} else {
					validation .setSuppressDropDownArrow(true);
					validation .setShowErrorBox(true);
				}
				sheet.addValidationData(validation);