通过poi读取Excel并携带id参数导入数据库，前后端实现超详细讲解

xiaoxiao2022-07-14 157

一、项目中存在这样一个需求：

1）获取当前请求页面URL中的一个参数值taskId

2）将Excel中数据读取和taskId一起存放到数据库(taskId和Excel中数据存在关联关系，但是taskId不在Excel中)

二、应用技术：

后台框架：Springmvc

持久层框架：mybatis

数据库：mysql

前端：jsp、Ajax

项目管理工具：maven，特别需要的pom配置用于操作Excel：

<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>

工程目录结构：

三、实现过程：

1、前端：

1）文件上传页面

2）上传js

</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">  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即可，效果图如下：

最新回复(0)