Eolink上传Excel到Java后台进行处理

约定不等于承诺〃 2023-09-23 18:05 100阅读 0赞

## Eolink上传文件配置： ##

![3db8c302ad6b44b591dbf550b6c1b78d.png][]

## 接收文件请求并进行业务处理 ##

@RequestMapping(value = "shangchuan")
    	@ResponseBody
    	public synchronized R fileUpload(HttpServletRequest request) {
    		try {
    			String[] fields = { "gddname", "lineName", "gddgpsjd", "gddgpswd", "remarks" };
    			List<DqGddname> list = ExcelImportUtil.getImportData(fields, DqGddname.class, request);
    			for (DqGddname dqGddname : list) {
    				EntityWrapper<DqLinename> entityWrapper = new EntityWrapper<DqLinename>();
    				Wrapper<DqLinename> wrapper = entityWrapper.eq("linename", dqGddname.getLineName());
    				DqLinename dqLinename = dqLinenameService.selectOne(wrapper);
    				if (dqLinename == null) {
    					return R.error("线路数据不存在");
    				} else {
    					dqGddname.setLineid(dqLinename.getId());
    				}
    				if (!"".equals(dqGddname.getGddgpsjd()) || !"".equals(dqGddname.getGddgpswd())) {
    					Double pox = Double.parseDouble(dqGddname.getGddgpsjd());
    					Double poy = Double.parseDouble(dqGddname.getGddgpswd());
    					Gps gps = PositionUtil.gps84_To_Gcj02(poy, pox);
    					Double px = gps.getWgLat();
    					Double py = gps.getWgLon();
    					dqGddname.setGpsGcjWd(String.valueOf(px));
    					dqGddname.setGpsGcjJd(String.valueOf(py));
    				}
    				EntityWrapper<DqGddname> eWrapper = new EntityWrapper<>();
    				eWrapper.eq("lineid", dqGddname.getLineid()).eq("gddname", dqGddname.getGddname());
    				DqGddname temp = dqGddnameService.selectOne(eWrapper);
    				if (temp != null) {
    					dqGddname.setId(temp.getId());
    				}
    			}
    			dqGddnameService.insertOrUpdateBatch(list);
    			return R.ok();
    		} catch (Exception e) {
    			logger.error("ERROR：", e);
    			return R.error("上传失败");
    		}
    	}

public static <T> List<T> getImportData(String[] fields, Class<T> clz, HttpServletRequest request)
    			throws IOException, InvalidFormatException, InstantiationException, IllegalAccessException,
    			NoSuchMethodException, InvocationTargetException, NoSuchFieldException, SecurityException {
    		return ExcelImportUtil.getImportData(0, fields, clz, request);
    	}

