java若依框架ruoyi导入Excel（附详细代码）

程序员温眉

已于 2023-12-05 09:45:02 修改

阅读量5k

点赞数 4

文章标签：笔记 java excel

于 2023-06-07 15:57:50 首次发布

本文链接：https://blog.csdn.net/sublime_k/article/details/131089842

版权

【版权所有，文章允许转载，但须以链接方式注明源地址，否则追究法律责任】
【创作不易，点个赞就是对我最大的支持】

前言

仅作为学习笔记，供大家参考
总结的不错的话，记得点赞收藏关注哦！

Excel导入

可能出现的问题

1.开发模板下载功能（如需定制列，可以单独创建一个实体类，@Excel注解定义好名字）

2.导出后的列表如果有字典， @Excel(name = “建筑业资质等级”,dictType=“sys_qualifications_leave”)

后台代码

controller层

 /**
     * 导出合理性分析列表
     */
    @RequiresPermissions("business:hlxfx:export")
    @Log(title = "合理性分析", businessType = BusinessType.EXPORT)
    @PostMapping("/export")
    @ResponseBody
    public AjaxResult export(GcQyHlxfx gcQyHlxfx) throws ParseException {
        List<GcQyHlxfx> list = gcQyHlxfxService.selectGcQyHlxfxList(gcQyHlxfx);
        ExcelUtil<GcQyHlxfx> util = new ExcelUtil<GcQyHlxfx>(GcQyHlxfx.class);
        return util.exportExcel(list, "合理性分析数据");
    }
    //导入数据
    @Log(title = "合理分析", businessType = BusinessType.IMPORT)
    @PostMapping("/importData")
    @ResponseBody
    public AjaxResult importData(MultipartFile file, boolean updateSupport) throws Exception
    {
        ExcelUtil<GcQyHlxfx> util = new ExcelUtil<GcQyHlxfx>(GcQyHlxfx.class);
        List<GcQyHlxfx> gcQyHlxfxList = util.importExcel(file.getInputStream());
        String message = gcQyHlxfxService.importHlxfx(gcQyHlxfxList, updateSupport, getLoginName());
        return AjaxResult.success(message);
    }
	//模板下载
    @GetMapping("/importTemplate")
    @ResponseBody
    public AjaxResult importTemplate()
    {
        ExcelUtil<GcQyHlxfxImport> util = new ExcelUtil<GcQyHlxfxImport>(GcQyHlxfxImport.class);
        return util.importTemplateExcel("合理分析");
    }

