Home Tutorials Poi Excel Cell Drop Down List
Questions:Ask|Latest


 
 

Share on Google+Share on Google+

Excel Cell Drop Down List

Advertisement
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.

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 :

Download Source Code

Advertisements

Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: August 19, 2011

Ask Questions?    Discuss: Excel Cell Drop Down List  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
Sriram
May 8, 2012
How to prevent values from being copy pasted

Hi, This works if user tries to enter invalid values and he would be prompted saying value is invalid. How would you restrict him if he copies the values from some cell and pastes them in the cell? Is there a key press event which can trigger this?
kiran
July 17, 2012
need this same for xlsx file

hi thanks for the tutorial , ineed help to create same data validation for xlsx files i am not able to find one ..Please help me in this thanks
DMCA.com