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

╰半橙微兮° 2022-01-28 09:39 256阅读 0赞

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

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>

工程目录结构：

![20190523213452125.png][]

![20190523213516386.png][]

**三、实现过程：**

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p3MV9jc2Ru_size_16_color_FFFFFF_t_70][]

[20190523213452125.png]: /images/20220128/c55fac9ca1004f4180f01ba68d5af4a7.png
[20190523213516386.png]: /images/20220128/1b5ff83b2bec43a9b126253df93f34ab.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p3MV9jc2Ru_size_16_color_FFFFFF_t_70]: /images/20220128/6ff21a39e4bb4316b33a31b876e82179.png