Apache POI 入力規則リストの生成

よく見かけるサンプルを基に書くと、以下のように固定リストを指定した書き方がある。

try(Workbook book = new XSSFWorkbook();
   OutputStream out = new FileOutputStream("a.xlsx")){

   XSSFSheet sheet = (XSSFSheet)book.createSheet("シート１");
   
   IntStream.rangeClosed(0, 20).boxed().forEach(i->{
      sheet.createRow(i).createCell(1).setCellValue(i);
   });
   
   // 入力規則 Helper
   XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
   
   // 入力データ
   XSSFDataValidationConstraint dvConstraint
     = (XSSFDataValidationConstraint)dvHelper.createExplicitListConstraint(new String[]{"11", "21", "31"});
   
   // 入力規則を適用させるセルの範囲   (int firstRow, int lastRow, int firstCol, int lastCol)
   CellRangeAddressList addressList = new CellRangeAddressList(0, 10, 0, 0);
   
   // 入力規則の定義
   XSSFDataValidation validation = (XSSFDataValidation)dvHelper.createValidation(dvConstraint, addressList);
   
   // ドロップダウンリストから選択できるようにする
   validation.setShowErrorBox(true);
   
   // データの入力規則を設定する
   sheet.addValidationData(validation);
   
   // 書込み
   book.write(out);
}catch(Exception e){

}

これは、これでいいんだけど、XSSFDataValidationConstraint としてシート内、別シートのセルの値になるように
したいことがあります。

createExplicitListConstraint　ではなくて、createFormulaListConstraint を使うのです。

Ｂ列４行目～１０行目のセルをプルダウン入力にするなら、、

XSSFDataValidationConstraint dvConstraint
 = (XSSFDataValidationConstraint)dvHelper.createFormulaListConstraint("$B4:$B10");

Ｂ列全部、、

XSSFDataValidationConstraint dvConstraint
 = (XSSFDataValidationConstraint)dvHelper.createFormulaListConstraint("$B:$B");

Ｂ列全部の指定なら行挿入しても入力規則プルダウンは追従するんですけど、$B4:$B10　の指定してしまうと
行挿入でいちいち修正メンテしなくてはならず不便です。
そこで、OFFSET関数を指定します。

XSSFDataValidationConstraint dvConstraint
 = (XSSFDataValidationConstraint)dvHelper.createFormulaListConstraint("OFFSET($B$3,0,0,COUNTA($B:$B)-1,1)");

こうすれば、Ｂ列３行目以降で値が存在する分、COUNTA関数で指定されて入力規則範囲になります。

OFFSET関数は、

OFFSET( startcell, toRow, toColumn, rangRow, rangeColumn )

startcellをスタートとして、
toRow行、toColumn列だけ移動したところを基準に、
rangRow行×rangeColumn列の範囲を選択する