Service层

     public String importHlxfx(List<GcQyHlxfx> gcQyHlxfxList, Boolean isUpdateSupport, String operName) {
        if (StringUtils.isNull(gcQyHlxfxList) || gcQyHlxfxList.size() == 0)
        {
            throw new ServiceException("导入数据不能为空！");
        }
        int successNum = 0;
        int failureNum = 0;
        StringBuilder successMsg = new StringBuilder();
        StringBuilder failureMsg = new StringBuilder();
        for (GcQyHlxfx res : gcQyHlxfxList)
        {
            try
            {
                // 验证是否存在这个企业
                GcQyJbxx jbxx = gcQyJbxxMapper.selectGcQyJbxxByxydm(res.getSocialCode());
                if (!StringUtils.isNull(jbxx)){
                    LocalDate localDate=LocalDate.now();
                    res.setJzyzzdj(jbxx.getJZYZZDJ());
                    res.setQyszcq(jbxx.getQYSZCQ());
                    GcQyHlxfx sfgcQyHlxfx = new GcQyHlxfx();
                    sfgcQyHlxfx.setSocialCode(res.getSocialCode());
                    DecimalFormat df2 = new DecimalFormat("00");
                    String yMonth=localDate.getYear()+"-"+df2.format(localDate.getMonth().getValue());
                    sfgcQyHlxfx.setYearMonths(yMonth);
                    List<GcQyHlxfx> gcQyHlxfxes = gcQyHlxfxMapper.selectGcQyHlxfxList(sfgcQyHlxfx);
                    GcQyHlxfx gcQyHlxfx = new GcQyHlxfx();
                    String jzbnd = "0";//本平台累积产值
                    if (gcQyHlxfxes.size()>0){
                        gcQyHlxfx = gcQyHlxfxes.get(0);
                        jzbnd = gcQyHlxfx.getBndzcz();
                    }
                    Integer i = 0;
                    Integer c = 0;
                    double b = 0;
                    String bnd = res.getZhptJzcyzcz();//智慧平台本年度累积产值
                    if (bnd.contains(".")){
                        b = Double.parseDouble(bnd);
                        i = (int)b;
                    }else{
                        i = Integer.parseInt(bnd);
                    }
                    if (jzbnd.contains(".")){
                        b = Double.parseDouble(jzbnd);
                        c = (int)b;
                    }else{
                        c = Integer.parseInt(jzbnd);
                    }
                    //计算校核
                   Integer checks = c - i;
                    if (checks>0){
                        res.setDataMatch(1L);
                    }else if (checks==0){
                        res.setDataMatch(2L);
                    }else if (checks<0){
                        res.setDataMatch(3L);
                    }
                    res.setChecks(Long.parseLong(checks.toString()));

                        if (gcQyHlxfxes.size()>0){
                       BeanValidators.validateWithException(validator, res);
                       res.setId(gcQyHlxfx.getId());
                        res.setUpdateBy(operName);
                        //res.setYearMonths(yMonth);
                        res.setGxsj(new Date());
                        res.setGxr(ShiroUtils.getSysUser().getUserId().toString());
                        this.updateGcQyHlxfx(res);
                        successNum++;
                        successMsg.append("<br/>" + successNum + "、企业： " + res.getQymc() + " 更新成功");
                    }
                    else
                    {
                        failureNum++;
                        failureMsg.append("<br/>" + failureNum + "、企业： " + res.getQymc() +" 本月未上报");
                    }

                }else {
                    failureNum++;
                    failureMsg.append("<br/>" + failureNum + "、企业： " + res.getQymc() +" <font color='red'>系统内未查到该企业信息!</font>");
                }

            }
            catch (Exception e)
            {
                failureNum++;
                String msg = "<br/>" + failureNum + "、企业： " + res.getQymc() + " 导入失败：";
                failureMsg.append(msg + e.getMessage());
                log.error(msg, e);
            }
        }

        if (failureNum > 0)
        {
            failureMsg.insert(0, "共" + successNum + " 条数据导入成功, " + failureNum + " 条数据格式不正确，错误如下：");
            throw new ServiceException(failureMsg.toString());
        }
        else
        {
            successMsg.insert(0, "恭喜您，数据已全部导入成功！共 " + successNum + " 条，数据如下：");
        }
        return successMsg.toString();
    }

前端

                url: prefix + "/list",
                createUrl: prefix + "/add",
                updateUrl: prefix + "/edit/{id}",
                removeUrl: prefix + "/remove",
                exportUrl: prefix + "/export",
                importUrl: prefix + "/importData",
                importTemplateUrl: prefix + "/importTemplate",

  <!-- 导入区域 -->
     <script id="importTpl" type="text/template">
         <form enctype="multipart/form-data" class="mt20 mb10">
             <div class="col-xs-offset-1">
                 <input type="file" id="file" name="file"/>
                 <div class="mt10 pt5">
                    <!-- <input type="checkbox" id="updateSupport" name="updateSupport" title="如果已经存在，更新这条数据。"> 是否更新已经存在的数据-->
                     请点击下载模板后进行导入
                     &nbsp;	<a onclick="$.table.importTemplate()" class="btn btn-default btn-xs"><i class="fa fa-file-excel-o"></i> 下载模板</a>
                 </div>
                 <font color="red" class="pull-left mt10">
                     提示：仅允许导入“xls”或“xlsx”格式文件！
                 </font>
             </div>
         </form>
     </script>

html

    <div class="btn-group-sm" id="toolbar" role="group">

                <a class="btn btn-warning" onclick="$.table.exportExcel()" shiro:hasPermission="business:hlxfx:export">
                    <i class="fa fa-download"></i> 导出
                </a>
                <a class="btn btn-info" onclick="$.table.importExcel()" shiro:hasPermission="system:hlxfx:export">
                    <i class="fa fa-upload"></i> 导入
                </a>
            </div>