/ / because I typed blindly, the data I verified is not this, but I wrote this in order to match the title, so it is possible that the parameters will be
/ / a little wrong in the table, but it can be easily seen in the experiment.

    String[] p = {"p1","p2"};
    String[] price = {"10","20"};
    int rindex = 0;
    Name name;
    HSSFWorkbook workbook = new HSSFWorkbook();//excel
    HSSFSheet sheet = workbook.createSheet("Info");//
    HSSFSheet hidesheet = workbook.createSheet("hideSheet");//
    
    HSSFRow row = hidesheet.createRow(rindexPP);
    //
    for(int i=0;i<p.size();iPP) {
        HSSFCell cell = row.createCell(i);
        cell.setCellValue(p[i]);
    }
    // 
    name = workbook.createName();
    name.setNameName("");
            name.setRefersToFormula("hidesheet!$A$"+rindex+":$"+judgePos(p.size())+"$"+rindex);
    for(int i=0;i<p.size();iPP){
        HSSFRow row = hidesheet.createRow(rindexPP);
        HSSFCell cell = row.createCell(0);
        cell.setCellValue(p[i]);
        cell = row.createCell(1);
        cell.setCellValue(price[i]);
    }
    String[] title = {"",""};
    setTitle(sheet ,title,0);
    
    HSSFRow allocationRow = sheet.createRow(rindexPP);
    **allocationRow.createCell(4).setCellFormula("LOOKUP(A2,hideSheet!A2:A3,hideSheet!B2:B3)");**
    
    // 
    DataValidation validation = getDataValidationByFormula("",2,1);
    // 
    sheet.addValidationData(validation);
    
    // 
    File file = new File(filePath);
    FileOutputStream out = new FileOutputStream(file);

    workbook.write(out);
    out.close();
    

/ / the above is probably the whole process, mainly setting up the LOOKUP function. I didn't work it out before because the function parameters were written wrong, but I didn't work it out for a long time, but I did it today.
/ / I will also post some of the functions used, some of which are written by others and some written by myself.

/ *

 * 
 * @param size
 * @return
 */
public String judgePos(int size) {
    String[] args= {"A","B","C","D","E","F","G","H","I","J","K","L","M","N",
            "O","P","Q","R","S","T","U","V","W","X","Y","Z"};
    boolean tag=true;
    StringBuffer reversepos=new StringBuffer();
    while(tag) {
        int pos=size%26;
        if(pos==0) {
            pos=25;
            size--;
        }else {
            pos-=1;
        }
        int result = size/26;
        if(result==0) {
            reversepos.append(args[pos]);
            tag=false;
        }else {
            reversepos.append(args[pos]);
            size/=26;
        }
    }
    return reversepos.reverse().toString();

}
/**
 * 
 * @param sheet
 * @param title
 */
public void setTitle(HSSFSheet sheet,String[] title,int index) {
    HSSFRow row = sheet.createRow(index);
    for(int i=0;i<title.length;iPP) {
        HSSFCell userNameLableCell = row.createCell(i);
        userNameLableCell.setCellValue(title[i]);
    }
}


/**
 * 
 *
 * @param formulaString
 * @param startRow
 * @param startCol
 * @return
 */
public static DataValidation getDataValidationByFormula(String formulaString,int startRow,int startCol) {
    // 
    DVConstraint constraint = DVConstraint.createFormulaListConstraint(formulaString);

    // 
    // :
    int firstRow = startRow-1;
    int lastRow = startRow-1;
    int firstCol = startCol - 1;
    int lastCol = startCol - 1;
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
    // 
    DataValidation validation = new HSSFDataValidation(regions, constraint);
    return validation;
}

}