Java Excel POI 添加下拉框

给自己打个广告，最近弄了个Excel的工具，支持下拉框，级联下拉框，隐藏Sheet，多线程大数据量导出，生产者消费者模式读取，代码大部分都有注释，有兴趣的朋友可以看看，觉得不错可以帮我弄个star什么的😄

方式

直接添加下拉框

/*** 创建下拉列表选项(单元格下拉框数据小于255字节时使用)** @param sheet 所在Sheet页面* @param values 下拉框的选项值* @param firstRow 起始行（从0开始）* @param lastRow 终止行（从0开始）* @param firstCol 起始列（从0开始）* @param lastCol 终止列（从0开始）*/public void createDropDownList(Sheet sheet, String[] values, int firstRow, int lastRow, int firstCol, int lastCol) {DataValidationHelper helper = sheet.getDataValidationHelper();CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);DataValidationConstraint constraint = helper.createExplicitListConstraint(values);DataValidation dataValidation = helper.createValidation(constraint, addressList);if (dataValidation instanceof HSSFDataValidation ) {dataValidation.setSuppressDropDownArrow(false);} else {dataValidation.setSuppressDropDownArrow(true);dataValidation.setShowErrorBox(true);}sheet.addValidationData(dataValidation);}

需要注意的是，这种方式下拉框数据的大小不能超过255个字节，否则会报异常。需要使用隐藏Sheet的方式解决

隐藏Sheet的方式

/*** 隐藏Sheet方式创建下拉框(单元格下拉框数据大于255字节时使用)** @param sheet 需要添加下拉框的Sheet* @param firstRow 起始行* @param firstCol 其实列* @param endRow 终止行* @param endCol 终止列* @param dataArray 下拉框数组* @param wbCreat 所在excel的WorkBook，用于创建隐藏Sheet* @param hidddenSheetName 隐藏Sheet的名称* @return*/public void createDropDownListWithHiddenSheet(Sheet sheet, int firstRow,int firstCol, int endRow,int endCol, String[] dataArray,Workbook wbCreat,String hidddenSheetName) {Sheet hidden = wbCreat.createSheet(hidddenSheetName);Cell cell = null;for (int i = 0, length = dataArray.length; i < length; i++) {String name = dataArray[i];Row row = hidden.createRow(i);cell = row.createCell(0);cell.setCellValue(name);}Name namedCell = wbCreat.createName();namedCell.setNameName(hidddenSheetName);namedCell.setRefersToFormula(hidddenSheetName + "!$A$1:$A$" + dataArray.length);//sheet设置为隐藏wbCreat.setSheetHidden(wbCreat.getSheetIndex(hidden), true);//加载数据,将名称为hidden的DataValidationConstraint constraint = null;// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol,endCol);// 创建 DataValidationDataValidation 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);}

在解析Excel数据的时候要注意忽略隐藏Sheet的数据