通过poi读取Excel并携带id参数导入数据库，前后端实现超详细讲解
一、 项目中存在这样一个需求：
1）获取当前请求页面URL中的一个参数值taskId
2）将Excel中数据读取和taskId一起存放到数据库(taskId和Excel中数据存在关联关系，但是taskId不在Excel中)
二、应用技术：
后台框架：Springmvc
持久层框架：mybatis
数据库：mysql
前端：jsp、Ajax
项目管理工具：maven，特别需要的pom配置用于操作Excel：
<!-- apache poi start -->
<!-- POI -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
<exclusions>
<exclusion>
<artifactId>commons-codec</artifactId>
<groupId>commons-codec</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>
<!-- apache poi end -->
工程目录结构：
三、实现过程：
1、前端：
1）文件上传页面
<form enctype="multipart/form-data" style="display:inline;white-space: nowrap;" id="batchUpload" action="case/upload" method="post" class="form-horizontal">
<button class="btn btn-success btn-xs" id="uploadEventBtn" style="height:26px;" type="button" >选择文件</button>
<input type="file" name="file" style="width:0px;height:0px;" id="uploadEventFile">
<input id="uploadEventPath" disabled="disabled" type="text" >
</form>
2）上传js
<script type="text/javascript">
var Point = 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 = '/case/upload/';
var id = getUrlParam('taskId');//获取URL中taskId
var formData = new FormData($('form')[0]);//获取form表单请求文件
formData.append("id",id);//添加请求参数，用户传给后台
console.log("id------------------------"+id);
point.sendAjaxRequest(url,'POST',formData);
}
};
this.sendAjaxRequest = function(url,type,data){
$.ajax({
url : url,
type : type,
data : data,
success : function(result) {
alert( result);
},
error : function() {
alert( "excel上传失败");
},
cache : false,
contentType : false,
processData : false
});
};
}
var point;
$(function(){
point = new Point();
point.init();
});
/*获取URL参数*/
function getUrlParam(name){
//构造一个含有目标参数的正则表达式对象
var reg = new RegExp("(^|&)"+ name +"=([^&]*)(&|$)");
//匹配目标参数
var r = window.location.search.substr(1).match(reg);
//返回参数值
if (r!=null) return unescape(r[2]);
return null;
}
</script>
2、Excel工具类
1）数据库连接：
package ***;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* @author zhaowei
* @date 2019年5月21日 下午3:37:08
*
*/
public class DBconnection {
private static Connection con = null;
private static String driverName = "com.mysql.jdbc.Driver";
private static String Name="automation_wr";
private static String Passwd="3faf8db433cc0235";
private static String url = "jdbc:mysql://test1254.db.58dns.org:4420/db58_automation";
public static Connection getConnection() {
try {
/*注册jdbc驱动*/
Class.forName(driverName);
/*获取数据库连接*/
con=DriverManager.getConnection(url,Name,Passwd);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
public static void closeConnection() {
if (con!=null) {
try {
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
2）读取Excel数据：
package ***;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
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;
import org.springframework.web.multipart.MultipartFile;
import com.bj58.automation.entity.CaseEntity;
/**
* @author zhaowei
* @date 2019年5月22日 下午1:09:33
*
*/
public class ReadExcel {
// 总行数
private int totalRows = 0;
// 总条数
private int totalCells = 0;
// 错误信息接收器
private String errorMsg;
//private List<PointEntity> pointEntities;
// 构造方法
public ReadExcel() {
}
// 获取总行数
public int getTotalRows() {
return totalRows;
}
// 获取总列数
public int getTotalCells() {
return totalCells;
}
// 获取错误信息
public String getErrorInfo() {
return errorMsg;
}
/**
* 读EXCEL文件，获取信息集合
*
* @param fielName
* @return
*/
public List<CaseEntity> getExcelInfo(MultipartFile mFile) {
String fileName = mFile.getOriginalFilename();// 获取文件名
List<CaseEntity> caseEntities = null;
try {
if (!validateExcel(fileName)) {// 验证文件名是否合格
return null;
}
boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
if (isExcel2007(fileName)) {
isExcel2003 = false;
}
caseEntities = createExcel(mFile.getInputStream(), isExcel2003);
} catch (Exception e) {
e.printStackTrace();
}
return caseEntities;
}
/**
* 根据excel里面的内容读取用例信息
*
* @param is输入流
* @param isExcel2003 excel是2003还是2007版本
* @return
* @throws IOException
*/
public List<CaseEntity> createExcel(InputStream is, boolean isExcel2003) {
List<CaseEntity> caseEntities=null;
try {
Workbook wb = null;
if (isExcel2003) {// 当excel是2003时,创建excel2003
wb = new HSSFWorkbook(is);
} else {// 当excel是2007时,创建excel2007
wb = new XSSFWorkbook(is);
}
caseEntities = readExcelValue(wb);// 读取Excel里面客户的信息
} catch (IOException e) {
e.printStackTrace();
}
return caseEntities;
}
/**
* 读取Excel里面用例的信息
*
* @param wb
* @return
*/
private List<CaseEntity> 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<CaseEntity> caseEntities = new ArrayList<CaseEntity>();
// 循环Excel行数
for (int r = 1; r < totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
CaseEntity caseEntity = new CaseEntity();
// 循环Excel的列
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 pointName = String.valueOf(cell.getNumericCellValue());
caseEntity.setPointName(pointName.substring(0, pointName.length() - 2 > 0 ? pointName.length() - 2 : 1));//埋点名称
} else {
caseEntity.setPointName(cell.getStringCellValue());// 埋点名称
}
} else if (c == 1) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
String platform = String.valueOf(cell.getNumericCellValue());
caseEntity.setPlatform(platform.substring(0, platform.length() - 2 > 0 ? platform.length() - 2 : 1));// 所属平台
} else {
caseEntity.setPlatform(cell.getStringCellValue());// 所属平台
}
} else if (c == 2) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
String platformVersion = String.valueOf(cell.getNumericCellValue());
caseEntity.setPlatformVersion(platformVersion.substring(0, platformVersion.length() - 2 > 0 ? platformVersion.length() - 2 : 1));// 支持的平台版本
} else {
caseEntity.setPlatformVersion(cell.getStringCellValue());// 支持的平台版本
}
}
else if (c == 3) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
String pointType = String.valueOf(cell.getNumericCellValue());
caseEntity.setPointType(pointType.substring(0, pointType.length() - 2 > 0 ? pointType.length() - 2 : 1));// 埋点类型 0:默认, 1:展现 2:点击
} else {
caseEntity.setPointType(cell.getStringCellValue());// 埋点类型 0:默认, 1:展现 2:点击
}
}
else if (c == 4) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
String pageType = String.valueOf(cell.getNumericCellValue());
caseEntity.setPageType(pageType.substring(0, pageType.length() - 2 > 0 ? pageType.length() - 2 : 1));// 支持的平台版本
} else {
caseEntity.setPageType(cell.getStringCellValue());// 支持的平台版本
}
}
else if (c == 5) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
String actionType = String.valueOf(cell.getNumericCellValue());
caseEntity.setActionType(actionType.substring(0, actionType.length() - 2 > 0 ? actionType.length() - 2 : 1));// 事件标识
} else {
caseEntity.setActionType(cell.getStringCellValue());// 事件标识
}
}
else if (c == 6) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
String cate = String.valueOf(cell.getNumericCellValue());
caseEntity.setCate(cate.substring(0, cate.length() - 2 > 0 ? cate.length() - 2 : 1));// 所属业务
} else {
caseEntity.setCate(cell.getStringCellValue());// 所属业务
}
}
else if (c == 7) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
String params = String.valueOf(cell.getNumericCellValue());
caseEntity.setParams(params.substring(0, params.length() - 2 > 0 ? params.length() - 2 : 1));// 参数
} else {
caseEntity.setParams(cell.getStringCellValue());// 参数
}
}
else if (c == 8) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
String sidDict = String.valueOf(cell.getNumericCellValue());
caseEntity.setSidDict(sidDict.substring(0, sidDict.length() - 2 > 0 ? sidDict.length() - 2 : 1));// 补充参数
} else {
caseEntity.setSidDict(cell.getStringCellValue());// 补充参数
}
}
else if (c == 9) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
String owner = String.valueOf(cell.getNumericCellValue());
caseEntity.setOwner(owner.substring(0, owner.length() - 2 > 0 ? owner.length() - 2 : 1));// 埋点所属人
} else {
caseEntity.setOwner(cell.getStringCellValue());// 埋点所属人
}
}
else if (c == 10) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
String iworkUrl = String.valueOf(cell.getNumericCellValue());
caseEntity.setIworkUrl(iworkUrl.substring(0, iworkUrl.length() - 2 > 0 ? iworkUrl.length() - 2 : 1));// iwork地址
} else {
caseEntity.setIworkUrl(cell.getStringCellValue());// iwork地址
}
}
else if (c == 11) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
String iOSPath = String.valueOf(cell.getNumericCellValue());
caseEntity.setiOSPath(iOSPath.substring(0, iOSPath.length() - 2 > 0 ? iOSPath.length() - 2 : 1));// iOS UI路径
} else {
caseEntity.setiOSPath(cell.getStringCellValue());// iOS UI路径
}
}
else if (c == 12) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
String AndPath = String.valueOf(cell.getNumericCellValue());
caseEntity.setAndPath(AndPath.substring(0, AndPath.length() - 2 > 0 ? AndPath.length() - 2 : 1));// 安卓 UI路径
} else {
caseEntity.setAndPath(cell.getStringCellValue());// 安卓 UI路径
}
}
}
}
// 添加到list
caseEntities.add(caseEntity);
}
return caseEntities;
}
/**
* 验证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)$");
}
}
3、后台实现：
1）定义实体(和数据库表字段对应，也是Excel中导入数据的来源依据)
package ***;
import java.util.Date;
/**
* @author zhaowei
* @date 2019年5月22日 下午5:50:43
*
*/
public class CaseEntity {
public String id;//主键
public int parentId;//父级Id
public String pointName;//埋点名称
public String platform;//所属平台 0:APP, 1:M 2:PC
public String platformVersion;//支持的平台版本
public String pointType;//埋点类型 0:默认, 1:展现 2:点击
public String pageType;//页面标识
public String actionType;//事件标识
public String cate;//所属业务
public String params;//参数
public String sidDict;//补充参数
public String owner;//埋点所属人
public String iworkUrl;//iwork地址
public String iOSPath;//iOS UI路径
public String AndPath;//安卓 UI路径
public String state;//埋点状态 0:正常 1:停用
public Date createTime;//创建时间
public String updateTime;//更新时间
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public int getParentId() {
return parentId;
}
public void setParentId(int parentId) {
this.parentId = parentId;
}
public String getPointName() {
return pointName;
}
public void setPointName(String pointName) {
this.pointName = pointName;
}
public String getPlatform() {
return platform;
}
public void setPlatform(String platform) {
this.platform = platform;
}
public String getPlatformVersion() {
return platformVersion;
}
public void setPlatformVersion(String platformVersion) {
this.platformVersion = platformVersion;
}
public String getPointType() {
return pointType;
}
public void setPointType(String pointType) {
this.pointType = pointType;
}
public String getPageType() {
return pageType;
}
public void setPageType(String pageType) {
this.pageType = pageType;
}
public String getActionType() {
return actionType;
}
public void setActionType(String actionType) {
this.actionType = actionType;
}
public String getCate() {
return cate;
}
public void setCate(String cate) {
this.cate = cate;
}
public String getParams() {
return params;
}
public void setParams(String params) {
this.params = params;
}
public String getSidDict() {
return sidDict;
}
public void setSidDict(String sidDict) {
this.sidDict = sidDict;
}
public String getOwner() {
return owner;
}
public void setOwner(String owner) {
this.owner = owner;
}
public String getIworkUrl() {
return iworkUrl;
}
public void setIworkUrl(String iworkUrl) {
this.iworkUrl = iworkUrl;
}
public String getiOSPath() {
return iOSPath;
}
public void setiOSPath(String iOSPath) {
this.iOSPath = iOSPath;
}
public String getAndPath() {
return AndPath;
}
public void setAndPath(String andPath) {
AndPath = andPath;
}
public String getState() {
return state;
}
public void setState(String state) {
this.state = state;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date date) {
this.createTime = date;
}
public String getUpdateTime() {
return updateTime;
}
public void setUpdateTime(String updateTime) {
this.updateTime = updateTime;
}
}
2）定义两个接口，一个是读取excel中的数据,生成list，另外一个是Excel导入埋点用例，分别如下：
读取Excel：
package ***;
import org.springframework.web.multipart.MultipartFile;
/**
* @author zhaowei
* @date 2019年5月22日 下午8:31:11
*
*/
public interface ExcelService {
/**
* 读取excel中的数据,生成list
*/
String readExcelFile( MultipartFile file,int parentId);
}
导入埋点用例：
package ***;
import com.bj58.automation.entity.CaseEntity;
/**
* @author zhaowei
* @date 2019年5月22日 下午1:06:07
*
*/
public interface PointService {
public void excelCase(CaseEntity caseEntity);//Excel导入埋点用例
}
3）实现以上两个接口，分别为：
读取Excel：
package ***;
import java.util.Date;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import com.bj58.automation.entity.CaseEntity;
import com.bj58.automation.service.ExcelService;
import com.bj58.automation.service.PointService;
import com.bj58.automation.util.ReadExcel;
/**
* @author zhaowei
* @date 2019年5月22日 下午8:35:15
*
*/
@Service("excelServices")
public class ExcelImp implements ExcelService{
@Autowired
PointService pointService;//业务接口
@Override
public String readExcelFile(MultipartFile file,int parentId) {
String result ="";
//创建处理EXCEL的类
ReadExcel readExcel=new ReadExcel();
//解析excel，获取上传的事件单
List<CaseEntity> caseEntities = readExcel.getExcelInfo(file);
//System.out.println(JSON.toJSON(caseEntities));
//至此已经将excel中的数据转换到list里面了,接下来就可以操作list,可以进行保存到数据库,或者其他操作,
//和你具体业务有关,这里不做具体的示范
for (CaseEntity caseEntity : caseEntities) {
caseEntity.setCreateTime(new Date());
caseEntity.setParentId(parentId);
pointService.excelCase(caseEntity);
}
if(caseEntities != null && !caseEntities.isEmpty()){
result = "上传成功";
}else{
result = "上传失败";
}
return result;
}
}
导入埋点用例：
package ***;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.bj58.automation.dao.CaseDao;
import com.bj58.automation.entity.CaseEntity;
import com.bj58.automation.service.PointService;
/**
* @author zhaowei
* @date 2019年5月22日 下午1:08:01
*
*/
@Service("pointServices")
public class PointImp implements PointService{
@Autowired
CaseDao caseDao;//数据访问接口
//Excel导入埋点用例
@Override
public void excelCase(CaseEntity caseEntity) {
caseDao.excelCase(caseEntity);
}
}
4）定义DAO：
package ***;
import com.bj58.automation.entity.CaseEntity;
/**
* @author zhaowei
* @date 2019年5月14日 上午9:53:43
*
*/
public interface CaseDao {
public void excelCase(CaseEntity caseEntity);//Excel导入埋点用例
}
5）定义持久层mapper
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bj58.automation.dao.CaseDao">
<!--
WARNING - @mbggenerated
This element is automatically generated by MyBatis Generator, do not modify.
-->
insert into t_point(parentId,pointName,platform,platformVersion,pointType,pageType,actionType,cate,params,sidDict,
owner,iworkUrl,iOSPath,AndPath,state,createTime,updateTime)
values(#{parentId,jdbcType=INTEGER},#{pointName,jdbcType=VARCHAR},#{platform,jdbcType=VARCHAR},
#{platformVersion,jdbcType=VARCHAR},#{pointType,jdbcType=VARCHAR},#{pageType,jdbcType=VARCHAR},
#{actionType,jdbcType=VARCHAR},#{cate,jdbcType=VARCHAR},#{params,jdbcType=VARCHAR},
#{sidDict,jdbcType=VARCHAR},#{owner,jdbcType=VARCHAR},#{iworkUrl,jdbcType=VARCHAR},
#{iOSPath,jdbcType=VARCHAR},#{AndPath,jdbcType=VARCHAR},0,#{createTime,jdbcType=VARCHAR},
#{updateTime,jdbcType=VARCHAR})
</insert>
</mapper>
6）控制层实现：
//上传Excel
@ResponseBody
@RequestMapping(value="/upload",method = RequestMethod.POST,produces="text/html;charset=UTF-8")
public String upload(@RequestParam(value = "file", required = false) MultipartFile file,@RequestParam(value="id",required = false)Integer id,
HttpServletRequest request, HttpServletResponse response){
String result=null;
result = excelService.readExcelFile(file,id);
return result;
}
基于不同的业务需求，具体要求也不一致。如果不携带额外参数只是Excel上传过程会比较简单，在以上前端和后台接口及实现去掉id即可，效果图如下：