SSM使用POI解析Excel数据并实现批量导入到数据库

本文参考此博客https://blog.csdn.net/qq_31170429/article/details/76473205?utm_source=blogxgwz1
稍作修改而来，记录一下。
下面开始进入正题：

1、下载需要的jar包：POI

Apache POI是Apache软件基金会的开源项目，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。本例中主要使用其中的两个包：HSSF提供读写Microsoft Excel XLS（2003版本）格式档案的功能。XSSF提供读写Microsoft Excel OOXML XLSX（2007+版本）格式档案的功能。

如果使用的是maven项目，则依赖如下：

		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi</artifactId>
		    <version>4.0.0</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi-ooxml</artifactId>
		    <version>4.0.0</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi-ooxml-schemas</artifactId>
		    <version>4.0.0</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-excelant -->
		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi-excelant</artifactId>
		    <version>4.0.0</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad -->
		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi-scratchpad</artifactId>
		    <version>4.0.0</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-examples -->
		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi-examples</artifactId>
		    <version>4.0.0</version>
		</dependency>

需要高版本的话，可以去这里查：https://mvnrepository.com/artifact/org.apache.poi

使用maven项目的话，导入上面6个依赖包，工具类就不会报错了。

传统项目，下载jar：首先进入POI下载页：http://poi.apache.org/download.html。 下载解压，需要用到的的8个jar在下图


截止我修改这篇博客时，poi的最新版本为4.1.2，避免版本不一致导致下面工具类出现错误的问题，如果需要4.0.0版本的jar包，去这里取：4.0.0-poi-jar 提取码: 4q59

2. 就是工具类了，有两个，ExcelUtil.java和ExcelBean.java，这两个类不用修改，可以直接拿来用。

import java.io.IOException;  
import java.io.InputStream;  
import java.math.BigDecimal;
 
import java.text.SimpleDateFormat;  
import java.util.ArrayList;  
import java.util.Date;
import java.util.List;  
  
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
import org.apache.poi.ss.usermodel.Cell;  
import org.apache.poi.ss.usermodel.Row;  
import org.apache.poi.ss.usermodel.Sheet;  
import org.apache.poi.ss.usermodel.Workbook;  
import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
  
  
public class ExcelUtil {  
      
    private final static String excel2003L =".xls";    //2003- 版本的excel  
    private final static String excel2007U =".xlsx";   //2007+ 版本的excel  
      
    /** 
     * 描述：获取IO流中的数据，组装成List<List<Object>>对象 
     * @param in,fileName 
     * @return 
     * @throws IOException  
     */  
    public  List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{  
        List<List<Object>> list = null;  
          
        //创建Excel工作薄  
        Workbook work = this.getWorkbook(in,fileName);  
        if(null == work){  
            throw new Exception("创建Excel工作薄为空！");  
        }  
        Sheet sheet = null;  //页数
        Row row = null;  //行数
        Cell cell = null;  //列数
          
        list = new ArrayList<List<Object>>();  
        //遍历Excel中所有的sheet  
        // 将最大的列数记录下来
        int lastCellNum = 0;
        for (int i = 0; i < work.getNumberOfSheets(); i++) {  
            sheet = work.getSheetAt(i);  
            if(sheet==null){continue;}  
              
            //遍历当前sheet中的所有行  
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {  
                row = sheet.getRow(j);  
                if(row==null||row.getFirstCellNum()==j){continue;}  
                  
                //遍历所有的列  
                List<Object> li = new ArrayList<Object>(); 
                // 比较当前行的列数跟表的最大的列数
                if (j == sheet.getFirstRowNum()) {
                	// 将第一行的列数设为最大
                	lastCellNum = row.getLastCellNum();
				}else {
					lastCellNum = lastCellNum > row.getLastCellNum() ? lastCellNum : row.getLastCellNum(); 
				}
                for (int y = row.getFirstCellNum(); y < lastCellNum; y++) {  
                    cell = row.getCell(y);  
                    li.add(this.getValue(cell));  
                } 
                list.add(li);  
            }  
        }  
 
        return list;  
        
    }  
      
    /** 
     * 描述：根据文件后缀，自适应上传文件的版本  
     * @param inStr,fileName 
     * @return 
     * @throws Exception 
     */  
    public  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{  
        Workbook wb = null;  
        String fileType = fileName.substring(fileName.lastIndexOf("."));  
        if(excel2003L.equals(fileType)){  
            wb = new HSSFWorkbook(inStr);  //2003-  
        }else if(excel2007U.equals(fileType)){  
            wb = new XSSFWorkbook(inStr);  //2007+  
        }else{  
            throw new Exception("解析的文件格式有误！");  
        }  
        return wb;  
    }  
  
