//因为是盲打过来的,我验证的数据并不是这个,但是为了符合题目还是写了这个,所以参数有可能在表格中位置会
//有点不对,但是实验一下还是可以很容易看出来的。
String[] p = {"p1","p2"};
String[] price = {"10","20"};
int rindex = 0;
Name name;
HSSFWorkbook workbook = new HSSFWorkbook();//excel文件对象
HSSFSheet sheet = workbook.createSheet("Info");//工作表对象
HSSFSheet hidesheet = workbook.createSheet("hideSheet");//隐藏一些信息
HSSFRow row = hidesheet.createRow(rindex++);
//设置物品编号
for(int i=0;i
HSSFCell cell = row.createCell(i);
cell.setCellValue(p[i]);
}
// 名称管理
name = workbook.createName();
name.setNameName("物品编号");
name.setRefersToFormula("hidesheet!$A$"+rindex+":$"+judgePos(p.size())+"$"+rindex);
for(int i=0;i
HSSFRow row = hidesheet.createRow(rindex++);
HSSFCell cell = row.createCell(0);
cell.setCellValue(p[i]);
cell = row.createCell(1);
cell.setCellValue(price[i]);
}
String[] title = {"物品编号","物品价格"};
setTitle(sheet ,title,0);
HSSFRow allocationRow = sheet.createRow(rindex++);
**allocationRow.createCell(4).setCellFormula("LOOKUP(A2,hideSheet!A2:A3,hideSheet!B2:B3)");**
// 得到验证对象
DataValidation validation = getDataValidationByFormula("物品编号",2,1);
// 工作表添加验证数据
sheet.addValidationData(validation);
// 生成输入文件
File file = new File(filePath);
FileOutputStream out = new FileOutputStream(file);
workbook.write(out);
out.close();
//以上大概就是个全过程,主要是设置LOOKUP函数,我原先一直没搞出来是因为函数参数写错了,搞半天没搞出来,今天搞出来了。
//用到的函数我也贴一下,有些用别人的,有些自己写的。
/**
* 返回所在列的字符
* @param size
* @return
*/
public String judgePos(int size) {
String[] args= {"A","B","C","D","E","F","G","H","I","J","K","L","M","N",
"O","P","Q","R","S","T","U","V","W","X","Y","Z"};
boolean tag=true;
StringBuffer reversepos=new StringBuffer();
while(tag) {
int pos=size%26;
if(pos==0) {
pos=25;
size--;
}else {
pos-=1;
}
int result = size/26;
if(result==0) {
reversepos.append(args[pos]);
tag=false;
}else {
reversepos.append(args[pos]);
size/=26;
}
}
return reversepos.reverse().toString();
}
/**
* 创建表头
* @param sheet
* @param title
*/
public void setTitle(HSSFSheet sheet,String[] title,int index) {
HSSFRow row = sheet.createRow(index);
for(int i=0;i
HSSFCell userNameLableCell = row.createCell(i);
userNameLableCell.setCellValue(title[i]);
}
}
/**
* 使用已定义的数据源方式设置一个数据验证
*
* @param formulaString
* @param startRow
* @param startCol
* @return
*/
public static DataValidation getDataValidationByFormula(String formulaString,int startRow,int startCol) {
// 加载下拉列表内容
DVConstraint constraint = DVConstraint.createFormulaListConstraint(formulaString);
// 设置数据有效性加载在哪个单元格上。
// 四个参数分别是:起始行、终止行、起始列、终止列
int firstRow = startRow-1;
int lastRow = startRow-1;
int firstCol = startCol - 1;
int lastCol = startCol - 1;
CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
// 数据有效性对象
DataValidation validation = new HSSFDataValidation(regions, constraint);
return validation;
}
}