POI 导出Excel 下拉列表多级联动

原文：https://blog.csdn.net/m0_37956938/article/details/78084503

一、主要步骤
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("县");
效果如图：

POI 导出Excel 下拉列表多级联动

2 创建隐藏sheet,列出下拉列表中需要展示的项信息

//得到第一级省名称，放在列表里
String[] provinceArr = {"江苏省","安徽省"};
//依次列出各省的市、各市的县
String[] cityJiangSu = {"南京市","苏州市","盐城市"};
String[] cityAnHui = {"合肥市","安庆市"};
String[] countyNanjing = {"六合县","江宁县"};
String[] countySuzhou = {"姑苏区","园区"};
String[] countyYancheng = {"响水县","射阳县"};
String[] countyLiuhe = {"瑶海区","庐阳区"};
String[] countyAnQing = {"迎江区","大观区"};
//将有子区域的父区域放到一个数组中
String[] areaFatherNameArr ={"江苏省","安徽省","南京市","苏州市","盐城市","合肥市","安庆市"};
Map<String,String[]> areaMap = new HashMap<String, String[]>();
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]);
}

POI 导出Excel 下拉列表多级联动
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 <= 25) {
char end = (char)(start + colCount - 1);
return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
} else {
char endPrefix = 'A';
char endSuffix = 'A';
if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-51之间，包括边界（仅两次字母表计算）
if ((colCount - 25) % 26 == 0) {// 边界值
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;
}
}

添加完名称管理器效果如图：

POI 导出Excel 下拉列表多级联动

4 利用名称管理器关联到隐藏sheet中信息

XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
// 举例：若formulaString = "INDIRECT($A$2)" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据，
//如果A2是江苏省，那么此处就是江苏省下的市信息。
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(formulaString);

设置关联后如图：此时B5单元格受A5单元格的值影响

POI 导出Excel 下拉列表多级联动
jar包：

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>

完整代码如下：

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 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 <= 25) {
char end = (char)(start + colCount - 1);
return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
} else {
char endPrefix = 'A';
char endSuffix = 'A';
if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-51之间，包括边界（仅两次字母表计算）
if ((colCount - 25) % 26 == 0) {// 边界值
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;
}
---------------------
原文：https://blog.csdn.net/m0_37956938/article/details/78084503

秒客网

POI 导出Excel 下拉列表多级联动

相关文章