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.

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

Related Tags for Data Validation from Spread Sheet Cell's Value:

More Tutorials from this section

• Create Simple Excel(.xls) document using Apache POI
• Create Excel(.xlsx) document using Apache POI
• Read Simple Excel(.xls) document using Apache POI
• Read Excel(.xlsx) document using Apache POI
• Format Cell of Simple Excel Sheet(.xls)
• Checking Date Value of Excel Cells
• Drawing a Shape in Excel Sheet
• Excel Freeze Pane Feature
• Insert Image into Excel Sheet
• Set Zoom Magnification of Excel Sheet
• Excel Sheet Footer
• Excel Sheet Header
• Excel Splits Pane Feature
• Identify Excel's cell data type
• Apache POI API for Microsoft Doc's Manipulation
• Excel Cell Comment
• Excel Cell Drop Down List
• Excel Data validation
• Excel Prompt User on Focus
• Creating Shapes using Shape Groups