使用POI为Excel添加数据有效性验证

以你之姓@ 2022-05-26 03:19 905阅读 0赞

根据客户要求需要添加数据有效性验证。但是在查找多方资料后发现，POI无法读取到文件中的有效性验证，只能添加。无奈之下只能通过配置文件的方式去添加了。

给Excel添加数据有效性的验证，xls格式和xlsx格式不同，通过阅读官方文档，总结出了以下方法

/\*\*  
\* 添加数据有效性检查.  
\* @param sheet 要添加此检查的Sheet  
\* @param firstRow 开始行  
\* @param lastRow 结束行  
\* @param firstCol 开始列  
\* @param lastCol 结束列  
\* @param explicitListValues 有效性检查的下拉列表  
\* @throws IllegalArgumentException 如果传入的行或者列小于0(< 0)或者结束行/列比开始行/列小  
\*/  
public static void setValidationData(Sheet sheet, int firstRow, int lastRow,  
int firstCol, int lastCol,String\[\] explicitListValues) throws IllegalArgumentException\{  
if (firstRow < 0 || lastRow < 0 || firstCol < 0 || lastCol < 0 || lastRow < firstRow || lastCol < firstCol) \{  
throw new IllegalArgumentException(“Wrong Row or Column index : ” + firstRow+”:”+lastRow+”:”+firstCol+”:” +lastCol);  
\}  
if (sheet instanceof XSSFSheet) \{  
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);  
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper  
.createExplicitListConstraint(explicitListValues);  
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);  
XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);  
validation.setSuppressDropDownArrow(true);  
validation.setShowErrorBox(true);  
sheet.addValidationData(validation);  
\} else if(sheet instanceof HSSFSheet)\{  
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);  
DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(explicitListValues);  
DataValidation validation = new HSSFDataValidation(addressList, dvConstraint);  
validation.setSuppressDropDownArrow(true);  
validation.setShowErrorBox(true);  
sheet.addValidationData(validation);  
\}  
\}