备案控制台
开发者社区 数据库 文章 正文

将Excel数据批量导入到数据库(项目案例)

2022-10-08 422
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云解析DNS，个人版 1个月
全局流量管理 GTM，标准版 1个月
云解析 DNS，旗舰版 1个月
简介： 我们今天就给大家介绍一个用途非常广泛的功能：批量导入，在很多系统中，这也是必须实现的功能。而且当Excel表结构越复杂时，实现的难度就越高。不管项目如何复杂，原理却基本相同，一般是前台页面选择Excel文件，后台获取后进行数据转化，然后循环执行Sql语句即可，了解这些原理后，一切也就变得简单。

我们今天就给大家介绍一个用途非常广泛的功能：批量导入，在很多系统中，这也是必须实现的功能。而且当Excel表结构越复杂时，实现的难度就越高。


不管项目如何复杂，原理却基本相同，一般是前台页面选择Excel文件，后台获取后进行数据转化，然后循环执行Sql语句即可，了解这些原理后，一切也就变得简单。


下面为了重点演示，项目进行简化，只有批量导入功能，将Excel数据导入到Mysql数据库中。


Mysql数据库user表结构


20210118110454197.png


Excel中数据


20210118110516838.png


如果Excel中必填项数据为空，提示导入失败，报告错误信息及位置，项目演示图：


20210118110329530.gif


如果数据正确，提示导入数据库成功：


20210118110712664.gif



具体实现过程


首先是JSP页面，name的名称是重点：


<form action="${pageContext.request.contextPath }/excelsave.action" method="post" enctype="multipart/form-data">
    <input type="file" name="fileinput" multiple="multiple"/>
    <button type="submit" name="subimit">上传</button>
</form>


前台点击上传后跳转到action处理，action中首先定义：


//这里特别注意获取fileinput要和页面的导入时name属性一致。
private File fileinput;
//文件名用得到就获取，一般用不到。
private String fileinputFileName;
//下面是get和set方法，省略
然后在方法体中直接调用：
//参数为获取到的文件
ExcelUtil(fileinput);


ExcelUtil是处理Excel工具类，直接使用，代码如下：


