POI导出Excel插入枚举值下拉框(单选和多选)

6,406 阅读2分钟

单选下拉框

有两种方式

  • 直接给单元格添加下拉框
  • 使用隐藏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.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
  1. 把这个Excel当成一个模板,每次导出Excel的时候都读取这个模板,再将下拉框选项的值填充进脚本设置的多选下拉框的列中就ok了

本文参考:blog.csdn.net/ticktak/art…