Data Validation from Spread Sheet Cell's Value

Data Validation from Spread Sheet Cell's Value

In this section, you will learn how to validate the value entered in a cell from the values already contained in the specified cell range.

EXAMPLE

In the below example, we put the validation on cell A1 from the value contained from B1 to D1. It also show you a drop down containing the value of the cells from B1 to D1. Given below the code and it's output :

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 DataValidationFrmCells {
public static void main(String args[]) throws FileNotFoundException{
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("Data Validation");
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
DVConstraint dvConstraint = DVConstraint.createFormulaListConstraint("$B$1:$D$1");
DataValidation dataValidation = new HSSFDataValidation(addressList,
dvConstraint);
dataValidation.setSuppressDropDownArrow(false);
sheet.addValidationData(dataValidation);
FileOutputStream fileOut = new FileOutputStream("xls/XLValidationFrmCells.xls");
try {
workbook.write(fileOut);
fileOut.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}

OUTPUT

If you enter value other than the values of cells from B1 to D1, it will show you error message. And also it will show the value from B1 to D1 inside drop down as follows :

Download Source Code