java读取excel层级结构的递归写法

java poi读取excel这种层级结构的递归写法,思路:同列的循环获取,子级递归获取,想清楚每一个单元格变化的地方,传不同的参数。exel的单元格的值只能先读取行,再获取列。

包括合并单元格的获取并不是有序的,需要根据行列的起始索引来判断是否是合并单元格。每次都要先进行判断。

public List<KnowledgeSystemField> readExcel(String filename, InputStream inputStream) {
		Workbook workbook = null;
		try {
			filename = filepath;
			String filetype = filename.substring(filename.lastIndexOf(".") + 1, filename.length());

			File excelfile = new File(filename);
			if (!excelfile.exists()) {
				log.info("路径为" + filename + "的文件不存在!");
				return null;
			}
			inputStream = new FileInputStream(excelfile);

			workbook = getWorkBook(inputStream, filetype);
			// 读取excel中的数据
			addRowKLevel(workbook);
			List<KnowledgeSystemField> resultDataList = null;
			// resultDataList = parseExcel(workbook);
			return resultDataList;
		} catch (Exception e) {
			log.warn("解析Excel失败,文件名:" + filename + " 错误信息:" + StrFunc.exception2str(e));
			return null;
		} finally {
			try {
				if (null != workbook) {
					workbook.close();
				}
				if (null != inputStream) {
					inputStream.close();
				}
			} catch (Exception e) {
				log.warn("关闭数据流出错!错误信息:" + e.getMessage());
				return null;
			}
		}
	}
public Workbook getWorkBook(InputStream in, String filetype) throws Exception {
		Workbook workbook = null;
		if (StrFunc.compareStr(filetype, XLS)) {
			workbook = new HSSFWorkbook(in);
		} else if (StrFunc.compareStr(filetype, XLSX)) {
			workbook = new XSSFWorkbook(in);
		}
		return workbook;
	}
public void addRowKLevel(Workbook workbook) {
		Sheet sheet = workbook.getSheetAt(0);
		if (sheet == null) {
			return;
		}
		int startRow = STARTROW;
		int endRow = sheet.getLastRowNum();
		endRow = 26;
		String dirId = "";
		getCellValue(startRow, endRow, 1, sheet, dirId);
	}

public void getCellValue(int firstRowIndex, int lastRowIndex, int column, Sheet sheet, String dirid) {
		if (column > 4) {
			return;
		}
		int lastRow = firstRowIndex;
		JSONObject multiRow = isMultiRow(firstRowIndex, lastRowIndex, column, sheet);
		if (multiRow.getBoolean("isMulti")) {
			lastRow = multiRow.getInt("lastRow");
		}
		while (lastRow <= lastRowIndex) {
			Cell cell = sheet.getRow(firstRowIndex).getCell(column);
			String knowId = addKnowOrGroup(column, dirid, cell);
			getCellValue(firstRowIndex, lastRow, column + 1, sheet, StrFunc.isNull(knowId) ? dirid : knowId);
			firstRowIndex = lastRow + 1;
			lastRow = firstRowIndex;
			multiRow = isMultiRow(firstRowIndex, lastRowIndex, column, sheet);
			if (multiRow.getBoolean("isMulti")) {
				lastRow = multiRow.getInt("lastRow");
			}
		}
	}
public JSONObject isMultiRow(int firstRowIndex, int lastRowIndex, int column, Sheet sheet) {
		JSONObject obj = new JSONObject();
		obj.put("isMulti", false);
		List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
		for (int j = 0; j < mergedRegions.size(); j++) {
			CellRangeAddress cr = mergedRegions.get(j);
			int firstColumn = cr.getFirstColumn();
			int lastColumn = cr.getLastColumn();
			int firstRow = cr.getFirstRow();
			int lastRow = cr.getLastRow();
			if (firstColumn == lastColumn && firstColumn == column) {
				if (firstRowIndex == firstRow && lastRow <= lastRowIndex) {
					obj.put("isMulti", true);
					obj.put("lastRow", lastRow);
					break;
				}
			}
		}
		return obj;
	}
private String addKnowOrGroup(int column, String dirid, Cell cell2) {
		String knowId = "";
		String cellvalue = convertCellValueToString(cell2);
		if (!StrFunc.isNull(cellvalue)) {
			boolean ifKnowExist = ifKnowExist(cellvalue);
			if (!ifKnowExist) {
				if (column == 1) {// 第一级分组
					knowId = addGroup(cellvalue, this.knowledgeSetId);
				} else if (column == 4) {// 最后一级图谱
					knowId = addKnowWithName(cellvalue, dirid);
				} else {
					knowId = addGroup(cellvalue, dirid);
				}
			}
		}
		return knowId;
	}

 

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值