/**
    	 * 从上传文件中解析出数据对象
    	 * @param sheetIndex 第几个sheet
    	 * @param fields 解析字段
    	 * @param clz 解析对象
    	 * @param request
    	 * @return
    	 * @throws IOException
    	 * @throws InvalidFormatException
    	 * @throws InstantiationException
    	 * @throws IllegalAccessException
    	 * @throws NoSuchMethodException
    	 * @throws InvocationTargetException
    	 * @throws NoSuchFieldException
    	 * @throws SecurityException
    	 */
    	public static <T> List<T> getImportData(int sheetIndex, String[] fields, Class<T> clz, HttpServletRequest request)
    			throws IOException, InvalidFormatException, InstantiationException, IllegalAccessException,
    			NoSuchMethodException, InvocationTargetException, NoSuchFieldException, SecurityException {
    		List<T> datas = new ArrayList<>();
    		Workbook wb = getWorkbookFromRequest(request);
    		Sheet sheet = wb.getSheetAt(sheetIndex);
    		// 获取总行数
    		int rows = sheet.getPhysicalNumberOfRows();
    		if (rows >= 2) {
    			for (int start = 1; start < rows; start++) {
    				// 从第三行开始逐行获取
    				Row row = sheet.getRow(start);
    				if (row == null) {
    					continue;
    				}
    
    				if (fields != null) {
    					T obj = clz.getDeclaredConstructor().newInstance();
    					for (int i = 0; i < fields.length; i++) {
    						Cell cell = row.getCell(i);
    						String cellValue = getCellValue(cell);
    
    						String fieldName = fields[i];
    						Field field = null;
    						try {
    							field = obj.getClass().getDeclaredField(fieldName);
    						} catch (NoSuchFieldException e) {
    							field = obj.getClass().getSuperclass().getDeclaredField(fieldName);
    						}
    						field.setAccessible(true);
    						setFieldValue(field, obj, cellValue);
    					}
    					datas.add(obj);
    				}
    			}
    		}
    		return datas;
    	}
    	
    	/**
    	 * 从上传文件中第一个sheet解析出数据对象
    	 * @param fields
    	 * @param clz
    	 * @param request
    	 * @return
    	 * @throws IOException
    	 * @throws InvalidFormatException
    	 * @throws InstantiationException
    	 * @throws IllegalAccessException
    	 * @throws NoSuchMethodException
    	 * @throws InvocationTargetException
    	 * @throws NoSuchFieldException
    	 * @throws SecurityException
    	 */
    	public static <T> List<T> getImportData(String[] fields, Class<T> clz, HttpServletRequest request)
    			throws IOException, InvalidFormatException, InstantiationException, IllegalAccessException,
    			NoSuchMethodException, InvocationTargetException, NoSuchFieldException, SecurityException {
    		return ExcelImportUtil.getImportData(0, fields, clz, request);
    	}
    
    	private static String getCellValue(Cell cell) {
    		String result = "";
    		if (cell != null) {
    			switch (cell.getCellType()) {
    			// 数字类型 +日期类型
    			case HSSFCell.CELL_TYPE_NUMERIC:
    				if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
    					SimpleDateFormat sdf = null;
    					if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
    						sdf = new SimpleDateFormat("HH:mm");
    					} else {// 日期
    						sdf = new SimpleDateFormat("yyyy-MM-dd");
    					}
    					Date date = cell.getDateCellValue();
    					result = sdf.format(date);
    				} else if (cell.getCellStyle().getDataFormat() == 58) {
    					// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
    					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    					double value = cell.getNumericCellValue();
    					Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
    					result = sdf.format(date);
    				} else {
    					HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
    					result = String.valueOf(dataFormatter.formatCellValue(cell));
    				}
    				break;
    			// String类型
    			case HSSFCell.CELL_TYPE_STRING:
    				result = String.valueOf(cell.getStringCellValue());
    				break;
    			case HSSFCell.CELL_TYPE_BLANK:
    				result = "";
    			default:
    				result = null;
    				break;
    			}
    		}
    
    		return result;
    	}
    
    	private static void setFieldValue(Field field, Object obj, String value) throws IllegalAccessException {
    		Class<?> typeClass = field.getType();
    		if (typeClass == int.class || typeClass == Integer.class) {
    			if (StringUtils.isEmpty(value)) {
    				field.set(obj, 0);
    			} else {
    				field.set(obj, Integer.valueOf(value));
    			}
    		} else if (typeClass == short.class || typeClass == Short.class) {
    			if (StringUtils.isEmpty(value)) {
    				field.set(obj, 0);
    			} else {
    				field.set(obj, Short.valueOf(value));
    			}
    		} else if (typeClass == byte.class || typeClass == Byte.class) {
    			if (StringUtils.isEmpty(value)) {
    				field.set(obj, 0);
    			} else {
    				field.set(obj, Byte.valueOf(value));
    			}
    		} else if (typeClass == double.class || typeClass == Double.class) {
    			if (StringUtils.isEmpty(value)) {
    				field.set(obj, 0);
    			} else {
    				field.set(obj, Double.valueOf(value));
    			}
    		} else if (typeClass == long.class || typeClass == Long.class) {
    			if (StringUtils.isEmpty(value)) {
    				field.set(obj, 0L);
    			} else {
    				field.set(obj, Long.valueOf(value));
    			}
    		} else if (typeClass == String.class) {
    			if (StringUtils.isEmpty(value)) {
    				field.set(obj, "");
    			} else {
    				field.set(obj, value);
    			}
    		} else if (typeClass == boolean.class || typeClass == Boolean.class) {
    			if (StringUtils.isEmpty(value)) {
    				field.set(obj, false);
    			} else {
    				field.set(obj, Boolean.valueOf(value));
    			}
    		} else if (typeClass == BigDecimal.class) {
    			if (StringUtils.isEmpty(value)) {
    				field.set(obj, BigDecimal.ZERO);
    			} else {
    				field.set(obj, new BigDecimal(value));
    			}
    		} else if (typeClass == Date.class) {
    			field.set(obj, StringUtils.isEmpty(value) ? null : DateUtils.parseDate(value));
    		} else {
    			field.set(obj, value);
    		}
    	}

这里是上传的excel表格数据并转换为java集合对象、然后进行业务逻辑处理判断最后保存到数据库

[3db8c302ad6b44b591dbf550b6c1b78d.png]: https://img-blog.csdnimg.cn/3db8c302ad6b44b591dbf550b6c1b78d.png