/**解析excel 工具类*/
@SuppressWarnings("rawtypes")
public class ExcelUtil {
  public FileInputStream fis ;
  public HSSFWorkbook workBook;
  public HSSFSheet sheet;
  public XMLUtil parseXmlUtil;
  public StringBuffer errorString;
  /**当前实体类的code**/
  public String curEntityCode;
  /**表头map对象：key:entityCode, value:headMap(index,headTitle)**/
  public Map curEntityHeadMap ;
  /**字段的必填：key:entityCode+headTitle, value:true(必填),false(不必填)**/
  public Map curEntityColRequired;
  /**存放每一行的数据**/
  public  List listDatas ;
  public ExcelUtil(File excelFile){
    try {
      if(excelFile == null){
        throw new FileNotFoundException();
      }
       fis = new FileInputStream(excelFile);       
       workBook = new HSSFWorkbook(fis);
       parseXmlUtil = new XMLUtil();
       errorString = new StringBuffer();
       readExcelData();
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    }catch (IOException e) {
      e.printStackTrace();
    } 
  }
  /**开始从excel读取数据**/  
  public void readExcelData(){
    int sheetSize = workBook.getNumberOfSheets();
    for(int i=0;i<sheetSize;i++){
      sheet = workBook.getSheetAt(i);
      String entityName = workBook.getSheetName(i);
      readSheetData(sheet,entityName);
    }
  }
  /**读每个sheet页的数据**/
  public void readSheetData(HSSFSheet sheet,String entityName){
       int rowNumbers = sheet.getPhysicalNumberOfRows();
       Map ent = (Map) parseXmlUtil.getEntityMap().get(entityName);
       this.setCurEntityCode((String) ent.get("code"));
       if(rowNumbers == 0){
         System.out.println("excel中数据为空！");
         errorString.append(Constans.ERROR_EXCEL_NULL);
       }      
       List colList = (List) parseXmlUtil.getColumnListMap().get(entityName);
       int xmlRowNum = colList.size();
       HSSFRow excelRow = sheet.getRow(0);
       int excelFirstRow = excelRow.getFirstCellNum();
       int excelLastRow = excelRow.getLastCellNum();
       if(xmlRowNum  != (excelLastRow-excelFirstRow)){
           System.out.println("xml列数与excel列数不相符，请检查");
           errorString.append(Constans.ERROR_EXCEL_COLUMN_NOT_EQUAL);
       }
       readSheetHeadData(sheet);
       readSheetColumnData(sheet,entityName);
     }
 /**读取sheet页中的表头信息**/
   @SuppressWarnings({ "unchecked", "static-access"})
  public void readSheetHeadData(HSSFSheet sheet){
       Map headMap = new HashMap();
       curEntityHeadMap = new HashMap();
       curEntityColRequired = new HashMap();
       HSSFRow excelheadRow = sheet.getRow(0);
       int excelLastRow = excelheadRow.getLastCellNum();
       String headTitle = "";
       for(int i=0;i<excelLastRow;i++){
         HSSFCell cell = excelheadRow.getCell(i);
         headTitle = this.getStringCellValue(cell).trim();
         if(headTitle.endsWith("*")){
           curEntityColRequired.put(this.getCurEntityCode()+"_"+headTitle,true);
         }else{
           curEntityColRequired.put(this.getCurEntityCode()+"_"+headTitle,false);
         }
         headMap.put(i, headTitle);
       }
       curEntityHeadMap.put(this.getCurEntityCode(), headMap);
     }
   /**读取sheet页里面的数据**/
   @SuppressWarnings({ "unchecked", "static-access" })
   public void readSheetColumnData(HSSFSheet sheet,String entityName){
     HSSFRow excelheadRow = sheet.getRow(0);
     int excelLastcell = excelheadRow.getLastCellNum();   //excel总列数
     int excelRowNum = sheet.getLastRowNum();  //excel总行数
     Map headMap = (Map) this.getCurEntityHeadMap().get(this.getCurEntityCode());    
     Map colMap = parseXmlUtil.getColumnMap();
     listDatas =new ArrayList();
     for(int i=1;i<excelRowNum+1;i++){//行循环            
       HSSFRow columnRow = sheet.getRow(i); 
       if(columnRow != null){
         Map curRowCellMap = new HashMap();
         for(int j =0; j<excelLastcell;j++){ //列循环
           int cout =  headMap.get(j).toString().indexOf("*");
           String headTitle ="";
           if(cout == -1){
            headTitle = headMap.get(j).toString();
           }else{
             headTitle =  headMap.get(j).toString().substring(0, cout);
           }                
           Map curColMap =  (Map) colMap.get(entityName+"_"+headTitle);
           String curColCode = (String) curColMap.get("code");
           String curColType = (String) curColMap.get("type");
           HSSFCell colCell = columnRow.getCell(j);
           String value =this.getStringCellValue(colCell);
           if(value != null){
             value = value.trim();
           }        
           String xmlColType = (String) curColMap.get("type");
           int intVal = 0;
           if(xmlColType.equals("int")){
             if(value != null) {
               intVal = Integer.valueOf(value);
             }
             curRowCellMap.put(curColCode, intVal);  //将这一行的数据以code-value的形式存入map
           }else{
              curRowCellMap.put(curColCode, value); 
           }
           /**验证cell数据**/
           validateCellData(i+1,j+1,colCell,entityName,headTitle,curColType,listDatas);
         }
         listDatas.add(curRowCellMap);
       } 
     }
     if(this.getErrorString().length() ==0){//如果没有任何错误，就保存
       saveExcelData(entityName);
       System.out.println("导入数据库成功");
     }else{
       //清理所有的缓存clearMap();现在暂时未清理
       String[] strArr = errorString.toString().split("<br>");
       for(String s: strArr){
         System.out.println(s);
       }
     }
   }
   /**验证单元格数据**/
   @SuppressWarnings("static-access")
   public void validateCellData(int curRow,int curCol,HSSFCell colCell,String entityName,String headName,String curColType,List listDatas){
     List rulList = (List) parseXmlUtil.getColumnRulesMap().get(entityName+"_"+headName);
     if(rulList != null && rulList.size()>0){
       for(int i=0 ; i<rulList.size() ; i++){
         Map rulM = (Map) rulList.get(i);
         String rulName = (String) rulM.get("name");
         String rulMsg = (String) rulM.get("message");
         String cellValue = "";
         if(this.getStringCellValue(colCell)==null) {
           //System.out.println("第"+curRow+"行,第"+curCol+"列:"+rulMsg);
         }else {
           cellValue = this.getStringCellValue(colCell).trim();
         }
         if(rulName.equals(Constans.RULE_NAME_NULLABLE)){      
           if(cellValue.equals("")||cellValue == null){
             errorString.append("导入失败，错误信息：第"+curRow+"行,第"+curCol+"列:"+rulMsg+"<br>");
           }
         }
      //这里写其他的验证规则。。。
      }
     }
   }
   /**保存excel里面的数据**/
   @SuppressWarnings("unchecked")
   public void saveExcelData(String entityName){
       List<User> users= new ArrayList();
     for(int i = 0 ; i<this.getListDatas().size();i++){
       Map excelCol = (Map) this.getListDatas().get(i);  //得到第 i 行的数据    
       User user = new User();
       try {
      User obj = (User) BeanToMapUtil.convertMap(user.getClass(), excelCol);
      users.add(obj);  
    } catch (IntrospectionException e) {      
      e.printStackTrace();
    } catch (IllegalAccessException e) {    
      e.printStackTrace();
    } catch (InstantiationException e) {    
      e.printStackTrace();
    } catch (InvocationTargetException e) {
      e.printStackTrace();
    }
     }
     /**批量保存数据**/
     Dao dao = new Dao();
     for(int i = 0;i<users.size();i++){
       try{
       dao.saveUser(users.get(i));
       }catch(Exception e){
         e.printStackTrace();
       }
     }
   }
   /**
   * 获得单元格字符串
   * @throws UnSupportedCellTypeException 
   */
  public static String getStringCellValue(HSSFCell cell) {
    if (cell == null){
      return null;
    }
    String result = "";
    switch (cell.getCellType()) {
      case HSSFCell.CELL_TYPE_BOOLEAN:
        result = String.valueOf(cell.getBooleanCellValue());
        break;
      case HSSFCell.CELL_TYPE_NUMERIC:
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
          java.text.SimpleDateFormat TIME_FORMATTER = new java.text.SimpleDateFormat(
              "yyyy-MM-dd");
          result = TIME_FORMATTER.format(cell.getDateCellValue());
        }
        else{
          double doubleValue = cell.getNumericCellValue();
          result = "" + doubleValue;
        }
        break;
      case HSSFCell.CELL_TYPE_STRING:
        if (cell.getRichStringCellValue() == null){
          result = null;
        }
        else{
          result = cell.getRichStringCellValue().getString();
        }
        break;
      case HSSFCell.CELL_TYPE_BLANK:
        result = null;
        break;
      case HSSFCell.CELL_TYPE_FORMULA:
        try{
          result = String.valueOf(cell.getNumericCellValue());   
            }catch(Exception e){
              result = cell.getRichStringCellValue().getString();
            }
        break;
      default:
        result = "";
    }
    return result;
  }
  public String getCurEntityCode() {
    return curEntityCode;
  }
  public void setCurEntityCode(String curEntityCode) {
    this.curEntityCode = curEntityCode;
  }
  public Map getCurEntityHeadMap() {
    return curEntityHeadMap;
  }
  public void setCurEntityHeadMap(Map curEntityHeadMap) {
    this.curEntityHeadMap = curEntityHeadMap;
  }
  public XMLUtil getParseXmlUtil() {
    return parseXmlUtil;
  }
  public void setParseXmlUtil(XMLUtil parseXmlUtil) {
    this.parseXmlUtil = parseXmlUtil;
  }
  public Map getCurEntityColRequired() {
    return curEntityColRequired;
  }
  public void setCurEntityColRequired(Map curEntityColRequired) {
    this.curEntityColRequired = curEntityColRequired;
  }
  public List getListDatas() {
    return listDatas;
  }
  public void setListDatas(List listDatas) {
    this.listDatas = listDatas;
  }
  public StringBuffer getErrorString() {
    return errorString;
  }
  public void setErrorString(StringBuffer errorString) {
    this.errorString = errorString;
  }
}


