java解析excel通用版

落日映苍穹つ 2022-11-07 01:47 206阅读 0赞

## 一、首先是依赖 ##

<dependency>
    			<groupId>org.apache.poi</groupId>
    			<artifactId>poi</artifactId>
    			<version>3.17</version>
    		</dependency>
    		<dependency>
    			<groupId>org.apache.poi</groupId>
    			<artifactId>poi-ooxml</artifactId>
    			<version>3.17</version>
    		</dependency>
    		<dependency>
    			<groupId>org.apache.poi</groupId>
    			<artifactId>poi-ooxml-schemas</artifactId>
    			<version>3.17</version>
    		</dependency>

## 二、方法 ##

@Override
        public String importFromExcel(String fileString) throws IOException { 
            Workbook workbook = null;
            File file = new File("D:\\A_Projects\\OMS1.0\\are-oms-tankInfo\\src\\main\\resources\\template\\移动操作配置模板.xlsx");
            InputStream is = new FileInputStream(file);
            if (file.getName().endsWith("xlsx")) { 
                workbook = new XSSFWorkbook(is);
            } else if (file.getName().endsWith("xls")) { 
                workbook = new HSSFWorkbook(is);
            }
            Iterator<Sheet> sheetIterator = workbook.sheetIterator();
            ArrayList<String> rowNames = new ArrayList<>();
            ArrayList<TankMovementEntity> tankMovementEntities = new ArrayList<>();
    
            while (sheetIterator.hasNext()) { 
                Sheet sheet = sheetIterator.next();
                Iterator<Row> rowIterator = sheet.rowIterator();
                Integer rowIndex = 0;
                while (rowIterator.hasNext()) { 
                    Row row = rowIterator.next();
                    if (rowIndex > 0) { 
                        Iterator<Cell> cellIterator = row.cellIterator();
                        if (rowIndex == 1) { 
                            while (cellIterator.hasNext()) { 
                                Cell next = cellIterator.next();
                                next.setCellType(CellType.STRING);
                                String stringCellValue = next.getStringCellValue();
                                rowNames.add(stringCellValue.replaceAll("\n", ""));
                            }
                        } else { 
                            TankMovementEntity tankMovementEntity = new TankMovementEntity();
                            Integer cellIndex = 0;
                            List<String> units = new ArrayList();
                            while (cellIterator.hasNext()) { 
                                Cell cell = cellIterator.next();
                                cell.setCellType(CellType.STRING);
                                String rowName = rowNames.get(cellIndex++);
                                try { 
                                    judgeToParse(tankMovementEntity, rowName, cell, units);
                                } catch (Exception e) { 
                                    throw new InterfaceException(3414, "第" + (cell.getRowIndex() + 1) + "行，" + (cell.getColumnIndex() + 1) + "列发生错误" + cell.toString());
                                }
                            }
                            Boolean hasValue = false;
                            try { 
                                Map<String, Object> fieldsValue = Utils.getFieldsValue(TankMovementEntity.class, tankMovementEntity);
                                Iterator<String> fieldKey = fieldsValue.keySet().iterator();
                                while (fieldKey.hasNext()) { 
                                    String key = fieldKey.next();
                                    Object value = fieldsValue.get(key);
                                    if (!"serialVersionUID".equals(key) && !StringUtils.isEmpty(fieldsValue.get(key))) { 
                                        hasValue = true;
                                        break;
                                    }
                                }
                                tankMovementEntity.setValid(1);
                            } catch (IllegalAccessException e) { 
                                e.printStackTrace();
                            }
                            if (!hasValue) { 
                                continue;
                            }
                            tankMovementEntity.setGmtCreate(Utils.getNowDate());
                            tankMovementEntity.setSupportAddPump(0);
                            tankMovementEntity.setSupportDelPump(0);
                            tankMovementEntity.setSupportSwitchTank(0);
                            tankMovementEntity.setSupportUpdatePump(0);
                            if (units.size() > 1) { 
                                units.stream().forEach(unit -> { 
                                    TankAreaEntity tankAreaEntity = tankAreaRepository.findByTankAreaCodeAndValid(unit, Constants.VALID_TRUE);
                                    if (tankAreaEntity == null) { 
                                        tankAreaEntity = new TankAreaEntity();
                                        tankAreaEntity.setId(Long.valueOf(1));
                                    }
                                    tankMovementEntity.setTankArea(tankAreaEntity);
                                    tankMovementEntity.setCrossTankArea(1);
                                    List<String> unContainUnit = units.stream().filter(unit1 -> !unit1.equals(unit)).collect(Collectors.toList());
                                    StringBuilder goalTankArea = new StringBuilder();
                                    unContainUnit.stream().forEach(un -> goalTankArea.append(un + ","));
                                    tankMovementEntity.setGoalTankArea(goalTankArea.substring(0, goalTankArea.lastIndexOf(",")));
                                    tankMovementEntity.setCrossTankArea(1);
                                    tankMovementEntities.add(tankMovementEntity);
                                });
                            } else { 
                                tankMovementEntity.setCrossTankArea(0);
                                TankAreaEntity tankAreaEntity = null;
                                if (units.size() > 0) { 
                                    tankAreaEntity = tankAreaRepository.findByTankAreaCodeAndValid(units.get(0), Constants.VALID_TRUE);
                                }
                                if (tankAreaEntity == null) { 
                                    tankAreaEntity = new TankAreaEntity();
                                    tankAreaEntity.setId(Long.valueOf(1));
                                }
                                tankMovementEntity.setTankArea(tankAreaEntity);
                                tankMovementEntities.add(tankMovementEntity);
                            }
                        }
                    }
                    rowIndex++;
                }
                tankMovementRepository.saveAll(tankMovementEntities);
            }
            return "true";
        }
    
        public JSONArray parseToJson(String str) { 
            String[] sources = str.split(",");
            JSONArray sourcesJson = new JSONArray();
            for (String source : sources) { 
                Matcher matcher = pattern.matcher(source);
                if (matcher.find()) { 
                    String tankAreaCode = matcher.group();
                    TankAreaEntity tankAreaEntity = tankAreaRepository.findByTankAreaCodeAndValid(tankAreaCode, Constants.VALID_TRUE);
                    JSONObject object = new JSONObject();
                    object.put("deviceCode", source);
                    object.put("tankAreaCode", tankAreaCode);
                    sourcesJson.add(object);
                }
            }
            return sourcesJson;
        }
    
        public void judgeToParse(TankMovementEntity tankMovementEntity, String rowName, Cell cell, List<String> units) { 
            String stringCellValue = cell.getStringCellValue().replaceAll("\n", ",");
            if ("连续".equals(stringCellValue)) { 
                Utils.setValue(TankMovementEntity.class, tankMovementEntity, rowName, 0);
            } else if ("批量".equals(stringCellValue)) { 
                Utils.setValue(TankMovementEntity.class, tankMovementEntity, rowName, 1);
            } else if ("N.A.".equals(stringCellValue)) { 
                Utils.setValue(TankMovementEntity.class, tankMovementEntity, rowName, null);
            } else if (!StringUtils.isEmpty(stringCellValue)) { 
                if (("sourceDevice").equals(rowName)) { 
                    Utils.setValue(TankMovementEntity.class, tankMovementEntity, rowName, parseToJson(stringCellValue).toJSONString());
                } else if (("pumpDevice").equals(rowName)) { 
                    Utils.setValue(TankMovementEntity.class, tankMovementEntity, rowName, parseToJson(stringCellValue).toJSONString());
                } else if (("goalDevice").equals(rowName)) { 
                    Utils.setValue(TankMovementEntity.class, tankMovementEntity, rowName, parseToJson(stringCellValue).toJSONString());
                } else if (("valveDevice").equals(rowName)) { 
                    Utils.setValue(TankMovementEntity.class, tankMovementEntity, rowName, parseToJson(stringCellValue).toJSONString());
                } else if (("unit").equals(rowName)) { 
                    String[] split = new String[20];
                    if (stringCellValue.contains(",")) { 
                        split = stringCellValue.split(",");
                        units.addAll(Arrays.asList(split));
                    } else { 
                        units.add(stringCellValue);
                    }
                } else { 
                    if (Utils.isNumeric(stringCellValue)) { 
                        Utils.setValue(TankMovementEntity.class, tankMovementEntity, rowName, Integer.valueOf(stringCellValue));
                    } else if (!StringUtils.isEmpty(stringCellValue)) { 
                        if (stringCellValue.contains("3或")) { 
                            Utils.setValue(TankMovementEntity.class, tankMovementEntity, rowName, 3);
                        } else { 
                            Utils.setValue(TankMovementEntity.class, tankMovementEntity, rowName, stringCellValue);
                        }
                    }
                }
            }
        }

