Excel实现批量更新数据

实现通过Excel批量更新数据

前端H5

<div id="batchUpdateGiftPoolWin" class="easyui-window" title="批量修改商品" style="width:350px;height:250px;display:none"
     data-options="iconCls:'icon-save',modal:true,closed:true">
    <div class="easyui-layout" data-options="fit:true" style="margin:30px 0 0 0;text-align:center;">
        <form id="batchUpdateForm" enctype="multipart/form-data" method="post">
            <div style="margin-bottom:40px">
                <input class="easyui-filebox" label="选择文件:" labelPosition="left" data-options="prompt:'请选择Excel文件'"
                       name="giftPoolImport" id="updateGiftPoolImport"
                       style="width:200px" buttonText="选择"
                       accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel">
            </div>
        </form>
        <div style="margin-top:40px;text-align:center;">
            <a href="#" class="easyui-linkbutton" style="width:80px;" onclick="submitImportUpdateGiftPool()">修改</a>
        </div>
    </div>
</div>

前端JS（部分找不到的对象可删除）

function submitImportUpdateGiftPool() {
    let updateGiftPoolImport=$("#updateGiftPoolImport").filebox('getValue');
    if(updateGiftPoolImport ==undefined || updateGiftPoolImport==''){
        $.messager.alert('温馨提示', "请上传文件");
        return ;
    }
    $.messager.progress({
        title: '请稍后',
        msg: '数据提交中...'
    });
    setTimeout(function(){
        $.messager.progress('close');
    },5000);
    $('#batchUpdateForm').form('submit', {
        url: "/mallAdm/batchUpdateGiftPool",
        type: 'POST',
        success: function (data) {
            let result = eval('(' + data + ')');  // change the JSON string to javascript object
            if (result.code === 200) {
                $('#batchUpdateForm').form('reset');
                $('#dg').datagrid('reload');
                $('#category1').combobox('reload');
                $.messager.progress("close");
                $('#batchUpdateGiftPoolWin').window('close');
                $.messager.alert('温馨提示', result.msg);
            }else {
                $('#batchUpdateForm').form('reset');
                $('#dg').datagrid('reload');
                $('#category1').combobox('reload');
                $.messager.progress("close");
                $('#batchUpdateGiftPoolWin').window('close');
                $.messager.alert('温馨提示', result.msg);
            }

        },
        error : function() {
            // $.messager.progress("close");
            $.messager.alert('提示框','系统异常，联系开发人员');
            return false;
        }
    });
}

Java代码
contorller层

@PostMapping("/batchUpdateGiftPool")
    public Result batchUpdateGiftPool(HttpServletRequest request, ImportParams params, HttpServletResponse response) throws Exception {
        List<MallGiftPoolErrorVO>  list = giftPackService.executeBatchUpdateGiftPoolImport(request, params);
        if(CollectionUtils.isEmpty(list)){
            return Result.success(SuccessEnum.GLOBAL_UPLOAD_SUCCESS);
        }
        Map<String, String> head = new LinkedHashMap<>();
        head.put("skuId","商品编号");
        head.put("msg","说明");
        ExcelUtils.export("自选礼包商品池错误信息", response, list, head);
        return Result.error(ErrorEnum.GLOBAL_UPLOAD_ERROR);
    }

service层

 @Override
    public List<MallGiftPoolErrorVO> executeBatchUpdateGiftPoolImport(HttpServletRequest request, ImportParams params) throws Exception {
        String userId = request.getSession().getAttribute(Constants.GLOBAL_USER_ID_KEY).toString();
        Map<String, String> args = Maps.newLinkedHashMap();
        args.put("skuId", "商品名称");
        args.put("costPrice", "自选成本价");

        // 解析为对应更新数据
        List<MallGiftPoolDTO> giftList = parseFile2GiftPoolList(request, args);
        if (CollectionUtils.isEmpty(giftList)) {
            throw new MallException(ErrorEnum.SETTLE_EXCEL_INFO_ERROR);
        }
        log.info("导入的自选商品池数量:{}",giftList.size());
        //校验excel数据是否正确，组装数据
        List<MallGiftPoolErrorVO> errorVOList = checkGitePool(giftList, userId);
        log.info("导入的自选商品池错误数量:{}",errorVOList.size());
        if (CollectionUtils.isEmpty(errorVOList)) {
            List<String> skuIds = giftList.stream().map(MallGiftPoolDTO::getSkuId).collect(Collectors.toList());
            List<String> skuIdDbs = mallGiftPackDao.findGiftPoolSkuIds(skuIds);
            //修改的数据
            List<MallGiftPoolDTO> editGiftPool = giftList.stream().filter(item -> skuIdDbs.contains(item.getSkuId())
            ).collect(Collectors.toList());
            if (!CollectionUtils.isEmpty(editGiftPool)) {
                batchUpdateGiftPoolList(editGiftPool);
            }
            log.info("导入的自选商品池修改数量:{}",editGiftPool.size());
        }
        return errorVOList;
    }

private List<MallGiftPoolDTO> parseFile2GiftPoolList(HttpServletRequest request, Map<String, String> args) throws Exception {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
        MultipartFile tmp = fileMap.get("giftPoolImport");

        // 转换为临时的file文件,用于解析excel
        File file = File.createTempFile(String.valueOf(LocalDateTime.now().toEpochSecond(ZoneOffset.of("+8"))),
                tmp.getOriginalFilename().substring(tmp.getOriginalFilename().lastIndexOf(".")));
        tmp.transferTo(file);

        // 将拼装好的头部信息及要返回的class信息等传入,解析出可用的excel属性
        List<MallGiftPoolDTO> list = ImportExcelUtils.inputExcel(MallGiftPoolDTO.class, file, file.getName(), args);
//        log.info("解析上传后的礼包导入数据 >> {}", JSON.toJSONString(list));

        // 删除临时文件
        FileUtils.deleteFiles(file);

        return list;
    }

ImportExcelUtils中的方法

/**
     * 导入Excel文件
     *
     * @param cls      实体类 (需要符合JavaBean的要求，并且要有set方法)
     * @param file     File文件
     * @param fileName 文件名
     * @param args     实体类字段和对应中文名的字符串
     * @return
     * @throws Exception
     * @author Jwenk
     * @Time 2017-01-11
     */
    public static <T> List<T> inputExcel(Class cls, File file, String fileName, Map<String, String> args) throws Exception {
        List<T> list = new ArrayList<>();

        //根据Class文件创建一个对象
        Workbook wb = parseExcelFile(file, fileName);

        // 获取第一个Sheet表
        Sheet sheet;
        // 获取总表数
        int sheetCount = wb.getNumberOfSheets();
        // 获取总行数
        int rowCount;

        // 遍历所有的Sheet
        for (int n = 0; n < sheetCount; n++) {
            sheet = wb.getSheetAt(n);
            rowCount = sheet.getPhysicalNumberOfRows();
            for (int i = 1; i <= rowCount - 1; i++) {
                T object = (T) cls.newInstance();
                boolean b = parseExcel(sheet.getRow(i), object, args);
                if (b){
                    list.add(object);
                }
            }
        }
        return list;
    }

结束