Java单表实现三级联动 java下拉框如何做三级联动

现在网上很多使用poi做excel导出的代码例子，不过我只找到一个三级联动的代码例子，而且几乎所有例子都是默认1，2，3，级菜单名称都不重复的，但是实际上重复很难避免。因为excel的机制是从上到下找匹配的菜单，如果在二级菜单有重复的时候就有可能造成找到的菜单不是我们需要的。所以我用那些现成的例子修改了下，能比较准确的找到对应的菜单，不过这个代码还有bug，就是只能在wps上完美使用，在office上不能下拉三级菜单。

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


public class ExportExcelUtil {
    private static String EXCEL_HIDE_SHEET_NAME = "paraSheet";
    private static String fistName = "";
    private static HSSFCellStyle dataStyle = null;


    //设置下拉列表的内容
    private static String[] companyList = {"一级", "新浪", "腾讯", "网易", "阿里"};
    private static String[] xlList = {"新浪","经理", "美工", "程序员"};
    private static String[] wyList = {"网易", "经理", "美工", "程序员"};
    private static String[] alList = {"阿里", "经理", "美工", "程序员"};
    private static String[] txList = {"腾讯", "经理", "美工", "程序员"};

    private static String[] xljlList = {"新浪","经理", "新浪经理1", "新浪经理2"};
    private static String[] xlmgList = {"新浪","美工", "新浪美工1", "新浪美工2"};
    private static String[] xlcxList = {"新浪","程序员", "新浪程序1", "新浪程序2"};
    private static String[] wyjlList = {"网易","经理", "网易经理1", "网易经理2"};
    private static String[] wymgList = {"网易","美工", "网易美工1", "网易美工2"};
    private static String[] wycxList = {"网易","程序员", "网易程序1", "网易程序2"};
    private static String[] aljlList = {"阿里","经理", "阿里经理1", "阿里经理2", "阿里经理3"};
    private static String[] almgList = {"阿里","美工", "阿里美工1", "阿里美工2", "阿里美工3"};
    private static String[] alcxList = {"阿里","程序员", "阿里程序1", "阿里程序2", "阿里程序3"};
    private static String[] txjlList = {"腾讯","经理", "腾讯经理1", "腾讯经理2", "腾讯经理3"};
    private static String[] txmgList = {"腾讯","美工", "腾讯美工1", "腾讯美工2", "腾讯美工3"};
    private static String[] txcxList = {"腾讯","程序员", "腾讯程序1", "腾讯程序2", "腾讯程序3"};
    private static ArrayList<String[]> levelOne = new ArrayList<String[]>();
    private static ArrayList<String[]> levelTow = new ArrayList<String[]>();
    private static ArrayList<String[]> levelThree = new ArrayList<String[]>();

    private static HSSFWorkbook wb = new HSSFWorkbook();

