poi文件解析

一、这篇文章主要描述使用poi文件解析技术实现excel的读取。解决通过上传excel实现批量添加的问题。其代码演示如下：

1、这次演示的项目采用的是ssh构建的maven项目,在项目中需要导入poi文件解析得相关jar包：

<properties>
		
		<commons.version>1.3.1</commons.version>
		
	</properties>      

<!-- 引入文件上传的相关JAR包 -->
		<dependency>
			<groupId>commons-fileupload</groupId>
			<artifactId>commons-fileupload</artifactId>
			<version>1.3.1</version>
		</dependency> 
	<!-- 引入文件上传的相关JAR包 -->
		<dependency>
			<groupId>commons-fileupload</groupId>
			<artifactId>commons-fileupload</artifactId>
			<version>${commons.version}</version>
		</dependency>

<!-- 引入poi文件解析的相关JAR包 -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml-schemas</artifactId>
			<version>3.15</version>
		</dependency>
		
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.15</version>
		</dependency>
	   

2、创建一个固定格式的excel表格：

3、创建一个相关的java类，：

package org.telecom.billingservice.bean;

import java.io.Serializable;
import java.util.Date;
import java.util.Set;


/**
 * 用户实体类
 * @author wzc
 *
 */

public class UserInfoBean implements Serializable{
	/**
	 * 
	 */
	private static final long serialVersionUID = 7728420004484655959L;
	/**用户ID*/
	private long id;
	/**用户名*/
	private String userName;
	/**用户密码*/
	private String userPwd;
	/**用户性别(0-男，1-女)*/
	private int userGender;
	/**用户真实姓名*/
	
	private String userReallName;
	/**用户邮箱*/
	
	private String userEmail;
	/**用户身份证*/
	
	private String userID;
	/**用户地址*/
	
	private String userAdress;
	/**用户QQ*/
	
	private String userQQ;
	/**用户电话*/
	
	private String userTelphone;
	/**状态(0-暂停使用,1-开通)*/
	
	private int userState; 
	/**用户创建时间*/
	
	private Date createTime;
	/**用户修改时间*/
	
	private AccountYearBean accountYearBean;
	public UserInfoBean() {
		super();
		// TODO Auto-generated constructor stub
	}
	
	public UserInfoBean(String userName, String userPwd) {
		super();
		this.userName = userName;
		this.userPwd = userPwd;
	}

	public long getId() {
		return id;
	}
	public void setId(long id) {
		this.id = id;
	}
	public String getUserName() {
		return userName;
	}
	public void setUserName(String userName) {
		this.userName = userName;
	}
	public String getUserPwd() {
		return userPwd;
	}
	public void setUserPwd(String userPwd) {
		this.userPwd = userPwd;
	}
	public int getUserGender() {
		return userGender;
	}
	public void setUserGender(int userGender) {
		this.userGender = userGender;
	}
	public String getUserReallName() {
		return userReallName;
	}
	public void setUserReallName(String userReallName) {
		this.userReallName = userReallName;
	}
	public String getUserEmail() {
		return userEmail;
	}
	public void setUserEmail(String userEmail) {
		this.userEmail = userEmail;
	}
	public String getUserID() {
		return userID;
	}
	public void setUserID(String userID) {
		this.userID = userID;
	}
	public String getUserAdress() {
		return userAdress;
	}
	public void setUserAdress(String userAdress) {
		this.userAdress = userAdress;
	}
	public String getUserQQ() {
		return userQQ;
	}
	public void setUserQQ(String userQQ) {
		this.userQQ = userQQ;
	}
	public String getUserTelphone() {
		return userTelphone;
	}
	public void setUserTelphone(String userTelphone) {
		this.userTelphone = userTelphone;
	}
	public int getUserState() {
		return userState;
	}
	public void setUserState(int userState) {
		this.userState = userState;
	}
	public Date getCreateTime() {
		return createTime;
	}
	public void setCreateTime(Date createTime) {
		this.createTime = createTime;
	}
	public Date getUpdateTime() {
		return updateTime;
	}
	public void setUpdateTime(Date updateTime) {
		this.updateTime = updateTime;
	}
	
	
	
	
}

4、创建一个读取excel文件的工具类

package org.telecom.billingservice.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.telecom.billingservice.bean.RoleInfoBean;
import org.telecom.billingservice.bean.UserInfoBean;

/**
 * 解析excel文件类
 * @author Deng
 *
 */
