Excel Data validation

Excel Data validation

In this section, you will learn how to validate data in a excel's cells using Apache POI.

Using Apache POI library, you can restrict value entered in a excel sheet's cell. User can enter only specified value. On entering the wrong value, it will show error message like "The value you entered in not valid ".

The code for the above is given below :

EXAMPLE

In the given below example, the value that can be entered into cell A1 is restricted to the following : 10,20,30. If the value entered in A1 is other than this, it will show you error message.

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 XLValidation {
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
.createExplicitListConstraint(new String[] { "10", "20", "30" });
DataValidation dataValidation = new HSSFDataValidation(addressList,
dvConstraint);
dataValidation.setSuppressDropDownArrow(true);
sheet.addValidationData(dataValidation);
FileOutputStream fileOut = new FileOutputStream("xls/XLValidation.xls");
try {
workbook.write(fileOut);
fileOut.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}

OUTPUT

If the value entered in the cell A1 is other than value specified i.e. 10,20,30, it will show you the following error message :

Download Source Code