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

缺乏、安全感 2024-03-17 19:56 109阅读 0赞

#### 需求：根据excel模板导入数据   sheet1：1-6行为固定格式，且需要取值(\{xxx\});7行开始为数据集合(list)   sheet2：都为固定格式，取值地方：\{xxx\} ####

#### 1、数据格式（两个Sheet） ####

![9a01bea6a7144af1b319f2c96a7dbffb.png][]

![a1e23b02df1d47ea9652a4292ab2daf3.png][]

#### 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);
            }
    
        }

[9a01bea6a7144af1b319f2c96a7dbffb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/cbe05ee02eb94dd0aa42eaacec278c34.png
[a1e23b02df1d47ea9652a4292ab2daf3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/a1a3b184a95440b8a06fd2d975607510.png