本文转自:
http://blog.csdn.net/fangkecool2008/article/details/50822533
如果以下方法不行,我反正试过了,可以是可以,但是有bug,我第二个sheet页设置了数据100个数字,1-100,第一个sheet页引用,第一行到第一100行能引用到,100行以后就引用不到了,准确的说,第一行第一列能引用到1-100的下拉,第二行第一列就只能引入2-100,即99个下拉,第三行就只能3-100,越来越少。
代码的引用我都是写死的$A1:$A100,引用到第一个sheet页中的第一列,(行数是1到15000行)可始终不行。
最终委婉的解决办法,也是下拉数值过大的最终解决办法: 在自己的项目中,放入模板,模板中设置好数据有效性,然后在代码中读取模板,修改数据有效性的值即可。
又来了一个坑,确定不了第一个sheet页,如性别,需要几个下拉值,如果模板做成10个,实际只有两个,那么就有8个空行,而且即使点击了忽略空行,依然会显示空行。
所以最好设置好到底有多少个数值,像性别,肯定就是3个,男、女、其他、其他的下拉就待定了。
还有一种方式: 加一个公式,先在需要下拉的地方点击公式,输入公式,然后在点击数据有效性,选中序列,值=公式名称即可
=INDIRECT("Sheet2!C1:C"&COUNTA(Sheet2!$C:$C))
数据有效性跨表引用的解决
为了防止数据的误输,我们需要限制数据录入格式,这就要用到数据有效性设置。 一、如何设定有效性 1、在设定有效性之前,要选择数据区域,否则仅对当前单元格进行设置。 2、点击EXCEL的“数据”-“有效性”菜单,可弹出“数据有效性”设置窗口。 3、有效性有“整数”“小数”“序列”等共8种。 今天这里只讲解“序列”型,并且重点放在跨表引用上。 二、“序列”型设置 1、序列设置格式1:手工输入序列值 使用英文逗号隔开的文本,被认为是序列,如“a,b,c,d,e”。 2、序列设置格式2:单元格区域 如=$C$10:$C$16,则将同一工作表下的C10:C16区域的值用作有效性序列。 三、跨表引用的处理 当你使用序列,试图引用其他表时,系统会发出错误提示,并且无法选择其他表。 我们可以使用下述两种办法解决: 1、使用公式 如我要在sheet2的单元格中设置有效性,序列的来源为sheet1的A2:A50区域,则在sheet2的有效性设置中选择序列,然后输入公式:=INDIRECT("sheet1!$A$2:$A$50"),则可达到目的。 INDIRECT函数,请参阅EXCEL帮助文件。 2、使用名称 名称可以理解为自定义变量。如上述例题,则先要点击“插入”——“名称”——“定义……”,弹出“定义名称”对话框,在里面定义AAA,引用位置为=sheet1!$A$2:$A$50。(我是应用到了这个) 然后在sheet2的有效性设置中选择序列,然后输入公式:=AAA。 |
public String exportprisoner() throws IOException {
try {
// Excel模板路径
// 重设头信息
ServletOutputStream out = getResponse().getOutputStream();
getResponse().reset();
getResponse()
.setContentType("applicationnd.ms-excel;charset=utf-8");
getResponse().setHeader(
"Content-Disposition",
"attachment;filename="
+ new String(("模版" + ".xls").getBytes(),
"iso-8859-1"));
String path = getRequest().getRealPath("/");
System.out.println(path);
File excelFile = new File(path+"download/test.xls"); // 读取本地项目中的模板
// 文件流
FileInputStream is = new FileInputStream(excelFile);
// 支持Excel 2003 2007
Workbook workbook = null;
try {
workbook = WorkbookFactory.create(is);
} catch (InvalidFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//得到数据有效性的值所放的位置
Sheet sheet2 = workbook.getSheet("Sheet2");
Row row = null;
Cell cell = null;
for (int j = 0; j < 200; j++) {
row = sheet2.createRow(j);
cell = row.createCell(0);
cell.setCellValue("新值"+j);
}
System.out.println(sheetCount);
workbook.write(out);
// 刷新输出流
out.flush();
// 关闭输出流
if (out != null) {
out.close();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
}
return null;
}
================================数据量较少的下拉的情况,用此方法
private HSSFWorkbook workbook = null;
private HSSFCellStyle titleStyle = null;
private HSSFCellStyle dataStyle = null;
/**
* 列头样式
* @param workbook
* @param sheet
*/
public void setTitleCellStyles(HSSFWorkbook workbook,HSSFSheet sheet){
titleStyle = workbook.createCellStyle();
//设置边框
titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置背景色
titleStyle.setFillForegroundColor(HSSFColor.WHITE.index);
titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//设置居中
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置字体
HSSFFont font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 11); //设置字体大小
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
titleStyle.setFont(font);//选择需要用到的字体格式
//设置自动换行
titleStyle.setWrapText(true);
//设置列宽 ,第一个参数代表列id(从0开始),第2个参数代表宽度值
sheet.setColumnWidth(0, 5000);
sheet.setColumnWidth(1, 5000);
sheet.setColumnWidth(2, 3000);
sheet.setColumnWidth(3, 7000);
sheet.setColumnWidth(4, 5000);
sheet.setColumnWidth(5, 5000);
sheet.setColumnWidth(6, 3000);
sheet.setColumnWidth(7, 3000);
sheet.setColumnWidth(8, 5000);
sheet.setColumnWidth(9, 5000);
sheet.setColumnWidth(10, 3000);
sheet.setColumnWidth(11, 3000);
sheet.setColumnWidth(12, 3000);
sheet.setColumnWidth(13, 3000);
sheet.setColumnWidth(14, 7000);
sheet.setColumnWidth(15, 7000);
sheet.setColumnWidth(16, 7000);
sheet.setColumnWidth(17, 7000);
sheet.setColumnWidth(18, 10000);
}
/**
* 数据样式
* @param workbook
* @param sheet
*/
public void setDataCellStyles(HSSFWorkbook workbook,HSSFSheet sheet){
dataStyle = workbook.createCellStyle();
//设置边框
dataStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
dataStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
dataStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
dataStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置背景色
dataStyle.setFillForegroundColor(HSSFColor.WHITE.index);
dataStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//设置居中
dataStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
//设置字体
HSSFFont font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 11); //设置字体大小
dataStyle.setFont(font);//选择需要用到的字体格式
//设置自动换行
dataStyle.setWrapText(true);
}
/**
* 创建一列数据
* @param currentRow
* @param textList
*/
public void creatRow(HSSFRow currentRow,List<String> textList){
if(textList!=null&&textList.size()>0){
int i = 0;
for(String cellValue : textList){
HSSFCell userNameLableCell = currentRow.createCell(i++);
userNameLableCell.setCellValue(cellValue);
}
}
}
/**
* 创建一列应用列头
* @param userinfosheet1
* @param userName
*/
public void creatAppRowHead(HSSFSheet userinfosheet1,int naturalRowIndex){
HSSFRow row = userinfosheet1.createRow(naturalRowIndex-1);
//0.所属分类
HSSFCell Cell1 = row.createCell(0);
Cell1.setCellValue("所属分类");
Cell1.setCellStyle(titleStyle);
//1.分类名称
HSSFCell Cell2 = row.createCell(1);
Cell2.setCellValue("分类名称");
Cell2.setCellStyle(titleStyle);
//2.资产类型
HSSFCell Cell3 = row.createCell(2);
Cell3.setCellValue("资产类型");
Cell3.setCellStyle(titleStyle);
//3.设备用途
HSSFCell Cell4 = row.createCell(3);
Cell4.setCellValue("设备用途");
Cell4.setCellStyle(titleStyle);
//4.品牌
HSSFCell Cell5 = row.createCell(4);
Cell5.setCellValue("品牌");
Cell5.setCellStyle(titleStyle);
//5.型号
HSSFCell Cell6 = row.createCell(5);
Cell6.setCellValue("型号");
Cell6.setCellStyle(titleStyle);
//6.数量
HSSFCell Cell7 = row.createCell(6);
Cell7.setCellValue("数量");
Cell7.setCellStyle(titleStyle);
//7.计量单位 台、套、个
HSSFCell Cell8 = row.createCell(7);
Cell8.setCellValue("计量单位");
Cell8.setCellStyle(titleStyle);
//8.购入原值
HSSFCell Cell9 = row.createCell(8);
Cell9.setCellValue("购入原值");
Cell9.setCellStyle(titleStyle);
//9.购入时间
HSSFCell Cell10 = row.createCell(9);
Cell10.setCellValue("购入时间");
Cell10.setCellStyle(titleStyle);
//10.现存状态 闲置、在用
HSSFCell Cell11 = row.createCell(10);
Cell11.setCellValue("现存状态");
Cell11.setCellStyle(titleStyle);
//11.仓库状态 在库、离库
HSSFCell Cell12 = row.createCell(11);
Cell12.setCellValue("仓库状态");
Cell12.setCellStyle(titleStyle);
//12.资产属性 完好、损坏
HSSFCell Cell13 = row.createCell(12);
Cell13.setCellValue("资产属性");
Cell13.setCellStyle(titleStyle);
//13.报废属性 正常、报废
HSSFCell Cell14 = row.createCell(13);
Cell14.setCellValue("报废属性");
Cell14.setCellStyle(titleStyle);
//14.保管人
HSSFCell Cell15 = row.createCell(14);
Cell15.setCellValue("保管人");
Cell15.setCellStyle(titleStyle);
//15.资金来源
HSSFCell Cell16 = row.createCell(15);
Cell16.setCellValue("资金来源");
Cell16.setCellStyle(titleStyle);
//16.供应商
HSSFCell Cell17 = row.createCell(16);
Cell17.setCellValue("供应商");
Cell17.setCellStyle(titleStyle);
//17.供应商电话
HSSFCell Cell18 = row.createCell(17);
Cell18.setCellValue("供应商电话");
Cell18.setCellStyle(titleStyle);
//18.备注说明
HSSFCell Cell19 = row.createCell(18);
Cell19.setCellValue("备注说明");
Cell19.setCellStyle(titleStyle);
}
/**
* 创建一列应用数据
* @param userinfosheet1
* @param userName
*/
public void creatAppRow(HSSFSheet userinfosheet1,String titels,int naturalRowIndex){
//在第一行第一个单元格,插入下拉框
HSSFRow row = userinfosheet1.createRow(naturalRowIndex-1);
//所属分类
String [] list1={"办公计算设备","网络交换设备","安全防御设备","办公打印设备","复印扫描设备","监控预警设备","影音传输设备","存储备份设备","机房运维设备","办公软件","工具软件","专业软件","系统软件","其他软件"};
//分类名称
String [] list2={"台式电脑","便携式电脑","平板电脑","智能终端","服务器","其他办公计算设备","交换机","路由器","中继设备","负载均衡","其他交换设备","防火墙","防毒墙","邮件网关","入侵防御","数据库审计","其他安全防御设备",
"上网行为管理","VPN","针式打印机","激光式打印机","喷墨式打印机","一体机(打印、复印、传值)","票据打印机","其他办公打印设备","一体机","复印机","扫描仪","传真机","新风空调设备","UPS电源"};
//资产类型
String [] list3={"主设备","介质","配件"};
//计量单位
String [] list4={"台","套","个"};
//现存状态
String [] list5={"闲置","在用"};
//仓库状态
String [] list6={"在库","离库"};
//资产属性
String [] list7={"完好","损坏"};
//报废属性
String [] list8={"正常","报废"};
//0.所属分类
HSSFCell cell1 = row.createCell(0);
cell1.setCellValue("请选择");
cell1.setCellStyle(dataStyle);
//1.分类名称
HSSFCell cell2 = row.createCell(1);
cell2.setCellValue("请选择");
cell2.setCellStyle(dataStyle);
//2.资产类型
HSSFCell cell3 = row.createCell(2);
cell3.setCellValue("请选择");
cell3.setCellStyle(dataStyle);
//3.设备用途
HSSFCell cell4 = row.createCell(3);
cell4.setCellValue(titels);
cell4.setCellStyle(dataStyle);
//4.品牌
HSSFCell cell5 = row.createCell(4);
cell5.setCellValue(titels);
cell5.setCellStyle(dataStyle);
//5.型号
HSSFCell cell6 = row.createCell(5);
cell6.setCellValue(titels);
cell6.setCellStyle(dataStyle);
//6.数量
HSSFCell cell7 = row.createCell(6);
cell7.setCellValue(titels);
cell7.setCellStyle(dataStyle);
//7.计量单位
HSSFCell cell8 = row.createCell(7);
cell8.setCellValue("请选择");
cell8.setCellStyle(dataStyle);
//8.购入原值
HSSFCell cell9 = row.createCell(8);
cell9.setCellValue(titels);
cell9.setCellStyle(dataStyle);
//9.购入时间
HSSFCell cell10 = row.createCell(9);
cell10.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(new Date()).toString());
cell10.setCellStyle(dataStyle);
//10.现存状态
HSSFCell cell11 = row.createCell(10);
cell11.setCellValue("请选择");
cell11.setCellStyle(dataStyle);
//11.仓库状态
HSSFCell cell12 = row.createCell(11);
cell12.setCellValue("请选择");
cell12.setCellStyle(dataStyle);
//12.资产属性
HSSFCell cell13 = row.createCell(12);
cell13.setCellValue("请选择");
cell13.setCellStyle(dataStyle);
//13.报废属性
HSSFCell cell14 = row.createCell(13);
cell14.setCellValue("请选择");
cell14.setCellStyle(dataStyle);
//14.保管人
HSSFCell cell15 = row.createCell(14);
cell15.setCellValue(titels);
cell15.setCellStyle(dataStyle);
//15.资金来源
HSSFCell cell16 = row.createCell(15);
cell16.setCellValue(titels);
cell16.setCellStyle(dataStyle);
//16.供应商
HSSFCell cell17 = row.createCell(16);
cell17.setCellValue(titels);
cell17.setCellStyle(dataStyle);
//17.供应商电话
HSSFCell cell18 = row.createCell(17);
cell18.setCellValue(titels);
cell18.setCellStyle(dataStyle);
//18.备注说明
HSSFCell cell19 = row.createCell(18);
cell19.setCellValue(titels);
cell19.setCellStyle(dataStyle);
//得到验证对象
DataValidation data_validation_list = this.getDataValidationByFormula(list1,naturalRowIndex,1); //从1开始下拉框处于第几列
//工作表添加验证数据
userinfosheet1.addValidationData(data_validation_list);
DataValidation data_validation_list2 = this.getDataValidationByFormula(list2,naturalRowIndex,2);
//工作表添加验证数据
userinfosheet1.addValidationData(data_validation_list2);
DataValidation data_validation_list3 = this.getDataValidationByFormula(list3,naturalRowIndex,3);
//工作表添加验证数据
userinfosheet1.addValidationData(data_validation_list3);
DataValidation data_validation_list8 = this.getDataValidationByFormula(list4,naturalRowIndex,8);
//工作表添加验证数据
userinfosheet1.addValidationData(data_validation_list8);
DataValidation data_validation_list11 = this.getDataValidationByFormula(list5,naturalRowIndex,11);
//工作表添加验证数据
userinfosheet1.addValidationData(data_validation_list11);
DataValidation data_validation_list12 = this.getDataValidationByFormula(list6,naturalRowIndex,12);
//工作表添加验证数据
userinfosheet1.addValidationData(data_validation_list12);
DataValidation data_validation_list13 = this.getDataValidationByFormula(list7,naturalRowIndex,13);
//工作表添加验证数据
userinfosheet1.addValidationData(data_validation_list13);
DataValidation data_validation_list14 = this.getDataValidationByFormula(list8,naturalRowIndex,14);
//工作表添加验证数据
userinfosheet1.addValidationData(data_validation_list14);
}
/**
* 使用已定义的数据源方式设置一个数据验证
* @param formulaString
* @param naturalRowIndex
* @param naturalColumnIndex
* @return
*/
public DataValidation getDataValidationByFormula(String[] formulaString,int naturalRowIndex,int naturalColumnIndex){
//加载下拉列表内容
DVConstraint constraint = DVConstraint.createExplicitListConstraint(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;
}
/**
* 生成导出下拉框excel
* @param outPathStr 输出路径
*/
public HSSFWorkbook ExportComboxExcel (HSSFWorkbook workbook) {
try {
// workbook = new HSSFWorkbook();//excel文件对象
HSSFSheet sheet1 = workbook.createSheet("sheet1");//工作表对象
//设置列头样式
this.setTitleCellStyles(workbook,sheet1);
//设置数据样式
this.setDataCellStyles(workbook,sheet1);
//创建一行列头数据
this.creatAppRowHead(sheet1,1);
//创建一行数据
for (int i = 2; i < 11; i++) {
this.creatAppRow(sheet1, "",i);
}
System.out.println("导出成功!");
} catch (Exception e) {
e.printStackTrace();
}
return workbook;
}
--------------------------------------------------------------------
action
/**
* 导出台帐登记表(模版).xls
* @author fangke
* @throws IOException
*/
public String ExprotExcelTemp() throws IOException{
HSSFWorkbook wb = null;
ExportComboxExcel ecbe = null;
try
{
//Excel模板路径
//重设头信息
ServletOutputStream out = response.getOutputStream();
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename="
+ new String(("台帐入库登记表(模版)" + ".xls")
.getBytes(), "iso-8859-1"));
wb = new HSSFWorkbook();
ecbe = new ExportComboxExcel();
wb=ecbe.ExportComboxExcel(wb);
wb.write(out);
//刷新输出流
out.flush();
//关闭输出流
if(out!=null){
out.close();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
}
return null;
}