单个列表 、多个列表（动态联动）
1 导入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
删除文本
2 展示效果图
2.1 一个下拉框
2.2 两个下拉框（动态联动）
2.3 三个下拉框（动态联动）
2.4 五个下拉框（动态联动）
3 展示代码
public static void main(String[] args) {
createSheetOne();
createSheetTwo();
createSheetThree();
createSheetFive();
}
/**
* 一个下拉框
*/
private static void createSheetOne(){
//导出标头信息
List<String> headers = Arrays.asList("姓名","性别","省","市","区","街道","社区","详情");
// 创建workbook
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
//创建sheet
HSSFSheet sheet = hssfWorkbook.createSheet();
// 创建表头，供用户输入
initHeaders(hssfWorkbook, sheet, headers);
String[] array = { "男","女","未知"};
//添加一个下拉行
writeDataOne(hssfWorkbook,array,0,1);
String downloadPath = "D:/personal/_下拉行.xlsx";
File desc = new File(downloadPath);
if (!desc.getParentFile().exists()) {
desc.getParentFile().mkdirs();
}
FileOutputStream out = null;
try {
out = new FileOutputStream(downloadPath);
hssfWorkbook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (out != null) {
try {
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 两个下拉框（联动）
*/
private static void createSheetTwo(){
//导出标头信息
List<String> headers = Arrays.asList("姓名","性别","省","市","区","街道","社区","详情");
// 创建workbook
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
//创建sheet
HSSFSheet sheet = hssfWorkbook.createSheet();
// 创建表头，供用户输入
initHeaders(hssfWorkbook, sheet, headers);
//一级
List<String> one = new ArrayList<>();
//二级
Map<String, List<String>> two = new HashMap<>();
List<String> two_1 = new ArrayList<>();
List<String> two_2 = new ArrayList<>();
one.add("江苏省");
one.add("浙江省");
two_1.add("南京市");
two_1.add("苏州市");
two_2.add("杭州市");
two_2.add("宁波市");
two.put("江苏省",two_1);
two.put("浙江省",two_2);
HSSFSheet sourceSheet = hssfWorkbook.createSheet("detail");
writeData(hssfWorkbook, sourceSheet, one, two);
setDataValid(hssfWorkbook, sheet, one, "2", "source");
String downloadPath = "D:/personal/_下拉行3.xlsx";
File desc = new File(downloadPath);
if (!desc.getParentFile().exists()) {
desc.getParentFile().mkdirs();
}
FileOutputStream out = null;
try {
out = new FileOutputStream(downloadPath);
hssfWorkbook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (out != null) {
try {
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 三个下拉框（联动）
*/
private static void createSheetThree(){
//导出标头信息
List<String> headers = Arrays.asList("姓名","性别","省","市","区","街道","社区","详情");
// 创建workbook
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
//创建sheet
HSSFSheet sheet = hssfWorkbook.createSheet();
// 创建表头，供用户输入
initHeaders(hssfWorkbook, sheet, headers);
//一级
List<String> one = new ArrayList<>();
//二级
Map<String, List<String>> two = new HashMap<>();
List<String> two_1 = new ArrayList<>();
List<String> two_2 = new ArrayList<>();
List<String> three_1 = new ArrayList<>();
List<String> three_2 = new ArrayList<>();
List<String> three_3 = new ArrayList<>();
List<String> three_4 = new ArrayList<>();
one.add("江苏省");
one.add("浙江省");
two_1.add("南京市");
two_1.add("苏州市");
two_2.add("杭州市");
two_2.add("宁波市");
three_1.add("海曙区");
three_1.add("江北区");
three_2.add("上城区");
three_2.add("下城区");
three_3.add("玄武区");
three_3.add("秦淮区");
three_4.add("虎丘区");
three_4.add("吴中区");
two.put("江苏省",two_1);
two.put("浙江省",two_2);
two.put("杭州市",three_2);
two.put("宁波市",three_1);
two.put("南京市",three_3);
two.put("苏州市",three_4);
HSSFSheet sourceSheet = hssfWorkbook.createSheet("detail");
writeData(hssfWorkbook, sourceSheet, one, two);
setDataValid(hssfWorkbook, sheet, one, "1", "source");
String downloadPath = "D:/personal/_下拉行4.xlsx";
File desc = new File(downloadPath);
if (!desc.getParentFile().exists()) {
desc.getParentFile().mkdirs();
}
FileOutputStream out = null;
try {
out = new FileOutputStream(downloadPath);
hssfWorkbook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (out != null) {
try {
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 五个下拉框（联动）
*/
private static void createSheetFive(){
//导出标头信息
List<String> headers = Arrays.asList("姓名","性别","省","市","区","街道","社区","详情");
// 创建workbook
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
//创建sheet
HSSFSheet sheet = hssfWorkbook.createSheet();
// 创建表头，供用户输入
initHeaders(hssfWorkbook, sheet, headers);
//一级
List<String> one = new ArrayList<>();
//二级
Map<String, List<String>> two = new HashMap<>();
List<String> two_1 = new ArrayList<>();
List<String> two_2 = new ArrayList<>();
List<String> three_1 = new ArrayList<>();
List<String> three_2 = new ArrayList<>();
List<String> three_3 = new ArrayList<>();
List<String> three_4 = new ArrayList<>();
List<String> four_1 = new ArrayList<>();
List<String> four_2 = new ArrayList<>();
List<String> five_1 = new ArrayList<>();
List<String> five_2 = new ArrayList<>();
one.add("江苏省");
one.add("浙江省");
two_1.add("南京市");
two_1.add("苏州市");
two_2.add("杭州市");
two_2.add("宁波市");
three_1.add("海曙区");
three_1.add("江北区");
three_2.add("上城区");
three_2.add("下城区");
three_3.add("玄武区");
three_3.add("秦淮区");
three_4.add("虎丘区");
three_4.add("吴中区");
four_1.add("玄武门街道");
four_1.add("锁金村街道");
//秦虹街道 夫子庙街道
four_2.add("秦虹街道");
four_2.add("夫子庙街道");
//中牌楼社区 康居里社区
five_1.add("中牌楼社区");
five_1.add("康居里社区");
//莲子营社区 东水关社区
five_2.add("莲子营社区");
five_2.add("东水关社区");
two.put("江苏省",two_1);
two.put("浙江省",two_2);
two.put("杭州市",three_2);
two.put("宁波市",three_1);
two.put("南京市",three_3);
two.put("玄武区",four_1);
two.put("秦淮区",four_2);
two.put("秦虹街道",five_1);
two.put("夫子庙街道",five_2);
HSSFSheet sourceSheet = hssfWorkbook.createSheet("detail");
writeData(hssfWorkbook, sourceSheet, one, two);
setDataValid(hssfWorkbook, sheet, one, "3", "source");
String downloadPath = "D:/personal/_下拉行5.xlsx";
File desc = new File(downloadPath);
if (!desc.getParentFile().exists()) {
desc.getParentFile().mkdirs();
}
FileOutputStream out = null;
try {
out = new FileOutputStream(downloadPath);
hssfWorkbook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (out != null) {
try {
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 生成主页面表头
*
* @param wb 工作簿
* @param mainSheet 行
* @param headers 表头
*/
private static void initHeaders(HSSFWorkbook wb, HSSFSheet mainSheet, List<String> headers) {
//表头样式
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
//字体样式
HSSFFont fontStyle = wb.createFont();
fontStyle.setFontName("微软雅黑");
fontStyle.setFontHeightInPoints((short) 12);
style.setFont(fontStyle);
//生成主内容
HSSFRow rowFirst = mainSheet.createRow(0);//第一个sheet的第一行为标题
mainSheet.createFreezePane(0, 1, 0, 1); //冻结第一行
//写标题
for (int i = 0; i < headers.size(); i++) {
HSSFCell cell = rowFirst.createCell(i); //获取第一行的每个单元格
mainSheet.setColumnWidth(i, 4000); //设置每列的列宽
cell.setCellStyle(style); //加样式
cell.setCellValue(headers.get(i)); //往单元格里写数据
}
}
/**
* 添加一个下拉行
* @param hssfWorkbook 工作簿
* @param arr 需要下拉的数据
* @param rowNum 开始行
* @param colNum 列
*/
private static void writeDataOne(HSSFWorkbook hssfWorkbook,String[] arr,int rowNum, int colNum){
Sheet sheetOne = hssfWorkbook.getSheetAt(0); // 获取第一个Sheet
// 创建下拉列表的区域，这里是第一行的第一列 CellRangeAddressList(开始行，终止行，开始列，终止列)
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(rowNum, 65535, colNum, colNum);
// 创建下拉列表数据
DVConstraint constraint = DVConstraint.createExplicitListConstraint(arr);
// 应用下拉列表
HSSFDataValidation dataValidation = new HSSFDataValidation(cellRangeAddressList, constraint);
sheetOne.addValidationData(dataValidation);
}
/**
* 二级联动导出Excel模板处理
*
* @param hssfWorkBook 工作簿
* @param mapSheet
* @param provinceList
* @param cityMap
*/
private static void writeData(HSSFWorkbook hssfWorkBook, HSSFSheet mapSheet, List<String> provinceList, Map<String, List<String>> cityMap) {
//循环将父数据写入siteSheet的第1行中
int siteRowId = 0;
HSSFRow provinceRow = mapSheet.createRow(siteRowId++);
provinceRow.createCell(0).setCellValue("主");
for (int i = 0; i < provinceList.size(); i++) {
provinceRow.createCell(i + 1).setCellValue(provinceList.get(i));
}
// 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
Iterator<String> keyIterator = cityMap.keySet().iterator();
while (keyIterator.hasNext()) {
String key = keyIterator.next();
List<String> son = cityMap.get(key);
HSSFRow siteRow = mapSheet.createRow(siteRowId++);
siteRow.createCell(0).setCellValue(key);
for (int i = 0; i < son.size(); i++) {
siteRow.createCell(i + 1).setCellValue(son.get(i));
}
// 添加名称管理器
String range = getRange(1, siteRowId, son.size());
Name name = hssfWorkBook.createName();
name.setNameName(key);
String formula = mapSheet.getSheetName() + "!" + range;
name.setRefersToFormula(formula);
}
}
/**
* @param HSSFWorkBook
* @param mainSheet
* @param oneList
* @param type 1:三级联动；2二级联动 3五级联动
* @param sheep HSSFWorkBook sheet名称，一个excel有多个联动时，名称不可重复
*/
private static void setDataValid(HSSFWorkbook HSSFWorkBook, HSSFSheet mainSheet, List<String> oneList, String type, String sheep) {
//设置省份下拉
HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper((HSSFSheet) mainSheet);
String[] dataArray = oneList.toArray(new String[0]);
HSSFSheet hidden = HSSFWorkBook.createSheet(sheep);
HSSFCell cell = null;
for (int i = 0, length = dataArray.length; i < length; i++) {
String name = dataArray[i];
HSSFRow row = hidden.createRow(i);
cell = row.createCell(0);
cell.setCellValue(name);
}
Name namedCell = HSSFWorkBook.createName();
namedCell.setNameName(sheep);
namedCell.setRefersToFormula(sheep + "!$A$1:$A$" + dataArray.length);
//加载数据
DVConstraint constraint = DVConstraint.createFormulaListConstraint(sheep);
CellRangeAddressList provinceRangeAddressList = null;
provinceRangeAddressList = new CellRangeAddressList(1, 1000, 2, 2);
DataValidation provinceDataValidation = dvHelper.createValidation(constraint, provinceRangeAddressList);
provinceDataValidation.createErrorBox("error", "请选择正确");
provinceDataValidation.setShowErrorBox(true);
HSSFWorkBook.setSheetHidden(HSSFWorkBook.getSheetIndex(hidden), true);// 设置sheet是否隐藏
mainSheet.addValidationData(provinceDataValidation);
for (int i = 0; i <= 10; i++) {//1:三级联动；2二级联动 3五级联动
// 三级联动处理："B"是指父类所在的列，i+1初始值为1代表从第2行开始，2要与“B”对应，为B的列号加1，假如第一个参数为“C”，那么最后一个参数就3
if ("1".equals(type)) {
setDataValidationThree('C', mainSheet, i + 1, 3);
}if ("2".equals(type)) {
setDataValidationTwo('C', mainSheet, i + 1, 3);
}if ("3".equals(type)) {
setDataValidationFive('C', mainSheet, i + 1, 3);
}
}
}
/**
* 设置五级联动有效性
*
* @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动
* @param sheet
* @param rowNum 行数
* @param colNum 列数
*/
private static void setDataValidationFive(char offset, HSSFSheet sheet, int rowNum, int colNum) {
HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet);
DataValidation dataValidationList1;
DataValidation dataValidationList2;
DataValidation dataValidationList3;
DataValidation dataValidationList4;
dataValidationList1 = getDataValidationByFormula("INDIRECT($" + offset + (rowNum) + ")", rowNum, colNum, dvHelper);
dataValidationList2 = getDataValidationByFormula("INDIRECT($" + (char) (offset + 1) + (rowNum) + ")", rowNum, colNum + 1, dvHelper);
dataValidationList3 = getDataValidationByFormula("INDIRECT($" + (char) (offset + 2) + (rowNum) + ")", rowNum, colNum + 2, dvHelper);
dataValidationList4 = getDataValidationByFormula("INDIRECT($" + (char) (offset + 3) + (rowNum) + ")", rowNum, colNum + 3, dvHelper);
sheet.addValidationData(dataValidationList1);
sheet.addValidationData(dataValidationList2);
sheet.addValidationData(dataValidationList3);
sheet.addValidationData(dataValidationList4);
}
/**
* 设置三级联动有效性
*
* @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动
* @param sheet
* @param rowNum 行数
* @param colNum 列数
*/
private static void setDataValidationThree(char offset, HSSFSheet sheet, int rowNum, int colNum) {
HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet);
DataValidation dataValidationList1;
DataValidation dataValidationList2;
dataValidationList1 = getDataValidationByFormula("INDIRECT($" + offset + (rowNum) + ")", rowNum, colNum, dvHelper);
dataValidationList2 = getDataValidationByFormula("INDIRECT($" + (char) (offset + 1) + (rowNum) + ")", rowNum, colNum + 1, dvHelper);
sheet.addValidationData(dataValidationList1);
sheet.addValidationData(dataValidationList2);
}
/**
* 设置二级联动有效性
*
* @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动
* @param sheet
* @param rowNum 行数
* @param colNum 列数
*/
private static void setDataValidationTwo(char offset, HSSFSheet sheet, int rowNum, int colNum) {
HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet);
DataValidation dataValidationList;
dataValidationList = getDataValidationByFormula("INDIRECT($" + offset + (rowNum) + ")", rowNum, colNum, dvHelper);
sheet.addValidationData(dataValidationList);
}
/**
* 提示信息
* @param formulaString
* @param naturalRowIndex
* @param naturalColumnIndex
* @param dvHelper
* @return
*/
private static DataValidation getDataValidationByFormula(String formulaString, int naturalRowIndex, int naturalColumnIndex, HSSFDataValidationHelper dvHelper) {
DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(formulaString);
CellRangeAddressList regions = new CellRangeAddressList(naturalRowIndex, 65535, naturalColumnIndex, naturalColumnIndex);
HSSFDataValidation data_validation_list = (HSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
data_validation_list.setEmptyCellAllowed(false);
if (data_validation_list instanceof HSSFDataValidation) {
// data_validation_list.setSuppressDropDownArrow(true);
data_validation_list.setShowErrorBox(true);
} else {
// data_validation_list.setSuppressDropDownArrow(false);
}
// 设置输入信息提示信息
data_validation_list.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！");
return data_validation_list;
}
/**
* 计算
*
* @param offset 偏移量，如果给0，表示从A列开始，1，就是从B列
* @param rowId 第几行
* @param colCount 一共多少列
* @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
*/
private 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;
}
}
4 总结
1 通过这种方式，后面不管是有多少级联动的excel模板，都可以实现
2 除了这种方式肯定还有其他的方式，这里暂时就不做过多说明了，能实现功能就行。
3这个代码可能也有待优化的地方，我只是提供一种思路，能优化的更完美更好。
3354
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
