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），然后在“添加”数据，设置数据的有效性选项（数据-数据有效性-序列；来源填写“=省份”）
Java代码
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;
}
}
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
http://ttaale.iteye.com/blog/836306
POI 操作Excel，创建下拉框
1. import
2. import
3.
4. import
5. import
6. import
7. import
8. import
9. import
10. import
11.
12. public class
13. {
14. public static void
15. {
16. "东软","华信","SAP","海辉"};
17. new
18. return;
19. }
20. public void
21. {
22. //文件初始化
23. new
24. "new sheet");
25.
26. //在第一行第一个单元格，插入下拉框
27. 0);
28. 0);
29.
30. //普通写入操作
31. "请选择");//这是实验
32.
33. //生成下拉列表
34.
35. //只对（0，0）单元格有效
36. new CellRangeAddressList(0,0,0,0);
37.
38. //生成下拉框内容
39. DVConstraint constraint = DVConstraint.createExplicitListConstraint(list);
40.
41. //绑定下拉框和作用区域
42. new
43.
44. //对sheet页生效
45. sheet.addValidationData(data_validation);
46.
47. //写入文件
48. FileOutputStream fileOut;
49. try
50. new FileOutputStream("workbook.xls");
51. wb.write(fileOut);
52. fileOut.close();
53. catch
54. // TODO Auto-generated catch block
55. e.printStackTrace();
56. }
57.
58. //结束
59. "Over");
60. }
61. } ＝＝＝＝＝＝＝＝＝＝＝＝＝＝
http://www.iteye.com/problems/65191
谁使用过POI3.8 设置EXCEL2007的数据有效性
------------------------------------------------------------------------------------------------------------------
问题补充：
hudingchen 写道
有问题吗？ 我写个例子，poi3.8,excel2007好用
Java代码
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;
}
你这个生成的EXCEL格式是2003的，我在生成EXCEL2007的时候，在实例化XSSFDataValidation 时候，不知道怎么写了，你以前弄好2007格式的吗，能给我一个例子吗，谢谢
我之前的代码，在2007下是好用的，不过没有使用XSSF，我试着用XSSF写了一下，但是生成的excel文件的数据有效性没有加上，不过代码我觉得没有问题的，先看下
Java代码
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);
}
后来我查了下文档，http://poi.apache.org/spreadsheet/quick-guide.html#Validation
其中有这么一句 Currently - as of version 3.5 - the XSSF stream does not support data validations and neither it nor the HSSF stream allow data validations to be recovered from sheets
也就是说3.5中XSSF是不支持validations的，我怀疑3.8也还不支持，估计以后会修正吧，希望对你有帮助。
