【无标题】

最新推荐文章于 2024-07-05 14:56:47 发布

思无邪心飞扬

最新推荐文章于 2024-07-05 14:56:47 发布

阅读量102

点赞数 2

文章标签： java

本文链接：https://blog.csdn.net/weixin_44744941/article/details/139648170

版权

java操作excel

//    @PostMapping(value = "/importUserInfo")  入参： FileUploadModel fileUploadModel
    public ResultMessage importUserInfo() throws Exception{
        FileUploadModel fileUploadModel = new FileUploadModel();
        fileUploadModel.setFileBody(FileUtil.file2Str("C:\\Users\\Admin\\Desktop\\userInfo.xlsx"));
        if(StringUtils.isEmpty(fileUploadModel.getFileName()) || StringUtils.isEmpty(fileUploadModel.getFileBody())){
            return ResultMessage.getInstance(CodeEnum.PARAM_ERROR);
        }

        if(!fileUploadModel.getFileName().endsWith(ExcelUtils.XLS) && !fileUploadModel.getFileName().endsWith(ExcelUtils.XLSX)){
            return ResultMessage.getInstance(CodeEnum.PARAM_ERROR.getCode(), "仅支持xls或xlsx文件！");
        }

        byte[] bytes = transFile(fileUploadModel.getFileBody());
        if(bytes == null){
            return ResultMessage.getFailInstance("文件解析异常！");
        }
        InputStream inputStream = new ByteArrayInputStream(bytes);
        Workbook workbook = ExcelUtils.transformInputStreamToWorkbook(fileUploadModel.getFileName(), inputStream);
        if (workbook == null) {
            return ResultMessage.getFailInstance("EXCEL数据读取异常！");
        }
        List<JSONObject> list = ExcelUtils.getExcelData(workbook, null);
        if(list == null){
            return ResultMessage.getFailInstance("EXCEL数据读取异常！");
        }

        if(logger.isInfoEnabled()){
            log.info("excel读取数据结果为：{}行，结果：{}", list.size(), JSON.toJSONString(list));
        }

        int insertNum = 0;

        for(int i=0;i<list.size();i++){
            JSONObject object = list.get(i);
            try {
                UserInfo info = new UserInfo();
                info.setId(Integer.parseInt(object.getString("id")));
			    info.setUserName(object.getString("userName"));
                info.setPassword(object.getString("password"));
                info.setStatus(Integer.parseInt(object.getString("status")));
                userInfoMapper.insertUserInfo(info);
                insertNum ++;
            }catch (Exception e){
                log.error("error:",e);
            }
        }
        JSONObject res = new JSONObject();
        res.put("insertNum", insertNum);
        return ResultMessage.getSuccessInstance(res);
    }

	private byte[] transFile(String fileStr){
            byte[] b = Base64.decodeBase64(fileStr);
            for(int i=0;i<b.length;++i){
                if(b[i]<0){
                    b[i]+=256;
                }
            }
            return b;
    }