public class AnalysisExcelUtil {
    public static  List<UserInfoBean> readExcel(String excelName) throws IOException {
        List<UserInfoBean> users=new ArrayList<UserInfoBean>();
    	//将文件读入
    	InputStream in  = new FileInputStream(new File(excelName));
   	    //创建工作簿
    	XSSFWorkbook wb = new XSSFWorkbook(in); 
    	//读取第一个sheet
    	Sheet sheet = wb.getSheetAt(0);
    	int totalRow=sheet.getLastRowNum();
    	Row row=null;
    	//循环读取科目
    	for (int i = 1; i <=totalRow; i++) {
    		//获取第i行
        	row = sheet.getRow(i);
            //这里封装一个用户对象，对象里面包含了角色，默认为普通用户
        	UserInfoBean user=new UserInfoBean();
        	user.setUserName(row.getCell(0).toString());
        	user.setUserReallName(row.getCell(1).toString());
        	user.setUserGender("男".equals(row.getCell(2).toString())?0:1);
        	user.setUserID(row.getCell(3)+"");
        	user.setUserAdress(row.getCell(4).toString());
        	user.setUserEmail(row.getCell(5).toString());
        	user.setUserQQ(row.getCell(6)+"");
        	user.setUserTelphone(row.getCell(7)+"");
        	user.setUserPwd("123456");
        	
        	//添加进入list集合
        	users.add(user);
           
		}
    	return users;	
    }

}

5、书写上传文件得controller，用于保存上传文件

package org.telecom.billingservice.usermag.controller;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.telecom.billingservice.bean.UserInfoBean;
import org.telecom.billingservice.usermag.handleservice.IAccountHandleService;
import org.telecom.billingservice.util.AnalysisExcelUtil;

/**
 * 文件上传
 * @author deng
 *
 */
@RequestMapping(value="/templates/account")
@Controller
public class FileUploadController {
	@Resource
	private IAccountHandleService accountHandleServiceImpl;
	/**
	 * springmvc框架的单文件上传
	 * @param file 临时文件对象
	 * @return
	 */
	@RequestMapping(value="/file",produces= {"application/json;charset=utf-8"})
	public @ResponseBody boolean fileUpload(@RequestParam("file")MultipartFile file) {
		String fileName = file.getOriginalFilename();
		FileOutputStream out =null;
		System.out.println(fileName);
		String path = "d:\\files";
		File dir = new File(path);
		if(!dir.exists()) {
			dir.mkdir();
		}
		
		path = path + File.separator + fileName;
		//将临时文件写入到真实文件中去
		try {
			 out = new FileOutputStream(path);
		     out.write(file.getBytes());
		     //同时解析excle文件
			List<UserInfoBean> list=AnalysisExcelUtil.readExcel(path);
			//打印解析后得结果
			system.outprint(list)
			//解析之后将返回得结果扔给消息服务器
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		finally {
			try {
				out.flush();
				out.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	        
		}
		return true;
	}
	
//	/**
//	 * springmvc框架的多文件上传
//	 * @param file 临时文件对象
//	 * @return
//	 */
//	@RequestMapping(value="/multi/upload",method= {RequestMethod.POST})
//	public String fileUpload(@RequestParam("fileName")CommonsMultipartFile[] files) {
//		for (CommonsMultipartFile file : files) {
//			System.out.println(file.getOriginalFilename());
//			
//			
//		}
//		
//		return "index";
//	}
//	
}

6、使用layui写一个上传文件得简单页面

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>

    <link rel="stylesheet" href="../../static/layui/css/layui.css" media="all">

    <script src="../../static/layui/layui.js"></script>
</head>
<body>
    
    <button type="button" class="layui-btn layui-btn-primary" id="test4"><i class="layui-icon"></i>只允许上传表格文件</button>
    
</body>
<script type="text/javascript">

layui.use(['form', 'upload'], function(){  //如果只加载一个模块，可以不填数组。如：layui.use('form')
	  var form = layui.form //获取form模块
	  ,upload = layui.upload; //获取upload模块
	  
	  upload.render({ //允许上传的文件后缀
		    elem: '#test4'
		    ,url: '/billingservice/templates/account/file'
		    ,accept: 'file' //普通文件
		    ,exts: 'xlsx' //只允许上传表格文件
		    ,done: function(res){
		      console.log(res)
		    }
		  });
});

</script>
</html>

7、启动tomcat服务器,访问文件上传页面、选择上传的excel即可