## 三、工具类方法 ##

/**
         * 通过方法名动态执行某个方法
         *
         * @param clazz
         * @param tankMovementEntity
         * @param methodName
         * @param parameters
         * @return
         * @throws InvocationTargetException
         * @throws IllegalAccessException
         * @throws NoSuchMethodException
         */
        public static Object executeMethod(Class clazz, TankMovementEntity tankMovementEntity, String methodName, Object... parameters) throws InvocationTargetException, IllegalAccessException, NoSuchMethodException { 
            ArrayList<Class> paramTypeList = new ArrayList();
            for (Object paramType : parameters) { 
                paramTypeList.add(paramType.getClass());
            }
            Class[] classArray = new Class[paramTypeList.size()];
            Method method = clazz.getMethod(methodName, paramTypeList.toArray(classArray));
            Object invoke = method.invoke(tankMovementEntity, parameters);
            return invoke;
        }
    
        /**
         * 设置属性值
         *
         * @param property
         * @param value
         */
        public static Boolean setValue(Class clazz, TankMovementEntity tankMovementEntity, String property, Object value) { 
            try { 
                Field declaredField = clazz.getDeclaredField(property);
                declaredField.setAccessible(true);
                declaredField.set(tankMovementEntity, value);
            } catch (NoSuchFieldException e) { 
                return false;
            } catch (IllegalAccessException e) { 
                return false;
            }
            return true;
        }
    
        /**
         * 利用正则表达式判断字符串是否是数字
         *
         * @param str
         * @return
         */
        public static boolean isNumeric(String str) { 
            Pattern pattern = Pattern.compile("[0-9]*");
            Matcher isNum = pattern.matcher(str);
            if (StringUtils.isEmpty(str) || !isNum.matches()) { 
                return false;
            }
            return true;
        }
    
        /**
         * 判断指定的单元格是否是合并单元格
         *
         * @param sheet
         * @param row    行下标
         * @param column 列下标
         * @return
         */
        public static boolean isMergedRegion(Sheet sheet, int row, int column) { 
            int sheetMergeCount = sheet.getNumMergedRegions();
            for (int i = 0; i < sheetMergeCount; i++) { 
                CellRangeAddress range = sheet.getMergedRegion(i);
                int firstColumn = range.getFirstColumn();
                int lastColumn = range.getLastColumn();
                int firstRow = range.getFirstRow();
                int lastRow = range.getLastRow();
                if (row >= firstRow && row <= lastRow) { 
                    if (column >= firstColumn && column <= lastColumn) { 
                        return true;
                    }
                }
            }
            return false;
        }
    
        /**
         * 获取合并单元格的值
         *
         * @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);
                        fCell.setCellType(CellType.STRING);
                        return fCell.getStringCellValue();
                    }
                }
            }
            return null;
        }
    
        /**
         * 获取所有属性值
         *
         * @return
         * @throws IllegalAccessException
         */
        public static Map<String, Object> getFieldsValue(Class clazz, TankMovementEntity tankMovementEntity) throws IllegalAccessException { 
            Map<String, Object> fieldValuesMap = new HashMap(16);
            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) { 
                field.setAccessible(true);
                Object fieldValue = field.get(tankMovementEntity);
                fieldValuesMap.put(field.getName(), fieldValue);
            }
            return fieldValuesMap;
        }