一、这篇文章主要描述使用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即可
