Excel2003和Excel2007对下拉选择和下拉级联选择的操作以及java程序的调用

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;  
     }
}

评论

2 楼 穷途末路 2014-01-03  

   给力，谢谢分享

1 楼 fruitking 2010-11-16  

使用的jar包有
dom4j-1.6.1.jar
poi-3.7-20101029.jar
poi-ooxml-3.7-20101029.jar
poi-ooxml-schemas-3.7-20101029.jar
poi-scratchpad-3.7-20101029.jar
xmlbeans-2.3.0.jar