    public static void main(String[] args) {

        levelOne.add(companyList);

        levelTow.add(xlList);
        levelTow.add(wyList);
        levelTow.add(alList);
        levelTow.add(txList);

        levelThree.add(xljlList);
        levelThree.add(xlmgList);
        levelThree.add(xlcxList);
        levelThree.add(wyjlList);
        levelThree.add(wymgList);
        levelThree.add(wycxList);
        levelThree.add(aljlList);
        levelThree.add(almgList);
        levelThree.add(alcxList);
        levelThree.add(txjlList);
        levelThree.add(txmgList);
        levelThree.add(txcxList);

        //创建内容的集合
        List list1 = new ArrayList();
        list1.add(levelOne);
        list1.add(levelTow);
        list1.add(levelThree);
        //创建公式的集合
        List list2 = new ArrayList();
        list2.add(levelOne);
        list2.add(levelTow);

        HSSFSheet sheet1 = wb.createSheet("sheet1");//工作表对象

        //设置单元格宽度
        setTitleCellStyles(sheet1);
        //设置数据样式
        setDataCellStyles(wb, sheet1);
        //创建一行列头数据
        creatAppRowHead(wb, sheet1);
        //创建隐藏页，flag标识隐藏或展示数据页，false展示，true隐藏
        creatExcelHidePage(wb, false, list1,list2);
        //创建一行数据
        int startRow = 2;
        for (int i = 1; i <= 10; i++) {
            creatAppRow(sheet1, i, "标题"+i, i,startRow);
            startRow ++;
        }

        setDataValidation(wb, 2, new int[]{3, 4, 5});  //设置校验，从第二行开始校验3.4.5列
        FileOutputStream fOut;//输出
        try {
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
            String format = dateFormat.format(new Date());
            fOut = new FileOutputStream("d:\\result_"+format+".xls ");
            wb.write(fOut);
            fOut.flush();
            fOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
    /**
     * @param workbook
     * @param flag     创建的参数sheet是否隐藏
     */
    public static void creatExcelHidePage(Workbook workbook, boolean flag, List list1, List list2) {
        Sheet hideInfoSheet = workbook.createSheet(EXCEL_HIDE_SHEET_NAME);
        int row = 0;
        int temp1 = 0;
        int temp2 = 0;
        /**
         * 这里的temp1和temp2需要注意一下，因为实际生产上因为各种原因某些二级菜单下没有三级菜单，所以这里需要判断，否则公式会错误造成某些三级菜单不能下拉
         */
        //创建隐藏页内容
        for (int i = 0; i < list1.size(); i++) {
            List m = (List) list1.get(i);
            for (int j = 0; j < m.size(); j++) {
                if (i == 0) {
                    String[] t = (String[]) m.get(j);
                    fistName = t[0];
                }
                if (m.get(j) != null) {
                    temp1 ++;
                    Row r = hideInfoSheet.createRow(row++);
                    creatRow(r, (String[]) m.get(j));
                }
            }
        }
        row = 1;
        //创建公式
        for (int i = 0; i < list2.size(); i++) {
            List m = (List) list2.get(i);
            for (int j = 0; j < m.size(); j++) {
                String[] temp = (String[]) m.get(j);
                if (temp != null) {
                    temp2 ++;
                    creatExcelName(workbook, temp[0], row++, 2, temp.length);
                }
            }
        }
        //设置三级校验
        for (int i = 2; i <= 20; i++) {
            Name name;
            name = workbook.createName();
            name.setNameName("三级菜单"+i);
            //这里temp2需要+1是因为一级菜单占一行
            name.setRefersToFormula("OFFSET(paraSheet!$C$"+(temp2+1)+",MATCH(sheet1!$C$"+(i)+"&sheet1!$D$"+(i)+",paraSheet!$A$"+(temp2+1)+":$A$"+(temp1)+"¶Sheet!$B$"+(temp2+1)+":$B$"+(temp1)+",0)-1,,,20)");
        }
        workbook.setSheetHidden(workbook.getSheetIndex(EXCEL_HIDE_SHEET_NAME), flag);
    }

    /**
     * 创建excel名称
     *
     * @param workbook
     */
    private static void creatExcelName(Workbook workbook, String cellName, int row, int start, int end) {
        Name name;
        name = workbook.createName();
        name.setNameName(cellName);
        name.setRefersToFormula(getNamestr(cellName, row, start, end));
    }

    private static String getNamestr(String sheetName, int row, int start, int end) {
        String str = "";
        if(row == 0){
            str = EXCEL_HIDE_SHEET_NAME + "!" + "$" + CellReference.convertNumToColString(start) + "$" + row + ":" + "$" + CellReference.convertNumToColString(end - 1) + "$" + row;
        }else{
            str = EXCEL_HIDE_SHEET_NAME + "!" + "$" + CellReference.convertNumToColString(start - 1) + "$" + row + ":" + "$" + CellReference.convertNumToColString(end - 1) + "$" + row;
        }
        //System.out.println(str);
        return  str;
    }

    /**
     * @param wb
     * @param startRow 超始行
     * @param cols     级联的列号数组
     */
    public static void setDataValidation(Workbook wb, int startRow, int[] cols) {
        int sheetIndex = wb.getNumberOfSheets();
        String[] colNames = getColName(cols);
        if (sheetIndex > 0) {
            Sheet sheet = wb.getSheetAt(0);
            DataValidation data_validation = null;
            DataValidation data_validation1 = null;
            DataValidation data_validation2 = null;
            for (int row = startRow; row < 20; row++) {
                data_validation = getDataValidationByFormula(fistName, row, cols[0]);
                data_validation1 = getDataValidationByFormula(fistName, row, cols[1]);
                data_validation2 = getDataValidationByFormula(fistName, row, cols[2]);
                sheet.addValidationData(data_validation);
                sheet.addValidationData(data_validation1);
                sheet.addValidationData(data_validation2);
                //这里设置的数据有效性校验能在wps上生效
                for (int i = 0; i < colNames.length; i++) {
                    if (i > 0 && i< colNames.length - 1){
                        data_validation = getDataValidationByFormula("INDIRECT($" + colNames[i-1] + "$" + row + ")", row, cols[i]);
                        sheet.addValidationData(data_validation);
                    }
                    if (i == colNames.length - 1) {
                        DataValidation data_validation_list2 = getDataValidationByFormula("三级菜单"+row, row, cols[i]);
                        sheet.addValidationData(data_validation_list2);
                    }
                }
            }
        }
    }

    /**
     * @param formulaString      公式
     * @param naturalRowIndex    验证的行
     * @param naturalColumnIndex 验证的列
     * @return
     */
    public static DataValidation getDataValidationByFormula(String formulaString, int naturalRowIndex, int naturalColumnIndex) {
        //加载下拉列表内容
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(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 = new HSSFDataValidation(regions, constraint);
        //设置输入信息提示信息
        //data_validation.createPromptBox("提示", "请使用下拉方式选择合适的值！");
        //设置输入错误提示信息
        data_validation.createErrorBox("提示", "你输入的值不合法，请下拉选择合适的值！");
        return data_validation;
    }

    static String[] getColName(int[] colIndex) {
        String[] colName = new String[colIndex.length];
        for (int i = 0; i < colIndex.length; i++) {
            colName[i] = CellReference.convertNumToColString(colIndex[i] - 1);
        }
        return colName;
    }


    /**
     * 创建一列应用列头
     * 使用富文本（HSSFRichTextString），这样能在同一单元格内设置多种不同字体
     * @param userinfosheet1
     */
    public static void creatAppRowHead(HSSFWorkbook workbook, HSSFSheet userinfosheet1) {
        HSSFCellStyle 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 redFont = (HSSFFont) workbook.createFont();
        redFont.setFontName("宋体");
        redFont.setFontHeightInPoints((short) 11); //设置字体大小
        redFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
        redFont.setColor(HSSFColor.RED.index);// 红色
        HSSFFont blackFont = (HSSFFont) workbook.createFont();
        blackFont.setFontName("宋体");
        blackFont.setFontHeightInPoints((short) 11); //设置字体大小
        blackFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
        blackFont.setColor(HSSFColor.BLACK.index);// 黑色
        //设置自动换行
        titleStyle.setWrapText(true);

        HSSFRow row = userinfosheet1.createRow(0);
        row.setHeight((short) 550);
        //0.序号
        HSSFCell serialNumberCell = row.createCell(0);
        HSSFRichTextString richString = new HSSFRichTextString("*序号");
        richString.applyFont(0, 1, redFont);
        richString.applyFont(1, 3, blackFont);
        serialNumberCell.setCellValue(richString);
        serialNumberCell.setCellStyle(titleStyle);

        //1.标题
        HSSFCell titleCell = row.createCell(1);
        richString = new HSSFRichTextString("*标题");
        richString.applyFont(0, 1, redFont);
        richString.applyFont(1, 3, blackFont);
        titleCell.setCellValue(richString);
        titleCell.setCellStyle(titleStyle);

        //3.一级项目
        HSSFCell systemCell = row.createCell(2);
        richString = new HSSFRichTextString("*一级菜单");
        richString.applyFont(0, 1, redFont);
        richString.applyFont(1, 5, blackFont);
        systemCell.setCellValue(richString);
        systemCell.setCellStyle(titleStyle);

        //4.二级项目
        HSSFCell groupCell = row.createCell(3);
        richString = new HSSFRichTextString("*二级菜单");
        richString.applyFont(0, 1, redFont);
        richString.applyFont(1, 5, blackFont);
        groupCell.setCellValue(richString);
        groupCell.setCellStyle(titleStyle);

        //4.三级项目
        HSSFCell nameCell = row.createCell(4);
        richString = new HSSFRichTextString("*三级菜单");
        richString.applyFont(0, 1, redFont);
        richString.applyFont(1, 5, blackFont);
        nameCell.setCellValue(richString);
        nameCell.setCellStyle(titleStyle);
    }

    /**
     * 创建一列应用数据
     *
     * @param userinfosheet1
     * @param startRow
     */
    public static void creatAppRow(HSSFSheet userinfosheet1, int num, String titels, int naturalRowIndex, int startRow) {

        HSSFRow row = userinfosheet1.createRow(naturalRowIndex);
        row.setHeight((short) 550);

        //0.序号
        HSSFCell serialNumberCell = row.createCell(0);
        serialNumberCell.setCellValue(num + "");
        serialNumberCell.setCellStyle(dataStyle);

        //1.标题
        HSSFCell titelCell = row.createCell(1);
        titelCell.setCellValue(titels);
        titelCell.setCellStyle(dataStyle);

        //2.一级
        HSSFCell proposeTimeCell = row.createCell(2);
        proposeTimeCell.setCellValue("请选择");
        proposeTimeCell.setCellStyle(dataStyle);

        //3.二级
        HSSFCell systemCell = row.createCell(3);
        systemCell.setCellValue("请选择");
        systemCell.setCellStyle(dataStyle);

        //4.三级
        HSSFCell groupCell = row.createCell(4);
        groupCell.setCellValue("请选择");
        groupCell.setCellStyle(dataStyle);

        //这里设置的数据有效性校验能在office生效
        for (int i = startRow; i < 20; i++) {
            //二级项目是根据一级项目选择
            DataValidation data_validation_list2 = getDataValidationByFormula("INDIRECT($C$"+i+")",i,4);
            userinfosheet1.addValidationData(data_validation_list2);

            //三级项目是根据额外的一条公式选择
            DataValidation data_validation_list3 = getDataValidationByFormula("三级菜单"+i,i,5);
            userinfosheet1.addValidationData(data_validation_list3);

        }
    }




    /**
     * 创建一行数据
     *
     * @param currentRow
     * @param textList
     */
    private static void creatRow(Row currentRow, String[] textList) {
        if (textList != null && textList.length > 0) {
            int i = 0;
            for (int j = 0; j < textList.length; j++) {
                Cell userNameLableCell = currentRow.createCell(i++);
                userNameLableCell.setCellValue(textList[j]);
            }
        }
    }


    /**
     * 单元格宽度
     * @param sheet
     */
    public static void setTitleCellStyles(HSSFSheet sheet) {

        //设置列宽 ,第一个参数代表列id(从0开始),第2个参数代表宽度值
        sheet.setColumnWidth(0, 2500);
        sheet.setColumnWidth(1, 3000);
        sheet.setColumnWidth(2, 4000);
        sheet.setColumnWidth(3, 4000);
        sheet.setColumnWidth(4, 4000);
    }

    /**
     * 数据样式
     *
     * @param workbook
     * @param sheet
     */
    public static 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_CENTER);
        //设置字体
        HSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11); //设置字体大小
        dataStyle.setFont(font);//选择需要用到的字体格式
        //设置自动换行
        dataStyle.setWrapText(true);
    }
}

