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<EngineerTunnelMonitorRecordForRimExcelVo> 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;
    }



    /**
     * 获取合并单元格的值
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    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);
        }

    }