java使用POI做多级联动导出excel

主要解决excel动态模板下拉联动问题：

如图：

下拉联动处理代码：

下拉长度在255以内：

private static DataValidation setDataValidation(Sheet sheet, String[] textList, int firstRow, int endRow, int firstCol, int endCol) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        //加载下拉列表内容
        DataValidationConstraint constraint = helper.createExplicitListConstraint(textList);
        constraint.setExplicitListValues(textList);
        //设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList((short) firstRow, (short) endRow, (short) firstCol, (short) endCol);
        //数据有效性对象
        DataValidation data_validation = helper.createValidation(constraint, regions);
        return data_validation;
    }

长度在255以上：

private static DataValidation setDataValidations(Sheet sheet, String[] textList, int firstRow, int endRows, int firstCol, int endCol, HSSFWorkbook book) {
		String hiddenSheet = "dd123";
		DVConstraint constraint = null;
		CellRangeAddressList addressList = null;
		DataValidation validation = null; // 数据验证
		HSSFSheet category1Hidden = book.createSheet(hiddenSheet); // 创建隐藏域
		for (int i = 0, length = textList.length; i < length; i++) { // 循环赋值（为了防止下拉框的行数与隐藏域的行数相对应来获取>=选中行数的数组，将隐藏域加到结束行之后）
			sheet.createRow(endRows + i).createCell(8).setCellValue(textList[i]);
		}
		Name category1Name = book.createName();
		category1Name.setNameName(hiddenSheet);
		category1Name.setRefersToFormula(hiddenSheet + "!I1:A" + (textList.length + endRows));
		constraint = DVConstraint.createFormulaListConstraint(hiddenSheet);
		addressList = new CellRangeAddressList((short) firstRow, (short) endRows, (short) firstCol, (short) endCol);
		validation = new HSSFDataValidation(addressList, constraint);
		book.setSheetHidden(1, true); // 1隐藏、0显示
		return validation;
	}

调用全例：

参数分别为：fileName--导出的文件名称  titleList--标题行  downData--下拉数据  downRows--下拉行  dateRows--日期格式行  textRows文本格式行

public static boolean createExcelTemp(String fileName, String[] titleList, List<String[]> downData, String[] downRows,
                                          String[] dateRows, String textRows[], HttpServletResponse response) {
		try {
			//创建工作簿
			HSSFWorkbook workbook = new HSSFWorkbook();
			//创建sheet表
			HSSFSheet sheet = workbook.createSheet("sheet1");
			// 设置表格默认列宽度为15个字节
			sheet.setDefaultColumnWidth(15);
			// 生成一个样式
			HSSFCellStyle style = workbook.createCellStyle();
			setStyle(style,workbook);
			// 生成并设置另一个样式
			HSSFCellStyle style2 = workbook.createCellStyle();
			setStyle2(style2,workbook);
			// 声明一个画图的顶级管理器
//			HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
			//生成标题行
			HSSFRow row = sheet.createRow(0);
			int Cell = 0;
			for(int i=0;i<titleList.length;i++) {
				HSSFCell cell = row.createCell(Cell);
				cell.setCellStyle(style);
				HSSFRichTextString text = new HSSFRichTextString(titleList[i]);
				cell.setCellValue(text);
				Cell++;
			}
			//添加下拉列表
			for(int i=0;i<downRows.length;i++) {
				int num = Integer.parseInt(downRows[i]);
				if(downData.get(i).length>=255){
					sheet.addValidationData(setDataValidations(sheet,downData.get(i),1,10000,num,num,workbook));
				}else{
					sheet.addValidationData(setDataValidation(sheet,downData.get(i),1,10000,num,num));
				}
			}
			//添加日期格式校验
			for(int i=0;i<dateRows.length;i++) {
				int num = Integer.parseInt(dateRows[i]);
				sheet.addValidationData(setDate(1,10000,num,num));
			}
			//设置文本格式
			CellStyle textStyle = workbook.createCellStyle();
            DataFormat format = workbook.createDataFormat();  
            textStyle.setDataFormat(format.getFormat("@")); 
			for (int j = 1; j < 10000; j++) {
				 Row rowID = sheet.createRow(j); 
				 for(int i=0;i<textRows.length;i++) {
					 int num = Integer.parseInt(textRows[i]);
					 Cell cell1 = rowID.createCell((short) num);  
		             cell1.setCellStyle(textStyle);
				 }
			}
			System.out.println("导出文件名称："+fileName);
			response.setContentType("application/vnd.ms-excel");
			response.setCharacterEncoding("utf-8");  
			response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("gbk"), "iso8859-1")+".xls");
			OutputStream ouputStream = response.getOutputStream();
			workbook.write(ouputStream);
			ouputStream.flush();   
			ouputStream.close();
			return true;
		} catch (IOException e) {
			e.printStackTrace();
		}
		return false;
	}