单选下拉框
有两种方式
- 直接给单元格添加下拉框
- 使用隐藏sheet的方式实现
直接给单元格添加下拉框：
使用这种方式时当下拉框里的枚举值超过20个时会报错，这种时候就需要使用第二种隐藏下拉框的方式
/**
* 创建下拉列表选项(单元格下拉框数据小于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);
}
使用隐藏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);
// 创建 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);
}
多选下拉框
由于Excel要做成多选下拉框，是需要用给Excel写宏的方式实现，但是使用POI没办法给Excel写宏，所以我们使用读取一个有Excel的宏的模板，然后往这个Excel里面写宏的方式实现
- 首先我们需要一个有宏的模板
1.1打开excel->文件->选项->自定义功能区->右侧的开发工具点击√
1.2开发工具->Visual Basic->双击需要添加宏的sheet->在右侧粘贴下面的代码
Sub Worksheet_Change(ByVal Target As Range)
'让数据有效性选择 可以多选,不可重复
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
If InStr(Cells(3, Target.Column), "多选") Then '本列的第三行的单元格是否包含对选 也可以直接用数字，数字是你想要多选的列是多少，多个用or连接。Target.Column = 7 Or 9
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If oldVal = "" Then
Else
If newVal = "" Then
Else '去除重复的字段
If InStr(1, oldVal, newVal) <> 0 Then
If InStr(1, oldVal, newVal) + Len(newVal) - 1 = Len(oldVal) Then '最后一个选项重复
Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 1)
Else
Target.Value = Replace(oldVal, newVal & ",", "") '不是最后一个选项重复的时候处理逗号
End If
Else '不是重复选项就视同增加选项
Target.Value = oldVal _
& "," & newVal '可以是任意符号隔开
End If
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
- 把这个Excel当成一个模板，每次导出Excel的时候都读取这个模板，再将下拉框选项的值填充进脚本设置的多选下拉框的列中就ok了
