POI导入Excel表格

最新推荐文章于 2022-11-30 22:40:03 发布

原创最新推荐文章于 2022-11-30 22:40:03 发布 · 277 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel #poi #file 文件上传

前端专栏收录该内容

24 篇文章

订阅专栏

本文介绍了一个使用Java实现的Excel文件导入案例，包括前端页面设计、JavaScript校验、后端控制器处理及服务层逻辑。该案例实现了从Excel文件中读取商家信息，并将其存储到数据库的功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

页面：

  		<form action="<%=basePath%>balance/insertmerchant" method="POST" enctype="multipart/form-data" id="miupload" style="font-size:10px;border:1px solid blue; width:500px;">
	    		<br>请选择文件：
	    		<input id="mifile" name="mifile" size="80" type="file"><input name="upload" id="upload" type="button" value="开始导入" onclick="checkform();"/>  
			</form>

JS：

	function checkform(){
		$('#upload').attr('disabled',"true");
		var filepath = $.trim($("#mifile").val());
		if(filepath == ''){
			alert('请先选择一个文件。');
			$('#upload').removeAttr("disabled"); 
		}else{
			var index = filepath.lastIndexOf('.');
			if(index == -1){
				alert('无法确定文件扩展名，请显示指定扩展名。');
				$('#upload').removeAttr("disabled"); 
			}else{
				var ext = filepath.substring(index + 1,filepath.length);
				if(ext.toLowerCase() != 'xls' && ext.toLowerCase() != 'xlsx'){
					alert('请选择excel文件。');
					$('#upload').removeAttr("disabled"); 
				}else{
				$.messager.progress({ 
						    title: '导入数据', 
						    msg: '正在导入...(导入时间与数据量有关)', 
						    text: '请稍候.......' 
						});
					$('#miupload').ajaxSubmit({
						type: 'post',
						url:"<%=basePath%>balance/insertmerchant",
						//async:异步
						async:false,
						success:function(result){
							//console.log(result);
							result = $.parseJSON(result.replace(/<.*?>/ig,""));
							//console.log(result);
							$('#merchantinfolist').datagrid('reload');
							$.messager.progress('close');
							$.messager.show({
							title:'导入结果提示',
							msg:result.message,
							timeout:5000,
							showType:'slide'
							});
							$('#upload').removeAttr("disabled"); 
						}
					});
				}
			}
			
		}
	}

Controller：

	/**
	 * 导入Excel
	 */
	@RequestMapping(value="/insertmerchant")
	@ResponseBody
	public JsonResult insertMerchant(HttpServletRequest request,HttpServletResponse response,@RequestParam("mifile") MultipartFile mifile){
		
		//System.out.println(mifile);
		if(mifile.isEmpty()||mifile==null){
			return new JsonResult(0,"文件上传错误");
		}
		InputStream is = null;
		List<MerchantInfo> list = null;
		try {
			is = mifile.getInputStream();
			Workbook workbook = WorkbookFactory.create(is);
			Sheet sheet = workbook.getSheetAt(0);
	        list = new ArrayList<MerchantInfo>();
	        int rowNum = sheet.getLastRowNum();
	        for (int i = 1; i <= rowNum; i++) {      
	            Row row = sheet.getRow(i);      
	            MerchantInfo merchantInfo = new MerchantInfo();
	            if(row.getCell(0)!=null){
	            	merchantInfo.setMerchantCode(getStringCellValue(row.getCell(0)).trim());
	            }
	            if(row.getCell(1)!=null){
	            	merchantInfo.setMerchantName(getStringCellValue(row.getCell(1)).trim());
	            }
	            if(row.getCell(2)!=null){
	            	merchantInfo.setMerchantAddr(getStringCellValue(row.getCell(2)).trim());
	            }
	            if(row.getCell(3)!=null){
	            	 merchantInfo.setMerchantContact(getStringCellValue(row.getCell(3)).trim());
	            }
	            if(row.getCell(4)!=null){
	            	merchantInfo.setMerchantTel(getStringCellValue(row.getCell(4)).trim());
	            }
	            
	            if(row.getCell(5)!=null){
	            	 merchantInfo.setMerchantType(getStringCellValue(row.getCell(5)).trim());
	            }
	            if(row.getCell(6)!=null){
	                merchantInfo.setExtensionType(getStringCellValue(row.getCell(6)).trim());
	            }
	            if(row.getCell(7)!=null){
	            	 merchantInfo.setCoverage(getStringCellValue(row.getCell(7)).trim());
	            }
	            if(row.getCell(8)!=null){
	            	  merchantInfo.setTerminalNum(getStringCellValue(row.getCell(8)).trim());
	            }
	            if(row.getCell(9)!=null){
	            	 merchantInfo.setCooperateBank(getStringCellValue(row.getCell(9)).trim());
	            }
	            if(row.getCell(10)!=null){
	            	  merchantInfo.setProperty(getStringCellValue(row.getCell(10)).trim());
	            }
				if(row.getCell(11)!=null){
					 merchantInfo.setMaintenance(getStringCellValue(row.getCell(11)).trim());	
					            }
				if(row.getCell(12)!=null){
					merchantInfo.setModel(getStringCellValue(row.getCell(12)).trim());
				}
				if(row.getCell(13)!=null){
					merchantInfo.setCreateTime(getStringCellValue(row.getCell(13)).trim());
				}
	            
	            if(row.getCell(14)!=null){
	            	  merchantInfo.setUserName(getStringCellValue(row.getCell(14)).trim());
	            }
	            merchantInfo.setState("0");
	            list.add(merchantInfo);
	            //System.out.println(list.get(i-1));
	        } 
	        //System.out.println(list.get(0));
	        int i = banlanService.InsertMerchant(list);
	        if(i>0){
	        	banlanService.compareMerchant();
		        return new JsonResult(1,"数据插入成功");
	        }else{
	        	return new JsonResult(0,"数据插入失败");
	        } 
			}catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return new JsonResult(0,"数据插入失败");
			}finally{
				try {
					if(is!=null){
						is.close();
					}
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
	}
	/**    
     * 获取单元格数据内容为字符串类型的数据    
     * @param cell Excel单元格    
     * @return String 单元格数据内容    
     */     
    private String getStringCellValue(Cell cell) {      
        String strCell = "";  
        java.text.DecimalFormat df = new DecimalFormat("#############");
        switch (cell.getCellType()) {      
        case Cell.CELL_TYPE_STRING:      
            strCell = cell.getStringCellValue();      
            break;      
        case Cell.CELL_TYPE_NUMERIC: 
        	if(DateUtil.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 = (Date) cell.getDateCellValue();
                strCell = sdf.format(date);
        	}else{
        		strCell = String.valueOf(df.format(cell.getNumericCellValue()));      
        	}
            
            break;      
        case Cell.CELL_TYPE_BOOLEAN:      
            strCell = String.valueOf(cell.getBooleanCellValue());      
            break;      
        case Cell.CELL_TYPE_BLANK:      
            strCell = "";      
            break;      
        default:      
            strCell = "";      
            break;      
        }      
        if (strCell.equals("") || strCell == null) {      
            return "";      
        }      
        if (cell == null) {      
            return "";      
        }      
        return strCell;      
    }