使用 Apache POI 创建搜索式下拉列表（动态检索）的 Excel 文件（不使用Filter函数）

在使用 Apache POI 操作 Excel 文件时，有时需要在一个工作表中创建一个可搜索的下拉列表（即输入部分字符可以动态筛选出只包含该字符的选项），其数据来源于另一个隐藏的工作表。本文将介绍如何实现这一功能，并解决过程中遇到的一些常见问题。
此外：仅针对excel实现。WPS2019版本以上自带动态检索，可惜规定只让用excel（hhh）

需求背景

我们需要在 Excel 文件的一个工作表中创建一个搜索式下拉列表，该列表的内容根据用户输入进行筛选，并且来源于另一个隐藏工作表。

实现步骤

1. 创建隐藏工作表并填充数据：在隐藏工作表中填充原始数据。
2. 设置筛选公式：使用公式在隐藏工作表中筛选数据。
3. 设置数据验证：在主工作表中创建数据验证，引用筛选后的数据范围。

代码实现

以下是完整的代码实现：

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;

public void createDropDownListWithHiddenSheet(Sheet sheet, int firstRow,
                                              int firstCol, int endRow,
                                              int endCol, String[] dataArray,
                                              Workbook wbCreat,
                                              String hiddenSheetName, String mainSheetName) {

    // Create hidden sheet and add data
    XSSFSheet hidden = (XSSFSheet) wbCreat.createSheet(hiddenSheetName);
    Cell cell = null;

    // Sort the dataArray in ascending order
    Arrays.sort(dataArray);

    // Create the header
    Row headerRow = hidden.createRow(0);
    cell = headerRow.createCell(0);
    cell.setCellValue("Factory Name");
    cell = headerRow.createCell(1);
    cell.setCellValue("Match");
    cell = headerRow.createCell(2);
    cell.setCellValue("Filtered Names");

    // Add data starting from row 1 and fill match column with formula
    for (int i = 0, length = dataArray.length; i < length; i++) {
        String name = dataArray[i];
        Row row = hidden.createRow(i + 1); // Start from row 1
        cell = row.createCell(0);
        cell.setCellValue(name);
        cell = row.createCell(1);
        cell.setCellFormula("IF(ISNUMBER(SEARCH(" + mainSheetName + "!$G$3,'6'!A" + (i + 2) + ")),1,0)");
        cell = row.createCell(2);
        cell.setCellFormula("IF(AND(B" + (i + 2) + "=1,LEN('6'!A" + (i + 2) + ")>0),'6'!A" + (i + 2) + ",\"\")");
    }

    // Define the array formula for the entire range
    int formulaLength = dataArray.length;
    String arrayFormula = "IFERROR(INDEX('6'!A:A, SMALL(IF(ISNUMBER(SEARCH(CELL(\"contents\"), '6'!$A$2:$A$888)), ROW('6'!$A$2:$A$888), \"\"), ROW())), \"\")";

    // Set array formula to the entire range in D column
    CellRangeAddress arrayFormulaRange = new CellRangeAddress(1, formulaLength, 3, 3);
    hidden.setArrayFormula(arrayFormula, arrayFormulaRange);

    // Hide the hidden sheet
    wbCreat.setSheetHidden(wbCreat.getSheetIndex(hidden), true);

    // Determine the last non-empty row in the final list column
    int lastNonEmptyRow = formulaLength;
    for (; lastNonEmptyRow > 0; lastNonEmptyRow--) {
        Row rowCheck = hidden.getRow(lastNonEmptyRow);
        if (rowCheck != null) {
            Cell cellToCheck = rowCheck.getCell(3);
            if (cellToCheck != null && cellToCheck.getCellType() != CellType.BLANK && !cellToCheck.getStringCellValue().isEmpty()) {
                break;
            }
        }
    }

    // Create the data validation formula using the final list range directly
    String dvFormula = "OFFSET(" + hiddenSheetName + "!$D$2, 0, 0, COUNTA(" + hiddenSheetName + "!$D$2:$D$" + (formulaLength + 1) + ") - COUNTIF(" + hiddenSheetName + "!$D$2:$D$" + (formulaLength + 1) + ", \"\"), 1)";

    // Create data validation constraint
    DataValidationConstraint constraint = null;
    CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
    DataValidation validation = null;

    if (sheet instanceof XSSFSheet || sheet instanceof SXSSFSheet) {
        DataValidationHelper dvHelper = sheet.getDataValidationHelper();
        constraint = dvHelper.createFormulaListConstraint(dvFormula);
        validation = dvHelper.createValidation(constraint, addressList);
    } else {
        constraint = DVConstraint.createFormulaListConstraint(dvFormula);
        validation = new HSSFDataValidation(addressList, constraint);
    }

    if (validation instanceof HSSFDataValidation) {
        validation.setSuppressDropDownArrow(false);
    } else {
        validation.setSuppressDropDownArrow(true);
        validation.setShowErrorBox(true);
    }

    validation.setEmptyCellAllowed(true); // Allow empty cells

    // Add validation to the sheet
    sheet.addValidationData(validation);
}

问题及解决方案

1. 动态行号问题：

   • 问题：在公式中需要动态获取当前行号。
   • 解决方案：使用 ROW() 函数来动态获取当前行号。

2. 空值过滤问题：

   • 问题：下拉列表中包含空值。
   • 解决方案：在数据验证公式中使用 COUNTA 和 COUNTIF 过滤空值。

   String dvFormula = "OFFSET(" + hiddenSheetName + "!$D$2, 0, 0, COUNTA(" + hiddenSheetName + "!$D$2:$D$" + (formulaLength + 1) + ") - COUNTIF(" + hiddenSheetName + "!$D$2:$D$" + (formulaLength + 1) + ", \"\"), 1)";
   

3. 公式生成问题：

   • 问题：在数组公式中避免生成 @ 符号。
   • 解决方案：使用 setArrayFormula 方法应用数组公式到整个范围。

   CellRangeAddress arrayFormulaRange = new CellRangeAddress(1, formulaLength, 3, 3);
   hidden.setArrayFormula(arrayFormula, arrayFormulaRange);
   

4. 为什么不用 FILTER 函数：

   • 问题：使用 FILTER 函数在较低版本的 Excel 中不兼容。
   • 解决方案：使用兼容性更好的 IF, SMALL, ROW, INDEX 和 IFERROR 等函数来实现相同的功能。