一、這篇文章主要描述使用poi文件解析技術(shù)實現(xiàn)excel的讀取。解決通過上傳excel實現(xiàn)批量添加的問題。其代碼演示如下：

1、這次演示的項目采用的是ssh構(gòu)建的maven項目,在項目中需要導(dǎo)入poi文件解析得相關(guān)jar包：

<properties><commons.version>1.3.1</commons.version></properties> <!-- 引入文件上傳的相關(guān)JAR包 --><dependency><groupId>commons-fileupload</groupId><artifactId>commons-fileupload</artifactId><version>1.3.1</version></dependency> <!-- 引入文件上傳的相關(guān)JAR包 --><dependency><groupId>commons-fileupload</groupId><artifactId>commons-fileupload</artifactId><version>${commons.version}</version></dependency><!-- 引入poi文件解析的相關(guān)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、創(chuàng)建一個固定格式的excel表格：

3、創(chuàng)建一個相關(guān)的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;/**狀態(tài)(0-暫停使用,1-開通)*/private int userState; /**用戶創(chuàng)建時間*/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、創(chuàng)建一個讀取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));//創(chuàng)建工作簿XSSFWorkbook wb = new XSSFWorkbook(in); //讀取第一個sheetSheet sheet = wb.getSheetAt(0);int totalRow=sheet.getLastRowNum();Row row=null;//循環(huán)讀取科目for (int i = 1; i <=totalRow; i++) {//獲取第i行row = sheet.getRow(i);//這里封裝一個用戶對象，對象里面包含了角色，默認(rèn)為普通用戶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");//添加進(jìn)入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 {@Resourceprivate 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);//打印解析后得結(jié)果system.outprint(list)//解析之后將返回得結(jié)果扔給消息服務(wù)器} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();return false;}finally {try {out.flush();out.close();} catch (IOException e) {// TODO Auto-generated catch blocke.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(){ //如果只加載一個模塊，可以不填數(shù)組。如：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服務(wù)器,訪問文件上傳頁面、選擇上傳的excel即可

總結(jié)

以上是生活随笔為你收集整理的poi文件解析的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。