    /** 
     * 描述：对表格中数值进行格式化 
     * @param cell 
     * @return 
     */  
  //解决excel类型问题，获得数值  
    public  String getValue(Cell cell) {  
        String value = "";  
        if(null==cell){  
            return value;  
        }  
        switch (cell.getCellType()) {  
        //数值型  
        case Cell.CELL_TYPE_NUMERIC:  
            if (HSSFDateUtil.isCellDateFormatted(cell)) {  
                //如果是date类型则 ，获取该cell的date值  
                Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()); 
				// 根据自己的实际情况，excel表中的时间格式是yyyy-MM-dd HH:mm:ss还是yyyy-MM-dd，或者其他类型
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                // 由于方法的返回值类型为String，这里将Date类型转为String，便于统一返回数据 
                value = format.format(date);;  
            }else {// 纯数字  
                BigDecimal big=new BigDecimal(cell.getNumericCellValue());  
                value = big.toString();  
                //解决1234.0  去掉后面的.0  
                if(null!=value&&!"".equals(value.trim())){  
                     String[] item = value.split("[.]");  
                     if(1<item.length&&"0".equals(item[1])){  
                         value=item[0];  
                     }  
                }  
            }  
            break;  
            //字符串类型   
        case Cell.CELL_TYPE_STRING:  
            value = cell.getStringCellValue().toString();  
            break;  
        // 公式类型  
        case Cell.CELL_TYPE_FORMULA:  
            //读公式计算值  
            value = String.valueOf(cell.getNumericCellValue());  
            if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串  
                value = cell.getStringCellValue().toString();  
            }  
            break;  
        // 布尔类型  
        case Cell.CELL_TYPE_BOOLEAN:  
            value = " "+ cell.getBooleanCellValue();  
            break;   
        default:  
            value = cell.getStringCellValue().toString();  
    }  
    if("null".endsWith(value.trim())){  
        value="";  
    }  
  return value;  
    }  
}  

如果case Cell.CELL_TYPE_NUMERIC:提示错误，则换成case NUMERIC: ，我看了下现在maven拉下来4.0.0版本的org.apache.poi.ss.usermodel.Cell类中已经没有CELL_TYPE_前缀的变量了，而是将变量放在了枚举类：org.apache.poi.ss.usermodel.CellType中。

import org.apache.poi.xssf.usermodel.XSSFCellStyle;

public class ExcelBean implements java.io.Serializable {  
    private String headTextName;//列头（标题）名  
    private String propertyName;//对应字段名  
    private Integer cols;//合并单元格数  
    private XSSFCellStyle cellStyle;  
      
    public ExcelBean(){  
          
    }  
    public ExcelBean(String headTextName, String propertyName){  
        this.headTextName = headTextName;  
        this.propertyName = propertyName;  
    }  
      
    public ExcelBean(String headTextName, String propertyName, Integer cols) {  
        super();  
        this.headTextName = headTextName;  
        this.propertyName = propertyName;  
        this.cols = cols;  
    }   
      
    public String getHeadTextName() {  
       return headTextName;  
   }  
 
   public void setHeadTextName(String headTextName) {  
       this.headTextName = headTextName;  
   }  
 
   public String getPropertyName() {  
       return propertyName;  
   }  
 
   public void setPropertyName(String propertyName) {  
       this.propertyName = propertyName;  
   }  
 
   public Integer getCols() {  
       return cols;  
   }  
 
   public void setCols(Integer cols) {  
       this.cols = cols;  
   }  
 
   public XSSFCellStyle getCellStyle() {  
       return cellStyle;  
   }  
 
   public void setCellStyle(XSSFCellStyle cellStyle) {  
       this.cellStyle = cellStyle;  
   }  
}  

3. 然后需要在你的xxxServeice文件中添加接口，一个是读取文件内容之后的插入方法，一个是读取文件的方法。

public boolean insert(User user);

String ajaxUploadExcel(HttpServletRequest request,HttpServletResponse response) throws Exception;

