java poi 实现 excel 联想式下拉框

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

本文主要用java poi 实现excel 的联想式下拉列表，前提条件代码中textlist 是需要事先排好序
话不多说，直接上代码

代码

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

public class TestMain {
    public static void main(String[] args) throws IOException, InvalidFormatException {
        Workbook wb = WorkbookFactory.create(new FileInputStream("D:\\test.xlsx"));
        Sheet sheet = wb.getSheetAt(0);
        TestMain.setXSSFValidationWithHiddenFuzzyQuery(wb,sheet, new String[]{"1", "2", "3","33","4","44"}, "提示", 1, 10, 0, 0);
        wb.write(new FileOutputStream("D:\\testq.xlsx"));
    }

    /**
     * 设置某些列的值只能输入预制的数据,显示下拉框（兼容超出一定数量的下拉框）.
     *
     * @param sheet 要设置的sheet.
     * @param textlist 下拉框显示的内容
     * @param promptContent 提示内容
     * @param firstRow 开始行
     * @param endRow 结束行
     * @param firstCol 开始列
     * @param endCol 结束列
     */
    public static void setXSSFValidationWithHiddenFuzzyQuery(Workbook wb,Sheet sheet, String[] textlist, String promptContent, int firstRow, int endRow, int firstCol, int endCol)
    {

        String hideSheetName = "combo_" + firstCol + "_" + endCol + sheet.getSheetName();
        Sheet hideSheet = wb.createSheet(hideSheetName); // 用于存储 下拉菜单数据
        for (int i = 0; i < textlist.length; i++)
        {
            hideSheet.createRow(i).createCell(0).setCellValue(textlist[i]);
        }
        // 创建名称，可被其他单元格引用
        Name name = wb.createName();
        String namesName = hideSheetName + "_data";
        name.setNameName(namesName);
        name.setRefersToFormula(hideSheetName + "!$A$1:$A$" + textlist.length);
        for(int col=firstCol;col<=endCol;col++){
            String colName = getColumnName(col+1);
            for(int i=firstRow;i<=endRow;i++){
                String currentCell = colName+(i+1);
                DataValidationHelper helper = sheet.getDataValidationHelper();
                String function = "=OFFSET("+namesName+",MATCH(\"*\"&"+currentCell+"&\"*\","+namesName+",0)-1,0,COUNTIF("+namesName+",\"*\"&"+currentCell+"&\"*\"),1)";
                // 加载下拉列表内容
                DataValidationConstraint constraint = helper.createFormulaListConstraint(function);
                // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
                CellRangeAddressList regions = new CellRangeAddressList(i, i, col, col);
                // 数据有效性对象
                DataValidation dataValidation = helper.createValidation(constraint, regions);

                dataValidation.setShowErrorBox(false);
                if (promptContent!=null&&!promptContent.equals(""))
                {
                    // 如果设置了提示信息则鼠标放上去提示
                    dataValidation.createPromptBox("", promptContent);
                    dataValidation.setShowPromptBox(true);
                }
                // 处理Excel兼容性问题
                if (dataValidation instanceof XSSFDataValidation)
                {
                    dataValidation.setSuppressDropDownArrow(true);

                }
                else
                {
                    dataValidation.setSuppressDropDownArrow(false);
                }
                sheet.addValidationData(dataValidation);

            }
        }


        // 设置hiddenSheet隐藏
//                wb.setSheetHidden(wb.getSheetIndex(hideSheet), true);

    }

    /**
     * 根据列值获取字母
     * @param columnNum
     * @return
     */
    public static String getColumnName(int columnNum) {

        int first;

        int last;

        String result = "";

        if (columnNum > 256)

            columnNum = 256;

        first = columnNum / 27;

        last = columnNum - (first * 26);

        if (first > 0)

            result = String.valueOf((char) (first + 64));

        if (last > 0)

            result = result + String.valueOf((char) (last + 64));

        return result;

    }
}

存在问题

本实现方法依然存在问题，若数组中既存在查询字段开头的内容又存在在中间的内容，就会丢失其中一部分内容，例如 数组为【“中文”，“中中文”，“学习”，“学中文”】，当搜索“中”时，“学中文”会被丢失；欢迎大家指导解决此问题，谢谢