Custom Data Validation to thousands of cells using apache poi

Question

I am creating an excel sheet in Java which has more than 10k records. For a particular column, I want to set a validation to all cells in that column that it can either be of "NEW_RULE-x" type where x >= 1 (NEW_RULE-1, NEW_RULE-2,..., etc.) or "x" type (1,2,...,etc.)

So manually in excel, I figured out applying below Custom Validation to cell (let's say D3) : "=OR(AND(LEFT(D3,9)="NEW_RULE-",MID(D3,10,1)>="1"),OR(AND(D3>=1,D3<=999999)))". If I want to apply it to D4, then the validation formula will have D4 instead of D3. Is their a generic way to do it for entire D column, so that I don't have to apply for each cell in Java code while iterating records? Below is the code to generate a quick excel sheet with data validation at D3 cell.

        String filePath = "D\\mysheet.xlsx";
        File file = new File(filePath);
        XSSFWorkbook workbook = new XSSFWorkbook();
        FileOutputStream fos;
        try {
            XSSFSheet sheet = workbook.createSheet();

            for (int i = 0; i <= 5; i++) {

                XSSFRow row = sheet.getRow(i);

                if(row == null)
                    row = sheet.createRow(i);

                XSSFCell cell=row.getCell(0);

                if(cell == null)
                    cell = row.createCell(0, XSSFCell.CELL_TYPE_STRING);

                cell.setCellValue("Keyword" +i);

                cell=row.getCell(1);

                if(cell == null)
                    cell = row.createCell(1, XSSFCell.CELL_TYPE_STRING);
                cell.setCellValue("PASS" +i);

                System.out.println("1");
            }
            XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
            XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createCustomConstraint(
                    "=OR(AND(LEFT(D3,9)=\"NEW_RULE-\",MID(D3,10,1)>=\"1\"),OR(AND(D3>=1,D3<=999999)))");

            //=OR(AND(LEFT(D3,9)="NEW_RULE-",MID(D3,10,1)>="1"),OR(AND(D3>=1,D3<=999999)))

            CellRangeAddressList addressList = new CellRangeAddressList(2,2,3,3);
            XSSFDataValidation dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
            sheet.addValidationData(dataValidation);
            fos = new FileOutputStream(filePath);
            workbook.write(fos);
            fos.close();
        }finally{
        }

    }```

Answer 1

At first: Do not put the equals sign = in front on formula strings when set via apache poi. The equals sign is not stored in Excel's storage. It only is visible in GUI.

And when ever formulas are used , even in conditional formatting and data validation, cell references can be relativ or absolut. For example A1 is a relativ reference while $A$1 is a absolut reference. So as you are using only relative references in your formula, the references will be adjusted to new cells in sheet. For example LEFT(D1,9)="NEW_RULE-" in cell D1 will be LEFT(D2,9)="NEW_RULE-" in cell D2 and LEFT(D3,9)="NEW_RULE-" in cell D3 and so on.

So the formula string "OR(AND(LEFT($D1,9)=\"NEW_RULE-\",MID($D1,10,1)>=\"1\"),OR(AND($D1>=1,$D1<=999999)))" can be adopted for the cells D1:D1000 and will always be correct for the current relative row in absolute column D.

Complete example:

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddressList;

class CreateExcelDataValidation {

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

  //Workbook workbook = new HSSFWorkbook(); String filePath = "./mysheet.xls";
  Workbook workbook = new XSSFWorkbook(); String filePath = "./mysheet.xlsx";

  Sheet sheet = workbook.createSheet();
 
  for (int i = 0; i <= 5; i++) {
   Row row = sheet.getRow(i);
   if (row == null) row = sheet.createRow(i);
   Cell cell = row.getCell(0);
   if (cell == null) cell = row.createCell(0);
   cell.setCellValue("Keyword" +i);
   cell = row.getCell(1);
   if (cell == null) cell = row.createCell(1);
   cell.setCellValue("PASS" +i);
  } 
  
  DataValidationHelper dvHelper = sheet.getDataValidationHelper();
  DataValidationConstraint dvConstraint = dvHelper.createCustomConstraint("OR(AND(LEFT($D1,9)=\"NEW_RULE-\",MID($D1,10,1)>=\"1\"),OR(AND($D1>=1,$D1<=999999)))");
  CellRangeAddressList addressList = new CellRangeAddressList(0, 1000, 3, 3);
  DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
  if (workbook instanceof XSSFWorkbook) validation.setShowErrorBox(true);
  sheet.addValidationData(validation);

  FileOutputStream out = new FileOutputStream(filePath);
  workbook.write(out);
  workbook.close();
  out.close();

 }
}