栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

java导入excel文件导入数据(后端springboot+前端angular)

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

java导入excel文件导入数据(后端springboot+前端angular)

前端(angular)

html层

  
  
  
    
  


  
TS层
value: number = 0;
showimport:boolean=false;
isDisplay:boolean = false;
importUrl:string = this.config.getContextPath() + '/importData/importExcel';

点击导入按钮触发点击事件时会将 this.showimport = true;  来显示html页面的导入页面

// 上传文件回调
onUpload(event) {
  var r = event.originalEvent.body;
  if ('0' === r.code) {
    this.value = 100;
    this.showimport = false;
    this.lastQryCriteria = new importDataQueryCriteria();
    this.lastQryCriteria.count = true;
    this.paginator.changePage(0);
    this.messageService.add({severity: 'success', summary: '提示', detail: '导入成功'});
    this.onQuery();//导入之后刷新页面的方法
  }else {
    this.value = 0;
    this.messageService.add({severity: 'info', summary: '提示', detail: r.message});
  }
  this.isDisplay = false;
}
// 上传后
onBeforeUpload(event) {
  this.isDisplay = true;
}

后端(springboot)

注意配置文件要加上接收文件大小

 servlet:
    multipart:
      enabled: true #是否启用http上传处理
      max-request-size: 100MB #最大请求文件的大小
      max-file-size: 20MB #设置单个文件最大长度
      file-size-threshold: 20MB #当文件达到多少时进行磁盘写入

Controller层


    @RequestMapping(value = "/importExcel")
    public RestResult importExcel(HttpServletRequest request) {
        RestResult result = new RestResult();
        result.setCode(RestResult.FAIL);
        try {
            importDataService.importExcel(request);
            result.setCode(RestResult.SUCCESS);
        } catch (Exception e) {
            result.setMessage(HandleException.handlingException(e,logger));
        }
        return result;
    }

service层


    void importExcel(HttpServletRequest request)throws Exception;

ServiceImpl层

