java - 是否可以纯粹使用 poi excel 创建依赖下拉列表

Question

第一个单元格是 1,2,3,4 的下拉列表，预期的第二个单元格是大于第一个单元格的值且小于的数字的下拉列表
或等于 4。

我从 Dependent Drop Down Lists in DataValidation 得到了一些直觉如下:

  CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 1, 1);
  DataValidationConstraint dvConstraint = 
  dvHelper.createFormulaListConstraint("INDIRECT(UPPER($A$1))");
  DataValidation validation =dvHelper.createValidation(dvConstraint, addressList);
  sheet.addValidationData(validation);

这个技巧的关键部分是在 dvHelper.createFormulaListConstraint("INDIRECT(UPPER($A$1))") 处显式给出列表公式

如果我将列表公式硬编码如下，就可以了:dvHelper.createFormulaListConstraint("$A$1:$A$4")

但在动态创建列表公式时不起作用连接(地址($B$1,1)，“:$A$4”)

也不能直接连接CONCATENATE("$A$1", ":$A$4")

Answer 1

我建议使用一个隐藏表来存储列表约束的列表值。然后命名范围以用作数据验证中的列表约束。

其中一个命名范围可以包含 INDEX 公式，该公式根据使用名称的单元格左侧单元格中已选择的值来获取列表值的一部分。

示例:

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.apache.poi.ss.util.*;

class CreateExcelDependentDataValidationLists {

 public static void main(String[] args) throws Exception{

   Workbook workbook = new XSSFWorkbook();  

   //hidden sheet for list values
   Sheet sheet = workbook.createSheet("ListSheet");
   sheet.createRow(0).createCell(0).setCellValue("SourceList");
   int r = 1;
   for (int i = 1; i < 5; i++) {
    sheet.createRow(r++).createCell(0).setCellValue(i);
   }
   //unselect that sheet because we will hide it later
   sheet.setSelected(false);

   //visible data sheet
   sheet = workbook.createSheet("Sheet1");

   //names for the list constraints
   Name namedCell = workbook.createName();
   namedCell.setNameName("List1To4");
   String reference = "ListSheet!$A$2:$A$5"; //List 1 to 4
   namedCell.setRefersToFormula(reference);

   namedCell = workbook.createName();
   namedCell.setNameName("ListLeftCellTo4");
   reference = "INDEX(List1To4,INDEX(Sheet1!$1:$1000,ROW(),COLUMN()-1)):INDEX(List1To4,4)"; //List n to 4
                //List1To4Position=ThisRow.ThisColumn-1 : List1To4LastPosition
   namedCell.setRefersToFormula(reference);

   sheet.createRow(0).createCell(0).setCellValue("1 to 4");
   sheet.getRow(0).createCell(1).setCellValue("n to 4");

   sheet.setActiveCell(new CellAddress("A2"));

   sheet.autoSizeColumn(0);
   sheet.autoSizeColumn(1);

   //data validations
   DataValidationHelper dvHelper = sheet.getDataValidationHelper();
   DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint("List1To4");
   CellRangeAddressList addressList = new CellRangeAddressList(1, 1, 0, 0);            
   DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);

   sheet.addValidationData(validation);

   dvConstraint = dvHelper.createFormulaListConstraint("ListLeftCellTo4");
   addressList = new CellRangeAddressList(1, 1, 1, 1);            
   validation = dvHelper.createValidation(dvConstraint, addressList);

   sheet.addValidationData(validation);

   //hide the ListSheet
   workbook.setSheetHidden(0, true);
   //set Sheet1 active
   workbook.setActiveSheet(1);

   FileOutputStream out = new FileOutputStream("CreateExcelDependentDataValidationLists.xlsx");
   workbook.write(out);
   workbook.close();
   out.close();

 }
}