excel导出模板带有下拉的模板

本文转自：

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 2007Workbook workbook = null;try {workbook = WorkbookFactory.create(is);} catch (InvalidFormatException e) {// TODO Auto-generated catch blocke.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;      }