需求：根据excel模板导入数据
sheet1：1-6行为固定格式，且需要取值({xxx});7行开始为数据集合(list)
sheet2：都为固定格式，取值地方：{xxx}
1、数据格式（两个Sheet）
2、代码
public static EngineerTunnelMonitorExcelVo importExcel(MultipartFile file){ if(file.isEmpty()){ return null; } EngineerTunnelMonitorExcelVo result=new EngineerTunnelMonitorExcelVo(); //获取excel对象 Workbook workbook= null; try { //获取excel workbook = new XSSFWorkbook(file.getInputStream()); //获取工作簿 Sheet sheetAt = workbook.getSheetAt(0); // String titl = getMergedRegionValue(sheetAt, 1, 1); if(null!=titl && !"".equals(titl) && titl.contains("周边")){ result.setTestPart(2); }else{ result.setTestPart(1); } //项目名称 result.setPointName(getMergedRegionValue(sheetAt,2 , 3)); //施工单位名称 result.setConstructionName(getMergedRegionValue(sheetAt, 2, 7)); //围岩级别 result.setWallRockLevel(getMergedRegionValue(sheetAt, 2, 10)); //断面里程 result.setFractureSurfaceMileage(getMergedRegionValue(sheetAt, 2, 13)); //建立日期 result.setBuildTime(getDataForExcel(getMergedRegionValue(sheetAt, 3,3))); //施工方法 result.setConstructMethod(getMergedRegionValue(sheetAt, 3, 7)); //观测方法 result.setObservationMethod(getMergedRegionValue(sheetAt, 3, 10)); //测点编号 result.setTestCode(getMergedRegionValue(sheetAt, 3, 13)); Integer rows=6; Integer index=1; ArrayList list=new ArrayList<>(); while (true){ if(null==sheetAt.getRow(rows)){ break; } EngineerTunnelMonitorRecordForRimExcelVo engineerTunnelMonitorRecordForRimExcelVo=new EngineerTunnelMonitorRecordForRimExcelVo(); engineerTunnelMonitorRecordForRimExcelVo.setTestTime(getDataForExcel(getCellData(sheetAt.getRow(rows).getCell(index)))) .setTestTemp(getCellData(sheetAt.getRow(rows).getCell(++index))) .setSpaceTime(getCellData(sheetAt.getRow(rows).getCell(++index))) .setSpaceFirstTime(getCellData(sheetAt.getRow(rows).getCell(++index))) .setMeasureOne(getCellData(sheetAt.getRow(rows).getCell(++index))) .setMeasureTwo(getCellData(sheetAt.getRow(rows).getCell(++index))) .setMeasureThree(getCellData(sheetAt.getRow(rows).getCell(++index))) .setAvgValue(getCellData(sheetAt.getRow(rows).getCell(++index))) .setLastDown(getCellData(sheetAt.getRow(rows).getCell(++index))) .setFirstDown(getCellData(sheetAt.getRow(rows).getCell(++index))) .setDownRate(getCellData(sheetAt.getRow(rows).getCell(++index))) .setSpaceFace(getCellData(sheetAt.getRow(rows).getCell(++index))) .setManageLevel(getCellData(sheetAt.getRow(rows).getCell(++index))); list.add(engineerTunnelMonitorRecordForRimExcelVo); index=1; rows++; } Sheet sheetTwo = workbook.getSheetAt(1); EngineerTunnelMonitorAnalyseExcelVo engineerTunnelMonitorAnalyse = new EngineerTunnelMonitorAnalyseExcelVo(); engineerTunnelMonitorAnalyse.setName(getMergedRegionValue(sheetTwo, 2, 3)) .setConstructionName(getMergedRegionValue(sheetTwo, 2, 7)) .setFractureSurfaceMileage(getMergedRegionValue(sheetTwo, 2, 10)) .setTestPart(getMergedRegionValue(sheetTwo, 3,3)) .setWallRockLevel(getMergedRegionValue(sheetTwo, 3, 7)) .setTestCode(getMergedRegionValue(sheetTwo, 3, 10)) .setFirstTime(getDataForExcel(getMergedRegionValue(sheetTwo, 4, 3))) .setLastTime(getDataForExcel(getMergedRegionValue(sheetTwo, 4, 7))) .setDataTaskTime(getDataForExcel(getMergedRegionValue(sheetTwo, 4, 10))) .setRegressionEquation(getMergedRegionValue(sheetTwo, 5, 3)) .setCoefficientAssociation(getMergedRegionValue(sheetTwo, 5, 9)) .setDisplacementLevel(getMergedRegionValue(sheetTwo, 6, 3)+";"+getMergedRegionValue(sheetTwo, 8, 3)) .setRate(getMergedRegionValue(sheetTwo, 10, 3)) .setConclusion(getMergedRegionValue(sheetTwo, 13, 3)); result.setEngineerTunnelMonitorRecordList(list); result.setEngineerTunnelMonitorAnalyse(engineerTunnelMonitorAnalyse); } catch (IOException e) { throw new RuntimeException(e); } return result; } public static String getMergedRegionValue(Sheet sheet ,int row , int column){ int sheetMergeCount = sheet.getNumMergedRegions(); for(int i = 0 ; i < sheetMergeCount ; i++){ CellRangeAddress ca = sheet.getMergedRegion(i); int firstColumn = ca.getFirstColumn(); int lastColumn = ca.getLastColumn(); int firstRow = ca.getFirstRow(); int lastRow = ca.getLastRow(); if(row >= firstRow && row <= lastRow){ if(column >= firstColumn && column <= lastColumn){ Row fRow = sheet.getRow(firstRow); Cell fCell = fRow.getCell(firstColumn); return getCellData(fCell); } } } return null ; } //获取单元格内容 public static String getCellData(Cell cell){ String value=""; if(null==cell){ return value; } switch (cell.getCellType()) { case FORMULA:value = "" + cell.getCellFormula(); break; case NUMERIC:value = "" + cell.getNumericCellValue(); break; case STRING:value = cell.getStringCellValue(); break; default: break; } return value; } //获取日期 public static Date getDataForExcel(String val){ try { if(StringUtils.isEmpty(val)){ return null; } return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(val); } catch (Exception e) { double excelDate = Double.valueOf(val); // 示例 Excel 日期和时间数值 long baseMillis = -2209017600000L; // 1900 年 1 月 1 日的毫秒数（注意 Excel 的 1900 年闰年错误，减去两天） // 计算日期和时间的毫秒数 long millis = baseMillis + (long) (excelDate * 24 * 60 * 60 * 1000) - 172800000; // 创建 Date 对象 return new Date(millis); } } 来源地址：https://blog.csdn.net/qq_43457781/article/details/131719851
