Convert excel format exception.You can try specifying the ‘excelType‘ yourself解决方案

jiazhongmin

已于 2024-03-27 13:56:45 修改

阅读量5k

点赞数 15

文章标签：数据库 excel java spring boot

于 2024-01-30 11:15:13 首次发布

本文链接：https://blog.csdn.net/weixin_42839973/article/details/135927131

版权

Convert excel format exception.You can try specifying the 'excelType' yourself报错解决方案

需求是需要上传文件到ftp上，生成工单文件夹，文件放在工单文件夹里边

这个需求需要在数据库中暂存二进制文件，因为工单生成在文件上传之后，同时也要解析动态excel,所以需要暂存数据库，上传完文件到FTP后删除。

错误代码如下：

 //上传并解析excel
        for (SelfServiceAnalysisFileBufferEntity selfServiceAnalysisFileBuffer : fileBufferList) {
            String itCode = UserUtils.getUserItCode();
            var date = Date.from(LocalDateTime.now().atZone(ZoneId.systemDefault()).toInstant());
            String ftpPath = DMMSFolderPrefixEnum.DMMS_SSA + "/" + SSAFolderTypeEnum.USER.getName() + "/" + workOrderId;
            //首先存入FileMapping表中
            SelfServiceAnalysisFileMappingEntity fileMapping = new SelfServiceAnalysisFileMappingEntity();
            fileMapping.setFileName(selfServiceAnalysisFileBuffer.getFileName());
            fileMapping.setFtpPath(ftpPath);
            SetValueUtils.setMethodCreateValVoid(fileMapping, itCode, date);
            selfServiceAnalysisFileMappingMapper.add(fileMapping);
            //处理fileId
            fileMapping.setFileId(SerialNumberUtils.serialNumberGenerate(fileMapping.getId()));
            selfServiceAnalysisFileMappingMapper.update(fileMapping);
            //如果pgsql存在说明需要解析上传到ftp中，如果不存在不需要重新解析上传ftp
            InputStream inputStream = new ByteArrayInputStream((byte[]) selfServiceAnalysisFileBuffer.getFileContent());
            //因为EasyExcel解析完excel之后流为空了 所以必须copy一份源流数据，不然上传的文件为空文件,然后 上传ftp,
            InputStream inputStream2 = new ByteArrayInputStream((byte[]) selfServiceAnalysisFileBuffer.getFileContent());
            try {
                ExcelReader excelReader = EasyExcel.read(inputStream).build();
                excelReader.read(EasyExcel.readSheet(0).registerReadListener(new EasyExcelSSAUserInputListener(selfServiceAnalysisFileInputTitleMapper, selfServiceAnalysisFileInputValueMapper, selfServiceAnalysisUserTableMappingMapper, selfServiceAnalysisUserInputOutputFieldMapper, ruleType, configInputOutputId, ssaRuleId, workOrderId, technicalField, fileMapping.getFileId(), itCode, date)).build());
            } catch (Exception e) {
                throw new BaseException("-999", e.getMessage());
            }
            FTPClientUtils.ftpUpload(ftpIp, ftpUserName, ftpPassword, ftpPath, selfServiceAnalysisFileBuffer.getFileName(), inputStream2);
            //删除pgsql中的源文件
            selfServiceAnalysisFileBufferMapper.delete(selfServiceAnalysisFileBuffer.getId());
        }

主要问题出在在文件解析之前已经上传FTP了

先上传FTP后，这块然后再去解析 inputStream后提示‘Convert excel format exception.You can try specifying the ‘excelType’ yourself’ 。

然后会搜索相关错误会让你指定excelType因为EasyExcel现在无法智能解析文件流,然后指定ExcelType 为 xlsx重新解析，就会提示‘The supplied file was empty (zero bytes long)’

这块证明inputStream为空了，因为已经上传FTP了

正确代码如下：

//上传并解析excel
        for (SelfServiceAnalysisFileBufferEntity selfServiceAnalysisFileBuffer : fileBufferList) {
            String itCode = UserUtils.getUserItCode();
            var date = Date.from(LocalDateTime.now().atZone(ZoneId.systemDefault()).toInstant());
            String ftpPath = DMMSFolderPrefixEnum.DMMS_SSA + "/" + SSAFolderTypeEnum.USER.getName() + "/" + workOrderId;
            //首先存入FileMapping表中
            SelfServiceAnalysisFileMappingEntity fileMapping = new SelfServiceAnalysisFileMappingEntity();
            fileMapping.setFileName(selfServiceAnalysisFileBuffer.getFileName());
            fileMapping.setFtpPath(ftpPath);
            SetValueUtils.setMethodCreateValVoid(fileMapping, itCode, date);
            selfServiceAnalysisFileMappingMapper.add(fileMapping);
            //处理fileId
            fileMapping.setFileId(SerialNumberUtils.serialNumberGenerate(fileMapping.getId()));
            selfServiceAnalysisFileMappingMapper.update(fileMapping);
            //如果pgsql存在说明需要解析上传到ftp中，如果不存在不需要重新解析上传ftp
            InputStream inputStream = new ByteArrayInputStream((byte[]) selfServiceAnalysisFileBuffer.getFileContent());
            try {
                ExcelReader excelReader = EasyExcel.read(inputStream).build();
                excelReader.read(EasyExcel.readSheet(0).registerReadListener(new EasyExcelSSAUserInputListener(selfServiceAnalysisFileInputTitleMapper, selfServiceAnalysisFileInputValueMapper, ssaRuleId, workOrderId, technicalField, fileMapping.getFileId(), itCode, date)).build());
            } catch (Exception e) {
                throw new BaseException("-999", e.getMessage());
            }
            FTPClientUtils.ftpUpload(ftpIp, ftpUserName, ftpPassword, ftpPath, selfServiceAnalysisFileBuffer.getFileName(), inputStream);
            //删除pgsql中的源文件
            selfServiceAnalysisFileBufferMapper.delete(selfServiceAnalysisFileBuffer.getId());
        }