Tree数据的处理
如题,处理List数据,行程Tree数据
1 实体类Deme代码
@Data
@AllArgsConstructor
@ToString(callSuper = true)
@NoArgsConstructor
@Builder
@JsonInclude(JsonInclude.Include.NON_EMPTY)
public class TreeBaseEntity {
private String id;
private String parentId;
private String name;
private Integer isLast;
private Integer sort;
private String ancestorIds;
private Integer level;
private List<TreeBaseEntity> children;
private Integer excelSize;
//获取子的长度
public Integer getChildrenCount(){
return this.children.size();
}
}
2 TreeUtils工具类编写,List转换成Tree数据工具类
ublic class TreeUtils {
/**
*
* @param list 原始数据
* @return 处理好的树形结构数据
*/
public static List<TreeBaseEntity> merge(List<TreeBaseEntity> list) {
return list.stream().filter(v -> v.getParentId()==null || v.getParentId().equals("")).map(s -> {
s.setChildren(getChildren(s, list));
return s;
//默认从第到高 升序排列
}).sorted(Comparator.comparing(TreeBaseEntity::getSort)).collect(Collectors.toList());
}
/**
* 递归查询子节点
* @param vo 根节点
* @param list 所有节点
* @return 根节点信息
*/
public static List<TreeBaseEntity> getChildren(TreeBaseEntity vo, List<TreeBaseEntity> list) {
List<TreeBaseEntity> children = list.stream().filter(s -> vo.getId().equals(s.getParentId())).map(sysDeptInfo -> {
sysDeptInfo.setChildren(getChildren(sysDeptInfo,list));
return sysDeptInfo;
}).sorted(Comparator.comparing(TreeBaseEntity::getSort)).collect(Collectors.toList());
return children;
}
/**
* 对层级数据进行操作
*/
}
##3 代码
//得到需要放入Excel的长度
for (TreeBaseEntity treeBaseEntity : list) {
treeBaseEntity.setExcelSize(this.setChildrenCount(treeBaseEntity.getId(), list));
}
private int setChildrenCount(String id,List<TreeBaseEntity> list) {
int count = 0;
for (TreeBaseEntity v : list) {
List<String> ids = Arrays.asList(v.getAncestorIds().split(","));
if (ids.contains(id) && v.getIsLast() ==1){
count++;
}
}
return count == 0? 1: count;
}
public static final ConcurrentHashMap<Integer,List<String>> levelMap = new ConcurrentHashMap<>();
往Map里面存放数据 key =>层数 value=> 每一层的关系
levelMap.clear();
for (int a = 0; a < 3; a++){
levelMap.put(a+1, Lists.newArrayList());
}
4 获取最大层数
Integer maxHeadLevel = Collections.max(headDataList.stream().map(v -> Integer.valueOf(v.getLevel())).collect(Collectors.toList()));
this.dellTheData(TreeUtils.merge(list),3);
5 进行map的数据填充,key=>就是层数,value就是List<>数据
private void dellTheData(List<TreeBaseEntity> list,Integer maxHeadLevel){
if (list!=null) {
for (int i = 0; i < list.size(); i++) {
TreeBaseEntity vo = list.get(i);
List<String> names = levelMap.get(vo.getLevel());
for (int a = 0; a<vo.getExcelSize(); a++){
names.add(vo.getId());
}
if (vo.getLevel() < maxHeadLevel && vo.getIsLast() ==1){
for (int a = vo.getLevel(); a < maxHeadLevel; a++){
//得到后面不是自己的 加上
List<String> strings = levelMap.get(a + 1);
strings.add(vo.getName());
levelMap.put(a + 1, strings);
}
}else {
levelMap.put(vo.getLevel(), names);
}
dellTheData(vo.getChildren(),maxHeadLevel);
}
}
}
6 Impl代码
@Service
public class TreeServiceImpl implements ITreeService {
public static final ConcurrentHashMap<Integer,List<String>> levelMap = new ConcurrentHashMap<>();
@Override
public void dellWithData() {
List<TreeBaseEntity> list = new ArrayList<TreeBaseEntity>() {{
add(TreeBaseEntity.builder().id("1").level(1).ancestorIds("1").isLast(0).sort(1).build());
add(TreeBaseEntity.builder().id("11").level(2).ancestorIds("1,11").isLast(0).sort(1).parentId("1").build());
add(TreeBaseEntity.builder().id("12").level(2).ancestorIds("1,12").isLast(0).sort(2).parentId("1").build());
add(TreeBaseEntity.builder().id("111").level(3).ancestorIds("1,11,111").sort(1).isLast(1).parentId("11").isLast(1).build());
add(TreeBaseEntity.builder().id("112").level(3).ancestorIds("1,11,112").sort(2).isLast(1).parentId("11").isLast(1).build());
add(TreeBaseEntity.builder().id("121").level(3).ancestorIds("1,12,121").sort(1).isLast(1).parentId("12").isLast(1).build());
add(TreeBaseEntity.builder().id("122").level(3).ancestorIds("1,12,122").sort(2).isLast(1).parentId("12").isLast(1).build());
add(TreeBaseEntity.builder().id("123").level(3).ancestorIds("1,12,123").sort(3).isLast(1).parentId("12").isLast(1).build());
}};
List<TreeBaseEntity> treeResult = TreeUtil.listToTree(list,
TreeBaseEntity::setChildren,
TreeBaseEntity::getId,
TreeBaseEntity::getParentId,
(l) -> l.getParentId() == null || l.getParentId().equals(""));
//得到需要放入Excel的长度
for (TreeBaseEntity treeBaseEntity : list) {
treeBaseEntity.setExcelSize(this.setChildrenCount(treeBaseEntity.getId(), list));
}
levelMap.clear();
for (int a = 0; a < 3; a++){
levelMap.put(a+1, Lists.newArrayList());
}
this.dellTheData(TreeUtils.merge(list),3);
System.err.println(levelMap);
}
private int setChildrenCount(String id,List<TreeBaseEntity> list) {
int count = 0;
for (TreeBaseEntity v : list) {
List<String> ids = Arrays.asList(v.getAncestorIds().split(","));
if (ids.contains(id) && v.getIsLast() ==1){
count++;
}
}
return count == 0? 1: count;
}
private void dellTheData(List<TreeBaseEntity> list,Integer maxHeadLevel){
if (list!=null) {
for (int i = 0; i < list.size(); i++) {
TreeBaseEntity vo = list.get(i);
List<String> names = levelMap.get(vo.getLevel());
for (int a = 0; a<vo.getExcelSize(); a++){
names.add(vo.getId());
}
if (vo.getLevel() < maxHeadLevel && vo.getIsLast() ==1){
for (int a = vo.getLevel(); a < maxHeadLevel; a++){
//得到后面不是自己的 加上
List<String> strings = levelMap.get(a + 1);
strings.add(vo.getName());
levelMap.put(a + 1, strings);
}
}else {
levelMap.put(vo.getLevel(), names);
}
dellTheData(vo.getChildren(),maxHeadLevel);
}
}
}
}
7 实现效果
8 导入Excel代码,可以参考我的,下面是Excel格式代码,省的说找不到
/**
* @Created by xinyu liang
* @Data: 2022/8/23 15:51
*/
public class ExcelUtils {
public static void setCellValue(Row row, Object value, CellStyle cellStyle, int cellIndex){
Cell cell = row.createCell(cellIndex);
if(value instanceof HSSFRichTextString){
cell.setCellValue((HSSFRichTextString)value);
}else if(value instanceof String){
cell.setCellValue((String)value);
}else if(value instanceof Boolean){
cell.setCellValue((Boolean)value);
}else if(value instanceof Double){
cell.setCellValue((Double)value);
}else if(value instanceof Date){
cell.setCellValue((Date)value);
}
cell.setCellStyle(cellStyle);
}
public static void mergeRegion(int firstRow, int lastRow, int firstCol, int lastCol, Sheet sheet) {
CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
sheet.addMergedRegion(region);
setBorderStyle(BorderStyle.THIN,region,sheet);
}
public static void setBorderStyle(BorderStyle border, CellRangeAddress region, Sheet sheet){
RegionUtil.setBorderBottom(border, region, sheet); //下边框
RegionUtil.setBorderLeft(border, region, sheet); //左边框
RegionUtil.setBorderRight(border, region, sheet); //右边框
RegionUtil.setBorderTop(border, region, sheet); //上边框
}
/**
* 创建单元格居中对齐样式
* @param wb
* @return
*/
public static CellStyle createStyleForCellUnLock(Workbook wb) {
//设置字体
Font headFont = wb.createFont();
headFont.setFontName("黑体");
headFont.setFontHeightInPoints((short) 12);
headFont.setBold(true);
//设置sheet样式
CellStyle style = wb.createCellStyle();
//加边框
style.setBorderBottom(BorderStyle.THIN); //下边框
style.setBorderLeft(BorderStyle.THIN);//左边框
style.setBorderTop(BorderStyle.THIN);//上边框
style.setBorderRight(BorderStyle.THIN);//右边框
style.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
style.setWrapText(true);//设置自动换行
style.setAlignment(HorizontalAlignment.CENTER);//左右居中
style.setFont(headFont);
style.setWrapText(true);
style.setLocked(Boolean.FALSE);
return style;
}
/**
* 创建单元格居中对齐样式
* @param wb
* @return
*/
public static CellStyle createStyleForCellLock(Workbook wb, SXSSFSheet sheet) {
//设置字体
Font headFont = wb.createFont();
headFont.setFontName("黑体");
headFont.setFontHeightInPoints((short) 12);
headFont.setBold(true);
//设置sheet样式
CellStyle style = wb.createCellStyle();
//加边框
style.setBorderBottom(BorderStyle.THIN); //下边框
style.setBorderLeft(BorderStyle.THIN);//左边框
style.setBorderTop(BorderStyle.THIN);//上边框
style.setBorderRight(BorderStyle.THIN);//右边框
style.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
style.setWrapText(true);//设置自动换行
style.setAlignment(HorizontalAlignment.CENTER);//左右居中
style.setFont(headFont);
style.setWrapText(true);
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setLocked(Boolean.TRUE);
sheet.protectSheet("password");
return style;
}
}
9 Excel自动合并行(工具类代码),列代码,利用前后文内容进行切割,记录切割点进行合并
public class OfficeUtil {
/**
*
* @param sheet 作为那个文本
* @param fontStyle 样式的选择
* @param Y 从第几列开始 cellNumber
* @param X 合并哪一列 RollNumber
* OfficeUtil.toMergeV4(sheet, fontStyle, 3, 2); //第二列
*/
public static HashMap<String, String> toMergeTheStand(Sheet sheet, CellStyle fontStyle, Integer Y , Integer X) {
List<Integer> numbers = new ArrayList<>();
List<Integer> cutPoint = new ArrayList<>();
cutPoint.add(Y);
HashMap<String, String> map = new HashMap<>();
HashMap<String, String> result = new HashMap<>();
int lastCellNum = sheet.getLastRowNum();
for (int i = 0; i<lastCellNum-Y; i++){
String vaule1 = sheet.getRow(i+Y).getCell(X).getStringCellValue();
String vaule2 = sheet.getRow(i+Y+1).getCell(X).getStringCellValue();
//进行两两判断,是的话就为一组数据
if (vaule1.equals(vaule2)){
numbers.add(i+Y);
numbers.add(i+Y+1);
}else {
//找出他们的不相等的点 进行记录 进行切割
cutPoint.add(i+Y+1);
}
}
//末尾
cutPoint.add(lastCellNum+1);
for (int i = 0; i < cutPoint.size() - 1; i++) {
//得到填充的字段名
String value = sheet.getRow(cutPoint.get(i)).getCell(X).getStringCellValue();
Integer vo1 = cutPoint.get(i);
Integer vo2 = cutPoint.get(i + 1) - 1;
StringBuffer cut1 = new StringBuffer();
cut1.append(value).append("%@").append(vo1);
StringBuffer cut2 = new StringBuffer();
cut2.append(vo1).append(",").append(vo2);
map.put(cut1.toString(), cut2.toString());
}
for (Map.Entry<String, String> entry : map.entrySet()) {
List<String> values = Arrays.asList(entry.getKey().split("%@"));
String value = values.get(0);
List<String> cutPointNumber = Arrays.asList(entry.getValue().split(","));
int begin = Integer.valueOf(cutPointNumber.get(0));
int end = Integer.valueOf(cutPointNumber.get(1));
if (begin!=end) {
ExcelUtils.mergeRegion(begin, end, X, X, sheet);
sheet.getRow(begin).getCell(X).setCellStyle(fontStyle);
result.put(entry.getKey(),entry.getValue());
}
}
return result;
}
public static HashMap<String, String> toMergeTheStandForEco(Sheet sheet, CellStyle fontStyle, Integer Y , Integer X, Map<String, List<EconomicIndexAttributeVO>> nameMap) {
List<Integer> numbers = new ArrayList<>();
List<Integer> cutPoint = new ArrayList<>();
cutPoint.add(Y);
HashMap<String, String> map = new HashMap<>();
HashMap<String, String> result = new HashMap<>();
int lastCellNum = sheet.getLastRowNum();
for (int i = 0; i<lastCellNum-Y; i++){
String vaule1 = sheet.getRow(i+Y).getCell(X).getStringCellValue();
String vaule2 = sheet.getRow(i+Y+1).getCell(X).getStringCellValue();
//进行两两判断,是的话就为一组数据
if (vaule1.equals(vaule2)){
Integer size = nameMap.get(vaule1).size();
if ( size != null && size == 2 ){
return result ;
}else {
numbers.add(i+Y);
numbers.add(i+Y+1);
}
}else {
//找出他们的不相等的点 进行记录 进行切割
cutPoint.add(i+Y+1);
}
}
//末尾
cutPoint.add(lastCellNum+1);
for (int i = 0; i < cutPoint.size() - 1; i++) {
//得到填充的字段名
String value = sheet.getRow(cutPoint.get(i)).getCell(X).getStringCellValue();
Integer vo1 = cutPoint.get(i);
Integer vo2 = cutPoint.get(i + 1) - 1;
StringBuffer cut1 = new StringBuffer();
cut1.append(value).append("%@").append(vo1);
StringBuffer cut2 = new StringBuffer();
cut2.append(vo1).append(",").append(vo2);
map.put(cut1.toString(), cut2.toString());
}
for (Map.Entry<String, String> entry : map.entrySet()) {
List<String> values = Arrays.asList(entry.getKey().split("%@"));
String value = values.get(0);
List<String> cutPointNumber = Arrays.asList(entry.getValue().split(","));
int begin = Integer.valueOf(cutPointNumber.get(0));
int end = Integer.valueOf(cutPointNumber.get(1));
if (begin!=end) {
ExcelUtils.mergeRegion(begin, end, X, X, sheet);
sheet.getRow(begin).getCell(X).setCellStyle(fontStyle);
result.put(entry.getKey(),entry.getValue());
}
}
return result;
}
public static List<Integer> toMergeTheAcross(Sheet sheet,Integer X) {
HashMap<Integer, String> cutPointMap = new HashMap<>();
List<Integer> cutPoint = new ArrayList<>();
cutPoint.add(0);
Row row = sheet.getRow(X);
int lastCellNum = sheet.getRow(X).getLastCellNum();
String value1 = "";
String value2 = "";
for (int i = 0; i<lastCellNum-1; i++){
if (row.getCell(i) !=null ){
row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
value1 = row.getCell(i).getStringCellValue();
}
if (row.getCell(i+1) !=null ){
row.getCell(i+1).setCellType(Cell.CELL_TYPE_STRING);
value2 = row.getCell(i+1).getStringCellValue();
}
//进行两两判断,是的话就为一组数据
if (!value1.equals(value2)){
//获得下一个末尾和这个不想当
cutPoint.add(i+1);
}
}
//末尾
cutPoint.add(lastCellNum);
for (int i = 0; i < cutPoint.size() -1; i++) {
if (cutPoint.get(i) + 1 != cutPoint.get(i+1)){
StringBuffer str = new StringBuffer();
str.append(cutPoint.get(i)).append(",").append(cutPoint.get(i+1)-1);
cutPointMap.put(cutPoint.get(i),str.toString());
}
}
List<Integer> beginList = new ArrayList<>();
for (Map.Entry<Integer, String> entry : cutPointMap.entrySet()) {
List<String> cutPointNumber = Arrays.asList(entry.getValue().split(","));
int begin = Integer.valueOf(cutPointNumber.get(0));
int end = Integer.valueOf(cutPointNumber.get(1));
beginList.add(begin);
try {
OfficeUtil.mergeRegion(X,X,begin,end,sheet);
}catch (Exception e){
System.err.println(e.getMessage());
}
}
return beginList;
}
/**
*
* @param firstRow Y1
* @param lastRow Y2
* @param firstCol X1
* @param lastCol X2
* @param sheet
*/
public static void mergeRegion(int firstRow, int lastRow, int firstCol, int lastCol, Sheet sheet) {
CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
sheet.addMergedRegion(region);
setBorderStyle(BorderStyle.THIN,region,sheet);
}
public static void setBorderStyle(BorderStyle border, CellRangeAddress region, Sheet sheet){
RegionUtil.setBorderBottom(border, region, sheet); //下边框
RegionUtil.setBorderLeft(border, region, sheet); //左边框
RegionUtil.setBorderRight(border, region, sheet); //右边框
RegionUtil.setBorderTop(border, region, sheet); //上边框
}
}
尾巴:
本来是想懒一点是少些点,不过最近业务需求搞得我把这些东西重头自己写了呀一份,嗯,希望帮到大家
有问题可以qq联系我:954248544
帮到大家的话,帮点个赞啦
[1]: https://blog.csdn.net/Lxy_CCr/article/details/121194205?spm=1001.2014.3001.5502
[2]: https://blog.csdn.net/Lxy_CCr/article/details/121164570?spm=1001.2014.3001.5502