改写了leader的程序,现在支持读取EXCEL表格(以路径字符串的形式作为参数传入),当然是那种一个sheet一张表的,而且格式比较固定,只能是这样的形式,然后将每个单元格的数据(包含行号、列号、sheet和数据)保存在一个UnitValue对象中,最后解析完成返回一个UnitValue的Set。
解析的具体程序:
package main.java.demo; import org.apache.commons.lang.StringUtils; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.File; import java.util.ArrayList; import java.util.HashSet; import java.util.List; public class XlsxUtils { public static HashSet<UnitValue> parseDataFromXLSX(String fullPathOfUploaded) throws RuntimeException { if (!fullPathOfUploaded.endsWith(".xlsx")) { throw new RuntimeException("仅支持 .xlsx 格式的Excel文件"); } HashSet<UnitValue> parsedUnitValues = new HashSet<>(); try (XSSFWorkbook xsb = new XSSFWorkbook(fullPathOfUploaded)) { XSSFSheet sheet = xsb.getSheetAt(0); boolean isDataFound = false; boolean isSheetParsed = false; String[] fileNames = fullPathOfUploaded.split("\\\\"); String hourName = fileNames[fileNames.length - 1].split("/")[0]; for (int row = sheet.getFirstRowNum(); row <= sheet.getLastRowNum() && !isSheetParsed; row++) { XSSFRow xsRowPrimary = sheet.getRow(row); if (xsRowPrimary == null) { continue; } for (int col = xsRowPrimary.getFirstCellNum(); col <= xsRowPrimary.getLastCellNum() && !isSheetParsed; col++) { XSSFCell cellPrimary = xsRowPrimary.getCell(col); if (cellPrimary == null) { continue; } if (cellPrimary.getCellType() == Cell.CELL_TYPE_STRING) { // 找到了第一个行编号 isDataFound = true; // 找所有的行的编号 List<Character> wellRowIndices = XlsxUtils.getRows(sheet, row, col); // 找所有的列的编号 List<Integer> wellColumnIndices = XlsxUtils.getColumns(sheet, sheet.getRow(row-1), row, col); if (wellRowIndices.size() == 0 || wellColumnIndices.size() == 0) { throw new RuntimeException("在" + hourName + "目录下的表格中没有解析到正确的孔位数据区域"); } for (int i = 0; i < wellRowIndices.size(); i++) { XSSFRow xsRowSearch = sheet.getRow(row + i); for (int j = 0; j < wellColumnIndices.size(); j++) { XSSFCell cellSearch = xsRowSearch.getCell(col + j + 1); Double value; if (cellSearch == null || cellSearch.getCellType() != Cell.CELL_TYPE_NUMERIC) { continue; } else { value = cellSearch.getNumericCellValue(); } int hourValue = Integer.valueOf(hourName.toUpperCase().replace("HOUR", "")); UnitValue unitValue = new UnitValue(hourValue, wellRowIndices.get(i), wellColumnIndices.get(j), value, null); parsedUnitValues.add(unitValue); } } isSheetParsed = true; } } } if (!isDataFound) { throw new RuntimeException("在" + hourName + "目录下的表格中没有找到数据"); } } catch (RuntimeException e) { throw e; } catch (Exception e) { e.printStackTrace(); throw new RuntimeException("上传数据解析失败"); } return parsedUnitValues; } private static List<Integer> getColumns(XSSFSheet sheet, XSSFRow xsRowPrimary, int row, int col) { List<Integer> wellColumnIndices = new ArrayList<>(); for (int k = col+1; k < xsRowPrimary.getLastCellNum(); k ++) { XSSFCell cellSearch = sheet.getRow(row-1).getCell(k); if (cellSearch == null || cellSearch.getCellType() != Cell.CELL_TYPE_NUMERIC) { break; // 终止well column index的寻找 } int numericCellValue = (int) cellSearch.getNumericCellValue(); // System.out.println("numericCellValue:"+numericCellValue); if (numericCellValue <= 0 || numericCellValue >= 100) { break; } wellColumnIndices.add(numericCellValue); } return wellColumnIndices; } private static List<Character> getRows(XSSFSheet sheet, int row, int col) { List<Character> wellRowIndices = new ArrayList<>(); for (int k = row ; k <= sheet.getLastRowNum(); k ++) { XSSFRow xsRowSearch = sheet.getRow(k); if (xsRowSearch == null) { break; // 终止well row index的寻找 } XSSFCell cellSearch = xsRowSearch.getCell(col); if (cellSearch == null || cellSearch.getCellType() != Cell.CELL_TYPE_STRING) { break; } String stringCellValue = cellSearch.getStringCellValue(); if (StringUtils.isEmpty(stringCellValue) || stringCellValue.trim().length() != 1) { break; } char c = stringCellValue.trim().charAt(0); if (c < 'A' || c > 'Z') { break; } wellRowIndices.add(c); } return wellRowIndices; } public static void deleteDir(File dir) { if (dir.isDirectory()) { String[] children = dir.list(); for (int i=0; i<children.length; i++) { deleteDir(new File(dir, children[i])); } } dir.delete(); } }