Java利用POI导入Excel数据(多个sheet、模板)

需求：根据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