项目中定义一个XML文件，主要做一些条件限制，比如用户名不能为空，email不能重复等，所以就有一个XML解析类：

/**解析xml工具类*/
@SuppressWarnings("rawtypes")
public class XMLUtil {
   /**entity map对象，key:name ,value:entity的属性map集**/
  public Map entityMap ;
  /**column map 对象，key:entityName_colName , value:column的属性map集 **/
  public Map columnMap;
  /**rule map 对象，key:entityName_colName_ruleName, value: rule 的map集：找到一行rule**/
  public Map ruleMap ;
  /**rules map 对象, key:entityName_colName, value: rules 的map集:找到该column下所有的rule**/
  public Map  columnRulesMap ;
  /**entity--column map: key:entityName, value: column list:根据实体类名得到所有的列**/
  public Map columnListMap ;
   /**column list**/
  public List columnList ;
    /**开始解析xml文件**/
  public XMLUtil(){
    SAXReader reader = new SAXReader();
    InputStream in = getClass().getClassLoader().getResourceAsStream("user.xml");//读取文件流，Url为controller.xml文件
    try {
      Document doc = reader.read(in);//获得文件实例
      Element root = doc.getRootElement();  
      Iterator itEntity = root.elements("entity").iterator();
      while(itEntity.hasNext()){
        Element entity = (Element) itEntity.next();
        parseEntity(entity);
      }
      /**测试entityMap 是否正确**/
      Map enMap = (Map) this.getEntityMap().get("用户表");
      Set<?> set = enMap.keySet();
      Iterator it = set.iterator();
      while(it.hasNext()){
        String uu = (String) it.next();
      }
    }catch(Exception e){
      e.printStackTrace();
    }
  }
   /**开始解析entity**/
  @SuppressWarnings("unchecked")
  public void parseEntity(Element entity){
    if(entity != null){
      /**对数据进行初始化设置**/
      columnListMap = new HashMap();
      columnMap = new HashMap();
      entityMap = new HashMap();
      ruleMap = new HashMap();
      columnRulesMap = new HashMap();
      columnList = new ArrayList();
      setEntityMap(entity);     
      String entityName = entity.attributeValue("name");
      Iterator itColumn = entity.elements("column").iterator();
      while(itColumn.hasNext()){
        Element column = (Element) itColumn.next();
        setColumnMap(entityName,column);
      }
      columnListMap.put(entityName, columnList);
    }
  }
  /**将entity放入entityMap中**/
  @SuppressWarnings("unchecked")
  public void setEntityMap(Element entity){   
    Map ent = new HashMap();
    String name = entity.attributeValue("name");
    String code = entity.attributeValue("code");
    ent.put("name", name);
    ent.put("code", code);
    entityMap.put(name, ent);     
  }
  /**将column放入columnMap中**/
  @SuppressWarnings("unchecked")
  public void setColumnMap(String entityName,Element column){
    if(column != null){   
      Map col = new HashMap();
      String name = column.attributeValue("name");
      String code = column.attributeValue("code");
      String type = column.attributeValue("type");
      col.put("name", name);
      col.put("code", code);
      col.put("type", type);
      String columnMapKey = entityName+"_"+name;    //eg:  用户表_用户名
      columnMap.put(columnMapKey, col);   
      columnList.add(col);
      Iterator ruleIt = column.elements("rules").iterator();  //获得rules
      while(ruleIt.hasNext()){
        Element rules = (Element)ruleIt.next(); 
          Iterator rule  = rules.elements("rule").iterator();   //获得 rule
          while(rule.hasNext()){
            Element ruleValid = (Element) rule.next();     //获得每一行rule
            setRuleMap(entityName,name,ruleValid);            
          }
      }
    }
  }
    /**将 rule 验证规则放入ruleMap中**/
  @SuppressWarnings("unchecked")
  public void setRuleMap(String entityName,String columnName,Element ruleValid){
    if(ruleValid != null){      
      String ruleName = ruleValid.attributeValue("name");
      String ruleMsg = ruleValid.attributeValue("message");
      Map ruleValidMap = new HashMap();
      ruleValidMap.put("name", ruleName);
      ruleValidMap.put("message", ruleMsg);
      String ruleStrKey = entityName+"_"+columnName+"_"+ruleName;
      String colStrKey = entityName+"_"+columnName;
      if(this.getColumnRulesMap().containsKey(colStrKey)){
          List valids = (List) this.getColumnRulesMap().get(colStrKey);
          valids.add(ruleValidMap);
        }else{
          List valids = new ArrayList();
          valids.add(ruleValidMap);
          this.columnRulesMap.put(colStrKey, valids);  //将每个column下的所有rules存入该map中
        }
      ruleMap.put(ruleStrKey, ruleValidMap); //将每个column下的一条rule存入该map中
    }
  }
  /**所有的get set 方法**/
  public Map getEntityMap() {
    return entityMap;
  }
  public void setEntityMap(Map entityMap) {
    this.entityMap = entityMap;
  }
  public Map getColumnMap() {
    return columnMap;
  }
  public void setColumnMap(Map columnMap) {
    this.columnMap = columnMap;
  }
  public Map getRuleMap() {
    return ruleMap;
  }
  public void setRuleMap(Map ruleMap) {
    this.ruleMap = ruleMap;
  }
  public Map getColumnRulesMap() {
    return columnRulesMap;
  }
  public void setColumnRulesMap(Map columnRulesMap) {
    this.columnRulesMap = columnRulesMap;
  }
  public Map getColumnListMap() {
    return columnListMap;
  }
  public void setColumnListMap(Map columnListMap) {
    this.columnListMap = columnListMap;
  }
}


