POI 2003/2007 下拉列表

Excel2007和Excel2003的部分功能菜单有所调整
比如2003的“插入-名称”，在2007中更为到“公式-定义的名称”
比如2003的“插入-名称-指定-首行”，在2007中更为到“公式-定义的名称-根据所选内容创建-首行”

Excel功能点应用：
相对位置和绝对位置，特别在某个行列的数据是参考另外某个行列的数据而变动的，相对位置的表示方法：A8、B9等等，绝对位置的表示方法：$A$8、$B$9（就是使用美元符号$）

隐藏页的数据引用。
2003中，假设sheet1是隐藏页，并先定义好数据（公式-定义的名称→定义；在sheet1中定义数据源名称：省份；引用位置：=Sheet1!$A$1:$A$5），然后在“添加”数据，设置数据的有效性选项（数据-数据有效性-序列；来源填写“=省份”）
2007中，假设sheet1是隐藏页，并先定义好数据（插入→名称→定义；在sheet1中定义数据源名称：省份；引用位置：=Sheet1!$A$1:$A$5），然后在“添加”数据，设置数据的有效性选项（数据-数据有效性-序列；来源填写“=省份”）

package com.fruitking.caipiao;import java.io.FileOutputStream;import java.io.IOException;import org.apache.poi.hssf.usermodel.DVConstraint;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFDataValidation;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.DataValidation;import org.apache.poi.ss.usermodel.Name;import org.apache.poi.ss.util.CellRangeAddressList;public class TestExcelSelect {   public static void main(String [] args) throws IOException {           HSSFWorkbook workbook = new HSSFWorkbook();//excel文件对象           HSSFSheet userinfosheet1 = workbook.createSheet("用户信息表-1");//工作表对象         HSSFSheet userinfosheet2 = workbook.createSheet("用户信息表-2");//工作表对象         //创建一个隐藏页和隐藏数据集         TestExcelSelect.creatHideSheet(workbook, "hideselectinfosheet");         //设置名称数据集         TestExcelSelect.creatExcelNameList(workbook);         //创建一行数据         TestExcelSelect.creatAppRow(userinfosheet1, "许果",1);         TestExcelSelect.creatAppRow(userinfosheet1, "刘德华",2);         TestExcelSelect.creatAppRow(userinfosheet1, "刘若英",3);         TestExcelSelect.creatAppRow(userinfosheet2, "张学友",1);         TestExcelSelect.creatAppRow(userinfosheet2, "林志玲",2);         TestExcelSelect.creatAppRow(userinfosheet2, "林熙蕾",3);                  //生成输入文件         FileOutputStream out=new FileOutputStream("success.xls");           workbook.write(out);           out.close();     }    /**   * 名称管理   * @param workbook   */  public static void creatExcelNameList(HSSFWorkbook workbook){  //名称管理         Name name;         name = workbook.createName();         name.setNameName("provinceInfo");         name.setRefersToFormula("hideselectinfosheet!$A$1:$E$1");         name = workbook.createName();         name.setNameName("浙江");         name.setRefersToFormula("hideselectinfosheet!$B$2:$K$2");         name = workbook.createName();         name.setNameName("山东");         name.setRefersToFormula("hideselectinfosheet!$B$3:$I$3");         name = workbook.createName();         name.setNameName("江西");         name.setRefersToFormula("hideselectinfosheet!$B$4:$E$4");         name = workbook.createName();         name.setNameName("江苏");         name.setRefersToFormula("hideselectinfosheet!$B$5:$I$5");         name = workbook.createName();         name.setNameName("四川");         name.setRefersToFormula("hideselectinfosheet!$B$6:$K$6");  }      /**   * 创建隐藏页和数据域   * @param workbook   * @param hideSheetName   */  public static void creatHideSheet(HSSFWorkbook workbook,String hideSheetName){   HSSFSheet hideselectinfosheet = workbook.createSheet(hideSheetName);//隐藏一些信息         //设置下拉列表的内容           String[] provinceList = {"浙江","山东","江西","江苏","四川"};         String[] zjProvinceList = {"浙江","杭州","宁波","温州","台州","绍兴","金华","湖州","丽水","衢州","舟山"};         String[] sdProvinceList = {"山东","济南","青岛","烟台","东营","菏泽","淄博","济宁","威海"};         String[] jxProvinceList = {"江西","南昌","新余","鹰潭","抚州"};         String[] jsProvinceList = {"江苏","南京","苏州","无锡","常州","南通","泰州","连云港","徐州"};         String[] scProvinceList = {"四川","成都","绵阳","自贡","泸州","宜宾","攀枝花","广安","达州","广元","遂宁"};         //在隐藏页设置选择信息         HSSFRow provinceRow = hideselectinfosheet.createRow(0);         TestExcelSelect.creatRow(provinceRow, provinceList);         HSSFRow zjProvinceRow = hideselectinfosheet.createRow(1);         TestExcelSelect.creatRow(zjProvinceRow, zjProvinceList);         HSSFRow sdProvinceRow = hideselectinfosheet.createRow(2);         TestExcelSelect.creatRow(sdProvinceRow, sdProvinceList);         HSSFRow jxProvinceRow = hideselectinfosheet.createRow(3);         TestExcelSelect.creatRow(jxProvinceRow, jxProvinceList);         HSSFRow jsProvinceRow = hideselectinfosheet.createRow(4);         TestExcelSelect.creatRow(jsProvinceRow, jsProvinceList);         HSSFRow scProvinceRow = hideselectinfosheet.createRow(5);         TestExcelSelect.creatRow(scProvinceRow, scProvinceList);         //设置隐藏页标志         workbook.setSheetHidden(workbook.getSheetIndex(hideSheetName), true);  }    /**   * 创建一列应用数据   * @param userinfosheet1   * @param userName   */  public static void creatAppRow(HSSFSheet userinfosheet1,String userName,int naturalRowIndex){  //构造一个信息输入表单，用户姓名，出生省份，出生城市         //要求省份是可以下拉选择的，出生城市根据所选择的省份级联下拉选择         //在第一行第一个单元格，插入下拉框         HSSFRow row = userinfosheet1.createRow(naturalRowIndex-1);         HSSFCell userNameLableCell = row.createCell(0);         userNameLableCell.setCellValue("用户姓名:");         HSSFCell userNameCell = row.createCell(1);         userNameCell.setCellValue(userName);         HSSFCell provinceLableCell = row.createCell(2);         provinceLableCell.setCellValue("出生省份:");         HSSFCell provinceCell = row.createCell(3);         provinceCell.setCellValue("请选择");         HSSFCell cityLableCell = row.createCell(4);         cityLableCell.setCellValue("出生城市:");         HSSFCell cityCell = row.createCell(5);         cityCell.setCellValue("请选择");                  //得到验证对象           DataValidation data_validation_list = TestExcelSelect.getDataValidationByFormula("provinceInfo",naturalRowIndex,4);         //工作表添加验证数据           userinfosheet1.addValidationData(data_validation_list);         DataValidation data_validation_list2 = TestExcelSelect.getDataValidationByFormula("INDIRECT($D"+naturalRowIndex+")",naturalRowIndex,6);         //工作表添加验证数据           userinfosheet1.addValidationData(data_validation_list2);  }    /**   * 创建一列数据   * @param currentRow   * @param textList   */  public static void creatRow(HSSFRow currentRow,String[] textList){   if(textList!=null&&textList.length>0){    int i = 0;    for(String cellValue : textList){     HSSFCell userNameLableCell = currentRow.createCell(i++);           userNameLableCell.setCellValue(cellValue);    }   }  }       /**   * 对Excel自然行列设置一个数据验证（并出现下拉列表选择格式）   * @param selectTextList   * @param naturalRowIndex   * @param naturalColumnIndex   * @return   */     public static DataValidation getDataValidationList(String[] selectTextList,int naturalRowIndex,int naturalColumnIndex){         //加载下拉列表内容        DVConstraint constraint = DVConstraint.createExplicitListConstraint(selectTextList);         //设置数据有效性加载在哪个单元格上。           //四个参数分别是：起始行、终止行、起始列、终止列           int firstRow = naturalRowIndex-1;         int lastRow = naturalRowIndex-1;         int firstCol = naturalColumnIndex-1;         int lastCol = naturalColumnIndex-1;         CellRangeAddressList regions=new CellRangeAddressList(firstRow,lastRow,firstCol,lastCol);           //数据有效性对象         DataValidation data_validation_list = new HSSFDataValidation(regions,constraint);           return data_validation_list;       }          /**      * 使用已定义的数据源方式设置一个数据验证      * @param formulaString      * @param naturalRowIndex      * @param naturalColumnIndex      * @return      */     public static DataValidation getDataValidationByFormula(String formulaString,int naturalRowIndex,int naturalColumnIndex){         //加载下拉列表内容        DVConstraint constraint = DVConstraint.createFormulaListConstraint(formulaString);          //设置数据有效性加载在哪个单元格上。           //四个参数分别是：起始行、终止行、起始列、终止列           int firstRow = naturalRowIndex-1;         int lastRow = naturalRowIndex-1;         int firstCol = naturalColumnIndex-1;         int lastCol = naturalColumnIndex-1;         CellRangeAddressList regions=new CellRangeAddressList(firstRow,lastRow,firstCol,lastCol);           //数据有效性对象          DataValidation data_validation_list = new HSSFDataValidation(regions,constraint);         return data_validation_list;       }}

 public static void main(String[] args) {  FileOutputStream out = null;  try {   // excel对象   HSSFWorkbook wb = new HSSFWorkbook();   // sheet对象   HSSFSheet sheet = wb.createSheet("sheet1");   // 输出excel对象   out = new FileOutputStream("C://aaa.xls");   // 取得规则   HSSFDataValidation validate = PoiTest.setValidate((short) 1,     (short) 1, (short) 1, (short) 1);   // 设定规则   sheet.addValidationData(validate);   // 输出excel   wb.write(out);   out.close();  } catch (FileNotFoundException e) {   e.printStackTrace();  } catch (IOException e) {   e.printStackTrace();  } finally {   if (out != null) {    try {     out.close();    } catch (IOException e) {     // TODO Auto-generated catch block     e.printStackTrace();    }   }  } } public static HSSFDataValidation setValidate(short beginRow,   short beginCol, short endRow, short endCol) {  // 创建一个规则：1-100的数字  DVConstraint constraint = DVConstraint.createNumericConstraint(    DVConstraint.ValidationType.INTEGER,    DVConstraint.OperatorType.BETWEEN, "1", "100");  // 设定在哪个单元格生效  CellRangeAddressList regions = new CellRangeAddressList(beginRow,    beginCol, endRow, endCol);  // 创建规则对象  HSSFDataValidation ret = new HSSFDataValidation(regions, constraint);  return ret; }

 public static void main(String[] args) {  FileOutputStream out = null;  try {   // excel对象   XSSFWorkbook wb = new XSSFWorkbook();   // sheet对象   XSSFSheet sheet = wb.createSheet("sheet1");   // 输出excel对象   out = new FileOutputStream("C://aaa1.xls");   // 取得规则   DataValidation validate = PoiTest.setValidate(sheet, (short) 1,     (short) 1, (short) 1, (short) 1);   // 设定规则   sheet.addValidationData(validate);   wb.write(out);   out.close();  } catch (FileNotFoundException e) {   e.printStackTrace();  } catch (IOException e) {   e.printStackTrace();  } finally {   if (out != null) {    try {     out.close();    } catch (IOException e) {     // TODO Auto-generated catch block     e.printStackTrace();    }   }  } } public static DataValidation setValidate(XSSFSheet sheet, short beginRow,   short beginCol, short endRow, short endCol) {  XSSFDataValidationHelper helper = new XSSFDataValidationHelper(sheet);  DataValidationConstraint constraint = helper.createNumericConstraint(    ValidationType.INTEGER, OperatorType.BETWEEN, "1", "100");  CellRangeAddressList regions = new CellRangeAddressList(beginRow,    beginCol, endRow, endCol);  return helper.createValidation(constraint, regions); }