Excel Cell Drop Down List
In this section, you will learn how to validate data entered in a cell and provide a drop down list of possible values to the user using Apache POI API.
Using Apache POI library, you can restrict value entered in a excel sheet's cell and also can provide drop list of possible value the cell can have. User can enter only specified value. On entering the wrong value, it will show error message like "The value you entered in not valid ".
EXAMPLE
In the given below example, the value that can be entered into cell A1 is restricted to the following : 10,20,30. The drop down is also provided with cell A1 having possible values i.e. 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 XLCellDropDown {
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(false);
sheet.addValidationData(dataValidation);
FileOutputStream fileOut = new FileOutputStream(
"xls/XLCellDropDown.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 :
You can select the value from the drop down as follows :
