精华内容
下载资源
问答
  • java 批量导入excel数据

    热门讨论 2012-04-04 12:46:37
    java平台中批量excel表格中数据导入到数据库中
  • 主要为大家详细介绍了Javaexcel数据批量导入方法,具有一定的参考价值,感兴趣的小伙伴们可以参考一下
  • 主要介绍了Java实现批量导入excel表格数据到数据库中的方法,结合实例形式详细分析了java导入Excel数据到数据库的具体步骤与相关操作技巧,需要的朋友可以参考下
  • 主要为大家详细介绍了Java实现excel数据导入,文中示例代码介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们可以参考一下
  • 主要为大家详细介绍了java使用POI批量导入excel数据的方法,具有一定的参考价值,感兴趣的小伙伴们可以参考一下
  • 主要为大家详细介绍了Java实现Excel批量导入数据,文中示例代码介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们可以参考一下
  • 主要为大家详细介绍了java实现批量导入Excel表格数据到数据库,文中示例代码介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们可以参考一下
  • 实现从Excel批量导入数据到MySQL 数据库中,可以同时导入多张Excel表,使用时在excel.table 中配置需要导入的表及可!
  • 导入Excel数据,验证数据是否符合标准,如果不符合则返回所有错误的行数,数据量每个Excel为2w~3w左右数据量,导入较慢,通过线程池把数据分块为每1000条为一批数据,同时导入数据库提高速度。 二、Excel文件标准 ...

    一、需求分析

    导入Excel数据,验证数据是否符合标准,如果不符合则返回所有错误的行数,数据量为每个Excel为2w~3w左右数据量,导入较慢,通过线程池把数据分块为每1000条为一批数据,同时导入数据库提高速度。

    二、Excel文件标准

    三、代码实现

    Controller

    	/**
    	 * 导入项目长势Excel
    	 * @param id 
    	 * @return
    	 * @author ygc 
    	 * @throws IOException  
    	 */
    	@RequestMapping("/ImportProjectGrowthExcel")
    	@SecurityParameter(inDecode=false,outEncode=true)
    	@ResponseBody
    	@Transactional(propagation=Propagation.REQUIRED,rollbackFor = Exception.class)
    	public JsonResultInfo ImportProjectGrowthExcel(HttpServletRequest request,HttpServletResponse response) throws IOException {	
    		response.setContentType("text/html;charset=UTF-8");   
    	    response.setHeader("Content-type", "application/json;charset=UTF-8");   
    	    JsonResultInfo info=new JsonResultInfo();  
    	    long startTime = System.currentTimeMillis();    //获取开始时间
    	    boolean temp=projectService.ImportProjectGrowthExcel(request,info);
    	    System.out.println("test:"+info.getResult());
    	    long endTime = System.currentTimeMillis(); 
    	    System.out.println("程序运行时间:" + (endTime - startTime) + "ms");    //输出程序运行时间
    //	    info.setCode(200); 
    //		info.setData(temp);   
    //		info.setResult("成功"); 
    //		info.setMessage("导入项目定损Excel"); 
    		System.out.println(info);
    		return info; 
    	}

    Service

    	public boolean ImportProjectGrowthExcel(HttpServletRequest request, JsonResultInfo info) {
    		MultipartResolver resolver = new CommonsMultipartResolver(request.getSession().getServletContext());
    		MultipartHttpServletRequest multipartRequest = resolver.resolveMultipart(request);
    		String requestData = multipartRequest.getParameter("requestData");
    		String encrypted = multipartRequest.getParameter("encrypted");
    		String content = DecryptionFileUtil.decryption(requestData, encrypted);
    		JSONObject jsonObject = JSON.parseObject(content);
    		String token = (String) jsonObject.get("token");
    		String userInfo = redisOperator.get(token);
    		JSONObject userJson = JSONObject.parseObject(userInfo);
    		SysUserInfo user = JSON.toJavaObject(userJson, SysUserInfo.class);	 
    		String uid = (String) jsonObject.get("uid");
    		info.setUid(uid);
    		if (RedisPool.checkToken(token) == false) {
    			return false;
    		}
    		int count=1;
    		JSONObject data = (JSONObject) jsonObject.get("data");
    		InsProjectGrowthtable image = JSONObject.toJavaObject(data, InsProjectGrowthtable.class);
    		String deletePath = Commons.MASTER_PATH + File.separator + Commons.IMPORT_PROJECT_GROWTH_EXCEL + File.separator+ image.getProjectid();
    		File itemPath = new File(deletePath);
    		FileUtil.deleteDir(itemPath);
    		Iterator<String> iter = multipartRequest.getFileNames();
    		String path = null;
    		String fileName=null;
    		while (iter.hasNext()) {
    			MultipartFile file = multipartRequest.getFile(iter.next().toString());
    			if (file.isEmpty()) {
    				continue;
    			} else {
    				fileName = file.getOriginalFilename();
    				// 文件路径,可以替换为自己的文件夹
    				String savePath = Commons.MASTER_PATH + File.separator + Commons.IMPORT_PROJECT_GROWTH_EXCEL
    						+ File.separator + image.getProjectid();
    
    				// String savePath="E:\\AMapUsermark";
    				File storeDirectory = new File(savePath);
    				if (!storeDirectory.exists()) {
    					storeDirectory.mkdirs();
    				}
    				// 文件路径+文件名
    				path = savePath + File.separator + fileName;
    				try {
    					// 保存文件
    					file.transferTo(new File(savePath, fileName));
    				} catch (IllegalStateException e) {
    					// TODO Auto-generated catch block
    	
    					e.printStackTrace();
    				} catch (IOException e) {
    		
    					// TODO Auto-generated catch block
    					e.printStackTrace();
    				}
    			}
    		}
    		// Excel解析
    		List<Map<Integer, String>> result = new ArrayList<Map<Integer, String>>();
    		File file = new File(path);
    		XSSFWorkbook workbook;
    		try {
    //			workbook = new XSSFWorkbook(FileUtils.openInputStream(file));
    			OPCPackage opcPackage = OPCPackage.open(file.getAbsolutePath());
    //			workbook = new XSSFWorkbook(new FileInputStream(file));
    			workbook = new XSSFWorkbook(opcPackage);
    			opcPackage.close();
    			// 第二种获取方式通过index 获取第一个表
    			Sheet sheet = workbook.getSheetAt(0);
    			int lastRowNum = sheet.getLastRowNum();
    			Map<String,Integer>	map1=new LinkedHashMap<String,Integer>();
                Row row = sheet.getRow(0);          
                //获取当前最后单元格列号
            	int lastCellNum = row.getLastCellNum();
                for(int j=0;j<lastCellNum;j++){
    	                Cell cell = row.getCell(j);
    	                if(cell==null) {
    	                	continue;
    	                }else {
    	                    if(cell.toString().equals("县")) {                	
    		                	map1.put("县", cell.getColumnIndex());
    		                }
    		                if(cell.toString().equals("镇")) {
    		                	map1.put("镇", cell.getColumnIndex());
    		                }
    		                if(cell.toString().equals("村")) {
    		                	map1.put("村", cell.getColumnIndex());
    		                }
    		                if(cell.toString().equals("地块编号")) {
    		                	map1.put("地块编号", cell.getColumnIndex());
    		                }
    		                if(cell.toString().equals("用户")) {
    		                	map1.put("用户", cell.getColumnIndex());
    		                }
    		                if(cell.toString().equals("种植面积")){
    		                	map1.put("种植面积", cell.getColumnIndex());
    		                }		             
    		                if(cell.toString().equals("1.0")){
    		                	map1.put("1.0", cell.getColumnIndex());
    		                }
    		                if(cell.toString().equals("2.0")){
    		                	map1.put("2.0", cell.getColumnIndex());
    		                }
    		                if(cell.toString().equals("3.0")){
    		                	map1.put("3.0", cell.getColumnIndex());
    		                }
    		                if(cell.toString().equals("4.0")){
    		                	map1.put("4.0", cell.getColumnIndex());
    		                }
    		                if(cell.toString().equals("5.0")){
    		                	map1.put("5.0", cell.getColumnIndex());
    		                }
    		                if(cell.toString().equals("6.0")){
    		                	map1.put("6.0", cell.getColumnIndex());
    		                }
    		                if(cell.toString().equals("7.0")){
    		                	map1.put("7.0", cell.getColumnIndex());
    		                }		  
    		                if(cell.toString().equals("8.0")){
    		                	map1.put("8.0", cell.getColumnIndex());
    		                }
    		                if(cell.toString().equals("9.0")){
    		                	map1.put("9.0", cell.getColumnIndex());
    		                }
    		                if(cell.toString().equals("10.0")){
    		                	map1.put("10.0", cell.getColumnIndex());
    		                }
    	                }
    	               
    	            }
    				
    			// 从第三行开始导入
    			int firstRowNum = 1;
    			Map<Integer, String> map = null;
    			// 解析数据
    			for (int i = firstRowNum; i <= lastRowNum; i++) {
    	        	map=new HashMap<Integer,String>();
    	            Row row1 = sheet.getRow(i);        
    	            //获取当前最后单元格列号
    	        	int lastCellNum1 = row1.getLastCellNum();
    	            for(int j=0;j<lastCellNum1;j++){
    		            if(map1.get("县")!=null) {
    		            	 Cell cell = row1.getCell(map1.get("县"));
    			    			if (cell != null) {
    								cell.setCellType(Cell.CELL_TYPE_STRING);
    								String value = cell.getStringCellValue();
    								map.put(map1.get("县"), value);
    								// System.out.print(value + " ");//注意value后面的空格
    							} else {
    								map.put(map1.get("县"),null);
    							}
    		            }
    		            if(map1.get("镇")!=null) {
    		            	Cell cell1 = row1.getCell(map1.get("镇"));
    		    			if (cell1 != null) {
    		    				cell1.setCellType(Cell.CELL_TYPE_STRING);
    							String value = cell1.getStringCellValue();
    							map.put(map1.get("镇"), value);
    						} else {
    							map.put(map1.get("镇"),null);
    						}
    		            }
    		            if(map1.get("村")!=null) {
    		            	  Cell cell2 = row1.getCell(map1.get("村"));
    			    			if (cell2 != null) {
    			    				cell2.setCellType(Cell.CELL_TYPE_STRING);
    								String value = cell2.getStringCellValue();
    								map.put(map1.get("村"), value);
    							} else {
    								map.put(map1.get("村"),null);
    							}
    		            }   
    		            if(map1.get("地块编号")!=null) {
    		            	 Cell cell3 = row1.getCell(map1.get("地块编号"));
    			    			if (cell3 != null) {
    			    				cell3.setCellType(Cell.CELL_TYPE_STRING);
    								String value = cell3.getStringCellValue();
    								map.put(map1.get("地块编号"), value);
    							} else {
    								map.put(map1.get("地块编号"),null);
    							}
    		            }    
    		            if(map1.get("用户")!=null) {
    		            	 Cell cell4 = row1.getCell(map1.get("用户"));
    			    			if (cell4 != null) {
    			    				cell4.setCellType(Cell.CELL_TYPE_STRING);
    								String value = cell4.getStringCellValue();
    								map.put(map1.get("用户"), value);
    							} else {
    								map.put(map1.get("用户"),null);
    							}
    		            }
    		            if(map1.get("种植面积")!=null) {
    		            	Cell cell5 = row1.getCell(map1.get("种植面积"));
    		    			if (cell5 != null) {
    		    				cell5.setCellType(Cell.CELL_TYPE_STRING);
    							String value = cell5.getStringCellValue();
    							map.put(map1.get("种植面积"), value);
    						} else {
    							map.put(map1.get("种植面积"),null);
    						}
    		            }   
    		            //未完成
    		            if(map1.get("1.0")!=null) {
    		            	 Cell cell6 = row1.getCell(map1.get("1.0"));
    			    			if (cell6 != null) {
    			    				cell6.setCellType(Cell.CELL_TYPE_STRING);
    								String value = cell6.getStringCellValue();
    								map.put(map1.get("1.0"), value);
    							} else {
    								map.put(map1.get("1.0"),null);
    							}
    		            }   
    		            //未完成
    		            if(map1.get("2.0")!=null) {
    		            	 Cell cell7 = row1.getCell(map1.get("2.0"));
    			    			if (cell7 != null) {
    			    				cell7.setCellType(Cell.CELL_TYPE_STRING);
    								String value = cell7.getStringCellValue();
    								map.put(map1.get("2.0"), value);
    							} else {
    								map.put(map1.get("2.0"),null);
    							}
    		            }    
    		            if(map1.get("3.0")!=null) {
    		            	 Cell cell8 = row1.getCell(map1.get("3.0"));
    			    			if (cell8 != null) {
    			    				cell8.setCellType(Cell.CELL_TYPE_STRING);
    								String value = cell8.getStringCellValue();
    								map.put(map1.get("3.0"), value);
    							} else {
    								map.put(map1.get("3.0"), null);
    						}
    		            }  
    		            if(map1.get("4.0")!=null) {
    		            	 Cell cell9 = row1.getCell(map1.get("4.0"));
    			    			if (cell9 != null) {
    			    				cell9.setCellType(Cell.CELL_TYPE_STRING);
    								String value = cell9.getStringCellValue();
    								map.put(map1.get("4.0"), value);
    							} else {
    								map.put(map1.get("4.0"), null);
    							}
    		            }
    		            if(map1.get("5.0")!=null) {
    		            	  Cell cell10 = row1.getCell(map1.get("5.0"));
    			    			if (cell10 != null) {
    			    				cell10.setCellType(Cell.CELL_TYPE_STRING);
    								String value = cell10.getStringCellValue();
    								map.put(map1.get("5.0"), value);
    							} else {
    								map.put(map1.get("5.0"), null);
    							}
    		            }   
    		            if(map1.get("6.0")!=null) {
    		            	   Cell cell11 = row1.getCell(map1.get("6.0"));
    			    			if (cell11 != null) {
    			    				cell11.setCellType(Cell.CELL_TYPE_STRING);
    								String value = cell11.getStringCellValue();
    								map.put(map1.get("6.0"), value);
    							} else {
    								map.put(map1.get("6.0"), null);
    							}
    		            } 
    		              
    		            if(map1.get("7.0")!=null) {
    		            	  Cell cell12 = row1.getCell(map1.get("7.0"));
    			    			if (cell12 != null) {
    			    				cell12.setCellType(Cell.CELL_TYPE_STRING);
    								String value = cell12.getStringCellValue();
    								map.put(map1.get("7.0"), value);
    							} else {
    								map.put(map1.get("7.0"), null);
    							}
    		            }
    		            if(map1.get("8.0")!=null) {
    		            	 Cell cell13 = row1.getCell(map1.get("8.0"));
    			    			if (cell13 != null) {
    			    				cell13.setCellType(Cell.CELL_TYPE_STRING);
    								String value = cell13.getStringCellValue();
    								map.put(map1.get("8.0"), value);
    							} else {
    								map.put(map1.get("8.0"), null);
    							}
    		            } 
    		            if(map1.get("9.0")!=null) {
    		            	 Cell cell14 = row1.getCell(map1.get("9.0"));
    			    			if (cell14 != null) {
    			    				cell14.setCellType(Cell.CELL_TYPE_STRING);
    								String value = cell14.getStringCellValue();
    								map.put(map1.get("9.0"), value);
    							} else {
    								map.put(map1.get("9.0"), null);
    							}
    		            }	   
    		            if(map1.get("10.0")!=null) {
    		            	 Cell cell14 = row1.getCell(map1.get("10.0"));
    			    			if (cell14 != null) {
    			    				cell14.setCellType(Cell.CELL_TYPE_STRING);
    								String value = cell14.getStringCellValue();
    								map.put(map1.get("10.0"), value);
    							} else {
    								map.put(map1.get("10.0"), null);
    							}
    		            }	   
    	            }
    	            result.add(map);
    			}
    			List<Integer> errorList=new ArrayList<Integer> ();
    			
    			
    		for (Map<Integer, String> eachValue : result) {
    			count++;
    			try {
    				
    				InsProjectGrowthtable table = new InsProjectGrowthtable();
    				table.setProjectid(image.getProjectid());
    	        	if(map1.get("县")!=null) {
    	        		table.setCounty(eachValue.get(map1.get("县")));
    	        	}
    	        	if(map1.get("镇")!=null) {
    	        		table.setTown(eachValue.get(map1.get("镇")));
    	        	}
    	        	if(map1.get("村")!=null) {
    	        		table.setCountry(eachValue.get(map1.get("村")));
    	        	}
    	        	if(map1.get("地块编号")!=null) {
    	        		table.setGrowthnum(eachValue.get(map1.get("地块编号")));
    	        	}
    	        	if(map1.get("用户")!=null) {
    	        		table.setGrowthuser(eachValue.get(map1.get("用户")));
    	        	}
    	        	if(map1.get("种植面积")!=null) {
    	        		if(eachValue.get(map1.get("种植面积"))==null || eachValue.get(map1.get("种植面积"))=="") {
    	        			table.setCropsarea(null);
    	        		}else {
    	        			table.setCropsarea(Double.valueOf(eachValue.get(map1.get("种植面积"))));
    	        		}
    	        		
    	        	}
    	        	if(map1.get("1.0")!=null) {
    	        		if(eachValue.get(map1.get("1.0"))==null || eachValue.get(map1.get("1.0"))=="") {
    	        			table.setOne(null);
    	        		}else {
    	        			table.setOne(Double.valueOf(eachValue.get(map1.get("1.0"))));
    	        		}
    	        		
    	        	}
    	        	if(map1.get("2.0")!=null) {
    	        		if(eachValue.get(map1.get("2.0"))==null || eachValue.get(map1.get("2.0"))=="") {
    	        			table.setTwo(null);
    	        		}else {
    	        			table.setTwo(Double.valueOf(eachValue.get(map1.get("2.0"))));
    	        		}
    	        		
    	        	}
    	        	if(map1.get("3.0")!=null) {
    	        		if(eachValue.get(map1.get("3.0"))==null || eachValue.get(map1.get("3.0"))=="") {
    	        			table.setThree(null);
    	        		}else {
    	        			table.setThree(Double.valueOf(eachValue.get(map1.get("3.0"))));
    	        		}
    	        		
    	        	}
    	        	if(map1.get("4.0")!=null) {
    	        		if(eachValue.get(map1.get("4.0"))==null || eachValue.get(map1.get("4.0"))=="") {
    	        			table.setFour(null);
    	        		}else {
    	        			table.setFour(Double.valueOf(eachValue.get(map1.get("4.0"))));
    	        		}
    	        		
    	        	}
    	        	if(map1.get("5.0")!=null) {
    	        		if(eachValue.get(map1.get("5.0"))==null || eachValue.get(map1.get("5.0"))=="") {
    	        			table.setFive(null);
    	        		}else {
    	        			table.setFive(Double.valueOf(eachValue.get(map1.get("5.0"))));
    	        		}
    	        		
    	        	}
    	        	if(map1.get("6.0")!=null) {
    	        		if(eachValue.get(map1.get("6.0"))==null || eachValue.get(map1.get("6.0"))=="") {
    	        			table.setSix(null);
    	        		}else {
    	        			table.setSix(Double.valueOf(eachValue.get(map1.get("6.0"))));
    	        		}
    	        	}
    	        	if(map1.get("7.0")!=null) {
    	        		if(eachValue.get(map1.get("7.0"))==null || eachValue.get(map1.get("7.0"))=="") {
    	        			table.setSeven(null);
    	        		}else {
    	        			table.setSeven(Double.valueOf(eachValue.get(map1.get("7.0"))));
    	        		}
    	        	}
    	        	if(map1.get("8.0")!=null) {
    	        		if(eachValue.get(map1.get("8.0"))==null || eachValue.get(map1.get("8.0"))=="") {
    	        			table.setEight(null);
    	        		}else {
    	        			table.setEight(Double.valueOf(eachValue.get(map1.get("8.0"))));
    	        		}
    	        	}
    	        	if(map1.get("9.0")!=null) {
    	        		if(eachValue.get(map1.get("9.0"))==null || eachValue.get(map1.get("9.0"))=="") {
    	        			table.setNine(null);
    	        		}else {
    	        			table.setNine(Double.valueOf(eachValue.get(map1.get("9.0"))));
    	        		}
    	        	}
    	        	if(map1.get("10.0")!=null) {
    	        		if(eachValue.get(map1.get("10.0"))==null || eachValue.get(map1.get("10.0"))=="") {
    	        			table.setTen(null);
    	        		}else {
    	        			table.setTen(Double.valueOf(eachValue.get(map1.get("10.0"))));
    	        		}
    	        	}
    			} catch (Exception e ) {
    			   e.printStackTrace();			
    			   errorList.add(count);
    			}  	
    		}
    		if(!errorList.isEmpty()) {
    			String errorCount = StringUtils.join(errorList.toArray(), ",");
    			   info.setCode(300);
    			   info.setData(false);
    			   info.setMessage("导入项目长势Excel");
    			   info.setResult("Excel文件第"+errorCount+"行字段不合法,请检查重新上传!");
    			   return false;
    		}
    		//数据分块为1000每批
    	    int batchNum=1000;
    		//插入
    		this.batchDeal(result,batchNum,image.getProjectid(),map1,info,fileName,user.getId(),projectMapper);
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    	    info.setCode(200); 
    		info.setData(true);   
    		info.setResult("成功"); 
    		info.setMessage("导入项目长势Excel"); 
    		return true;
    	}
    	 //线程池数据分批导入
    	 private void batchDeal(List<Map<Integer, String>> data, int batchNum,int projectid, Map<String, Integer> map1, JsonResultInfo info, String fileName, Integer id, ProjectMapper projectMapper) throws InterruptedException {
    		          int totalNum = data.size();
    		          int pageNum = totalNum % batchNum == 0 ? totalNum / batchNum : totalNum / batchNum + 1;
    		          ExecutorService executor = Executors.newFixedThreadPool(pageNum);      
    		          try {
    		              CountDownLatch countDownLatch = new CountDownLatch(pageNum);
    		              List<Map<Integer, String>> subData = null;
    		              int fromIndex, toIndex;
    		              for (int i = 0; i < pageNum; i++) {
    		                 fromIndex = i * batchNum;
    		                 toIndex = Math.min(totalNum, fromIndex + batchNum);
    		                 subData = data.subList(fromIndex, toIndex);
    		                ImportTask task = new ImportTask(subData, countDownLatch,projectid,map1,info,fileName,id,projectMapper);
    		                executor.execute(task);
    		             }
    		             // 主线程必须在启动其它线程后立即调用CountDownLatch.await()方法,
    		             // 这样主线程的操作就会在这个方法上阻塞,直到其它线程完成各自的任务。
    		             // 计数器的值等于0时,主线程就能通过await()方法恢复执行自己的任务。
    		             countDownLatch.await();
    		         } finally {
    		             // 关闭线程池,释放资源
    		             executor.shutdown();
    		         }
    		     
    		          
    	}

    utils

    package com.kero99.utils;
    
    import java.util.Date;
    import java.util.List;
    import java.util.Map;
    import java.util.concurrent.CountDownLatch;
    import org.springframework.transaction.interceptor.TransactionAspectSupport;
    import com.kero99.mapper.ProjectMapper;
    import com.kero99.pojo.InsProjectGrowthtable;
    /**
     * 线程池分批导入数据
     * @author ygc
     *
     */
    public class ImportTask implements Runnable {
             private List<Map<Integer, String>> list;
             private CountDownLatch countDownLatch;
             private int projectid;
             private Map<String, Integer> map1;
             private JsonResultInfo info;
             private String fileName;
             private Integer id;
             private ProjectMapper projectMapper;
             public ImportTask(List<Map<Integer, String>> data, CountDownLatch countDownLatch,int projectid, Map<String, Integer> map1, JsonResultInfo info, String fileName, Integer id, ProjectMapper projectMapper) {
                 this.list = data;
                 this.countDownLatch = countDownLatch;
                 this.projectid=projectid;
                 this.map1=map1;
                 this.info=info;
                 this.fileName=fileName;
                 this.id=id;
                 this.projectMapper=projectMapper;
             }
     
             @Override
             public void run() {
    //        		int count=1;
                 if (null != list) {
                	 for (Map<Integer, String> eachValue : list) {
    //         			count++;
             			try {
             				InsProjectGrowthtable table = new InsProjectGrowthtable();
             				table.setProjectid(projectid);
             	        	if(map1.get("县")!=null) {
             	        		table.setCounty(eachValue.get(map1.get("县")));
             	        	}
             	        	if(map1.get("镇")!=null) {
             	        		table.setTown(eachValue.get(map1.get("镇")));
             	        	}
             	        	if(map1.get("村")!=null) {
             	        		table.setCountry(eachValue.get(map1.get("村")));
             	        	}
             	        	if(map1.get("地块编号")!=null) {
             	        		table.setGrowthnum(eachValue.get(map1.get("地块编号")));
             	        	}
             	        	if(map1.get("用户")!=null) {
             	        		table.setGrowthuser(eachValue.get(map1.get("用户")));
             	        	}
             	        	if(map1.get("种植面积")!=null) {
             	        		if(eachValue.get(map1.get("种植面积"))==null || eachValue.get(map1.get("种植面积"))=="") {
             	        			table.setCropsarea(null);
             	        		}else {
             	        			table.setCropsarea(Double.valueOf(eachValue.get(map1.get("种植面积"))));
             	        		}
             	        		
             	        	}
             	        	if(map1.get("1.0")!=null) {
             	        		if(eachValue.get(map1.get("1.0"))==null || eachValue.get(map1.get("1.0"))=="") {
             	        			table.setOne(null);
             	        		}else {
             	        			table.setOne(Double.valueOf(eachValue.get(map1.get("1.0"))));
             	        		}
             	        		
             	        	}
             	        	if(map1.get("2.0")!=null) {
             	        		if(eachValue.get(map1.get("2.0"))==null || eachValue.get(map1.get("2.0"))=="") {
             	        			table.setTwo(null);
             	        		}else {
             	        			table.setTwo(Double.valueOf(eachValue.get(map1.get("2.0"))));
             	        		}
             	        		
             	        	}
             	        	if(map1.get("3.0")!=null) {
             	        		if(eachValue.get(map1.get("3.0"))==null || eachValue.get(map1.get("3.0"))=="") {
             	        			table.setThree(null);
             	        		}else {
             	        			table.setThree(Double.valueOf(eachValue.get(map1.get("3.0"))));
             	        		}
             	        		
             	        	}
             	        	if(map1.get("4.0")!=null) {
             	        		if(eachValue.get(map1.get("4.0"))==null || eachValue.get(map1.get("4.0"))=="") {
             	        			table.setFour(null);
             	        		}else {
             	        			table.setFour(Double.valueOf(eachValue.get(map1.get("4.0"))));
             	        		}
             	        		
             	        	}
             	        	if(map1.get("5.0")!=null) {
             	        		if(eachValue.get(map1.get("5.0"))==null || eachValue.get(map1.get("5.0"))=="") {
             	        			table.setFive(null);
             	        		}else {
             	        			table.setFive(Double.valueOf(eachValue.get(map1.get("5.0"))));
             	        		}
             	        		
             	        	}
             	        	if(map1.get("6.0")!=null) {
             	        		if(eachValue.get(map1.get("6.0"))==null || eachValue.get(map1.get("6.0"))=="") {
             	        			table.setSix(null);
             	        		}else {
             	        			table.setSix(Double.valueOf(eachValue.get(map1.get("6.0"))));
             	        		}
             	        	}
             	        	if(map1.get("7.0")!=null) {
             	        		if(eachValue.get(map1.get("7.0"))==null || eachValue.get(map1.get("7.0"))=="") {
             	        			table.setSeven(null);
             	        		}else {
             	        			table.setSeven(Double.valueOf(eachValue.get(map1.get("7.0"))));
             	        		}
             	        	}
             	        	if(map1.get("8.0")!=null) {
             	        		if(eachValue.get(map1.get("8.0"))==null || eachValue.get(map1.get("8.0"))=="") {
             	        			table.setEight(null);
             	        		}else {
             	        			table.setEight(Double.valueOf(eachValue.get(map1.get("8.0"))));
             	        		}
             	        	}
             	        	if(map1.get("9.0")!=null) {
             	        		if(eachValue.get(map1.get("9.0"))==null || eachValue.get(map1.get("9.0"))=="") {
             	        			table.setNine(null);
             	        		}else {
             	        			table.setNine(Double.valueOf(eachValue.get(map1.get("9.0"))));
             	        		}
             	        	}
             	        	if(map1.get("10.0")!=null) {
             	        		if(eachValue.get(map1.get("10.0"))==null || eachValue.get(map1.get("10.0"))=="") {
             	        			table.setTen(null);
             	        		}else {
             	        			table.setTen(Double.valueOf(eachValue.get(map1.get("10.0"))));
             	        		}
             	        	}
             				table.setCreateuser(id);
             				table.setCreatedate(new Date());
             				table.setDate(fileName);
             				int temp = projectMapper.saveExcelProjectGrowthInfo(table);
             			} catch (Exception e ) {
             			   e.printStackTrace();			
    //         			   info.setCode(300);
    //         			   info.setData(false);
    //         			   info.setMessage("导入长势Excel");
    //         			   info.setResult("Excel文件第"+count+"行字段不合法,请检查重新上传!");
             			   TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();//如果updata2()抛了异常,updata()会回滚,不影响事物正常执行                                                                     
             		
             			}
             			   
                     	
             		}
                 }
                 // 发出线程任务完成的信号
                 countDownLatch.countDown();
             }
         }

     

    展开全文
  • 本文是基于Apache poi类实现的批量导入读取Excel文件,所以要先引入Apache poi的依赖 <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <...

    本文是基于Apache poi类实现的批量导入读取Excel文件,所以要先引入Apache poi的依赖

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

    在引入依赖之后,我们就可以开始进行操作了,首先,导入Excel数据,我们要先能够读取Excel每一行每一列的内容,只有读取到内容了,才可以将内容存入数组,最后实现插入数据库。所以我们要先读取Excel表格的数据,我的项目是springboot,我在service定义了一个读取方法,然后在impl里面进行实现,具体代码为

    public class ImportOrderDTO {
        private String filePath;
    
        public String getFilePath() {
            return filePath;
        }
    
        public void setFilePath(String filePath) {
            this.filePath = filePath;
        }
    }    
    /**
         * 读取导入数据 excel 内容
         *
         * @param importOrderDTO 导入参数
         * @param rootPath       根路径
         * @return result
         */
        public static final String SEPA = File.separator;//这是下面用到的常量,自己放好位置
        private List<User> readExcel(ImportOrderDTO importOrderDTO, String rootPath) {
            List<User> excelContent = new ArrayList<>();
            try {
                InputStream inputStream = new FileInputStream(rootPath + SEPA + importOrderDTO.getFilePath());
                XSSFWorkbook wb = new XSSFWorkbook(inputStream);
                //遍历所有表,只支持xlsx,xls的是H的类
                XSSFSheet xssfSheet = wb.getSheetAt(0);
                int lastRowNum = xssfSheet.getLastRowNum();
                for (int i = 0; i <= lastRowNum; i++) {
    //            通过下标获取行
                    XSSFRow row = xssfSheet.getRow(i);
    //            从行中获取数据
                    if (row.getRowNum() == 0) {
                        continue;
                    }
    
                    //第一列为空就跳出
                    if (row.getCell(0) == null) {
                        continue;
                    }
                    /**
                     * getNumericCellValue() 获取数字
                     * getStringCellValue 获取String,设置表格类型为String,可以避免很多问题
                     */
                    row.getCell(0).setCellType(CellType.STRING);
                    row.getCell(1).setCellType(CellType.STRING);
                    row.getCell(3).setCellType(CellType.STRING);
                    row.getCell(5).setCellType(CellType.STRING);
                   //UserInformPO是我自己定义的数据类,你们导入需要哪些数据就封装哪些,这就不用多讲了吧
                    UserInformPO userInformPO = new UserInformPO();
                    userInformPO.setAccount(row.getCell(0).getStringCellValue());
                    userInformPO.setIdCard(row.getCell(1).getStringCellValue());
                    userInformPO.setAvatar(row.getCell(2).getStringCellValue());
                    userInformPO.setNickname(row.getCell(3).getStringCellValue());
                    userInformPO.setSex(row.getCell(4).getStringCellValue());
                    String salt = EncryptUtils.createSalt();
                    userInformPO.setSalt(salt);
                    //职业类别
                    userInformPO.setIntegral(0);
                    userInformPO.setVipLevel(0);
                    userInformPO.setIsEnabled(0);
                    userInformPO.setDelFlag(0);
                    //然后将po转到我的实体类entity里面并将实体类加入到数组,方便正式执行批量导入的时候可以用,一些没有的类是我自己定义的加密的(规范)
                    User user = new User();
                    if (userInformPO.getSex().equals(SexEnum.MAN.getText())) {
                        user.setSex(SexEnum.MAN.getValue());
                    } else {
                        user.setSex(SexEnum.WOMAN.getValue());
                    }
                    user.setAccount(userInformPO.getAccount());
                    user.setIdCard(userInformPO.getIdCard());
                    user.setAvatar(userInformPO.getAvatar());
                    user.setNickname(userInformPO.getNickname());
                    user.setPassword(userInformPO.getPassword());
                    user.setSalt(userInformPO.getSalt());
                    user.setIntegral(userInformPO.getIntegral());
                    user.setVipLevel(userInformPO.getVipLevel());
                    user.setDepartmentId(31);
                    user.setIsEnabled(userInformPO.getIsEnabled());
                    user.setDelFlag(userInformPO.getDelFlag());
                    user.setUpdateTime(new Timestamp(System.currentTimeMillis()));
                    user.setCreateTime(new Timestamp(System.currentTimeMillis()));
                    //加入到数组中并且该方法返回该数组
                    excelContent.add(user);
                }
            } catch (FileNotFoundException e) {
                throw new ServerException("文件不存在");
            } catch (IOException e) {
                System.out.println(e);
                throw new ServerException("读取文件失败");
            }
            return excelContent;
        }

    在上面写完读取Excel表单数据后,就可以开始写插入数据库的方法了,我用的是mybatis plus ,方法直接写到下面,大家不会陌生,返回方法是我封装的类,大家用自己项目的稍加修改就可以

    public Result importUserWithExcel(ImportOrderDTO importOrderDTO, String rootPath) {
            try {
                //调用上面的方法,读取前端传过来的参数和文件路径
                List<User> excelContent = readExcel(importOrderDTO, rootPath);
                if (excelContent.isEmpty()) {
                    return ResultUtil.error("数据为空");
                }
                //下面都是一些逻辑处理,大家一定看得懂就不多说了
                List<User> userList = userMapper.selectList(new EntityWrapper<User>());
                List<User> sameList = new ArrayList<>();
                List<User> differentList = new ArrayList<>();
                for (User excelStudent : excelContent) {
                    //数据不同
                    boolean flag = true;
                    for (User user : userList) {
                        if (user.getAccount().equals(excelStudent.getAccount())) {
                            //相同的数据
                            flag = false;
                            sameList.add(excelStudent);
                        }
                    }
                    if (flag) {
                        //如果导入的数据与上面判断的条件相等了,就执行插入操作
                        differentList.add(excelStudent);
                        userMapper.insert(excelStudent);
                    }
                }
                if (differentList.size() == 0) {
                    //判断如果导入的数据跟当前的数据一致的话
                    return ResultUtil.error("导入数据与当前数据一致!");
                }
                return ResultUtil.successWithMessage("数据导入成功");
            } catch (Exception e) {
                System.out.println(e);
                return ResultUtil.error("数据导入失败,请检查导入文件格式与模板文件是否相同!");
            }
        }

    service层方法写完了,接着就是controller了,直接上代码,工具类在下面

        @PostMapping(value = "/importExcel")
        public Result importExcel(HttpServletRequest request, @RequestBody ImportOrderDTO importOrderDTO) {
            String rootPath = ExcelUtils.getRootPath(request);
            System.out.println(importOrderDTO.getFilePath());
            return userService.importUserWithExcel(importOrderDTO, rootPath);
        }

    这是获取根路径的工具类

    public class ExcelUtils {
        /**
         * 获取上传根路径
         *
         * @param request 请求信息
         * @return string
         */
        public static String getRootPath(HttpServletRequest request) {
            return request.getSession().getServletContext().getRealPath(UtilConstant.UPLOAD_PATH);
        }
    
        public static Workbook getWorkbook(String excelType) {
            try {
                return WorkbookFactory.create(!ExcelTypeEnum.XLS.getVal().equals(excelType));
            } catch (IOException e) {
                throw new ServerException("创建excel文件失败", e);
            }
        }
    
        /**
         * 创建表
         *
         * @param wb         目标文件
         * @param sheetName  表名
         * @param sheetTitle 表头
         * @return sheet
         */
        public static Sheet createSheet(Workbook wb, String sheetName, String[] sheetTitle) {
            Sheet sheet = wb.createSheet(sheetName);
    
            Row row = sheet.createRow(0);
    
            for (int i = 0; i < sheetTitle.length; i++) {
                row.createCell(i).setCellValue(sheetTitle[i]);
            }
    
            return sheet;
        }
    
    
        /**
         * 创建单元格.
         *
         * @param row    行
         * @param column 列
         * @param value  值
         */
        public static void createCell(Row row, int column, String value) {
            Cell cell = row.createCell(column);
            cell.setCellValue(value);
        }
    }

    好了,一个简单的Excel批量导入数据就完成了,感谢支持,大家有什么问题都可以评论留言哦

    展开全文
  • 今天小编就为大家分享一篇解决Java导入excel大量数据出现内存溢出的问题,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧
  • Java实现Excel批量导入数据库

    千次阅读 2020-02-14 00:15:05
    springboot实现Excel批量导入数据库 前言:Java实现,Excel数据表,MySQL数据库,具体步骤如下…

    springboot实现Excel批量导入数据库

    前言:项目开发中最容易碰到的需求即将Excel数据批量上传到数据库中存储 -> Java实现,Excel数据表,MySQL数据库,具体步骤如下…


    1.新建excel表,填写测试数据

    在这里插入图片描述

    2.新建项目工程,修改pom依赖以及application.properties如下

    pom.xml

    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.1.1</version>
    </dependency>
    <!--java对象状态自动映射到关系数据库中数据上-->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-orm</artifactId>
        <version>5.0.2.RELEASE</version>
    </dependency>
    <!--实现类与xml之间的相互转换-->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-oxm</artifactId>
        <version>5.0.2.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>5.2.12.Final</version>
    </dependency>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-jpamodelgen</artifactId>
        <version>5.2.12.Final</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.data</groupId>
        <artifactId>spring-data-jpa</artifactId>
        <version>2.2.3.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <!--前端页面我这里使用了thymeleaf模板引擎-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-thymeleaf</artifactId>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.12</version>
    </dependency>
    <!--使用POI读取文件-->
    <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>
    

    application.properties

    spring.datasource.username=123456
    spring.datasource.password=123456
    spring.datasource.url=jdbc:mysql://localhost:3306/excel?useUnicode=true&useSSL=false&characterEncoding=UTF-8&serverTimezone=UTC
    #配置通过jpa自动创建表
    spring.jpa.hibernate.ddl-auto=create
    #打印SQL
    spring.jpa.show-sql=true
    
    3.创建实体类,进行关系映射

    启动项目就会自动创建实体类中的表,创建完之后会发现数据库里的字段和实体类里的字段顺序是不一样,是乱序状态,是因为hibernate源码中用的是TreeMap存储实体类字段,TreeMap属性是无序的;所以解决办法如下:

    • 找到源码文件
      在这里插入图片描述
    • 在本项目中创建一个和源码类一样的包结构和一样名字的类,复制源码文件所有代码到新建的类中
      在这里插入图片描述
    • 将上图标识的TreeMap 修改为LinkedHashMap修改好之后启动项目,会发现程序走的是新创建的类
    package com.wxy.excel.entity;
    import javax.persistence.Column;
    import javax.persistence.Entity;
    import javax.persistence.Id;
    import javax.persistence.Table;
    import java.io.Serializable;
    
    @Entity
    @Table(name = "excel")
    public class Excel implements Serializable {
        //serialVersionUID适用于java序列化机制
        private static final long serialVersionUID = 1L;
        @Id
        @Column(length=36)
        private String id;
        @Column(length=45,nullable=false,unique=true)
        private String username;
        @Column(length=100,nullable=false,unique=true)
        private String email;
        @Column(length=45,nullable=false)
        private String password;
        @Column(length=45)
        private String role;
    
       //省略getter and setter 以及 constructor
    
    
    
    4.Excel的DAO类接口,与Excel有关的持久化操作方法
    package com.wxy.excel.mapper;
    import com.wxy.excel.entity.Excel;
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.stereotype.Repository;
    
    @Repository
    public interface ExcelRepository extends JpaRepository<Excel,String> {
    }
    
    
    5.创建service层接口

    Excel的Service类接口,与Excel有关的业务逻辑方法

    package com.wxy.excel.service;
    
    import org.springframework.web.multipart.MultipartFile;
    
    public interface ExcelService {
    
        boolean getExcel(MultipartFile file) throws Exception;
    }
    
    
    6.service层方法实现

    Excel的Service类,与用户信息有关的业务逻辑方法

    package com.wxy.excel.service;
    import com.wxy.excel.mapper.ExcelRepository;
    import com.wxy.excel.entity.Excel;
    import org.apache.poi.ss.usermodel.*;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import org.springframework.web.multipart.MultipartFile;
    import java.util.ArrayList;
    import java.util.List;
    
    @Service
    public class ExcelServiceImpl implements ExcelService{
        @Autowired
        private ExcelRepository excelRepository;
        @Override
        public boolean getExcel(MultipartFile file) throws Exception {
            List<Excel> list = new ArrayList<Excel>();
            //1.得到上传的表
            Workbook workbook2 = WorkbookFactory.create(file.getInputStream());
            //2.获取test工作表 注意test就是excel下面的sheet名称
            Sheet sheet2 = workbook2.getSheet("test");
            //3.获取表的总行数
            int num = sheet2.getLastRowNum();
            //4.获取表总列数
            int col = sheet2.getRow(0).getLastCellNum();
            //5.遍历excel每一行
            for (int j = 0; j <= num; j++) {
                Row row1 = sheet2.getRow(j);
                // 如果单元格中有数字或者其他格式的数据,则调用setCellType()转换为string类型
                Cell cell1 = row1.getCell(0);
                cell1.setCellType(CellType.STRING);
                //获取表中第i行,第2列的单元格
                Cell cell2 = row1.getCell(1);
                cell2.setCellType(CellType.STRING);
                //获取excel表的第i行,第3列的单元格
                Cell cell3 = row1.getCell(2);
                cell3.setCellType(CellType.STRING);
                Cell cell4 = row1.getCell(3);
                cell4.setCellType(CellType.STRING);
                Cell cell5 = row1.getCell(4);
                cell5.setCellType(CellType.STRING);
                //这里new 一个对象,用来装填从页面上传的Excel数据,字段根据上传的excel决定
                Excel excel= new Excel();
                excel.setId(cell1.getStringCellValue());
                excel.setUsername(cell2.getStringCellValue());
                excel.setEmail(cell3.getStringCellValue());
                excel.setPassword(cell4.getStringCellValue());
                excel.setRole(cell5.getStringCellValue());
                list.add(excel);
                System.out.println("excel"+excel);
            }
            excelRepository.saveAll(list);
            return true;
        }
    }
    
    
    7.Controller层实现
    package com.wxy.excel.controller;
    import com.wxy.excel.service.ExcelService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Controller;
    import org.springframework.ui.Model;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.PostMapping;
    import org.springframework.web.multipart.MultipartFile;
    
    @Controller
    public class ExcelController {
    
        @Autowired
        private ExcelService excelService;
    
        @GetMapping("/upload")
        public String goUpload(){
            return "upload";
        }
        @PostMapping("/excel")
        public String upload(MultipartFile file, Model model) throws Exception {
            boolean flag = excelService.getExcel(file);
            if(flag){
                model.addAttribute("Message", "上传成功");
            }else{
                model.addAttribute("Message", "上传失败");
            }
            return "upload";
        }
    }
    
    
    8.前端html实现
    <!DOCTYPE html>
    <html lang="en">
    <head>
        <meta charset="UTF-8">
        <title>批量上传</title>
    </head>
    <body>
        <form enctype="multipart/form-data" method="post" action="/excel">
            <input type="file" name="file" /> <input type="submit" value="上传" />
        </form>
    </body>
    </html>
    
    9.项目结构图,以及实现文件上传效果如下

    在这里插入图片描述

    在这里插入图片描述

    10.项目demo已经上传到github,下载地址

    https://github.com/Wangxy9527/ExcelToDatabase

    或者直接clone即可:

    git@github.Wangxy9527/ExcelToDatabase.git

    https://github.com/Wangxy9527/ExcelToDatabase.git

    结语:本文主要实现功能为将Excel数据导入到数据库中,不懂不理解或者对以上文章有疑问的小伙伴欢迎留言…

    展开全文
  • JAVA实现Excel批量导入

    万次阅读 2019-04-30 09:27:04
    * @Description: 批量导入客户信息 * @param @param request * @param @param response * @param @return * @return String * @throws */ @RequestMapping("impExcel") @ResponseBody public String ...

    一、模板下载:

    先将模板放在项目WebRoot下的download文件夹下:

     

     View Code

     

     

    复制代码

    /**
         * 
         * @Title: download 
         * @Description: 下载本地文件
         * @param @param path
         * @param @param response
         * @param @param request
         * @return void
         * @throws
         */
        @RequestMapping(value = "/download")
        public void download(String type, HttpServletResponse response, HttpServletRequest request) {
            
            User u = getUser(request.getSession());//SessionUtils.getUser(request.getSession());
            
            //获取网站部署路径(通过ServletContext对象),用于确定下载文件位置,从而实现下载  
            //path = request.getServletContext().getRealPath("/") + "download\\" + "我的客户导入模板.xls";
            String path = request.getServletContext().getRealPath("/");  
            
            List<BasedataResp> list1 = null;
            List<BasedataResp> list2 = null;
            QueryBasedataParam param1 = new QueryBasedataParam();
            QueryBasedataParam param2 = new QueryBasedataParam();
            param1.setCorpId(Long.valueOf(u.getCorpId()));
            param2.setCorpId(Long.valueOf(u.getCorpId()));
            
            String fileName = "";
            if("1".equals(type)){
                
                fileName = "我的客户导入模板";
                param1.setLabel("custom_status"); //客户状态
                param2.setLabel("custom_level"); //客户分级
            } else if("2".equals(type)){
                
                fileName = "客户关联联系人导入模板";
                param1.setLabel("contacts_role"); //角色关系
                param2.setLabel("contacts_relation"); //亲密程度
            }
            String url = path + "download\\" + fileName + ".xls";
            
            try {  
                
                if(param1.getLabel()!=null && !"".equals(param1.getLabel())){
                    list1 = basedataService.selectBasedataInfo(param1); //查询系统标签 
                    list2 = basedataService.selectBasedataInfo(param2); //查询系统标签 
                    write(url, list1, list2);
                }
                ExcelExportUtil.getExcel(url, fileName, response); //下载sheet
                
            } catch (Exception e) {  
                System.out.println(e.getMessage());
            }  
        }
        
        /**
         * 
         * @Title: write 
         * @Description: 向已存在的Excel写入数据 
         * @param @param file
         * @param @param list
         * @param @param list2
         * @param @return
         * @return String
         * @throws
         */
        private String write(String file, List<BasedataResp> list, List<BasedataResp> list2) {
            
            try {
                
                FileInputStream fs = new FileInputStream(file);  //获取已有的Excel  
                POIFSFileSystem ps = new POIFSFileSystem(fs);  //使用POI提供的方法得到excel的信息   
                HSSFWorkbook wb = new HSSFWorkbook(ps);    
                HSSFSheet sheet1 = wb.getSheetAt(0);  //获取第一个工作表,一个excel可能有多个工作表 
                HSSFSheet sheet2 = wb.getSheetAt(1); //获取第二个sheet
                HSSFSheet sheet3 = wb.getSheetAt(2); //获取第三个sheet
                
                sheet2.removeRow(sheet2.getRow(0));
                sheet3.removeRow(sheet3.getRow(0));
                
                FileOutputStream out = new FileOutputStream(file);  //向d://test.xls中写数据
                
                HSSFRow row;
           //向第二个sheet写入数据(第一个sheet中的下拉选项)
                for (int i = 0; i < list.size(); i++) {
                    row = sheet2.createRow((short)(i)); //创建行  
                    row.createCell(0).setCellValue(list.get(i).getName()); //设置第一个(从0开始)单元格的数据
                }
                
           //向第三个sheet写入数据(第一个sheet中的下拉选项) 
                HSSFRow row2;
                for (int i = 0; i < list2.size(); i++) {
                    row2 = sheet3.createRow((short)(i)); //创建行    
                    row2.createCell(0).setCellValue(list2.get(i).getName()); //设置第一个(从0开始)单元格的数据
                }
                
                out.flush();  
                wb.write(out);    
                out.close(); 
                
            } catch (Exception e) {
                System.out.println(e.getMessage());
            }
            
            return "success";
        }

    复制代码

     

    复制代码

    /**
         * 
         * @Title: getExcel 
         * @Description: 下载指定路径的Excel文件 
         * @param @param url 文件路径
         * @param @param fileName  文件名
         * @param @param response
         * @return void
         * @throws
         */
        public static void getExcel(String url, String fileName, HttpServletResponse response){
            
             try {  
                
                //1.设置文件ContentType类型,这样设置,会自动判断下载文件类型  
                response.setContentType("multipart/form-data");  
                
                //2.设置文件头:最后一个参数是设置下载文件名  
                response.setHeader("Content-Disposition", "attachment;filename="  
                        + new String(fileName.getBytes("gb2312"), "ISO-8859-1") + ".xls"); //支持中文文件名
                
                //通过文件路径获得File对象
                File file = new File(url);  
                
                FileInputStream in = new FileInputStream(file);  
                //3.通过response获取OutputStream对象(out)  
                OutputStream out = new BufferedOutputStream(response.getOutputStream());  
                
                int b = 0;  
                byte[] buffer = new byte[2048];  
                while ((b=in.read(buffer)) != -1){  
                    out.write(buffer,0,b); //4.写到输出流(out)中  
                }  
                
                in.close();
                out.flush(); 
                out.close();  
                
            } catch (IOException e) {  
                e.printStackTrace();  
            }  
        }
        

    复制代码

     

    模板截图:

     

    二、通过Excel模板导入数据:

    复制代码

        /**
         * 
         * @Title: impExcel 
         * @Description: 批量导入客户信息
         * @param @param request
         * @param @param response
         * @param @return
         * @return String
         * @throws
         */
        @RequestMapping("impExcel")
        @ResponseBody
        public String impExcel(MultipartHttpServletRequest request,HttpServletResponse response){
            
            ReturnStandardDataFormat standardData = new ReturnStandardDataFormat(CustomConstants.CUSTOM_SELECT_EXCEPTION,"导入客户信息失败",null);
            
            MultipartFile file = request.getFile("file");
            ExcelReader er = new ExcelReader();
            int count =0;
            int error =0;
            int success = 0;
            
            List<Custom> list_ = new ArrayList<Custom>();
            User u = getUser(request.getSession());//SessionUtils.getUser(request.getSession());
            Long corpId = Long.valueOf(u.getCorpId());
            Date date = new Date();
            String returnMsg = "";
            int index = 1;
            
            try {
                
                List<Map<Integer,String>> list = er.readExcelContentByList(file.getInputStream()); //读取Excel数据内容
                count = list.size();
                
                for(Map<Integer,String> map : list){
                    
                    if(map.get(0)==null || "".equals(map.get(0))){
                        returnMsg += "第"+index+"行:【客户简称(必填)】列不能为空;";
                    } else if(map.get(1)==null || "".equals(map.get(1))){
                        returnMsg += "第"+index+"行:【客户全称(必填)】列不能为空;";
                    } else {
                        int num = 0;
                        QueryCustomParam params = new QueryCustomParam();
                        params.setShortName(map.get(0));
                        params.setCorpId(Long.valueOf(u.getCorpId()));
                        num = customService.checkCustom(params); //查询相同客户
                        
                        if(num==0){
                            Custom custom = new Custom();
                            custom.setId(UUIDUtil.getLongUUID());
                            custom.setShortName(map.get(0)==null? null : map.get(0));
                            custom.setName(map.get(1)==null? null : map.get(1));
                            custom.setNumber(map.get(2)==null? null : map.get(2));
                            custom.setAddress(map.get(3)==null? null : map.get(3));
                            custom.setUrl(map.get(4)==null? null : map.get(4));
                            custom.setDescription(map.get(5)==null? null : map.get(5));
                            custom.setCustomStatusId(map.get(6)==null? null : basedataService.getLabelId("custom_status", map.get(6), corpId) ); 
                            custom.setCustomLevelId(map.get(7)==null? null : basedataService.getLabelId("custom_level", map.get(7), corpId) );         
                            custom.setCreaterId(Long.valueOf(u.getUserId()));
                            custom.setCreateDate(date);
                            custom.setUpdaterId(Long.valueOf(u.getUserId()));
                            custom.setUpdateDate(date);
                            custom.setCorpId(Long.valueOf(u.getCorpId()));
                            
                            list_.add(custom);
                            
                        } else {
                            returnMsg += "第"+index+"行:【客户简称(必填)】列:"+ map.get(0)+"已存在;";
                        }
                        index++;
                    }
                }
                
                int cuccess = customService.batchInsert(list_); //批量导入客户信息
                
                standardData.setReturnCode(0);
                standardData.setReturnData(null);
                
                error = count - success;
                standardData.setReturnMessage(returnMsg);
                
            } catch (Exception e) {
                log.error("批量导入客户信息异常:" + e.getMessage());
                standardData.setReturnMessage(e.getMessage());
            }
            
            return JsonHelper.encodeObject2Json(standardData, "yyyy-MM-dd HH:mm:ss");
        }
        

    复制代码

     

    读取Excel内容工具类:

    复制代码

    /**
         * 读取Excel数据内容
         * @param InputStream
         * @return List<Map<String, String>>  Map的key是列Id(0代表第一列),值是具体内容
         */
        public List<Map<Integer, String>> readExcelContentByList(InputStream is) {
            
            List<Map<Integer, String>> list = new ArrayList<Map<Integer,String>>();
            
            try {
                //fs = new POIFSFileSystem(is);
                wb = new HSSFWorkbook(is);
                //wb = new XSSFWorkbook(is);
            } catch (IOException e) {
                e.printStackTrace();
            }
            
            sheet = wb.getSheetAt(0);
            
            // 得到总行数
            int rowNum = sheet.getLastRowNum();
            row = sheet.getRow(0);
            int colNum = row.getPhysicalNumberOfCells();
            
            // 正文内容应该从第二行开始,第一行为表头的标题
            for (int i = 1; i <= rowNum; i++) {
                row = sheet.getRow(i);
                int j = 0;
                Map<Integer,String> map = new HashMap<Integer, String>();
                
                while (j < colNum) {
                    // 每个单元格的数据内容用"-"分割开,以后需要时用String类的replace()方法还原数据
                    // 也可以将每个单元格的数据设置到一个javabean的属性中,此时需要新建一个javabean
                    // str += getStringCellValue(row.getCell((short) j)).trim() +
                    // "-";
                    
                    map.put(j, getCellFormatValue(row.getCell((short) j)).trim().replaceAll("\t\r", ""));
                    //str += getCellFormatValue(row.getCell((short) j)).trim() + "    ";
                    j++;
                }
                list.add(map);
            }
            return list;
        }
    展开全文
  • 并把识别后的数据批量导入到mysql数据库 所用知识点 springboot 2.1.9 excel文件上传 excel数据批量识别 excel数据上传到mysql数据库 jpa的使用 jpa的使用我们在上一节已经给大家讲过了,...
  • java批量导入Excel数据到数据库

    千次阅读 2018-01-24 17:15:26
    "Excel内没有数据!" ); } getData (sheet, lineNum, rows, person); } catch (Exception e){ e.printStackTrace(); } } public static void getData(Sheet sheet, int lineNum, int rowNum, Person...
  • java 解析Excel 并判断解析的数据类型__Eclipse工程,纯手工,无复制
  • //5、写入实体数据 if(null != tbTaxOrderList && tbTaxOrderList.size() > 0){ for (int i = 0; i (); i++) { //3.2、创建行----内容行 row = sheet.createRow(i+1); TbTaxOrder order = (TbTaxOrder)...
  • JAVA POI批量导入EXCEL数据到数据库

    千次阅读 2018-03-13 14:21:35
    首先先记录下碰到的问题: 原先想直接传要上传的文件路径到后端,然后后端绝对定位到相应文件进行数据的解析,后面发现浏览器这边为了安全问题,是不能获得文件的真实路径的,只能获得一个虚假的路径,然后这种做法就行...
  • 利用poi获取excel中大量数据,然后利用jdbc批量插入到mysql,直接运行main方法
  • java运用poi把excel数据导入到oracle数据库中,上场内容未自己写的小demo,可下载查看,不清楚的可以联系我进行帮助。
  • Java实现Excel数据批量导入数据库 概述: 这个小工具类是工作中的一个小插曲哦,因为提数的时候需要跨数据库导数... 有的是需要从oracle导入mysql ,有的是从mysql导入oracle 实践之后我们会发现 使用navicat...
  • 批量获取excel的指定列表的值转换格式后导入word文档指定位置中;适用于代理人(律师)整理案件文书,或者一些需要批量操作的工作者
  • 1、创建导入抽象类package com.gcloud.common.excel; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.PrintStream; import java.sql....
  • 1.导入poi相关jar包 对于只操作2003及以前版本的excel,只需要导入poi-XXX.jar ,如果还需要对2007及以后版本进行操作,则...2. 导入excel的工具类-ImportExcel import org.apache.poi.hssf.usermodel.HSSFWorkboo...
  • Java批量导入excel

    2020-05-06 14:12:58
    使用POI进行批量导入 public static List<MountingArchivesMeterRequest> importExcel(MultipartFile newFile) throws Exception { List temp = new ArrayList(); Workbook workbook = null; workbook =.....
  • javaexcel表格批量导入 批量添加

    千次阅读 2018-09-10 10:24:52
    /** * * @Title: upload * @Description: 批量上传 * @return HResponse * @author fun * @date 2018年9月7日 */ @ResponseBody @RequestMapping(value = "/upload", method =...
  • excel批量导入数据

    2013-07-15 21:23:34
    读取excel数据,并将每50行批量上传到oracle10g数据库中
  • Java实现Excel表格数据批量导入

    千次阅读 2017-10-25 12:36:08
    一、前台上传Excel表格,传递到后台 &lt;input type="file" name="excel" id="excel" accept="application/vnd.ms-excel"/&gt; 其中accept限定上传的文件只能是...
  • springboot批量导入excel数据

    千次阅读 2019-06-11 10:30:02
    小白今天闲着没事,在公司摸鱼,以为今天有事无聊的一天,突然上头说小子,今天实现一下批量导入Excel数据吧,当时我的内心是拒绝的,然后默默打开idea。 2 介绍 2.1 框架 java本身并不支持读取excel,所有读取excel...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 6,358
精华内容 2,543
关键字:

java批量导入excel数据

java 订阅