java 简单 Excel 批量导入demo

1.在jsp页面form表单中添加如下代码

<form enctype="multipart/form-data" id="myForm"   method="post">
<button type="button" class="btn btn-primary" onclick="importExcel()">导入</button>
<input type="file" name="file"   id="uploadFile" >
</form>

js中的提交事件如下：

function importExcel() {
    $.ajax({
        url : importExcelURL,
        type: 'POST',
        dataType:"json",
        processData: false,
        contentType: false,
        data: new FormData($('#myForm')[0]),
        success : function(data) {
            showMsgModal(data.message);
            if(data.code == 200){
                dTable.ajax.reload();
            }
        }
    });
}

controller中代码：

  @RequestMapping(value = "/import", method=RequestMethod.POST)
     public JsonBean importExcel(@RequestParam(value="file",required = false) MultipartFile file){
                JsonBean jsonBean = new JsonBean();
                try{
                    int result = deviceProbeOffsetService.readExcelFile(file);
                    if(result > 0){
                        jsonBean.setMessage("导入成功");
                        jsonBean.setCode("200");
                    }else if(result == -1){
                        jsonBean.setMessage("Excel没有数据！");
                        jsonBean.setCode("400");
                    }
                }catch (Exception e){
                    jsonBean.setMessage("导入失败");
                    jsonBean.setCode("500");
                }
                 return jsonBean;
     }

service中代码：

    @Override
    public int readExcelFile(MultipartFile file) {
                int result = 0;
                try{
                    InputStream in = file.getInputStream();
                    String fileName = file.getOriginalFilename();
                    Workbook wb = null;
                    String fileType = fileName.substring(fileName.lastIndexOf("."));
                    //根据excel文件后缀不同创建不同workbook
                    if(".xls".equals(fileType)){
                        wb = new HSSFWorkbook(in);  
                    }else if(".xlsx".equals(fileType)){
                        wb = new XSSFWorkbook(in); 
                    }
                    //这里只对一个sheet做管理，若需要多个sheet请添加循环处理
                    Sheet sheet = wb.getSheetAt(0);
                    if(sheet == null){
                        return -1;
                    }
                    //循环读取每一行数据
                    for(int i = 1; i < (sheet.getLastRowNum()+1);i++){
                        Row row = sheet.getRow(i);
                        String fdProbeId = row.getCell(0).getStringCellValue();            
                            DeviceProbeOffset df = new DeviceProbeOffset();
                            df.setFdProbeId(fdProbeId);
                            df.setFdOffset1(String.valueOf(row.getCell(1).getNumericCellValue()));
                            df.setFdOffset2(String.valueOf(row.getCell(2).getNumericCellValue()));
                            df.setFdState("0");
                            //数据库保存操作
                            int count = deviceProbeOffsetDao.insertProbeOffset(df);
                            if(count >0 ){
                                result ++;
                            }
                        
                    }
                }catch (IOException e){
                    throw new AppException("io 处理发生异常！");
                }
             return result;
            }

下面是Java pom文件中相应的导入所需的poi jar包 ：（注意：jar包版本必须一致，否则会发生异常，且以下jar放在pom最上面）

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