POI——动态修改Excel模板下拉框

需求：

在导入页面放置了的Excel模板，最开始内容是写死的。现在需要将数据库的数据动态的加载到excel模板的下拉框中。使用的为poi.xssf

相关jar包

poi-3.10-FINAL-20140208.jar
poi-ooxml-3.10-FINAL-20140208.jar
poi-ooxml-schemas-3.10-FINAL-20140208.jar

方法思路：

读取Excel模板文件——从数据库读取下拉框所需数据——将读取出的数据先写入另一张sheet（数据源sheet）——为展示数据的sheet设置数据验证规则，增加数据验证——将更新后的workbook保存到新的path

该方法返回path到controller，之后经过文件流处理即可实现模板的下载

   	/**
   	 * update poi select 
   	 * @param realPath
   	 * @author cloudHeart
   	 * @throws FileNotFoundException 
   	 */
	public String setPOISelect(String realPath) throws FileNotFoundException {
		InputStream is = null;
		XSSFWorkbook wb = null;
		
		//读取文件 
	     try {
			is = new FileInputStream(new File(realPath));
			wb = new XSSFWorkbook(is);
			XSSFSheet sheet = wb.getSheetAt(0);
			int rows = sheet.getLastRowNum() + 1; //行数
			System.out.println("rows = " + rows);
			
			//设置部门下拉框
			ArrayList<String> orgNameList = new ArrayList<>(30);
			List<Org> orgList = dao.findAll();
			for (Org one : orgList) {
				String name = one.getOrgName();
				orgNameList.add(name);
			}
			String[] orgArray = orgNameList.toArray(new String[orgNameList.size()]); 
			wb = selectUpda're(wb, sheet, orgArray, 2, 9, 1, 1, 2, 'B');

			
			//由于POI打开读取文件后再保存时bug问题, 只能重新定义一个新的Excel写入数据  
			String subPath = realPath.substring(0, realPath.lastIndexOf("\\"));
			String path = subPath.concat("\\模板");
			createExcel(wb, path);
			return path;
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return realPath; 
	    
		
	}

因为可能更新多个不同类型下拉框数据，所以设置了selectNum和selectAlpha

	/**
     * @param wb XSSFWorkbook对象
     * @param realSheet 需要操作的sheet对象
     * @param datas 下拉的列表数据
     * @param startRow 开始行
     * @param endRow 结束行
     * @param startCol 开始列
     * @param endCol 结束列
     * @param selectMum 数据源sheet的下拉数据源对应列
     * @param selectAlpha 数据源sheet的下拉数据对应字母
     * @return
     * @throws Exception
     */
    public XSSFWorkbook dropDownList2003(XSSFWorkbook wb, XSSFSheet realSheet, String[] datas, int startRow, int endRow,
                                                int startCol, int endCol, int selectNum ,char seletAlpha)
                                                                                                                       throws Exception {
    	String hiddenSheetName = "字典项";
       // XSSFWorkbook workbook = (XSSFWorkbook) wb;
        // 数据源sheet 原模板已有专门的数据源sheet，没有的话用 wb.createSheet("名称")创建一个即可
        XSSFSheet hidden = (XSSFSheet) wb.getSheetAt(1);
        // 数据源sheet页不显示
        wb.setSheetHidden(1, true);
        // 将下拉列表的数据放在数据源sheet上
        XSSFRow row = null;
        XSSFCell cell = null;
        for (int i = 0, length = datas.length; i < length; i++) {
            //row = hidden.createRow(i);
            row = hidden.getRow(i);
            if(row == null || ("").equals(row)){
            	row = hidden.createRow(i);
            }
           cell = row.getCell(selectNum-1);
           if(cell == null || ("").equals(cell)){
        	   cell = row.createCell(selectNum -1);
           }
           cell.setCellValue(datas[i]);
        }   
        	//创建规则
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(realSheet);
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
        		.createFormulaListConstraint(hiddenSheetName + "!$"+seletAlpha+"$1:$"+seletAlpha+"" + datas.length);
        //DataValidationConstraint  constraint = DVConstraint.createFormulaListConstraint(hiddenSheetName + "!$"+seletAlpha+"$1:$"+seletAlpha+"" + datas.length);
        CellRangeAddressList addressList = null; // 设定在哪个单元格生效  
        DataValidation validation = null; // 创建规则对象  
        row = null;
        cell = null;
        // 循环指定单元格下拉数据
        for (int i = startRow; i <= endRow; i++) {
            row = (XSSFRow) realSheet.getRow(i);
            cell = row.getCell(startCol); 
            addressList = new CellRangeAddressList(i, i, startCol, endCol);
            validation = dvHelper.createValidation(dvConstraint, addressList);
            //validation = new HSSFDataValidation(addressList, constraint);
            //HSSF和XSSF的创建数据验证有区别 realSheet.addValidationData(validation);
            realSheet.addValidationData(validation);
        }

        return wb;
    }

根据路径创建Excel

/** 
     * 根据路径创建Excel 
     * @param workbook 
     * @param path 
     */  
    public void createExcel(Workbook workbook, String path) {  
        FileOutputStream fileOut = null;  
        try {  
            fileOut = new FileOutputStream(path);  
            workbook.write(fileOut);  
        } catch (Exception e) {  
            logger.error("Error create excel: ", e.getMessage());  
        } finally {  
            try {  
                if(fileOut != null) {  
                    fileOut.close();  
                }  
                  
            } catch (IOException e) {  
                e.printStackTrace();  
            }  
        }  
    }