原文链接：Java poi实现Excel 下拉联动
Java 实现Excel 下拉联动，本示例中实现了省市区乡镇村联动。适用于03版本Excel。
依赖
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<!--模板导入-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>ooxml-schemas</artifactId>
<version>1.1</version>
</dependency>
ExcelTest
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @Author zhouchuangbin
* @Date 2021/8/2
* @Description
*/
public class ExcelTest {
/**
* 影响最大行数
*/
private static final int XLS_MAX_ROW = 60000;
/**
* 导出模板
*
* @param provinceList
* @param areaFatherNameList
* @param areaMap
* @throws IOException
*/
public static void exportHSSFTemplate(List<String> provinceList, List<String> areaFatherNameList, Map<String, List<String>> areaMap) {
String[] tileList = new String[]{"姓名", "手机号", "省", "市", "县", "乡镇", "村"};
//创建工作簿对象
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet");
sheet.setDefaultColumnWidth(18);
HSSFRow row = sheet.createRow(0);
HSSFCellStyle style = getStyle(wb);
HSSFCell cell = null;
int provinceIndex = 0;
for (int i = 0; i < tileList.length; i++) {
String title = tileList[i];
if ("省".equals(title)) {
provinceIndex = i;
}
cell = row.createCell(i);
cell.setCellValue(title);
cell.setCellStyle(style);
}
//创建隐藏目录
createHideSheetHSSF(wb, provinceList, areaFatherNameList, areaMap);
//如果省市区的excel位置不是如上tileList中的位置，需要变更则需要INDIRECT中所在的列名称
// 省规则
DVConstraint provConstraint = DVConstraint.createExplicitListConstraint(provinceList.toArray(new String[]{}));
CellRangeAddressList provRangeAddressList = new CellRangeAddressList(1, XLS_MAX_ROW, provinceIndex, provinceIndex);
DataValidation provinceDataValidation = new HSSFDataValidation(provRangeAddressList, provConstraint);
provinceDataValidation.createErrorBox("error", "请选择正确的省份");
sheet.addValidationData(provinceDataValidation);
//市规则
CellRangeAddressList cityRange = new CellRangeAddressList(1, XLS_MAX_ROW, provinceIndex + 1, provinceIndex + 1);
DataValidation cityValidation = new HSSFDataValidation(cityRange, DVConstraint.createFormulaListConstraint("INDIRECT($C1)"));
cityValidation.createErrorBox("error", "请选择正确的市");
sheet.addValidationData(cityValidation);
//区县规则
CellRangeAddressList areaRange = new CellRangeAddressList(1, XLS_MAX_ROW, provinceIndex + 2, provinceIndex + 2);
DataValidation areaValidation = new HSSFDataValidation(areaRange, DVConstraint.createFormulaListConstraint("INDIRECT($D1)"));
areaValidation.createErrorBox("error", "请选择正确的县");
sheet.addValidationData(areaValidation);
//区县乡镇
CellRangeAddressList townRange = new CellRangeAddressList(1, XLS_MAX_ROW, provinceIndex + 3, provinceIndex + 3);
DataValidation townValidation = new HSSFDataValidation(townRange, DVConstraint.createFormulaListConstraint("INDIRECT($E1)"));
townValidation.createErrorBox("error", "请选择正确的乡镇");
sheet.addValidationData(townValidation);
//村规则
CellRangeAddressList villageRange = new CellRangeAddressList(1, XLS_MAX_ROW, provinceIndex + 4, provinceIndex + 4);
DataValidation villageValidation = new HSSFDataValidation(villageRange, DVConstraint.createFormulaListConstraint("INDIRECT($F1)"));
villageValidation.createErrorBox("error", "请选择正确的村");
sheet.addValidationData(villageValidation);
FileOutputStream fileOut;
try {
fileOut = new FileOutputStream("d://excel_template.xls");
wb.write(fileOut);
fileOut.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 创建隐藏页
*
* @param wb
* @param provinceArr
* @param areaFatherNameArr
* @param areaMap
*/
public static void createHideSheetHSSF(HSSFWorkbook wb, List<String> provinceArr, List<String> areaFatherNameArr, Map<String, List<String>> areaMap) {
//创建一个专门用来存放地区信息的隐藏sheet页
HSSFSheet hideSheet = wb.createSheet("area");
int rowId = 0;
// 设置第1行，存省的信息
Row provinceRow = hideSheet.createRow(rowId++);
for (int i = 0; i < provinceArr.size(); i++) {
Cell provinceCell = provinceRow.createCell(i);
provinceCell.setCellValue(provinceArr.get(i));
}
// 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
for (int i = 0; i < areaFatherNameArr.size(); i++) {
String key = areaFatherNameArr.get(i);
List<String> son = areaMap.get(key);
Row prow = hideSheet.createRow(rowId++);
prow.createCell(0).setCellValue(key);
System.out.println(key);
for (int j = 0; j < son.size(); j++) {
Cell cell = prow.createCell(j + 1);
cell.setCellValue(son.get(j));
}
// 添加名称管理器
String range = getRange(1, rowId, son.size());
Name name = wb.createName();
//key不可重复
name.setNameName(key);
String formula = "area!" + range;
name.setRefersToFormula(formula);
}
wb.setSheetHidden(1, true);
}
/**
* 计算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;
}
}
/**
* 样式
*
* @param wb
* @return
*/
private static HSSFCellStyle getStyle(HSSFWorkbook wb) {
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
//cellStyle.setWrapText(true);//自动换行
HSSFFont font = wb.createFont();
font.setFontName("宋体");//设置字体名称
font.setFontHeightInPoints((short) 12);//字体大小
font.setBold(true);
cellStyle.setFont(font);
return cellStyle;
}
public static void main(String[] args) {
//省数据
List<String> provinceArr = Arrays.asList("广东省", "河北省");
//所有区域父类（只到最后一层的上级即可）
List<String> areaFatherNameArr = Arrays.asList("广东省", "河北省", "深圳市", "衡水市", "宝安区", "武强县", "西乡街道办事处", "街关镇");
//父子类关系
Map<String, List<String>> areaMap = new HashMap<>();
areaMap.put("广东省", Arrays.asList("深圳市"));
areaMap.put("河北省", Arrays.asList("衡水市"));
areaMap.put("深圳市", Arrays.asList("宝安区"));
areaMap.put("衡水市", Arrays.asList("武强县"));
areaMap.put("宝安区", Arrays.asList("西乡街道办事处"));
areaMap.put("武强县", Arrays.asList("街关镇"));
areaMap.put("西乡街道办事处", Arrays.asList("西乡社区居委会", "固戍社区居委会", "南昌社区居委会", "永丰社区居委会"));
areaMap.put("街关镇", Arrays.asList("五里屯村委会", "北关村委会", "西关村委会", "南关村委会"));
exportHSSFTemplate(provinceArr, areaFatherNameArr, areaMap);
}
}