我在实际生产上是这样生成隐藏页的数据的

private void creatExcelHidePage(HSSFWorkbook workbook, boolean flag) {
		//获取所有步骤信息
		Map<String, List<ProjectEntity>> level = projectElementService.findAllProject();
		ArrayList<String[]> levelOne = new ArrayList<String[]>();
		ArrayList<String[]> levelTow = new ArrayList<String[]>();
		ArrayList<String[]> levelThree = new ArrayList<String[]>();

		ArrayList<String> levelOneArray = new ArrayList<String>();
		levelOneArray.add("一级项目");
		List<ProjectEntity> level1 = level.get("level1");
		List<ProjectEntity> level2 = level.get("level2");
		List<ProjectEntity> level3 = level.get("level3");
		for (int i = 0; i < level1.size(); i++) {
			levelOneArray.add(level1.get(i).getProjectName());

			ArrayList<String> levelTwoArray = new ArrayList<String>();
			levelTwoArray.add(level1.get(i).getProjectName());
			String[] twoArr = null;
			for (int j = 0; j < level2.size(); j++) {
				if (level1.get(i).getProjectId().equals(level2.get(j).getParentId())) {
					levelTwoArray.add(level2.get(j).getProjectName());	//+level2.get(j).getProjectId()level1.get(i).getProjectName()++"_"+i
					twoArr = new String[levelTwoArray.size()];
					levelTwoArray.toArray(twoArr);
				}
				//System.out.println(Arrays.toString(threeArr));
			}
			levelTow.add(twoArr);
			//System.out.println(Arrays.toString(twoArr));
		}

		for (int i = 0; i < level2.size(); i++) {
			ArrayList<String> levelThreeArray = new ArrayList<String>();
			//levelThreeArray.add(level1.get(i).getProjectName());
			for (int j = 0; j < level1.size(); j++) {
				if(level2.get(i).getParentId().equals(level1.get(j).getProjectId())){
					levelThreeArray.add(level1.get(j).getProjectName());	//++"_"+j    level2.get(i).getProjectName()
				}
			}
			String levelTwoprojectName = level2.get(i).getProjectName();

			levelThreeArray.add(levelTwoprojectName);	//+level2.get(i).getProjectId()
			String[] threeArr = null;
			for (int k = 0; k < level3.size(); k++) {
				if (level2.get(i).getProjectId().equals(level3.get(k).getParentId())) {
					levelThreeArray.add(level3.get(k).getProjectName());
					threeArr = new String[levelThreeArray.size()];
					levelThreeArray.toArray(threeArr);
				}
			}
			levelThree.add(threeArr);
		}
		String[] oneArr = new String[levelOneArray.size()];
		levelOneArray.toArray(oneArr);
		levelOne.add(oneArr);

		List resultList = new ArrayList();
		resultList.add(levelOne);
		resultList.add(levelTow);
		resultList.add(levelThree);
		List resultList1 = new ArrayList();
		resultList1.add(levelOne);
		resultList1.add(levelTow);
		//System.out.println(Arrays.toString(oneArr));
		Sheet hideInfoSheet = workbook.createSheet(ExcelExportUtil.EXCEL_HIDE_SHEET_NAME);//隐藏一些信息
		//创建参数sheet 里面内容
		int row = 0;
		int temp1 = 0;
		int temp2 = 0;
		for (int i = 0; i < resultList.size(); i++) {
			List m = (List) resultList.get(i);
			for (int j = 0; j < m.size(); j++) {
				if (i == 0) {
					String[] t = (String[]) m.get(j);
					ExcelExportUtil.fistName = t[0];
				}
				if (m.get(j) != null) {
					temp1 ++;
					Row r = hideInfoSheet.createRow(row++);
					ExcelExportUtil.creatRow(r, (String[]) m.get(j),workbook);
				}
			}
		}
		row = 1;
		for (int i = 0; i < resultList1.size(); i++) {
			List m = (List) resultList1.get(i);
			for (int j = 0; j < m.size(); j++) {
				String[] temp = (String[]) m.get(j);
				if (temp != null) {
					temp2 ++;
					ExcelExportUtil.creatExcelName(workbook, temp[0], row++, 2, temp.length);
				}
			}
		}

		for (int i = 4; i <= 200; i++) {
			Name name;
			name = workbook.createName();
			name.setNameName("三级项目"+i);
			name.setRefersToFormula("OFFSET(paraSheet!$C$"+(temp2+1)+",MATCH(sheet1!$C$"+(i)+"&sheet1!$D$"+(i)+",paraSheet!$A$"+(temp2+1)+":$A$"+(temp1)+"¶Sheet!$B$"+(temp2+1)+":$B$"+(temp1)+",0)-1,,,30)");
		}

		workbook.setSheetHidden(workbook.getSheetIndex(ExcelExportUtil.EXCEL_HIDE_SHEET_NAME), flag);
	}