POI Export Excel Insert enumeration values drop down box (single and multiple)

Radio drop – down box

There are two ways

• Add drop-down boxes directly to cells
• Use a hidden sheet

Add a drop-down box directly to the cell:

The second method of hiding the dropdown box is used when an error occurs when the enumeration in the dropdown box exceeds 20 values

/** * Create drop-down list option (used when cell drop-down box data is less than 255 bytes) **@paramSheet is in the sheet page *@paramValues Indicates the option value * in the drop-down box@paramFirstRow start row (starting from 0) *@paramLastRow terminates rows (starting from 0) *@paramFirstCol Start column (starting from 0) *@paramLastCol terminates the column (starting from 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);
    }
Copy the code

Using a hidden sheet:

/** * Hide Sheet to create drop - down box (use cell drop - down box if the data is larger than 255 bytes) **@paramSheet Sheet * to which a drop-down box is to be added@paramFirstRow start row *@paramFirstCol is actually a column *@paramEndRow terminates row *@paramEndCol terminates column *@paramDataArray Array of drop-down boxes *@paramWbCreat WorkBook in Excel, used to create hidden sheets *@paramHidddenSheetName Name of the hidden 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 is set to hide
        wbCreat.setSheetHidden(wbCreat.getSheetIndex(hidden), true);
        // Load data, rename hidden
        DataValidationConstraint constraint = null;
        // Set the cell on which data validity is loaded. The four parameters are: start row, end row, start column, and end column
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol,
                endCol);
        / / create the 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);
    }
Copy the code

Multi-select drop-down box

Because Excel needs to be made into a multi-selection drop-down box, it needs to be realized by writing macros for Excel, but it cannot be realized by using POI, so we use to read a template with Excel macros and then write macros into the Excel

1. First we need a template with macros. 1.1 Open Excel -> Files -> Options -> Custom Zones -> Development Tools on the right click √ 1.2 Development Tools ->Visual Basic-> Double click the sheet to add macros -> Paste the following code on the right

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, If the third row of the Column contains a pair of columns, you can also use a number. The number is how many columns you want to select. Target.Column = 7 Or 9 Application.Undo oldVal = Target.Value Target.Value = newVal If oldVal = "" Then Else If newVal = If InStr(1, oldVal, newVal) <> 0 Then If InStr(1, oldVal, newVal) <> 0 Then NewVal + Len(oldVal) -1 = Len(oldVal) Then Len(oldVal) - Len(newVal) - 1) Else Target.Value = Replace(oldVal, newVal & ",", Value = oldVal _ & ",") 'is not the last option. & newVal 'can be any symbol separated End If End If End If End If End If End If End If End If exitHandler: application. EnableEvents = True End SubCopy the code

2. Think of this Excel as a template, read it every time you export Excel, and fill in the values of the dropdown options in the columns of the multi-selection dropdown set by the script

This paper reference: blog.csdn.net/ticktak/art…