举个例子:
        public static final String CREDIT_CODE = "信用代码";  来进行要导入excel表格表头的对比

 
    @Override
    @Transactional
    public void importExcel(HttpServletRequest request) throws Exception {
        //获取当前登录人主键和当前时间
        long userId = InvocationInfoProxy.getUserId();
        Date nowDate = new Date();
        //获取文件集合
        List list_f = ExcelUtil.getFile((MultipartHttpServletRequest)request);
        //获取文件信息
        MultipartFile file = list_f.get(0).getFileMultipart();
        //检查文件类型
        ExcelUtil.checkFile(file);
        // 获得Workbook工作薄对象
        Workbook workbook = ExcelUtil.getWorkBook(file);
        //遍历sheet工作表
        for (int sheetNum = 0; sheetNum < 1 ; sheetNum++) {
            // 获得当前sheet工作表
            Sheet sheet = workbook.getSheetAt(sheetNum);
            if (Validator.isNull(sheet)) {
                continue;
            }
            // 获得当前sheet的开始行
            int firstRowNum = sheet.getFirstRowNum();
            // 获得当前sheet的结束行
            int lastRowNum = sheet.getLastRowNum();
            //获得当前行的开始列  
            int firstCellNum = sheet.getRow(firstRowNum).getFirstCellNum();  
            //获得当前行的列数  
            int lastCellNum = sheet.getRow(firstRowNum).getLastCellNum();
            //标题行
            Row titleRow=sheet.getRow(firstRowNum);  
            //错误提示信息
            StringBuffer error = ExcelUtil.getErrorStringBuffer(sheet);
            //导入信息保存
            List list_ide = new ArrayList();
            importDataEntity importDataEntity = null;
            for (int rowNum = firstRowNum+1; rowNum <= lastRowNum; rowNum++) {
                 // 获得当前行
                Row row = sheet.getRow(rowNum);
                if (Validator.isNull(row)) {
                    continue;
                }
                //导入信息
                importDataEntity = new importDataEntity();
                for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
                      String cellValue = ExcelUtil.getCellValue(row,cellNum);
                      String titleCellValue = ExcelUtil.getCellValue(titleRow,cellNum);
                      
                      if(Validator.equals(CREDIT_CODE, titleCellValue)) {
                          if(Validator.isNull(cellValue)) {
                              throw new BusinessException(error+"第"+(rowNum+1)+"xxx为空");
                          } else if(cellValue.length() > 32) {
                              throw new BusinessException(error+"第"+(rowNum+1)+"xxx长度不能超过32");
                          }

         //查询是否重复(唯一字段)
                          List list_c = importDataService.listCreditCode(cellValue);
                          if(Validator.isNotNull(list_c) && list_c.size()>0) {
                            throw new BusinessException(error+"第"+(rowNum+1)+"行,xxx:"+cellValue+",已存在");
                        }
                          importDataEntity.setCreditCode(cellValue);
                          continue;
                      }
                     
                      if(Validator.equals(ENTERPRISE_NAME, titleCellValue)) {
                          if(Validator.isNull(cellValue)) {
                              throw new BusinessException(error+"第"+(rowNum+1)+"xxx不能为空");
                          } else if(cellValue.length() > 100) {
                              throw new BusinessException(error+"第"+(rowNum+1)+"xxx不能超过100");
                          }
                          importDataEntity.setEnterpriseName(cellValue);
                          continue;
                      }
                     
                      if(Validator.equals(BUSINESS_SCOPE, titleCellValue)) {
                          importDataEntity.setBusinessScope(cellValue);
                          continue;
                      }
                     
                      if(Validator.equals(LEGAL_PERSON, titleCellValue)) {
                          importDataEntity.setLegalPerson(cellValue);
                          continue;
                      }
                     
                      if(Validator.equals(ENTERPRISE_ADDRESS, titleCellValue)) {
                          importDataEntity.setEnterpriseAddress(cellValue);
                          continue;
                      }
                    
                      if(Validator.equals(LEGAL_PERSON_CARD, titleCellValue)) {
                          importDataEntity.setLegalPersonCard(cellValue);
                          continue;
                      }
                     
                      if(Validator.equals(LEGAL_PERSON_CARD_ID, titleCellValue)) {
                          importDataEntity.setLegalPersonCardId(cellValue);
                          continue;
                      }
                    
                      if(Validator.equals(ENTERPRISE_TYPE, titleCellValue)) {
                          importDataEntity.setEnterpriseType(cellValue);
                          continue;
                      }
                    
                      if(Validator.equals(ENTERPRISE_PHONE, titleCellValue)) {
                          importDataEntity.setEnterprisePhone(cellValue);
                          continue;
                      }
                     
                      if(Validator.equals(REGISTERED_CAPITAL, titleCellValue)) {
                          importDataEntity.setRegisteredCapital(cellValue);
                          continue;
                      }
                     
                      if(Validator.equals(START_DATE, titleCellValue)) {
                        DateFormat fmt =new SimpleDateFormat("yyyy-MM-dd");
                        Pattern pattern = Pattern.compile("[0-9]*");
                        Matcher isNum = pattern.matcher(cellValue);
                        if( !isNum.matches() ){               
                            Date date = fmt.parse(cellValue);
                            importDataEntity.setStartDate(date);
                        } else {
                            Date date = fmt.parse(DateUtils.formatDate(HSSFDateUtil.getJavaDate(Double.parseDouble(cellValue)), DateUtils.DATE_FMT_YYYY_MM_DD));
                            importDataEntity.setStartDate(date);
                        }
                          continue;
                      }
                     
                      if(Validator.equals(END_DATE, titleCellValue)) {
                        if(cellValue.equals("长期")) {
                            DateFormat fmt =new SimpleDateFormat("yyyy-MM-dd");
                            Date date1=fmt.parse("9999-01-01");
                            importDataEntity.setEndDate(date1);
                         }else {
                             DateFormat fmt =new SimpleDateFormat("yyyy-MM-dd");
                             Pattern pattern = Pattern.compile("[0-9]*");
                             Matcher isNum = pattern.matcher(cellValue);
                             if( !isNum.matches() ){               
                                 Date date = fmt.parse(cellValue);
                                 importDataEntity.setEndDate(date);
                             } else {
                                 Date date = fmt.parse(DateUtils.formatDate(HSSFDateUtil.getJavaDate(Double.parseDouble(cellValue)), DateUtils.DATE_FMT_YYYY_MM_DD));
                                importDataEntity.setEndDate(date);
                             }
                         }
                          continue;
                      }

                      if(Validator.equals(BUSINESS_TYPE, titleCellValue)) {
                          importDataEntity.setBusinessType(cellValue);
                          continue;
                      }
            
                      if(Validator.equals(BUSINESS_CODE, titleCellValue)) {
                          importDataEntity.setBusinessCode(cellValue);
                          continue;
                      }
                }

                importDataEntity.setMerchantDataId(IDGenerator.get32UUID());
                importDataEntity.setCreateDate(nowDate);
                importDataEntity.setModifyDate(nowDate);
                importDataEntity.setCreator(userId);
                importDataEntity.setModifier(userId);
                list_ide.add(importDataEntity);
                
                //保存导入信息
                if(rowNum%100 == 0 || rowNum == lastRowNum) {
                    importDataService.saveimportDataList(list_ide);
                    list_ide = new ArrayList();
                }
            }
        }
    }
    
    
    @Override
    public int saveimportDataList(List list_id) {
        return importDataMapper.insertAllimportData(list_id);
    }

mapper层

int insertAllimportData(@Param("list_id")List list_id);

sql语句例子:

oracle:


      insert all
      
        into 表名
       
         
            MERCHANT_DATA_ID,
         

        
            CREDIT_CODE,
         

       

       
         
            #{item.merchantDataId,jdbcType=VARCHAR},
         

       
            #{item.creditCode,jdbcType=VARCHAR},
         

       

   

    select 1 from dual
 

MySQL

 
        insert into 表名
        (
        MERCHANT_DATA_ID,
        CREDIT_CODE
        )
        values
       
            (
            #{item.merchantDataId,jdbcType=VARCHAR},
            #{item.creditCode,jdbcType=VARCHAR}
            )
       

 

转载请注明:文章转载自 www.mshxw.com
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号