poi从excel中读取父子关系型（树形）数据结构到数据库

今天遇到一个问题，就是excel中的数据是树形的，需要读进数据库，并保存树形结构，

excel结构，程序代码如下。

其中“描述“和”提升意见“只是某记录的属性，一级，二级，三级，四级，五级是该记录的层级，一级最高，为根。

程序如下：

基础数据结构：KeyQualityEntity

public class KeyQualityEntity extends Base implements java.io.Serializable {private static final long serialVersionUID = 5454155825314635342L;private Long id;/** 名称 */private String name;/** 描述 */private String detail;/** 提升建议 */private String promptAdvice;/** 上级 */private KeyQualityEntity parent;/** 等级 */private Integer level;    private Long parentId;// columns ENDpublic KeyQualityEntity() {}public KeyQualityEntity(Long id) {this.id = id;}public KeyQualityEntity setId(Long value) {pUpdate("id", id);this.id = value;return this;}public java.lang.Long getId() {return this.id;}public java.lang.String getName() {return this.name;}public KeyQualityEntity setName(String value) {pUpdate("name", name);this.name = value;return this;}public java.lang.String getDetail() {return detail;}public KeyQualityEntity setDetail(String detail) {pUpdate("detail", detail);this.detail = detail;return this;}public java.lang.String getPromptAdvice() {return promptAdvice;}public KeyQualityEntity setPromptAdvice(String promptAdvice) {pUpdate("promptAdvice", promptAdvice);this.promptAdvice = promptAdvice;return this;}public KeyQualityEntity getParent() {return parent;}public Integer getLevel() {return level;}public KeyQualityEntity setParent(KeyQualityEntity parent) {if (parent != null)pUpdate("parentId", parent.getId());this.parent = parent;return this;}public KeyQualityEntity setLevel(Integer level) {pUpdate("level", level);this.level = level;return this;}    public Long getParentId() {        return parentId;    }    public KeyQualityEntity setParentId(Long parentId) {        pUpdate("parentId",parentId);        this.parentId = parentId;        return this;    }}

操作类：

public void readExcel() throws IOException {InputStream is = new FileInputStream("C:\\Users\\liu\\Desktop\\素质点 (1).xls");HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);// 循环工作表Sheetfor (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);if (hssfSheet == null) {continue;}Long previousId = null;// 上条记录的idInteger previousLevel = 1;// 上条记录的等级// 循环行Row// 第一行为说明，不进行读取for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {HSSFRow hssfRow = hssfSheet.getRow(rowNum);if (hssfRow == null) {continue;}KeyQualityEntity kqe = new KeyQualityEntity();// 设置描述kqe.setDetail(hssfRow.getCell(0).getStringCellValue());// 设置提升建议kqe.setPromptAdvice(hssfRow.getCell(1).getStringCellValue());// 循环列Cell// 0：描述 1：提升意見 2：一級 3：二級 4：三級 5：四級 6：五級Integer level; // 标示当前行的层级for (int coluNum = 2; coluNum <= 6; coluNum++) {HSSFCell hssfCell = hssfRow.getCell(coluNum);// 如果该列为null则循环下一列if (hssfCell == null) {continue;}String name = hssfRow.getCell(coluNum).getStringCellValue();if (StringUtils.isNotBlank(name)) {level = coluNum - 1;kqe.setName(name);kqe.setLevel(level);// 如果上条记录的等级比本条记录等级小，则说明上条记录是该记录的父亲，更新 previousId和// previousLevelif (previousLevel < level) {/* * 伪代码 * kqe.setParent(new * KeyQualityEntity(previousId));   //设置上条记录的id为本记录父亲id * kqe.save();       //保存进数据库并设置保存后id在kqe里 */previousId = kqe.getId();previousLevel = level;} else {/**  伪代码，查询按id倒序的等级等于当前记录等级level的第一条数据，如果结果为null，说明是第一个根  ，设置parentId为null，如果有结果，则设置当前记录parentId为查出来的记录的parentIdList<String> usePara = new ArrayList<>();usePara.add("order by id desc");ListResult<KeyQualityEntity> result = generalBeanDao.query(new KeyQualityEntity().setLevel(level), "keyQualityEntity",null, usePara);List<KeyQualityEntity> keyQualityEntities = result.getList();if (CollectionUtils.isEmpty(keyQualityEntities)) {kqe.setParent(new KeyQualityEntity(null));} else {kqe.setParent(new KeyQualityEntity(keyQualityEntities.get(0).getParentId()));}*/kqe.save(); //  保存kqe到数据库，得到保存后idpreviousId = kqe.getId(); //更新    previousId和   previousLevelpreviousLevel = level;}}}}}}