Apache POI- HSSFDataValidation Formula Validation

[Mamatha Kv]

Hello,


I have been looking HSSFDataValidation and was able to run the sample below.

My requirement is I need to apply this formula for Entire column to achieve the need like

When user enters data in col2 (B) of greater than the number specified in col1 (A) and then show the ErrorBox.

I am able to get the formula working through code only for first cell as per below code... I want it to dynamically apply for the entire column.

Please suggest...

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.CellRangeAddressList;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class TestThis{ 
public static void main1(String[] args) {
        // New Workbook. 
        File outputFile = new File("C:/mamatha.xls");
        try {
           
            FileOutputStream fos = new FileOutputStream(outputFile);
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("Validation");
            Row row = sheet.createRow(0);
            Cell cell = row.createCell(0);
            cell.setCellValue(5);
            cell = row.createCell(1);
            cell.setCellValue("good");
            row = sheet.createRow(1);
            cell = row.createCell(0);
            cell.setCellValue(7);
            cell = row.createCell(1);
            cell.setCellValue("now");

            //String formula = "IF(LEN($B$1) > $A$1, FALSE, $B$1)";
            String formula = "IF(LEN($B$1:$B10) > $A$1:$A10, FALSE, $B$1:$B10)";
            CellRangeAddressList addressList = new CellRangeAddressList();
            addressList.addCellRangeAddress(0, 1, 3, 1);
            DVConstraint constraing = DVConstraint.createFormulaListConstraint(formula);

            HSSFDataValidation dataValidation = new HSSFDataValidation(addressList, constraing);
            dataValidation.setEmptyCellAllowed(true);
            dataValidation.setShowPromptBox(true);
            dataValidation.setSuppressDropDownArrow(false);
            dataValidation.createErrorBox("Invalid input !!", " Length of Col B > colA ");
            sheet.addValidationData(dataValidation);
            workbook.write(fos);
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}

Thanks
Mamatha

[Ulf Dittmer]

A couple hints on how to make it easier for others to help you:

Please UseCodeTags when posting code of any length (I have added those now for you). It's unnecessarily hard to read the code otherwise, making it less likely that people will do so.

Also, you can make it much easier on others by posting an SSCCE - that means including all necessary imports. If people have to figure those out by themselves, it's much less likely they will bother to do so. You can edit your post to add the imports by using the button.