apache POI3.2 java操作excel 设置数据有效性,实现excel单元格列表和提示

转自：http://blog.csdn.net/lk_blog/article/details/7231583

手工设置:

excel菜单栏上--数据--有效性--允许--序列,

excel菜单栏上--数据--有效性--输入信息.

程序现实:

首先,http://poi.apache.org/官方下载POI3.2 jar包.

[java] view plain copy

1. import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
2. import org.apache.poi.hssf.usermodel.HSSFSheet;  
3. import org.apache.poi.hssf.usermodel.HSSFDataValidation;  
4. import org.apache.poi.hssf.usermodel.DVConstraint;  
5. import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;  
6. import org.apache.poi.hssf.util.CellRangeAddressList;  
7. import java.io.IOException;  
8. import java.io.FileOutputStream;  
9.   
10. public class PoiTest1{  
11.       
12.     public static void main(String [] args) throws IOException {  
13.         HSSFWorkbook wb=new HSSFWorkbook();//excel文件对象  
14.         HSSFSheet sheetlist=wb.createSheet("sheetlist");//工作表对象  
15.         HSSFSheet sheetview=wb.createSheet("sheetview");  
16.         FileOutputStream out=new FileOutputStream("success.xls");  
17.           
18.         //得到验证对象  
19.         HSSFDataValidation data_validation_list = PoiTest1.setDataValidationList((short)1,(short)1,(short)2,(short)2);  
20.           
21.         HSSFDataValidation data_validation_view = PoiTest1.setDataValidationView((short)1,(short)1,(short)2,(short)2);  
22.         //设置提示内容,标题,内容  
23.         data_validation_view.createPromptBox("mm","www.tangxiangcun.com");  
24.         //工作表添加验证数据  
25.         sheetlist.addValidationData(data_validation_list);  
26.         sheetview.addValidationData(data_validation_view);  
27.         wb.write(out);  
28.         out.close();  
29.     }  
30.       
31.     public static HSSFDataValidation setDataValidationList(short firstRow,short firstCol,short endRow, short endCol){  
32.         //设置下拉列表的内容  
33.         String[] textlist={"列表1","列表2","列表3","列表4","列表5"};  
34.           
35.         //加载下拉列表内容  
36.         DVConstraint constraint=DVConstraint.createExplicitListConstraint(textlist);  
37.         //设置数据有效性加载在哪个单元格上。  
38.           
39.         //四个参数分别是：起始行、终止行、起始列、终止列  
40.         CellRangeAddressList regions=new CellRangeAddressList(firstRow,firstCol,endRow,endCol);  
41.         //数据有效性对象  
42.         HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);  
43.           
44.         return data_validation_list;  
45.     }  
46.     public static HSSFDataValidation setDataValidationView(short firstRow,short firstCol,short endRow, short endCol){  
47.         //构造constraint对象  
48.         DVConstraint constraint=DVConstraint.createCustomFormulaConstraint("B1");  
49.         //四个参数分别是：起始行、终止行、起始列、终止列  
50.         CellRangeAddressList regions=new CellRangeAddressList(firstRow,firstCol,endRow,endCol);  
51.         //数据有效性对象  
52.         HSSFDataValidation data_validation_view = new HSSFDataValidation(regions, constraint);  
53.           
54.         return data_validation_view;  
55.     }  
56.       
57. }