| POI 导出Excel 下拉列表多级联动（以省市县三级联动为例） | 您所在的位置：网站首页 › 下拉菜单三级联动模板 › POI 导出Excel 下拉列表多级联动（以省市县三级联动为例） |
POI 导出Excel 下拉列表多级联动（以省市县三级联动为例）
|
一、主要步骤 1 创建workbook,创建要展示的sheet,即需要用户填写的sheet,设置表头等必须字段 2 创建隐藏sheet,列出下拉列表中需要展示的项信息 3 名称管理器设置name 4 利用名称管理器关联到隐藏sheet中信息 5 设置有效性，添加验证 二、详细步骤 可以先熟悉在excel文件中新建多级联动做法，可参照Excel多级下拉菜单联动自动匹配内容 （http://blog.csdn.net/dearmorning/article/details/54312084） 级联也可参照http://blog.csdn.net/readiay/article/details/52397419，写的很详细，包含使用POI写excel，并且添加数据校验，以及省市区级联菜单设置，分Excel2003与Excel2007两个版本这些问题的具体步骤和代码，本文主要是拆分详解excel级联 1 创建workbook,创建要展示的sheet,即需要用户填写的sheet,设置表头等必须字段 // 创建一个excel Workbook book = new XSSFWorkbook(); // 创建需要用户填写的数据页 // 设计表头 Sheet sheetPro = book.createSheet("省市县"); Row row0 = sheetPro.createRow(0); row0.createCell(0).setCellValue("省"); row0.createCell(1).setCellValue("市"); row0.createCell(2).setCellValue("县");效果如图： 未隐藏前效果如图：图中每种颜色方框内省市县互相对应，并且不可重复 3 名称管理器设置name // 添加名称管理器 String range = getRange(1, rowId, son.length); Name name = book.createName(); //key不可重复,将父区域名作为key name.setNameName(key); String formula = "area!" + range; name.setRefersToFormula(formula); /** * 计算formula * @param offset 偏移量，如果给0，表示从A列开始，1，就是从B列 * @param rowId 第几行 * @param colCount 一共多少列 * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1 * */ public String getRange(int offset, int rowId, int colCount) { char start = (char)('A' + offset); if (colCount // 边界值 endSuffix = (char)('A' + 25); } else { endSuffix = (char)('A' + (colCount - 25) % 26 - 1); } } else {// 51以上 if ((colCount - 25) % 26 == 0) { endSuffix = (char)('A' + 25); endPrefix = (char)(endPrefix + (colCount - 25) / 26 - 1); } else { endSuffix = (char)('A' + (colCount - 25) % 26 - 1); endPrefix = (char)(endPrefix + (colCount - 25) / 26); } } return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId; } }添加完名称管理器效果如图： 设置关联后如图：此时B5单元格受A5单元格的值影响 jar包： org.apache.poi poi 3.14 org.apache.poi poi-ooxml 3.14完整代码如下： package com.cc.cs; import java.io.FileOutputStream; import java.util.HashMap; import java.util.Map; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DataValidation; import org.apache.poi.ss.usermodel.DataValidationConstraint; import org.apache.poi.ss.usermodel.Name; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddressList; import org.apache.poi.util.IOUtils; import org.apache.poi.xssf.usermodel.XSSFDataValidation; import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint; import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class NewTest { public static void main(String[] args) { Cascade(); } public static void Cascade() { // 创建一个excel @SuppressWarnings("resource") Workbook book = new XSSFWorkbook(); // 创建需要用户填写的sheet XSSFSheet sheetPro = (XSSFSheet) book.createSheet("省市县"); Row row0 = sheetPro.createRow(0); row0.createCell(0).setCellValue("省"); row0.createCell(1).setCellValue("市"); row0.createCell(2).setCellValue("区"); //得到第一级省名称，放在列表里 String[] provinceArr = {"江苏省","安徽省"}; //依次列出各省的市、各市的县 String[] cityJiangSu = {"南京市","苏州市","盐城市"}; String[] cityAnHui = {"合肥市","安庆市"}; String[] countyNanjing = {"六合县","江宁县"}; String[] countySuzhou = {"姑苏区","园区"}; String[] countyYancheng = {"响水县","射阳县"}; String[] countyLiuhe = {"瑶海区","庐阳区"}; String[] countyAnQing = {"迎江区","大观区"}; //将有子区域的父区域放到一个数组中 String[] areaFatherNameArr ={"江苏省","安徽省","南京市","苏州市","盐城市","合肥市","安庆市"}; Map areaMap = new HashMap(); areaMap.put("江苏省", cityJiangSu); areaMap.put("安徽省",cityAnHui); areaMap.put("南京市",countyNanjing); areaMap.put("苏州市", countySuzhou); areaMap.put("盐城市",countyYancheng); areaMap.put("合肥市",countyYancheng); areaMap.put("合肥市", countyLiuhe); areaMap.put("安庆市",countyAnQing); //创建一个专门用来存放地区信息的隐藏sheet页 //因此也不能在现实页之前创建，否则无法隐藏。 Sheet hideSheet = book.createSheet("area"); //这一行作用是将此sheet隐藏，功能未完成时注释此行,可以查看隐藏sheet中信息是否正确 //book.setSheetHidden(book.getSheetIndex(hideSheet), true); int rowId = 0; // 设置第一行，存省的信息 Row provinceRow = hideSheet.createRow(rowId++); provinceRow.createCell(0).setCellValue("省列表"); for(int i = 0; i < provinceArr.length; i ++){ Cell provinceCell = provinceRow.createCell(i + 1); provinceCell.setCellValue(provinceArr[i]); } // 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。 for(int i = 0;i < areaFatherNameArr.length;i++){ String key = areaFatherNameArr[i]; String[] son = areaMap.get(key); Row row = hideSheet.createRow(rowId++); row.createCell(0).setCellValue(key); for(int j = 0; j < son.length; j ++){ Cell cell = row.createCell(j + 1); cell.setCellValue(son[j]); } // 添加名称管理器 String range = getRange(1, rowId, son.length); Name name = book.createName(); //key不可重复 name.setNameName(key); String formula = "area!" + range; name.setRefersToFormula(formula); } XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheetPro); // 省规则 DataValidationConstraint provConstraint = dvHelper.createExplicitListConstraint(provinceArr); // 四个参数分别是：起始行、终止行、起始列、终止列 CellRangeAddressList provRangeAddressList = new CellRangeAddressList(1, 20, 0, 0); DataValidation provinceDataValidation = dvHelper.createValidation(provConstraint, provRangeAddressList); //验证 provinceDataValidation.createErrorBox("error", "请选择正确的省份"); provinceDataValidation.setShowErrorBox(true); provinceDataValidation.setSuppressDropDownArrow(true); sheetPro.addValidationData(provinceDataValidation); //对前20行设置有效性 for(int i = 2;i < 20;i++){ setDataValidation("A" ,sheetPro,i,2); setDataValidation("B" ,sheetPro,i,3); } FileOutputStream os = null; try { os = new FileOutputStream("D:/testbbb.xlsx"); book.write(os); } catch (Exception e) { e.printStackTrace(); } finally { IOUtils.closeQuietly(os); } } /** * 设置有效性 * @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动 * @param sheet * @param rowNum 行数 * @param colNum 列数 */ public static void setDataValidation(String offset,XSSFSheet sheet, int rowNum,int colNum) { XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet); DataValidation data_validation_list; data_validation_list = getDataValidationByFormula( "INDIRECT($" + offset + (rowNum) + ")", rowNum, colNum,dvHelper); sheet.addValidationData(data_validation_list); } /** * 加载下拉列表内容 * @param formulaString * @param naturalRowIndex * @param naturalColumnIndex * @param dvHelper * @return */ private static DataValidation getDataValidationByFormula( String formulaString, int naturalRowIndex, int naturalColumnIndex,XSSFDataValidationHelper dvHelper) { // 加载下拉列表内容 // 举例：若formulaString = "INDIRECT($A$2)" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据， //如果A2是江苏省，那么此处就是江苏省下的市信息。 XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.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); // 数据有效性对象 // 绑定 XSSFDataValidation data_validation_list = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions); data_validation_list.setEmptyCellAllowed(false); if (data_validation_list instanceof XSSFDataValidation) { data_validation_list.setSuppressDropDownArrow(true); data_validation_list.setShowErrorBox(true); } else { data_validation_list.setSuppressDropDownArrow(false); } // 设置输入信息提示信息 data_validation_list.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！"); // 设置输入错误提示信息 //data_validation_list.createErrorBox("选择错误提示", "你输入的值未在备选列表中，请下拉选择合适的值！"); return data_validation_list; } /** * 计算formula * @param offset 偏移量，如果给0，表示从A列开始，1，就是从B列 * @param rowId 第几行 * @param colCount 一共多少列 * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1 * */ public static String getRange(int offset, int rowId, int colCount) { char start = (char)('A' + offset); if (colCount // 边界值 endSuffix = (char)('A' + 25); } else { endSuffix = (char)('A' + (colCount - 25) % 26 - 1); } } else {// 51以上 if ((colCount - 25) % 26 == 0) { endSuffix = (char)('A' + 25); endPrefix = (char)(endPrefix + (colCount - 25) / 26 - 1); } else { endSuffix = (char)('A' + (colCount - 25) % 26 - 1); endPrefix = (char)(endPrefix + (colCount - 25) / 26); } } return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId; } } } |
2 创建隐藏sheet,列出下拉列表中需要展示的项信息![按图示列出级联下拉列表所需信息]
4 利用名称管理器关联到隐藏sheet中信息
【本文地址】
公司简介
联系我们