一张数据库表的多级分类java源码_Java基于POI实现excel任意多级联动下拉列表——支持从数据库查询出多级数据后直接生成【附源码】...

CREATE TABLE `area` (

`area_id`intNOT NULL AUTO_INCREMENT,

`area_name` varchar(64) NOT NULL,

`area_desc` varchar(256) DEFAULT NULL,

`parent_area_id`intDEFAULT NULL,

PRIMARY KEY (`area_id`)

) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

初始化数据

省级数据：

NSERT INTO area(area_name,area_desc) VALUES ("四川","四川省"),("浙江","浙江省"),("广东","广东省");

市级数据：

INSERT INTO area(area_name,area_desc, parent_area_id) VALUES ("南充","南充市", 1),("成都","成都市", 1), ("广元","广元市", 1),("杭州","杭州市", 2),("温州","温州市", 2),("绍兴","绍兴市", 2),("宁波","宁波市", 2),("广州","广州市", 3),("佛山","佛山市", 3);

县级数据：

INSERT INTO area(area_name,area_desc, parent_area_id) VALUES ("西充","西充县", 4),("仪陇","仪陇县", 4),("武侯","武侯区", 5),("龙泉","龙泉区", 5),("青羊","青羊区", 5),("剑阁","剑阁县", 6),("青川","青川县", 6);

INSERT INTO area(area_name,area_desc, parent_area_id) VALUES ("西湖","西湖区", 7),("江干","江干区", 7),("鹿城","鹿城区", 8),("龙湾","龙湾区", 8),("上虞","上虞区", 9),("越城","越城区", 9),("江北","江北区", 10),("镇海","镇海区", 10);

INSERT INTO area(area_name,area_desc, parent_area_id) VALUES ("白云","白云区", 11),("天河","天河区", 11),("顺德","顺德区", 12),("南海","南海区", 12);

(2)实现逻辑说明

递归查询数据源表(area),构建“以parent_area_id为key，子区域名称列表为value的HashMap”

(a)第一级区域查询，根据parent_area_id为空的查询出第一级区域列表

List firstAreaNames = newArrayList();

String queryArea0= "select area_id, area_name from area where parent_area_id IS NULL";

Map area0List = new LinkedHashMap<>();int areaLevel = 1;

jdbc.query(queryArea0, rs->{

area0List.put(rs.getInt("area_id"), rs.getString("area_name"));

firstAreaNames.add(rs.getString("area_name"));

});

areaList.put("一级区域", firstAreaNames);

以区域ID为key,子区域名称列表为value的HashMap定义如下: private Map> areaList = new LinkedHashMap<>();

(b)传入parent_area_id查询子区域area_id和area_name，如此反复递归，直到没有子区域为止

Map subAreas =queryAreaInfo(area0List);while (subAreas.keySet().size() > 0) {

areaLevel++;

subAreas=queryAreaInfo(subAreas);

}

queryAreaInfo函数定义：

private Map queryAreaInfo(MapparentAreas) {

Map subAreas = new LinkedHashMap<>();for(Integer areaId : parentAreas.keySet()) {

String queryArea= "select area_id, area_name from area where parent_area_id = '" + areaId.intValue() + "'";

List areaNames = newArrayList();

jdbc.query(queryArea, rs->{

subAreas.put(rs.getInt("area_id"), rs.getString("area_name"));

areaNames.add(rs.getString("area_name"));

});if (areaNames.size() > 0) {

areaList.put(parentAreas.get(areaId), areaNames);

}

}returnsubAreas;

}

注：必须用LinkedHashMap，否则初始化数据会重新排序，导致后续生成下拉列表出错

(c)根据计算出的区域层级，动态构造首行标题栏

for (int i = 1; i <= areaTotalLevel; i++) {

String cellValue= convertToChineseNumber(i) + "级区域";

firstRow.createCell(columnIndex++).setCellValue(cellValue);

}

根据构建的“以parent_area_id为key，子区域名称列表为value的HashMap”，创建名称管理器和数据验证

#zs#** 构造名称管理器和数据验证及公式

*

*@paramworkbook 目标工作簿

*@paramfile 输出的文件全路径

*@paramdropDownDataSource 以父级id为key,子级名称列表为value的集合

