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)