4. 实现类中才是需要根据实际修改的东西。

	@Override
	public boolean insert(User user) {
		return userMapper.insert(user);
	}

	@Override
	public String ajaxUploadExcel(HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;    
        
        MultipartFile file = multipartRequest.getFile("upfile");  
        if(file.isEmpty()){  
            try {
				throw new Exception("文件不存在！");
			} catch (Exception e) {
				e.printStackTrace();
			}  
        }  
          
        InputStream in =null;  
        try {
			in = file.getInputStream();
		} catch (IOException e) {
			e.printStackTrace();
		}  
        
		List<List<Object>> listob = null; 
		try {
			listob = new ExcelUtil().getBankListByExcel(in,file.getOriginalFilename());
		} catch (Exception e) {
			e.printStackTrace();
		}   
		
	    //该处可调用service相应方法进行数据保存到数据库中，现只对数据输出  
        for (int i = 0; i < listob.size(); i++) {  
            List<Object> lo = listob.get(i);  
            User vo = new User(); 
            /*这里是主键验证，根据实际需要添加，可要可不要，加上之后，可以对现有数据进行批量修改和导入
            User j = null;
			try {
				j = userMapper.selectByPrimaryKey(Integer.valueOf(String.valueOf(lo.get(0))));
			} catch (NumberFormatException e) {
				// TODO Auto-generated catch block
				System.out.println("没有新增");
			}*/
			    //vo.setUserId(Integer.valueOf(String.valueOf(lo.get(0))));  // 刚开始写了主键，由于主键是自增的，又去掉了，现在只有批量插入的功能，不能对现有数据进行修改了
	            vo.setUserTel(String.valueOf(lo.get(0)));     // 表格的第一列   注意数据格式需要对应实体类属性
	            vo.setIntegral(Integer.valueOf(String.valueOf(lo.get(1))));   // 表格的第二列
	            //vo.setRegTime(Date.valueOf(String.valueOf(lo.get(2)))); 
	            //由于数据库中此字段是datetime，所以要将字符串时间格式：yyyy-MM-dd HH:mm:ss，转为Date类型
	            if (lo.get(2) != null && lo.get(2) != "") {
	            	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	            	vo.setRegTime(sdf.parse(String.valueOf(lo.get(2))));
				}else {
					vo.setRegTime(new Date());
				}
	            System.out.println("从excel中读取的实体类对象："+ vo);
	            userMapper.insert(vo);
			/*if(j == null)
			{
		            userMapper.insert(vo);
			}
			else
			{
		            userMapper.updateByPrimaryKey(vo);
			}*/
        }
        System.out.println("文件导入成功！");
        return "文件导入成功！";
	}

5. Controller类

	@ResponseBody  
    @RequestMapping(value="fileUpload.do", produces = "application/text; charset=utf-8") 
    public String UploadExcel(HttpServletRequest request,HttpServletResponse response) throws Exception {  
        return userService.ajaxUploadExcel(request, response);
    }

6. jsp页面，我这里采用的是form提交表单的方式，使用accept=".xls,.xlsx"对input的文件格式进行初步过滤

    <form method="post"  enctype="multipart/form-data" id="form1" action="user/fileUpload.do">  
        <table>  
         <tr>  
            <td>上传文件: </td>  
            <td> <input id="upfile" type="file" name="upfile" accept=".xls,.xlsx" /></td>  
            <td><input  type="submit" value="提交" onclick="return checkData()" /></td>  
         </tr>  
        </table>    
    </form>

js判断：

<script type="text/javascript">  
         //JS校验form表单信息  
         function checkData(){  
            var fileDir = $("#upfile").val();  
            var suffix = fileDir.substr(fileDir.lastIndexOf("."));  
            if("" == fileDir){  
                alert("选择需要导入的Excel文件！"); 
                return false;  
            }  
            if(".xls" != suffix && ".xlsx" != suffix ){  
                alert("选择Excel格式的文件导入！");  
                return false;  
            }  
            return true;  
         }  
</script> 

之前使用ajax上传excel没有弄好，趁着今天有空，又改了试试，这里记录一下：

ajax文件上传用jquery ajaxFileUpload插件的话会非常方便，下载jquery ajaxFileUpload文件，然后在项目中引入
链接: https://pan.baidu.com/s/1RYqCwd1o0rmKbpbB4AC6Ig 提取码: g7zx

<script type="text/javascript" src="<%=basePath%>/js/ajaxfileupload.js"></script>

在页面中使用jquery ajaxFileUpload，先看看我们的html代码

<td> <input id="upfile" type="file" name="upfile" accept=".xls,.xlsx" /></td>  
<td><input id="btn" name="btn" type="button" class="scbtn" value="ajax方式提交"  /></td>

js代码如下，“fileElementId”属性表示的是input标签中定义的id，这个很重要：

	//ajax提交excel
    $(document).ready(function(){
	    $("#btn").click(function(){  
		    if(checkData()){  
		    	$.ajaxFileUpload({
		            url:"user/ajaxUpload.do",
		            type:"POST",
		            dataType: "text",
		            fileElementId :"upfile",
		            success:function (data) {
		            	//alert(data);
						console.log(data);
						$("#upfile").val(""); 
		            },
		            error:function(erro){
		                console.log(erro);
		            }
		        });
		    }       
	    });
    });

最后就是在java后台中实现excel文件上传的方法，用到了springmvc的MultipartFile类，形参upfile要和input中的name=“upfile” 属性一致，代码如下：

    @RequestMapping(value="ajaxUpload.do", produces = "application/text; charset=utf-8") 
    public void ajaxUploadExcel(@RequestParam("upfile") MultipartFile file,HttpServletRequest request,HttpServletResponse response) throws Exception {  
        String msg = userService.ajaxUploadExcel(request, response);
		response.setContentType("text/html;charset=UTF-8");//这些设置必须要放在getWriter的方法之前，
		response.getWriter().print(msg);
    }

至此两种方式实现excel批量导入数据到数据库就完成了，这里可以根据需要选择自己喜欢的方式，如果你有更好的方式，欢迎与我交流。