*@paramdataSourceSheetName 作为数据源的工作表名称

*@paramcolumnStep 起始列的列号(以下表0为初始列)

*@paramtotalLevel 总共的层级数量

*@throwsIOException

*@throwsInvalidFormatException#fzs#

private void Cascade(Workbook workbook, File file, Map>dropDownDataSource,final String dataSourceSheetName, final int columnStep, final int totalLevel) throwsIOException, InvalidFormatException {

Sheet dataSourceSheet=workbook.createSheet(dataSourceSheetName);

workbook.setSheetHidden(workbook.getSheetIndex(dataSourceSheet),true);

Row headerRow= dataSourceSheet.createRow(0);

String[] firstValidationArray= null;boolean firstTime = true;int columnIndex = 0;//构造名称管理器数据源

for(String key : dropDownDataSource.keySet()) {

Cell cell=headerRow.createCell(columnIndex);

cell.setCellValue(key);if (dropDownDataSource.get(key) == null || dropDownDataSource.get(key).size() == 0) {continue;

}

ArrayList values =(ArrayList) dropDownDataSource.get(key);if(firstTime) {

firstValidationArray= values.toArray(newString[values.size()]);

}int dataRowIndex = 1;for(String value : values) {

Row row= firstTime ?dataSourceSheet.createRow(dataRowIndex) : dataSourceSheet.getRow(dataRowIndex);if (row == null) {

row=dataSourceSheet.createRow(dataRowIndex);

}

row.createCell(columnIndex).setCellValue(value);

dataRowIndex++;

}//构造名称管理器

String range = buildRange(columnIndex, 2, values.size());

Name name=workbook.createName();

name.setNameName(key);

String formula= dataSourceSheetName + "!" +range;

name.setRefersToFormula(formula);

columnIndex++;

firstTime= false;

}

Sheet assetSheet= workbook.getSheetAt(0);//第一级设置DataValidation

XSSFDataValidationHelper dvHelper = newXSSFDataValidationHelper((XSSFSheet) assetSheet);

DataValidationConstraint firstConstraint=dvHelper.createExplicitListConstraint(firstValidationArray);

CellRangeAddressList firstRangeAddressList= new CellRangeAddressList(1, MAX_ROWS, 0 + columnStep, 0 +columnStep);

DataValidation firstDataValidation=dvHelper.createValidation(firstConstraint, firstRangeAddressList);

firstDataValidation.setSuppressDropDownArrow(true);

assetSheet.addValidationData(firstDataValidation);//剩下的层级设置DataValidation

for (int i = 1; i < totalLevel; i++) {char[] offset = new char[1];

offset[0] = (char) ('A' + columnStep + i - 1);

String formulaString= buildFormulaString(new String(offset), 2);

XSSFDataValidationConstraint dvConstraint=(XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(formulaString);

CellRangeAddressList regions= new CellRangeAddressList(1, MAX_ROWS, 0 + columnStep + i, 0 + columnStep +i);

XSSFDataValidation dataValidationList=(XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);

dataValidationList.setSuppressDropDownArrow(true);

assetSheet.addValidationData(dataValidationList);

}//输出数据到文件

FileOutputStream os = null;try{

os= newFileOutputStream(file);

workbook.write(os);

}catch(Exception e) {

e.printStackTrace();

}finally{

IOUtils.closeQuietly(os);

}

}

说明：

构造名称引用的数据源区域：

private String buildRange(int offset, int startRow, introwCount) {char start = (char) ('A' +offset);return "$" + start + "$" + startRow + ":$" + start + "$" + (startRow + rowCount - 1);

}

构造indirect公式：

private String buildFormulaString(String offset, introwNum) {return "INDIRECT($" + offset + (rowNum) + ")";

}

最终实现效果

名称管理器的数据源工作表：

名称管理器：

生成的模板:

附：

本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。

*******************************************************************************************

精力有限，想法太多，专注做好一件事就行

我只是一个程序猿。5年内把代码写好，技术博客字字推敲，坚持零拷贝和原创

写博客的意义在于锻炼逻辑条理性，加深对知识的系统性理解，锻炼文笔，如果恰好又对别人有点帮助，那真是一件令人开心的事

*******************************************************************************************