XML解析类定义完成后，ExcelUtil会调用处理，处理完成后返回，导入结束。

至于数据库连接，任何方式都行，这里不做要求，另外需要定义一个实体User，属性根据自己的业务设置。


以上就是具体实现过程，如果有任何问题，私信，我们共同交流讨论。


文章标签：
云解析DNS
Java
SQL
关系型数据库
MySQL
数据格式
数据库
XML
数据库连接
关键词：
excel数据
数据库数据
数据库案例
数据库项目
excel导入
猫头鹰源码(同名B站)
目录
相关文章
jianz123
|
2天前
|
数据格式 Python
Python代码示例，读取excel表格，将行数据转为列数据。（10）
【7月更文挑战第10天】Python代码示例，读取excel表格，将行数据转为列数据。
jianz123
13 2
jianz123
|
5天前
|
ARouter 关系型数据库 MySQL
Django项目同时连接多个不同的数据库(7)
【7月更文挑战第7天】在Django项目中配置连接多个数据库，你需要： 1. 在`settings.py`中配置多个数据库， 2. 在`settings.py`内设置数据库路由，指定每个应用使用的数据库，
jianz123
18 2
weixin_836869520
|
6天前
|
Oracle 关系型数据库 Java
Oracle数据库导入工具IMP详解与用法
Oracle数据库导入工具IMP详解与用法
weixin_836869520
23 2
weixin_836869520
|
6天前
|
SQL XML Java
解决Spring Boot项目中的数据库迁移问题
解决Spring Boot项目中的数据库迁移问题
weixin_836869520
21 2
67vpym2vvkfhs
|
3天前
|
前端开发 JavaScript PHP
技术心得：前端点击按钮，导入excel文件，上传到后台，excel接收和更新数据
技术心得：前端点击按钮，导入excel文件，上传到后台，excel接收和更新数据
67vpym2vvkfhs
6 0
申公豹
|
4天前
|
数据可视化 数据挖掘 数据处理
Python对Excel两列数据进行运算【从基础到高级的全面指南】
【7月更文挑战第6天】使用Python的`pandas`库处理Excel数据，涉及安装`pandas`和`openpyxl`，读取数据如`df = pd.read_excel(&#39;data.xlsx&#39;)`，进行运算如`df[&#39;Sum&#39;] = df[&#39;Column1&#39;] + df[&#39;Column2&#39;]`，并将结果写回Excel。`pandas`还支持数据筛选、分组、可视化、异常处理和性能优化。通过熟练运用这些功能，可以高效分析Excel表格。
申公豹
8 0
聚娃科技开发者团队
|
7天前
|
SQL XML Java
解决Spring Boot项目中的数据库迁移问题
解决Spring Boot项目中的数据库迁移问题
聚娃科技开发者团队
9 0
爱你三千遍斯塔克
|
9天前
|
前端开发 JavaScript NoSQL
软件开发常见流程之若依项目修改，前后端本地启动，本地必须有Redis+Navicate启动数据库
软件开发常见流程之若依项目修改，前后端本地启动，本地必须有Redis+Navicate启动数据库
爱你三千遍斯塔克
12 0
爱你三千遍斯塔克
|
10天前
|
SQL 关系型数据库 MySQL
Navicate，数据库，Mysql，改表，4月29日Finished - Unsuccessfully，导出数据不妨，右键，备份一下Mysql数据库的内容，你想导入和导出数据不如，用查询的方式去做
Navicate，数据库，Mysql，改表，4月29日Finished - Unsuccessfully，导出数据不妨，右键，备份一下Mysql数据库的内容，你想导入和导出数据不如，用查询的方式去做
爱你三千遍斯塔克
342 0
泥腿子架构师
|
15天前
|
Oracle 关系型数据库 Java
Oracle数据库导入工具IMP详解与用法
Oracle数据库导入工具IMP详解与用法
泥腿子架构师
22 0