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

benzhou

　　手工设置:
　　excel菜单栏上--数据--有效性--允许--序列,
　　excel菜单栏上--数据--有效性--输入信息.
　　程序现实:
　　首先,http://poi.apache.org/官方下载POI3.2 jar包.
import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFDataValidation;import org.apache.poi.hssf.usermodel.DVConstraint;import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;import org.apache.poi.hssf.util.CellRangeAddressList;import java.io.IOException;import java.io.FileOutputStream;public class PoiTest1{public static void main(String [] args) throws IOException {HSSFWorkbook wb=new HSSFWorkbook();//excel文件对象HSSFSheet sheetlist=wb.createSheet("sheetlist");//工作表对象HSSFSheet sheetview=wb.createSheet("sheetview");FileOutputStream out=new FileOutputStream("success.xls");//得到验证对象HSSFDataValidation data_validation_list = PoiTest1.setDataValidationList((short)1,(short)1,(short)2,(short)2);HSSFDataValidation data_validation_view = PoiTest1.setDataValidationView((short)1,(short)1,(short)2,(short)2);//设置提示内容,标题,内容data_validation_view.createPromptBox("mm","www.tangxiangcun.com");//工作表添加验证数据sheetlist.addValidationData(data_validation_list);sheetview.addValidationData(data_validation_view);wb.write(out);out.close();}public static HSSFDataValidation setDataValidationList(short firstRow,short firstCol,short endRow, short endCol){//设置下拉列表的内容String[] textlist={"列表1","列表2","列表3","列表4","列表5"};//加载下拉列表内容DVConstraint constraint=DVConstraint.createExplicitListConstraint(textlist);//设置数据有效性加载在哪个单元格上。//四个参数分别是：起始行、终止行、起始列、终止列CellRangeAddressList regions=new CellRangeAddressList(firstRow,firstCol,endRow,endCol);//数据有效性对象HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);return data_validation_list;}public static HSSFDataValidation setDataValidationView(short firstRow,short firstCol,short endRow, short endCol){//构造constraint对象DVConstraint constraint=DVConstraint.createCustomFormulaConstraint("B1");//四个参数分别是：起始行、终止行、起始列、终止列CellRangeAddressList regions=new CellRangeAddressList(firstRow,firstCol,endRow,endCol);//数据有效性对象HSSFDataValidation data_validation_view = new HSSFDataValidation(regions, constraint);return data_validation_view;}}　　




　　手工设置:
　　excel菜单栏上--数据--有效性--允许--序列,
　　excel菜单栏上--数据--有效性--输入信息.
　　程序现实:
　　首先,http://poi.apache.org/官方下载POI3.2 jar包.
import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFDataValidation;import org.apache.poi.hssf.usermodel.DVConstraint;import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;import org.apache.poi.hssf.util.CellRangeAddressList;import java.io.IOException;import java.io.FileOutputStream;public class PoiTest1{public static void main(String [] args) throws IOException {HSSFWorkbook wb=new HSSFWorkbook();//excel文件对象HSSFSheet sheetlist=wb.createSheet("sheetlist");//工作表对象HSSFSheet sheetview=wb.createSheet("sheetview");FileOutputStream out=new FileOutputStream("success.xls");//得到验证对象HSSFDataValidation data_validation_list = PoiTest1.setDataValidationList((short)1,(short)1,(short)2,(short)2);HSSFDataValidation data_validation_view = PoiTest1.setDataValidationView((short)1,(short)1,(short)2,(short)2);//设置提示内容,标题,内容data_validation_view.createPromptBox("mm","www.tangxiangcun.com");//工作表添加验证数据sheetlist.addValidationData(data_validation_list);sheetview.addValidationData(data_validation_view);wb.write(out);out.close();}public static HSSFDataValidation setDataValidationList(short firstRow,short firstCol,short endRow, short endCol){//设置下拉列表的内容String[] textlist={"列表1","列表2","列表3","列表4","列表5"};//加载下拉列表内容DVConstraint constraint=DVConstraint.createExplicitListConstraint(textlist);//设置数据有效性加载在哪个单元格上。//四个参数分别是：起始行、终止行、起始列、终止列CellRangeAddressList regions=new CellRangeAddressList(firstRow,firstCol,endRow,endCol);//数据有效性对象HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);return data_validation_list;}public static HSSFDataValidation setDataValidationView(short firstRow,short firstCol,short endRow, short endCol){//构造constraint对象DVConstraint constraint=DVConstraint.createCustomFormulaConstraint("B1");//四个参数分别是：起始行、终止行、起始列、终止列CellRangeAddressList regions=new CellRangeAddressList(firstRow,firstCol,endRow,endCol);//数据有效性对象HSSFDataValidation data_validation_view = new HSSFDataValidation(regions, constraint);return data_validation_view;}}