java 上传excel

直接附上案例：

环境：easyUi框架和ssm集成框架，java语言 ，maven管理jar包，tomcat服务器版本8.5， jdk1.8

设计思路：

1、循环遍历每行每列，内层根据列遍历根据io流读取到的信息将每个字段所处位置和实体字段相对应，根据使用set（）方法保存如实体。

2、根据行遍历将实体保存入List集合中，最终返回list集合

3、遍历list集合，将每个实体存入到数据库中（这里是逐条添加）

导入jar 包

所需jar包依赖

<!-- 导入poi依赖 -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.10.1</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.14-beta1</version>
		</dependency>
		<dependency>
			<groupId>commons-codec</groupId>
			<artifactId>commons-codec</artifactId>
			<version>1.10</version>
		</dependency>
		<!--文件上传 -->
		<dependency>
			<groupId>commons-fileupload</groupId>
			<artifactId>commons-fileupload</artifactId>
			<version>1.3.1</version>
		</dependency>
		<dependency>
			<groupId>commons-io</groupId>
			<artifactId>commons-io</artifactId>
			<version>2.4</version>
		</dependency>
		<!-- 数据库连接池 -->
		<dependency>
			<groupId>com.mchange</groupId>
			<artifactId>c3p0</artifactId>
			<version>0.9.5.2</version>
		</dependency>

		<!-- 数据库驱动 -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.6</version>
		</dependency>

jsp页面（内部url自己参考修改）

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
 <form enctype="multipart/form-data" id="batchUpload"  action="/excel/import" method="post" class="form-horizontal"> 
     <table>
     <tr>
     <td><button class="btn btn-success btn-xs" id="uploadEventBtn" style="height:30px; width:100px"  type="button" >选择文件</button>  </td>
     <td><input type="file" name="file"  style="width:0px;height:0px;" id="uploadEventFile">
      <input id="uploadEventPath"  disabled="disabled"  type="text" placeholder="请择excel表" style="border: 1px solid #e6e6e6; height: 26px;width: 200px;" /></td>
      </td>
     </tr>
     <tr>
     <td><button class="btn btn-success btn-xs" id="downLoadTemplate" style="height:30px; width:100px"  type="button" >导出模板</button> </td>
     <td> 
     <button type="button" class="btn btn-success btn-sm" style="height:30px; width:200px" onclick="user.uploadBtn()" >上传</button> 
    </tr>
     </table>   
    </form>  
 
<script type="text/javascript">
$("#downLoadTemplate").click(function(){
	console.log("aaa");
	$("<form>").attr({"action":"${pageContext.request.contextPath}/site/excelTemplate","method":"POST"}).appendTo("body").submit();
    });
    var User = function() {
        this.init = function() {
            //模拟上传excel  
            $("#uploadEventBtn").unbind("click").bind("click", function() {
                $("#uploadEventFile").click();
            });
            $("#uploadEventFile").bind("change", function() {
                $("#uploadEventPath").attr("value",    $("#uploadEventFile").val());
            });
        };
        //点击上传钮  
        this.uploadBtn = function() {
            var uploadEventFile = $("#uploadEventFile").val();
            if (uploadEventFile == '') {
                alert("请择excel,再上传");
            } else if (uploadEventFile.lastIndexOf(".xls") < 0) {//可判断以.xls和.xlsx结尾的excel  
                alert("只能上传Excel文件");
            } else {
                var url = "/SmartTravel/site/import";
                var formData = new FormData($('form')[0]);
                user.sendAjaxRequest(url, "POST", formData);
            }
        };
        this.sendAjaxRequest = function(url, type, data) {
            $.ajax({
                url : url,
                type : type,
                data : data,
                dataType : "json",
                success : function(result) {
                    alert(result.message);
                },
                error : function(result) {
                    alert(result.message);
                },
                cache : false,
                contentType : false,
                processData : false
            });
        };
    };
    var user;
    $(function() {
        user = new User();
        user.init();
    });
</script>

daoImpl（设置相对应的字段名和位置）

public class SiteReadExcel {
	 // 总行数
	      private int totalRows = 0;
	      // 总条数
	      private int totalCells = 0;
	      // 错误信息接收器
	      private String errorMsg;
	  
	      // 构造方法
	      public SiteReadExcel() {
	      }
	  
	      // 获取总行数
	      public int getTotalRows() {
	          return totalRows;
	      }
	  	
	      // 获取总列数
	     public int getTotalCells() {
	         return totalCells;
	      }
	  
	     // 获取错误信息
	      public String getErrorInfo() {
	          return errorMsg;
	      }
	  
	      /**
	       * 读EXCEL文件，获取信息集合
	       * 
	       * @param fielName
	       * @return
	       */
	      public List<Site> getExcelInfo(MultipartFile mFile) {
	          String fileName = mFile.getOriginalFilename();// 获取文件名
	  //        List<Map<String, Object>> userList = new LinkedList<Map<String, Object>>();
	          try {
	             if (!validateExcel(fileName)) {// 验证文件名是否合格
	                 return null;
	             }
	              boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
	              if (isExcel2007(fileName)) {
	                  isExcel2003 = false;
	              }
	              return createExcel(mFile.getInputStream(), isExcel2003);
	          } catch (Exception e) {
	              e.printStackTrace();
	          }
	          return null;
	      }
	 
