Excel Validating Value in Range

In this section, you will learn how to validate a cell's value within a defined range using Apache POI.

Excel Validating Value in Range

Excel Validating Value in Range

In this section, you will learn how to validate a cell's value within a defined range using Apache POI.

EXAMPLE

In this below example, you will learn how to validate a cell's value within a defined range. Here, defined range is 10 to 100.

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;

public class XLValueRangeValidation {
public static void main(String args[]) throws FileNotFoundException{
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("Message on Focus");
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
DVConstraint dvConstraint = DVConstraint.createNumericConstraint(
DVConstraint.ValidationType.INTEGER,
DVConstraint.OperatorType.BETWEEN, "10", "100");
DataValidation dataValidation = new HSSFDataValidation(addressList,
dvConstraint);
sheet.addValidationData(dataValidation);
FileOutputStream fileOut = new FileOutputStream("xls/XLValueRange.xls");
try {
workbook.write(fileOut);
fileOut.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}

OUTPUT

If the value entered in A1 is not in the range (between 10 to 100)

Download Source Code