java POI 實現(xiàn)excel表格下拉框選擇一個值，另一個單元格自動填充內(nèi)容

//因為是盲打過來的，我驗證的數(shù)據(jù)并不是這個，但是為了符合題目還是寫了這個，所以參數(shù)有可能在表格中位置會
//有點不對，但是實驗一下還是可以很容易看出來的。

    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(rindex++);
    //設(shè)置物品編號
    for(int i=0;i<p.size();i++) {
        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();i++){
        HSSFRow row = hidesheet.createRow(rindex++);
        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(rindex++);
    **allocationRow.createCell(4).setCellFormula("LOOKUP(A2,hideSheet!A2:A3,hideSheet!B2:B3)");**
    
    // 得到驗證對象
    DataValidation validation = getDataValidationByFormula("物品編號",2,1);
    // 工作表添加驗證數(shù)據(jù)
    sheet.addValidationData(validation);
    
    // 生成輸入文件
    File file = new File(filePath);
    FileOutputStream out = new FileOutputStream(file);

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

//以上大概就是個全過程，主要是設(shè)置LOOKUP函數(shù)，我原先一直沒搞出來是因為函數(shù)參數(shù)寫錯了，搞半天沒搞出來，今天搞出來了。
//用到的函數(shù)我也貼一下，有些用別人的，有些自己寫的。

/**

 * 返回所在列的字符
 * @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();

}
/**
 * 創(chuàng)建表頭
 * @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;i++) {
        HSSFCell userNameLableCell = row.createCell(i);
        userNameLableCell.setCellValue(title[i]);
    }
}


/**
 * 使用已定義的數(shù)據(jù)源方式設(shè)置一個數(shù)據(jù)驗證
 *
 * @param formulaString
 * @param startRow
 * @param startCol
 * @return
 */
public static DataValidation getDataValidationByFormula(String formulaString,int startRow,int startCol) {
    // 加載下拉列表內(nèi)容
    DVConstraint constraint = DVConstraint.createFormulaListConstraint(formulaString);

    // 設(shè)置數(shù)據(jù)有效性加載在哪個單元格上。
    // 四個參數(shù)分別是：起始行、終止行、起始列、終止列
    int firstRow = startRow-1;
    int lastRow = startRow-1;
    int firstCol = startCol - 1;
    int lastCol = startCol - 1;
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
    // 數(shù)據(jù)有效性對象
    DataValidation validation = new HSSFDataValidation(regions, constraint);
    return validation;
}

}