	      /**
	      * 根据excel里面的内容读取站点信息
	      * 
	      * @param is      输入流
	      * @param isExcel2003   excel是2003还是2007版本
	      * @return
	      * @throws IOException
	      */
	      public List<Site> createExcel(InputStream is, boolean isExcel2003) {
	          try {
	              Workbook wb = null;
	              if (isExcel2003) {// 当excel是2003时,创建excel2003
	                  wb = new HSSFWorkbook(is);
	              } else {// 当excel是2007时,创建excel2007
	                  wb = new XSSFWorkbook(is);
	             }
	              return readExcelValue(wb);// 读取Excel里面客户的信息
	          } catch (IOException e) {
	              e.printStackTrace();
	         }
	          return null;
	      }
	  
	      /**
	       * 读取Excel里面客户的信息
	      * 
	      * @return
	      */
	     private List<Site> readExcelValue(Workbook wb) {
	         // 得到第一个shell
	         Sheet sheet = wb.getSheetAt(0);
	         // 得到Excel的行数
	         this.totalRows = sheet.getPhysicalNumberOfRows();
	         // 得到Excel的列数(前提是有行数)
	         if (totalRows > 1 && sheet.getRow(0) != null) {
	             this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
	         }
	         List<Site> siteList = new ArrayList<>();
	         // 循环Excel行数
	         for (int r = 1; r < totalRows; r++) {
	             Row row = sheet.getRow(r);
	             if (row == null) {
	                 continue;
	             }
	             // 循环Excel的列
	             List<Site> list = new ArrayList<>();
	             Site site = new Site();
	             for (int c = 0; c < this.totalCells; c++) {
	                 Cell cell = row.getCell(c);
	                 if (null != cell) {
	                    if (c == 0) {
	                         // 如果是纯数字,比如你写的是25,cell.getNumericCellValue()获得是25.0,通过截取字符串去掉.0获得25
	                         if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
	                             String siteName = String.valueOf(cell.getNumericCellValue());
	                             site.setSiteName(siteName.substring(0, siteName.length() - 2 > 0 ? siteName.length() - 2 : 1));
	                         } else {
	                        	 site.setSiteName(cell.getStringCellValue());// 名称
	                         }
	                     } else if (c == 1) {
	                    	 if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
	                             String direction = String.valueOf(cell.getNumericCellValue());
	                             site.setDirection(direction.substring(0, direction.length() - 2 > 0 ? direction.length() - 2 : 1));
	                         } else {
	                        	 site.setDirection(cell.getStringCellValue());// 方向
	                         }
	                     } else if (c == 2) {
	                    	 if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
	                             String type = String.valueOf(cell.getNumericCellValue());
	                             site.setType(type.substring(0, type.length() - 2 > 0 ? type.length() - 2 : 1));
	                         } else {
	                        	 site.setType(cell.getStringCellValue());//类型 
	                         }
	                     }
	                     }
	                   
	                 }
	             }
                 // 添加到list
                 siteList.add(site);
	         }
	         return siteList;
	     }
	 
	     /**
	      * 验证EXCEL文件
	      * 
	      * @param filePath
	      * @return
	      */
	     public boolean validateExcel(String filePath) {
	         if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
	             errorMsg = "文件名不是excel格式";
	             return false;
	         }
	         return true;
	    }
	 
	     // @描述：是否是2003的excel，返回true是2003
	     public static boolean isExcel2003(String filePath) {
	         return filePath.matches("^.+\\.(?i)(xls)$");
	     }
	 
	     // @描述：是否是2007的excel，返回true是2007
	    public static boolean isExcel2007(String filePath) {
	        return filePath.matches("^.+\\.(?i)(xlsx)$");
	    }
	     
	 }

Controller

 
	 //导入excel
	    @RequestMapping(value = "/import", method=RequestMethod.POST)
	    @ResponseBody
	    public Map<String, Object> importExcel(@RequestParam(value="file",required = false) MultipartFile file, HttpServletRequest request,HttpServletResponse response){
	        Map<String, Object> map = new HashMap<String, Object>();
	        String result = siteService.readExcelFile(file);  
	        map.put("message", result);
	        return map;  
	    }  

service

 /**
	  * 上传
	  * @param file
	  * @return result
	  */
	 public String readExcelFile(MultipartFile file) {
	        String result = "";  
	        //创建处理EXCEL的类  
	        SiteReadExcel siteReadExcel = new SiteReadExcel();  
	        //解析excel，获取上传的事件单  
	        List<Site> siteList = siteReadExcel.getExcelInfo(file);  
	        //至此已经将excel中的数据转换到list里面了,接下来就可以操作list,可以进行保存到数据库,或者其他操作,  
	        for(Site site:siteList){
	            int ret = siteMapper.insertSite(site);
	            if(ret == 0){
	                result = "插入数据库失败";
	            }
	        }
	        if(siteList != null && !siteList.isEmpty()){  
	            result = "上传成功";  
	        }else{  
	            result = "上传失败";  
	        }  
	        return result;  
	    }

mapper

public Integer insertSite(Site site);

Mapper.xml

<insert id="insertSite">
	insert into site 
	 values
   (null,#{siteName},#{direction},#{type},#{inLongitude},#{inLatitude},#{outLongitude},#{outLatitude},#{siteLongitude},#{siteLatitude},#{limitSpeed},#{forProvince},#{forCity},#{forCounty},#{state},#{represent},#{fictitious})
</insert>

实体：略